Skip to content

Instantly share code, notes, and snippets.

View forstie's full-sized avatar

Scott Forstie forstie

  • IBM
View GitHub Profile
@forstie
forstie / frca_info.sql
Created February 7, 2024 12:01
The path to achieving improved security on IBM i is sometimes a winding road. SQL can help to get you there on time. In this installment, FRCA is raised from obscurity to something understandable. #SQLcandoit,
--
-- Subject: HTTP Servers and FRCA
-- Author: Scott Forstie
-- Date : February, 2024
-- Features Used : This Gist uses SQL PL, qsys2.object_statistics, qsys2.syspartitionstat,
-- SQL aliases, SQL built-in functions, UDTF, qsys2.ifs_read, and pipe
--
-- HTTP Server configuration detail resides in the IFS.
-- The HTTP Server instance file (QUSRSYS/QATMHINSTC *FILE)
-- points to the IFS path to use for each specific server.
@forstie
forstie / coolstuff.read_joblog.sql
Last active March 18, 2024 12:10
Joblogs... important, but needlessly difficult to automate or consume. This gist takes on this topic....
--
-- Subject: Reading a joblog
-- Author: Scott Forstie
-- Date : January, 2024
-- Features Used : This Gist uses SQL PL, qsys2.joblog_info, sys2.history_log_info, QSYS2.SPOOLED_FILE_INFO,
-- systools.SYSTOOLS.SPOOLED_FILE_DATA, systools.ended_job_info, listagg
--
-- If you're like me, you've found it frustrating that a joblog is only a joblog while the job is active.
-- What happens when the joblog wraps or the job completes? That's up to whomever constructed the job,
-- but in many cases the answer is one or more spooled files exist with the joblog data.
@forstie
forstie / Reset environment variables in a job.sql
Created January 18, 2024 19:33
This gist comes from client requests to have a mechanism to "reset" environment variables within a job.
-- Subject: Reset environment variables in a job
-- Author: Scott Forstie
-- Date : January, 2024
-- Features Used : This Gist uses SQL PL, qsys2.ENVIRONMENT_VARIABLE_INFO, and qsys2.qcmdexc
--
--
-- When a job is started, it "inherits" all system level environment variables as job level environment variables.
-- From that point on, the job can add, change, and remove job level environment variables.
-- But... what should a job do if they want to reset or return to the initial state of environment variables?
@forstie
forstie / Collection Services made easy with SQL.sql
Last active October 4, 2023 15:01
The Collection Services (CS) config and CS data hold a goldmine of operational insight about the IBM i. This Gist shows how a little bit of SQL can open the door to gaining insight and value from this data.
--
-- =======================================================================
-- Subject: See Collection Services (CS) config and query CS data with SQL
-- =======================================================================
--
-- Author: Scott Forstie
-- Date : September, 2023
-- Email : forstie@us.ibm.com
--
-- This Gist is a two-fer:
@forstie
forstie / Reactive index strategy.sql
Last active August 26, 2023 01:40
Database and SQL performance tuning is a persistent focus, which many times leads into the indexing strategy. In this gist, I show how some of the existing tools can be tied together to achieve an automated "DBE in a box".
-- Subject: DBE in a box
-- Author: Scott Forstie
-- Date : August, 2023
-- Features Used : This Gist uses SQL PL, qsys2.SYSTEM_STATUS_INFO_basic, systools.lprintf, qsys2.sysixadv, qsys2.sysindexes, QSYS2.SYSINDEXSTAT, SYSTOOLS.ACT_ON_INDEX_ADVICE, QSYS2.RESET_TABLE_INDEX_STATISTICS and SYSTOOLS.REMOVE_INDEXES
--
-- Note:
-- 1) Indexes are not free, and care should be taken when deciding whether an index is worth creating
-- 2) Indexes are not free, and care should be taken to understand whether adequate system resources are available before creating additional indexes
-- 3) Indexes are not free, and care should be taken to establish a well trained Database Engineer (DBE) responsible for your IBM i partitions
--
@forstie
forstie / Using LICOPT and initAuto.sql
Last active July 2, 2023 05:48
The inspiration for this Gist came from a client. A piece of code had a long-standing problem where a variable was not initialized. Given the unpredictable nature of uninitialized made the topic hard to approach. This Gist shows how the IBM i Optimizing Translator can be used to find such problems within a dev or test environment.
-- Subject: Using initAuto to find uninitialized variable problems in ILE code
-- Author: Scott Forstie
-- Date : June, 2023
-- Features Used : This Gist uses SQL PL, INCLUDE, Change Program (CHGPGM) CL command, qsys2.joblog_info, qsys2.program_info, qsys2.qcmdexc, and QSYS2.BOUND_MODULE_INFO
--
-- A) Programmers use declared variables and structures to facilitate program logic.
-- B) Programmers should always initialize or assign declared variables and structures to contain known and expected values.
-- C) Programmers sometime miss step B.
--
-- This Gist shows how the IBM i Optimizing Translator can help.
@forstie
forstie / PTF Cover Letters.sql
Last active August 25, 2023 15:36
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.
-- 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
Last active December 27, 2023 19:27
The request... Is it possible to extract data from IBM i into JSON format with a Db2 service?
--
-- 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
Last active June 13, 2023 20:28
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.
--
-- 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.
--
-- 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