Last Updated on 16 May 2020 by Roberto De Pedrini
This is the third part of the IBM i system management FAQ & Howto collection: you can see the other collections here from these two links:
IBM i system management: FAQ & Howto (Part 1)
IBM i system management: FAQ & Howto (Part 2)
IBM i system management: FAQ & Howto (Part 3)
Index
iAdmin-FAQ-032 Monitor triggers
There is a great post on Simon Hutchinson’s RPGPGM.COM that shows how to keep triggers in a data library under control:
RPGPGM.COM: Finding triggers using SYSTRIGGER
iAdmin-FAQ-033 Terminate JOB in Dump … that do not close with ENDJOB
Occasionally it may happen that a JOB cannot be terminated correctly during the DUMP phase with the ENDJOB command (WRKACTJOB Option 4).
In extreme cases, a specific command can be used: ENDJOBABN End Job Abnormal but all the risks of this command must be assessed: the Job is forcibly terminated but the system is flagged as “Abnormal End System” and some features may be disabled until the next IPL …
Before using it, check it well
iAdmin-FAQ-034 Quickly check users * DISABLED (and their last valid login)
SELECT USER_NAME, USRCLS, STATUS,
DATE (PRVSIGNON) LastSignon
FROM QSYS2.USER_INFO
WHERE STATUS = '* DISABLED'
order by DATE (PRVSIGNON) desc;
More info: https://www.rpgpgm.com/2015/11/getting-information-about-user-profiles.html
iAdmin-FAQ-035 List of all users of the QPGMR group
SELECT CAST (GROUPNAME AS CHAR (10)) AS GROUP,
CAST (USERNAME AS CHAR (10)) AS USER
FROM QSYS2.GROUP_PROFILE_ENTRIES
WHERE GROUPNAME = 'QPGMR';
More info: https://www.rpgpgm.com/2015/11/getting-information-about-user-profiles.html
iAdmin-FAQ-036 List of all users with * ALLOBJ authorization
SELECT USER_NAME,
USRCLS,
SPCAUT
FROM QSYS2.USER_INFO
WHERE STATUS = '* ENABLED'
AND SPCAUT LIKE '% * ALLOBJ%'
OR USER_NAME IN (SELECT USERNAME
FROM QSYS2.GROUP_PROFILE_ENTRIES
WHERE GRPPRF IN (SELECT USER_NAME
FROM QSYS2.USER_INFO
WHERE SPCAUT LIKE '% * ALLOBJ%'));
More info: https://www.rpgpgm.com/2015/11/getting-information-about-user-profiles.html
iAdmin-FAQ-037 Alternatives to WRKOBJ to search for objects in the system? QSYS2.OBJECT_STATISTICS
Instead of WRKOBJ the QSYS2.OBJECT_STATISTICS view allows much more flexibility and programmability. Let’s see some examples:
List of journals and journal receivers in a specific library:
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS ('MYLIB', '* JRN * JRNRCV')) AS X
List of program and service program objects in a library (simple list without all details)
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS ('MYLIB', 'PGM SRVPGM', '* ALLSIMPLE')) X
List of all objects in a library with creation date> 1 year and not used for 1 year
SELECT OBJNAME, OBJTYPE,
CAST (OBJCREATED AS DATE) AS CREATED_DATE,
CAST (LAST_USED_TIMESTAMP AS DATE) AS LAST_USED,
DAYS_USED_COUNT
FROM TABLE (QSYS2.OBJECT_STATISTICS ('MYLIB', 'ALL')) A
WHERE LAST_USED_TIMESTAMP <current date - 1 year
OR (OBJCREATED <current date - 1 year AND DAYS_USED_COUNT = 0);
More information on:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm
https://www.rpgpgm.com/2016/01/using-sql-for-objects-statistics.html
iAdmin-FAQ-038 IBM i on Power Performance FAQ
In the FAQ400 blog, in a FAQ post, a reference to the IBM i performance Bible, just updated in early May 2020, could not be missing:
IBM i on Power – Performance FAQ – May 1, 2020
iAdmin-FAQ-039 Users * ALLOBJ and non SSL access
We know that leaving normal users with * ALLOBJ permissions can be dangerous from a security point of view …. if they access directly without SSL security even worse: in this Github GIST by Scott Forstie we find just a simple SQL statement to list all these conditions:
ALLOBJ users coming in over non SSL network interfaces .sql
iAdmin-FAQ-040 Analyze the SQL DML statements for Insert / Update / Delete by reading the Plan Cache
It is possible to analyze the SQL statements that update / delete and insert records in our tables to check times, numbers of times and procedures that execute them. Slow and repeated instructions can be found to intervene on the indices or on the writing of the instruction itself.
There is an excellent Github Gist by Scott Forstie that presents an entire SQL script for extraction, creation of a summary table, analysis of the data collected:
--- Roberto De Pedrini Faq400.com