Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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…
-- =============================================================
-- Author: Scott Forstie
-- Date : September 8, 2019
-- Revised: August 28, 2020
--
-- 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 setup steps appear below
-- =============================================================
--
-- description: Review user connections (summary)
--
select client_userid, count(*) as client_user_count
from coolstuff.exit_tracker
group by client_userid
order by 2 desc;
stop;
--
-- description: Review application connections (summary)
--
select client_applname, count(*) as client_name_count
from coolstuff.exit_tracker
group by client_applname
order by 2 desc;
stop;
--
-- description: Review System i Navigator users (summary)
-- note: System i Navigator is beyond End of Support
-- reference: https://www.ibm.com/support/pages/ibm-i-access-windows
--
select client_applname, count(*) as dead_nav_count
from coolstuff.exit_tracker
where client_applname like '%System%'
group by client_applname
order by 2 desc;
stop;
--
-- description: Review System i Navigator users (summary)
-- note: System i Navigator is beyond End of Support
-- reference: https://www.ibm.com/support/pages/ibm-i-access-windows
--
select client_applname, client_userid, e.*
from coolstuff.exit_tracker e
where client_applname like '%System%'
order by client_userid, client_applname, when ;
stop;
--
-- description: What versions of ACS are being used?
-- Note: If no build information is shown, the version is 1.1.8.3 (or older)
--
select client_userid, client_programid
from coolstuff.exit_tracker
where client_programid like '%acsbundle.jar%'
group by client_userid, client_programid
order by client_userid, client_programid;
stop;
-- Full view of the data
select *
from coolstuff.exit_tracker
order by client_userid, when;
stop;
-- =============================================================
--
-- setup
--
-- =============================================================
create schema coolstuff;
create or replace table coolstuff.exit_tracker (
exit, when, job, jobd, sbs, client_applname, client_userid,
client_wrkstnname, client_programid, client_acctng, interface_name,
interface_type, interface_level) as
(select cast(NULL as varchar(30)), current timestamp as when, job_name,
job_description_library concat '/' concat job_description as jd,
subsystem_library_name concat '/' concat subsystem as sbs,
client_applname, client_userid, client_wrkstnname,
client_programid, client_acctng, interface_name, interface_type,
interface_level
from table (
qsys2.active_job_info(
job_name_filter => '*', detailed_info => 'ALL')
))
with no data
on replace delete rows;
grant all on table coolstuff.exit_tracker to public;
create or replace variable coolstuff.exit_name varchar(30) default 'QIBM_QZDA_SQL1';
grant read on variable coolstuff.exit_name to public;
--
-- description: ZDA exit... only does a call to coolstuff/logit1
--
cl:addlible qsysinc;
cl:crtsrcpf qtemp/qclsrc;
cl:addpfm file(qtemp/qclsrc) mbr(ZDAEXIT1);
insert into qtemp.qclsrc values
(1,010101,'PGM PARM(&FLAG &REQUEST) '),
(2,010101,'DCL VAR(&FLAG) TYPE(*CHAR) LEN(1) '),
(3,010101,'DCL VAR(&REQUEST) TYPE(*CHAR) LEN(285) '),
(4,010101,'runsql sql(''set coolstuff.exit_name = ''''QIBM_QZDA_SQL1'''' '') commit(*NONE) '),
(5,010101,'call coolstuff/logit1 '),
(6,010101,'ENDPGM ');
cl:CRTCLPGM PGM(COOLSTUFF/ZDAEXIT1) SRCFILE(QTEMP/QCLSRC);
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(LOGIT1);
insert into qtemp.qcsrc values
(1,010101,'#include <stddef.h> '),
(2,010101,'#include <stdio.h> '),
(3,010101,'#include <stdlib.h> '),
(4,010101,'static int onetime = 0; '),
(5,010101,'int maxtries = 10; '),
(6,010101,'int tries = 0; '),
(7,010101,'int main(int argc, char **argv) '),
(8,010101,'{ '),
(9,010101,'EXEC SQL INCLUDE SQLCA; '),
(10,010101,'EXEC SQL INCLUDE SQLDA; '),
(11,010101,'EXEC SQL BEGIN DECLARE SECTION; '),
(12,010101,'char tempuser[50]; '),
(13,010101,'int userlen; '),
(14,010101,'EXEC SQL END DECLARE SECTION; '),
(15,010101,'if (onetime == 0 & tries < maxtries) { '),
(16,010101,' tries++; '),
(17,010101,' memset(tempuser, ''\x00'', sizeof(tempuser)); '),
(18,010101,' exec sql values current client_userid into :tempuser; '),
(19,010101,' userlen = strlen(tempuser); '),
(20,010101,' if (userlen > 0) { '),
(21,010101,' onetime = 1; '),
(22,010101,' exec sql insert into coolstuff.exit_tracker '),
(23,010101,' select coolstuff.exit_name, '),
(24,010101,' current timestamp, job_name, '),
(25,010101,' job_description_library concat ''/'' '),
(26,010101,' concat job_description, subsystem_library_name '),
(27,010101,' concat ''/'' concat subsystem, client_applname, '),
(28,010101,' client_userid, client_wrkstnname, '),
(29,010101,' client_programid, client_acctng, '),
(30,010101,' interface_name, interface_type, interface_level '),
(31,010101,' from table(qsys2.active_job_info( '),
(32,010101,' job_name_filter => ''*'', '),
(33,010101,' detailed_info => ''ALL'') '),
(34,010101,' ); '),
(35,010101,'}}} ');
cl:CRTSQLCI OBJ(COOLSTUFF/LOGIT1) SRCFILE(QTEMP/QCSRC) COMMIT(*NONE) OUTPUT(*PRINT) USRPRF(*OWNER) DYNUSRPRF(*OWNER);
cl:CRTPGM PGM(COOLSTUFF/LOGIT1) MODULE(COOLSTUFF/LOGIT1) ACTGRP(*CALLER) ALWRINZ(*YES);
cl: GRTOBJAUT OBJ(COOLSTUFF/LOGIT1) OBJTYPE(*PGM) USER(*PUBLIC);
cl: GRTOBJAUT OBJ(COOLSTUFF/ZDAEXIT1) OBJTYPE(*PGM) USER(*PUBLIC);
cl: GRTOBJAUT OBJ(COOLSTUFF) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*USE);
cl: ADDEXITPGM EXITPNT(QIBM_QZDA_SQL1) FORMAT(ZDAQ0100) PGMNBR(1) PGM(COOLSTUFF/ZDAEXIT1) TEXT('Log usage exit pgm');
stop;
-- To remove:
-- cl: RMVEXITPGM EXITPNT(QIBM_QZDA_SQL1) FORMAT(ZDAQ0100) PGMNBR(1);
-- cl: ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED);
-- cl: strPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT);
-- DROP TABLE coolstuff.exit_tracker;
-- DROP VARIABLE variable coolstuff.exit_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment