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 / 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;
@forstie
forstie / Display Software Resources
Created Sep 28, 2019
The Display Software Resources (DSPSFWRSC) command allows you to show, print, or write to an output file the list of installed software resources. This SQL example shows how to externalize the same detail by extracting message text and transforming numerics into integer form.
View Display Software Resources
--
-- category: Software Resources
-- description: DSPSFWRSC for SQL users
--
create or replace function coolstuff.whatsinstalled ()
returns table (
product varchar(7) ccsid 37, load integer, option integer,
software_text varchar(132) ccsid 37
)
external action
@forstie
forstie / Glengarry Glen Ross - SQL Style
Created Sep 24, 2019
Ok movie buffs, here is a fictional before and after, with a happy ending. Use these techniques to bring database engineering to bear in your data center.
View Glengarry Glen Ross - SQL Style
--
-- Existing file... not very user friendly
--
create schema gggr;
create table gggr.sales (
CL varchar(100),
PTY varchar(100),
SP varchar(30),
PG char(1)
);
@forstie
forstie / 3-part naming for UDTFs
Created Sep 18, 2019
For User Defined Table Functions (UDTFs), a trick needs to be employed. Include a where clause whose only purpose is to push the UDTF invocation to the remote database!
View 3-part naming for UDTFs
--
-- Traditional 3-part name SQL would reference a table, view, or procedure
--
call otherRDB.schema.procedure123();
stop;
insert into localschema.fact_table
select * from otherRDB.remoteschema.fact_table;
stop;
@forstie
forstie / ZDA mystery solved
Last active Nov 5, 2019
This example shows several things worthy of attention. System managers can utilize exit program to establish improved auditing, understanding, and real time business rules using SQL. For QZDASOINIT jobs, it can be easily considered an unsolvable mystery. With the help of Db2 for i Client Special Registers, we can understand a great deal about ZD…
View ZDA mystery solved
-- =============================================================
-- Author: Scott Forstie
-- Date : September 8, 2019
-- Description: Have you ever wondered what's driving all
-- those QZDASxINIT jobs?
-- This example shows how to establish an
-- exit program to capture client special register
-- and other detail for ZDA connections.
--
-- Note: The source and setp steps appear below
@forstie
forstie / dynamic VALUES INTO
Created Aug 29, 2019
This example shows how to use dynamic SQL (PREPARE and EXECUTE) to implement a VALUES INTO statement.
View dynamic VALUES INTO
create procedure qgpl.values_into (out pout integer)
begin
declare values_into_stmt varchar(1000) ccsid 37;
set values_into_stmt = 'values 1+2+3 into ?';
prepare values_into_query from values_into_stmt;
execute values_into_query using pout;
end;
call qgpl.values_into(?);
-- Note that 6 is returned...
You can’t perform that action at this time.