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 / 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,
@forstie
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;
You can’t perform that action at this time.