forstie
/ ALLOBJ users with default passwords.sql
Created Nov 16, 2019
Security implementations can and should be monitored closely and on a regular cadence. This is one example where SQL can be used instead of the Analyze Default Passwords (ANZDFTPWD) command.
View ALLOBJ users with default passwords.sql
| -- | |
| -- Note, this example might take a while to run because its doing an exhaustive evaluation of which | |
| -- users have *ALLOBJ special authority, either directly in their profile or indirectly via | |
| -- group profile membership. | |
| -- | |
| -- Next, those "super" users are evaluated to determine which (if any) of them have their password | |
| -- set to match their user profile. (aka using a default password) | |
| -- | |
| -- If this returns zero rows... good! | |
| -- |
forstie
/ Temporalize a library.sql
Created Nov 8, 2019
System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.
View Temporalize a library.sql
| -- | |
| -- | |
| -- description: find database files and deploy Temporal over them | |
| -- note: The history table will be named <existing-table-name>_HISTORY | |
| -- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i | |
| -- minvrm: V7R3M0 | |
| -- | |
| CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10)) | |
| BEGIN |
forstie
/ journal receivers attached or not.sql
Created Oct 23, 2019
I was asked to provide a way to find journal receivers and discern whether or not they are attached or detached.
View journal receivers attached or not.sql
| -- | |
| -- description: Which journal receivers are currently attached? | |
| -- | |
| select attached_journal_receiver_library, attached_journal_receiver_name | |
| from qsys2.journal_info | |
| where journal_library = 'PRODLIB' | |
| order by 1,2; | |
forstie
/ Counting objects in a library.sql
Created Oct 22, 2019
There's frequently more than one way to code a solution. Today I was asked how to use SQL to count the number of objects within a library. There are two examples... one is very fast, but requires IBM i 7.4.
View Counting objects in a library.sql
| -- | |
| -- description: Count objects in a library (superfast) | |
| -- minvrm: v7r4m0 | |
| -- | |
| with libs (ln) as ( | |
| select objname | |
| from table ( | |
| qsys2.object_statistics('*ALLSIMPLE', 'LIB') | |
| ) | |
| ) |
forstie
/ SQL generated table comparison query
Last active Nov 5, 2019
SQL, LISTAGG(), QSYS2.SYSCOLUMNS2, and IS NOT DISTINCT all team up here to generate a table level comparision query.
View SQL generated table comparison query
| -- | |
| -- Imagine that you have 2 versions of the same table. | |
| -- The tables have the same format. | |
| -- This example provides an SQL function that generates a table compare query. | |
| -- | |
| create schema coolstuff; | |
| create table coolstuff.table_master as (select * from qsys2.syslimtbl limit 100) with data; | |
| create table coolstuff.table_secondary as (select * from qsys2.syslimtbl limit 100) with data; | |
| select * from coolstuff.table_secondary; | |
| update coolstuff.table_secondary set limit_category = 555 limit 10; |
forstie
/ Numbify Packed Decimals using SQL
Last active Nov 5, 2019
SQL scalar functions can transform data into information.
View Numbify Packed Decimals using SQL
| -- | |
| -- | |
| -- Description: Convert packed decimal numbers into decimals | |
| -- | |
| -- | |
| cl:addlible qsysinc; | |
| cl:clrlib qtemp; | |
| cl:crtsrcpf qtemp/qcsrc; | |
| cl:addpfm file(qtemp/qcsrc) mbr(NIB); | |
| insert into qtemp.qcsrc values |
forstie
/ Mask birthdays with RCAC
Created Oct 14, 2019
This example shows how to use a Column Mask to prevent users from seeing the actual birthdate.
View Mask birthdays with RCAC
| -- | |
| -- RCAC Book: www.redbooks.ibm.com/redpieces/abstracts/redp5110.html | |
| -- | |
| -- | |
| -- Who is allowed to deploy COLUMN MASKs and ROW PERMISSIONs | |
| -- | |
| cl:CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(SCOTTF) USAGE(*ALLOWED); | |
| call qsys.create_sql_sample('COFFEEBEAN'); |
forstie
/ Optimize journaled database files
Created Oct 12, 2019
Subtle options in how objects are journaled can have a big impact on performance.
View Optimize journaled database files
| -- | |
| -- | |
| -- Find journaled database files that can be adjusted for improved performance | |
| -- | |
| -- Resources: | |
| -- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm | |
| -- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf | |
| -- | |
| select 'TOYSTORE', objname as file, omit_journal_entry, journal_images, objtype, objowner, | |
| objdefiner, objcreated, objsize, objtext, objlongname, last_used_timestamp, journaled, |
forstie
/ Optimize local journals
Created Oct 12, 2019
View Optimize local journals
| -- | |
| -- Search for journals that can be easily improved | |
| -- | |
| -- Resources: | |
| -- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqviewjournalinfo.htm | |
| -- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf | |
| -- | |
| select journal_library, journal_name, receiver_maximum_size, remove_internal_entries, asp_number, | |
| journal_aspgrp, attached_journal_receiver_name, attached_journal_receiver_library, | |
| message_queue, message_queue_library, delete_receiver_option, delete_receiver_delay, |
forstie
/ SQL Environmental Limits
Created Oct 12, 2019
Review the top consumers of SQL resources since the last IPL.
View SQL Environmental Limits
| -- Resource: | |
| -- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqhealthenvlimits.htm | |
| -- | |
| DECLARE GLOBAL TEMPORARY TABLE Health_Environmental_Limits | |
| LIKE QSYS2.QSQHENVLIM | |
| WITH REPLACE | |
| NOT LOGGED ON ROLLBACK PRESERVE ROWS | |
| ON COMMIT PRESERVE ROWS; |
NewerOlder