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 / ZDA mystery solved
Last active Sep 9, 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,
@forstie
forstie / JSON_TABLE and survival tips for shredding JSON with SQL
Created Aug 1, 2019
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
View JSON_TABLE and survival tips for shredding JSON with SQL
-- This fails to return data....why?
SELECT cusip, issueDate, bidToCoverRatio
FROM JSON_TABLE(
SYSTOOLS.HTTPGETCLOB('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null),
'$.root[*]'
COLUMNS(cusip VARCHAR(10) PATH '$.cusip',
issueDate Timestamp PATH '$.issueDate',
bidToCoverRatio double PATH '$.bidToCoverRatio')
) AS X;
stop;
@forstie
forstie / Remove *IOSYSCFG from users and groups
Created Jul 28, 2019
This example shows the power of the REGEXP_REPLACE built-in function, when combined with IBM i Services for security and dynamic SQL.
View Remove *IOSYSCFG from users and groups
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date : July 28, 2019
--
-- Subtract '*IOSYSCFG from all users by producing the CHGUSRPRF command
-- necessary to get the job done
--
with iosyscfg_users (user_name) as (
select authorization_name
from qsys2.user_info
@forstie
forstie / Dates and TIMESTAMP_FORMAT
Last active Jul 22, 2019
Formatting date data into true date and time date types
View Dates and TIMESTAMP_FORMAT
-- Author: Scott Forstie
-- Email: forstie@us.ibm.com
create or replace variable coolstuff.decdate dec(6,0);
set coolstuff.decdate = '190718';
-- July 18, 2019 (yes, really!)
values timestamp_format(varchar(coolstuff.decdate), 'YYMMDD');
-- Wow
-- Yowza
@forstie
forstie / User profile ownership and basic authorities.sql
Last active Jul 9, 2019
The first query identifies those users who are lacking authority to use their own *USRPRF. This lack of authority can cause annoying failures in software products. The other queries are used to review whether the *USRPRF ownership implementation matches the strategy.
View User profile ownership and basic authorities.sql
--
-- description: Which users lack basic authority to their own user profile?
--
select *
from qsys2.object_privileges
where object_type = '*USRPRF'
and object_name = authorization_name
and (object_operational <> 'YES'
or object_management <> 'YES'
@forstie
forstie / Revieve details for active 5250 sessions.sql
Last active Jun 30, 2019
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
View Revieve details for active 5250 sessions.sql
--
-- description: Find active Telnet or Interactive 5250 sessions
--
-- resource: ibm.biz/WellDefinedIBMiPorts
--
select remote_address, remote_port, authorization_name as user_name, job_name
from qsys2.netstat_job_info n
where local_port in (23,992) and job_type = 'INTERACTIVE';
--
@forstie
forstie / GO SAVE Option 21 history via SQL.sql
Created Jun 28, 2019
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history.
View GO SAVE Option 21 history via SQL.sql
-- Author: Scott Forstie
-- Date : June 28, 2019
-- Email : forstie@us.ibm.com
--
-- ==============
-- Basic Version
-- ==============
--
--
@forstie
forstie / Change trigger programs in production.sql
Created Jun 26, 2019
This example shows how to use the ALLOW_DDL_CHANGES_WHILE_OPEN QAQQINI option within a specific job to avoid the requirement of needing an exlusive lock for the *FILE object, before making a change to a trigger program.
View Change trigger programs in production.sql
--
-- Create, Replace, Delete, or Disable trigger programs
-- without acquiring an exclusive lock over the file
-- =======
--
-- Resource: ibmsystemsmag.com/blogs/i-can/august-2017/manage-trigger-programs-in-productions
-- Prepare to break the rules
call qsys2.override_qaqqini(1, '', '');
-- Rules broken
You can’t perform that action at this time.