Skip to content

Instantly share code, notes, and snippets.

Scott Forstie forstie

View GitHub Profile
@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 / 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 / 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...
@forstie
forstie / ROUND vs QUANTIZE
Created Aug 18, 2019
This example shows that QUANTIZE can be used along with decfloat rounding mode to achieve programmer control of rounding numeric values.
View ROUND vs QUANTIZE
values current decfloat rounding mode;
set current decfloat rounding mode= round_half_even;
stop;
-- ===============================================================================================================
--
-- QUANTIZE uses the decfloat rounding mode.
-- This is the default: ROUND_HALF_EVEN
--
-- Round to nearest value; if values are equidistant, round so that the final digit is even.
-- If the discarded digits represent greater than half (0.5) of the value of a number in the next left position,
You can’t perform that action at this time.