Scott Forstie forstie
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.
This file contains 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
| -- 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.
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.
This file contains 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
| -- | |
| -- 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.
This file contains 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
| -- | |
| -- 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 - robert.andrews@us.ibm.com | |
| -- | |
| 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...
This file contains 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
| -- | |
| -- 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.
This file contains 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
| -- 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 |
forstie
/ controlling adopted authority.sql
Last active
August 17, 2023 15:36
If you use adopted authority, how do you avoid allowing code that you call from taking a free ride on your elevated authority? One answer lies within this gist...
forstie
/ Interactive Users and usage.sql
Created
December 7, 2020 17:36
This gist shows a glimpse into the realm of what's possible when using SQL and IBM i Services to monitor and manage interactive users.
This file contains 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
| -- | |
| -- description: What are ALL my interactive users doing right now? (summary) | |
| -- ==== | |
| -- | |
| select j.authorization_name as user_name, u.text_description as description, | |
| count(*) as job_count | |
| from qsys2.user_info as u | |
| join table ( | |
| qsys2.active_job_info( | |
| subsystem_list_filter => 'QINTER', detailed_info => 'ALL') |
forstie
/ Managing MSGW jobs.sql
Created
December 4, 2020 18:03
For this gist, I was asked to provide a query that would find jobs that have been stuck on Message Wait (MSGW) status for > 90 minutes. There's a 3 part progression to reach the solution.
This file contains 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
| -- | |
| -- Find active jobs (in QBATCH) that are in message wait status | |
| -- | |
| with msgw_jobs (jn) as ( | |
| select job_name | |
| from table ( | |
| qsys2.active_job_info(subsystem_list_filter => 'QBATCH') | |
| ) x | |
| where job_status = 'MSGW' | |
| ) |
forstie
/ Well isnt that special.sql
Last active
December 3, 2020 18:49
I was asked to provide a technique for the SQL user to access the special authorities granted to user and group profiles, and return the data in a non-list form for ease of reporting and analysis. To accomplish this request, I used the SYSTOOLS.SPLIT table function, but had to be careful to use the perfect split character (3 spaces), trim off bl…
This file contains 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
| -- | |
| -- Complete review of special authorities granted to user profiles | |
| -- | |
| select user_name, ordinal_position, ltrim(element) as special_authority | |
| from qsys2.user_info, table ( | |
| systools.split(rtrim(special_authorities), ' ') | |
| ) b | |
| where user_name not in (select authorization_name | |
| from qsys2.authids | |
| where authorization_attr = 'GROUP'); |