Skip to content

Instantly share code, notes, and snippets.

Avatar

Scott Forstie forstie

143 followers · 0 following
  • IBM
View GitHub Profile
@forstie
forstie / PTF Cover Letters.sql
Last active April 28, 2023 09:15
The request... show how SQL can be used to narrow the field down to those PTFs that have special instructions, and only return the special instructions. The following example focuses on what an admin might do AFTER loading PTFs, but BEFORE applying them.
View PTF Cover Letters.sql
-- Subject: PTF Cover Letters
-- Author: Scott Forstie
-- Date : April, 2023
-- Features Used : This Gist uses qsys2.ptf_info, SQL Alias, ltrim, rtrim(), dynamic SQL, SQL PL, PIPE
--
-- Note:
-- 1) Whether PTF Cover Letters exist on your IBM i is up to whomever ordered the PTFs
-- Use the Copy PTF Cover Letter (CPYPTFCVR) command to bring in PTF cover letters
-- https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/cpyptfcvr.html
-- 2) The file QGPL/QAPZCOVER *FILE contains one member for each PTF Cover Letter
@forstie
forstie / db2_to_json.sql
Created April 14, 2023 03:55
The request... Is it possible to extract data from IBM i into JSON format with a Db2 service?
View db2_to_json.sql
--
-- Subject: The request... return SQL services detail using JSON.
-- Author: Scott Forstie
-- Date : April, 2023
-- Features Used : This Gist uses qsys2.syscolumns2, listagg(), rtrim(), dynamic SQL, SQL PL, PIPE
--
-- Note:
-- When someone asks you to return Db2 for i data "as JSON", they probably want you
-- to publish a JSON document, which contains good key names and of course, the data.
--
@forstie
forstie / Query Supervisor - Holding a job.sql
Created April 8, 2023 14:52
The request... show how Query Supervisor could be used to HOLD a job. The criteria for which situations merit a job being held are left to the reader. The example shows how QS could react to a long running query issued by an interactive user.
View Query Supervisor - Holding a job.sql
--
-- Subject: Query Supervisor - Holding a job
-- Author: Scott Forstie
-- Date : April, 2023
-- Features Used : This Gist uses qsys2.query_supervisor, qsys2.EXIT_PROGRAM_INFO, aliases, qsys2.qcmdexc, qsys2.ADD_QUERY_THRESHOLD, qsys2.REMOVE_QUERY_THRESHOLD, qsys2.joblog_info, and QSYS2.ACTIVE_JOB_INFO
--
-- Notes:
-- ===============================================
-- 1) Query Supervisor (QS) exists in IBM i 7.3 and higher
-- (SF99703 Level 24, SF99704 Level 13, SF99950 <any>)
@forstie
forstie / Decimal column checker upper.sql
Created March 24, 2023 21:26
The request was this... I want to see how close some internal identification columns are to maxing out their maximum value. For example a counter that’s defined as DECIMAL(7,0) has a high value of 9,995,000 would indicate that we need to intercede ASAP.
View Decimal column checker upper.sql
--
-- Subject: Decimal column checker upper
-- Author: Scott Forstie
-- Date : March, 2023
-- Features Used : This Gist uses qsys2.syscolumns2, qsys2.object_statistics, RPAD, COALESCE, and SQL PL
--
-- Notes:
-- ===============================================
-- 1) This example can help find those columns which rely upon ever increasing values
-- 2) The examples could be revised to study other numeric columns, or ever descending values
@forstie
forstie / Undocumented IBM i Services UDTFs.sql
Last active April 10, 2023 19:05
This request has come in many times: Provide a UDTF alternative to a an SQL View for some of the IBM i (SQL) Services. My response is that the UDTFs already exist, are sometimes not documented, and all times are OK for users to query directly.
View Undocumented IBM i Services UDTFs.sql
--
-- Subject: Undocumented IBM i Services UDTFs
-- Author: Scott Forstie
-- Date : March, 2023
-- Features Used : This Gist uses undocumented UDTFs for IBM i (SQL) Services
--
-- Notes:
-- ===============================================
-- 1) IBM i (SQL) Services frequently have SQL Views, which are documented here:
-- https://www.ibm.com/docs/en/i/7.5?topic=optimization-i-services
@forstie
forstie / Audit Journal Management.sql
Last active April 12, 2023 21:18
I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit
View Audit Journal Management.sql
--
-- Subject: Audit Journal Management
-- Author: Scott Forstie
-- Date : March, 2023
-- Features Used : This Gist uses qsys2.security_info, qsys2.journal_info, qsys2.object_statistics, qsys2.qcmdexc, CTEs, sysibmadm.env_sys_info, and SYSTOOLS.split
--
-- Notes:
-- ===============================================
-- 1) There are many configuration options to consider using when establishing the Audit Journal, this Gist uses SQL to examine some of the most important choices.
-- 2) Its important to have a retention strategy for audit journal - journal receivers
@forstie
forstie / Bringing ORDER to a VIEW.sql
Last active April 12, 2023 21:16
The request... change Db2 for i to allow ORDER BY on CREATE VIEW. Well, we aren't going to do that because its non-standard. This gist shows a path forward using the existing support.
View Bringing ORDER to a VIEW.sql
--
-- Subject: Bringing ORDER to a VIEW
-- Author: Scott Forstie
-- Date : February, 2023
-- Features Used : This Gist uses UDTFs, SQL DDL, PIPE, SQL PL, SQL global variables
--
-- Notes:
-- ===============================================
-- The SQL Standard dictates that the ORDER BY clause cannot be
-- includes in the CREATE VIEW defintion.
@forstie
forstie / Are programs in QRPLOBJ being used.sql
Created February 8, 2023 00:13
The request here was simple, are there active jobs that had objects in QRPLOBJ on the stack? The solution was a little tricky, because jobs can end in the middle of doing the analysis.
View Are programs in QRPLOBJ being used.sql
--
-- Subject: Are we running with *PGMs or *SRVPGMs that reside within QRPLOBJ?
-- Author: Scott Forstie
-- Date : February, 2023
-- Features Used : This Gist uses qsys2.stack_info, CTEs, PIPE, SQL PL
--
-- Notes:
-- ===============================================
-- 1) Programs and Serice Programs get moved to QRPLOBJ when they are (re)created with replace(*YES).
-- 2) It is not safe to delete *PGMs or *SRVPGMs from QRPLOBJ, if you base your decision upon locks.
@forstie
forstie / Defective PTF Currency.sql
Last active March 13, 2023 10:34
PTFs should help, not hurt. That's the credo, goal, and expectation. But... sometimes things go the wrong way. This gist shows how to use SQL to consume an IBM provided resource, compare what you have locally and most importantly, tell you if you are exposed to a known defective PTF. Please use this gist to gain skills with SQL, but more importa…
View Defective PTF Currency.sql
--
-- Subject: Is this IBM i at risk of a known defective PTF?
-- Author: Scott Forstie
-- Date : February, 2023
-- Features Used : This Gist uses qsys2.http_get, a defective PTF service from IBM, CTEs, sysibmadm.env_sys_info, string manipulation BIFs, SYSTOOLS.split
--
-- Notes:
-- ===============================================
-- 1) The data returned here is the same data you would find when using
-- Go QMGTOOLS/MG option 24 (PTF Menu) --> option 3 (Compare DEFECTIVE PTFs from IBM)
@forstie
forstie / Searching the IFS by name or date.sql
Last active January 14, 2023 08:34
The request was to provide an easy to use and customize approach for finding files within the IFS based upon generic names and including the date they were created.
View Searching the IFS by name or date.sql
-- With this style of SQL, you can search the IFS by file name, or by creation date, or both!
-- In fact, it is simple to search by any criteria you'd like to use.
--
-- Find files within the IFS where:
-- --> The name starts with "P"
-- --> The file suffix is ".txt"
-- --> The file was created on January 10, 2022
--