Skip to content

Instantly share code, notes, and snippets.

Avatar

Scott Forstie forstie

  • IBM
View GitHub Profile
@forstie
forstie / Send row changes to a data queue using JSON.sql
Last active Dec 1, 2022
This example shows how easy it is to pull together a data streaming service using SQL, JSON, triggers, and a Data Queue.
View Send row changes to a data queue using JSON.sql
x----------------------------------------------------------------------------------------------------
--
-- Title: Demonstrate how to capture changes being made to a table, publish the changes using
-- JSON publishing functions, and send the detail to a data queue
-- Author: Scott Forstie
-- Date: June, 2020
--
--
----------------------------------------------------------------------------------------------------
@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 / Employment Days.sql
Last active Nov 18, 2022
I was asked how SQL could compute the number of days difference between two dates columns, where one column might contain NULL. The timestamp_format and timestampdiff built-in functions get the job done, with a little help from coalesce.
View Employment Days.sql
create table toystore.employee_deetz (
Company_Id bigint,
Employee_Id bigint,
FirstName varchar(100) for sbcs data,
SurName varchar(100) for sbcs data,
Employed_Start_Date date,
Employed_End_Date date);
insert into toystore.employee_deetz values
(101, 10001, 'Scott', 'Forstie', '07/11/1989', NULL);
@forstie
forstie / QBATCH job study.sql
Created May 30, 2021
I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.
View QBATCH job study.sql
--
-- Subject: QBATCH Job Analysis
-- Author: Scott Forstie
-- Date : May 30, 2021
-- Note : This Gist leverages different built-in functions and grouping support to explore job history
--
-- ================================================================================================
--
-- description: 20 Jobs that ran (started and ended) the longest in QBATCH over the last 24 hours
@forstie
forstie / printf to the Joblog using SQL.sql
Last active Oct 20, 2022
printf to the Joblog using SQL
View printf to the Joblog using SQL.sql
--
-- description: printf to the Joblog, using SQL
--
cl:addlible qsysinc;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(LPRINTF);
insert into qtemp.qcsrc values
(1,010101,'{'),
(2,010101,'extern int Qp0zLprintf (char *format, ...);'),
@forstie
forstie / Generating spreadsheets with SQL.sql
Last active Oct 19, 2022
In this working example, we establish an SQL table which contains the spreadsheets we'd like to have generated. Each row in the table includes the query that will supply the data for the spreadsheet, and the location in the IFS where the spreadsheet should reside. Further, a different procedure emails the spreadsheets to an interested consumer. …
View Generating spreadsheets with SQL.sql
-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date : January 10, 2020
-- =================================================================
--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:
@forstie
forstie / Remove *IOSYSCFG from users and groups
Last active Oct 3, 2022
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 / Object ownership by user - total report.sql
Last active Oct 1, 2022
This gist combines several IBM i (SQL) Services to produce an easy to consume UDTF. Pass in a user name and you'll get back all the QSYS and IFS objects they own, ordered by size descending.
View Object ownership by user - total report.sql
create or replace function systools.objects_owned(
user_name varchar(10) FOR SBCS DATA)
RETURNS TABLE (
object DBCLOB(16M) CCSID 1200,
object_type varchar(10) for sbcs data,
size bigint,
sizeform varchar(50) FOR SBCS DATA,
created timestamp,
changed timestamp,
@forstie
forstie / How to discern where to find todays Collection Services info.sql
Created Aug 5, 2020
If you want to automate analysis of Collection Services, you can use this approach to use SQL to discern the library in use by Collection Services (CS) and the member name in the CS files that corresponds to the current day.
View How to discern where to find todays Collection Services info.sql
begin
execute immediate 'create or replace variable qpfrdata.current_CS_library varchar(10) for sbcs data';
execute immediate 'create or replace variable qpfrdata.current_CS_member varchar(10) for sbcs data';
call qsys2.qcmdexc('QSYS/CHKPFRCOL');
set (qpfrdata.current_CS_library,qpfrdata.current_CS_member) =
(select rtrim(substr(message_tokens,1,10)) as cs_lib, rtrim(substr(message_tokens,11,10)) as cs_mbr
from table(qsys2.joblog_info('*')) where message_id = 'CPI0A16' order by ordinal_position desc limit 1);
@forstie
forstie / AUINTERNALS made easy with SQL.sql
Created Jun 30, 2022
The request... find an alternative to STRSST for monitoring AUINTERNALS security limits
View AUINTERNALS made easy with SQL.sql
--
-- Subject: The request... find an alternative to STRSST for monitoring security limits
-- Author: Scott Forstie
-- Date : June, 2022
-- Features Used : This Gist uses qsys2.user_storage
--
-- Background: We need to see user profiles trending and approaching limits!
-- Some important system limits information is only found within the bowels of the
-- operating system and accessed via Start System Service Tools (STRSST).
--