Skip to content

Instantly share code, notes, and snippets.

Scott Forstie forstie

Block or report user

Report or block forstie

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@forstie
forstie / SWAP_DYNUSRPRF.sql
Created Nov 24, 2019
With the latest Db2 PTF Groups for IBM i 7.3 and 7.4, you can now identify and fix those *PGM and *SRVPGM's that use SQL and were built incorrectly. This example finds those cases where *OWNER will be used for static SQL, but *USER will be used for dynamic SQL. The procedure swaps the dynamic user profile setting to *OWNER. This utility approach…
View SWAP_DYNUSRPRF.sql
--
-- description: Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select user_profile, dynamic_user_profile, program_schema, program_name, program_type,
module_name, program_owner, program_creator, creation_timestamp, default_schema,
"ISOLATION", concurrentaccessresolution, number_statements, program_used_size,
number_compressions, statement_contention_count, original_source_file,
original_source_file_ccsid, routine_type, routine_body, function_origin,
function_type, number_external_routines, extended_indicator, c_nul_required,
naming, target_release, earliest_possible_release, rdb, consistency_token,
@forstie
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
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
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
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
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
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
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
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,
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,
You can’t perform that action at this time.