forstie’s gists

forstie / QBATCH job study.sql

Created May 30, 2021 17:10

I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.

	--
	-- 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 / Employment Days.sql

Last active November 18, 2022 17:40

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.

	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 / prtprvaut.sql

Created May 14, 2021 14:02

Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit

	--
	-- If you're not ready to use IFS_OBJECT_PRIVILEGES, this might help you...
	-- Note: A special thanks to Sue Romano for her help with this Gist
	--

	-- https://www.ibm.com/docs/en/i/7.4?topic=services-ifs-object-privileges-table-function
	cl:PRTPVTAUT OBJTYPE(STMF) DIR('/') SCHSUBDIR(YES);

	--
	-- create an alias over the most recent execution of PRTPRVAUT

forstie / QAPMJOBL.sql

Created May 13, 2021 13:47

I was asked how SQL could transform Collection Services data, in this case Job Performance Data, into a more consumable form. SQL built-in functions and CASE expressions get the job done.

	--
	-- Collection Services data file: QAPMJOBL (JOB PERFORMANCE DATA)
	--
	-- Resource:
	-- https://www.ibm.com/docs/en/i/7.4?topic=data-collection-services-files-qapmjobs-qapmjobl
	--

	--
	-- Raw
	--

forstie / SQL DDL with nc.sql

Created April 26, 2021 11:41

SQL DML includes the WITH NC clause to avoid having the data change participate in the transaction. SQL DDL does not include the WITH NC clause, but the savvy SQL user can leverage an AUTONOMOUS procedure to achieve the same behavior.

	-- Assumption... the connection is setup to use COMMIT(*CHG)

	-- =========================================================================
	--
	-- By default, SQL Data Definition Language (DDL) cannot use WITH NC
	--
	-- =========================================================================

	CREATE TABLE QTEMP.T035 (FLD1 CHARACTER (9) CCSID 37 NOT NULL DEFAULT '',
	FLD2 CHARACTER (30) CCSID 37 NOT NULL DEFAULT '',

forstie / NetServer Shares and IFS path availability.sql

Created April 21, 2021 14:52

I was asked to show how SQL could be used to identify when IBM i NetServer is sharing IFS paths, but some of those paths are unavailable.

	--
	-- How do you determine whether IFS paths being shared via IBM� i NetServer are available or unavailable?
	-- ======================================================================================================
	--
	--
	-- IBM� i NetServer shares - Unavailable IFS share detail
	--
	select SERVER_SHARE_NAME, PATH_NAME, 'Unavailable' as Share_availability, TEXT_DESCRIPTION
	from qsys2.server_share_info
	where share_type = 'FILE' and

forstie / Row level auditing.sql

Last active March 4, 2022 15:45

I was asked how to incorporate row level auditing detail into tables. While Temporal tables with Generated columns is a powerful combination, the following example demonstrates a different approach.

	--
	-- I was asked how to incorporate row level auditing detail into tables.
	-- While Temporal tables with Generated columns is a powerful combination,
	-- the following example demonstrates a different approach.
	--
	-- One fun aspect about the solution is the use of INCLUDE SQL on the triggers...
	--
	--------------------------------------------------------------------------------------------------

	--------------------------------------------------------------------------------------------------

forstie / Protect the IFS root for *PUBLIC

Created February 8, 2021 19:48

When an IFS directory includes W (write), you are exposed to malware attacks. Use this to review and overcome this topic for the all important ROOT directory.

	--
	-- When an IFS directory includes W (write), you are exposed to malware attacks
	-- Use this to review and overcome this topic for the all important ROOT directory
	--
	-- For help on this or related security topics, contact Robert and team...
	-- http://ibm.biz/IBMiSecurity
	-- Robert Andrews - [email protected]
	--
	stop;

forstie / Gist look at the library list

Created January 29, 2021 15:53

I was asked how object_statistics could be used with *LIBL and *USRLIBL to produce accurate and ordered results. Gist look at this...

	--
	-- Use NAMING(*SYS) - system naming mode to leverage the power of the library list
	--
	cl: crtlib lib1;
	cl: crtlib lib3;
	cl: crtlib lib2;
	cl:CRTDUPOBJ OBJ(QSQPTABL) FROMLIB(QSYS2) OBJTYPE(FILE) TOLIB(LIB1) DATA(YES);
	cl:CRTDUPOBJ OBJ(QSQPTABL) FROMLIB(QSYS2) OBJTYPE(FILE) TOLIB(LIB2) DATA(YES);
	cl:CRTDUPOBJ OBJ(QSQPTABL) FROMLIB(QSYS2) OBJTYPE(FILE) TOLIB(LIB3) DATA(YES);
	cl: addlible lib1;

forstie / auditing a job queue

Last active January 8, 2021 22:49

I was asked, "how can you detemine which user held a job queue"? While there is more than one approach to answering this question, here's an example that leverages the secure audit journal log.

	-- To be able to audit holding of a job queue, you need to:
	-- 1) Enable object auditing
	-- 2) Configure object auditing for specific job queues
	cl: CHGSYSVAL SYSVAL(QAUDCTL) VALUE('AUDLVL OBJAUD *NOQTEMP');
	cl: CHGOBJAUD OBJ(QGPL/KIDDIEJOBQ) OBJTYPE(JOBQ) OBJAUD(CHANGE);
	stop;

	--
	-- T-ZC audit journal entry:
	-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzarl/rzarlf77.htm

Scott Forstie forstie