forstie
/ Row level auditing.sql
Last active Feb 12, 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; |
forstie
/ auditing a job queue
Last active Jan 8, 2021
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.
View auditing a job queue
| -- 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 Dec 10, 2020
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 Dec 7, 2020
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.
View Interactive Users and usage.sql
| -- | |
| -- 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 Dec 4, 2020
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.
View Managing MSGW jobs.sql
| -- | |
| -- 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 Dec 3, 2020
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…
View Well isnt that special.sql
| -- | |
| -- 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'); |
forstie
/ Library sizes and more.sql
Created Nov 27, 2020
With Db2 PTF Group SF99703 level 22 and Db2 PTF Group SF99704 level 10 (aka TR9 and TR3 timed enhancements), the LIBRARY_INFO UDTF has optional input paramters to provide better performing queries for library specific questions.
View Library sizes and more.sql
| -- category: IBM i Services | |
| -- description: Librarian - Library Info | |
| -- minvrm: V7R3M0 | |
| -- | |
| create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default | |
| 'create or replace table coolstuff.library_sizes | |
| (library_name, schema_name, | |
| -- qsys2.library_info() columns |
forstie
/ Who am i.sql
Last active Jan 21, 2021
I was asked to provide a query that pulls together some of the basic identity detail for the IBM i you're connect to... which got me to this....
View Who am i.sql
| -- | |
| -- Who am i? | |
| -- | |
| select s.host_name, e.host_name, 'IBM i ' concat os_version concat '.' concat os_release as ibmi_level, | |
| serial_number, machine_type, machine_model, | |
| ipl_mode, ipl_type, attention_light, | |
| total_cpus, e.configured_cpus, | |
| configured_memory, total_memory, | |
| s.* | |
| from sysibmadm.env_sys_info e, qsys2.system_status_info s; |
NewerOlder