forstie
/ change command default.sql
Created Jun 4, 2021
Before an upgrade, rollswap, or just for good hygiene, its good to know which CL commands have had their command defaults changed. Here's an approach that works all the way back to IBM i 7.2.
View change command default.sql
| -- | |
| -- Which CL commands have had their parameter defaults changed? | |
| -- (On IBM i 7.3 and higher) | |
| -- | |
| with libs (lib) as ( | |
| select objname | |
| from table ( | |
| qsys2.OBJECT_STATISTICS('*ALLAVL', '*LIB') | |
| ) | |
| ) |
forstie
/ QBATCH job study.sql
Created May 30, 2021
I was asked to show how SQL could be used to analyze QBATCH subsystem job history. Super grouping to the rescue.
View QBATCH job study.sql
| -- | |
| -- 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
Created May 18, 2021
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.
View Employment Days.sql
| 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
Someone sent me an "SQL Challenge". Challenge accepted! #SQLcandoit
View prtprvaut.sql
| -- | |
| -- 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
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.
View QAPMJOBL.sql
| -- | |
| -- 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 Apr 26, 2021
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.
View SQL DDL with nc.sql
| -- 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 Apr 21, 2021
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 Apr 16, 2021
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.
View Row level auditing.sql
| -- | |
| -- 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 Feb 8, 2021
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.
View Protect the IFS root for *PUBLIC
| -- | |
| -- 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 Jan 29, 2021
I was asked how object_statistics could be used with *LIBL and *USRLIBL to produce accurate and ordered results. Gist look at this...
View Gist look at the library list
| -- | |
| -- 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; |
NewerOlder