IBM i System Management: FAQ & Howto (Part 4) IT

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)

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:

Extract SQL DML insight from the Plan Cache.sql

--- Roberto De Pedrini Faq400.com