Scott Forstie forstie
forstie
/ Reactive index strategy.sql
Last active
July 29, 2024 19:32
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".
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 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
/ 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 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
/ 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- 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
/ db2_to_json.sql
Last active
May 2, 2024 07:42
The request... Is it possible to extract data from IBM i into JSON format with a Db2 service?
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ Query Supervisor - Holding a job.sql
Last active
November 8, 2024 22:18
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ Audit Journal Management.sql
Last active
May 2, 2024 07:53
I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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. |