IBM i system management: FAQ & Howto (Part 3) EN

Last Updated on 1 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)

iAdmin-FAQ-021 Rebuilding Indexes and Logical Views (EDTRBDAP)

Following an incorrect shutdown of the system, for example an electrical black-out or a system left to go up to 99% of disk occupancy, it may happen that by rebooting the system all the Logical File / Index in use at that time are not correctly reconstructed moment.

If logical files are used explicitly (as in the case of native I / O and the logical file declared in the dcl-f (F) specifications, the operating system takes care of rebuilding the logical file … but if they are indexes of the tables, created, for example, by the suggestions of the Index-Advisor, they are not taken into consideration by our SQL statements with the result of arriving at obscene response times.

By launching the EDTRBDAP command we can check and recreate all the defective indexes and return the performances to the previous level.

https://www.ibm.com/support/pages/using-edtrbdap-command-rebuild-invalid-access-paths

iAdmin-FAQ-022 Change a group of users quickly (CHGUSRPRF * XX * MYOPTION (YYY))

If we want to modify a group of users by setting a certain option to all, we cannot use a command like CHGUSRPRF (MYFILTER) MYOPTION … It is not possible.

However, we can use SQL to build a script to always run within ACS’s “Run SQL script”. For example, if we wanted to set the LMTDEVSSN (* YES) parameter to limit the use of a single session for radio frequency users (which we suppose are RFxxxxx) we can create the script with SQL, save it in a TXT file and always run the result from SQL interface:

SELECT 
'CL: CHGUSRPRF USRPRF (' || AUTHORIZATION_NAME || ') LMTDEVSSN (* YES);' 
FROM QSYS2.USER_INFO WHERE AUTHORIZATION_NAME LIKE 'RF%';

A second way, always from ACS “Execute SQL script” or also from STRSQL it is possible to write SQL logic in a SQL-Compound-statement (we talk about it in this post of “SQL FAQ for DB2 for i“)

begin

    declare cmd varchar (256);

    for vl as c1 cursor for
        select
        *
        FROM QSYS2.USER_INFO WHERE AUTHORIZATION_NAME LIKE 'LAB%'
        do

        set cmd = 'CHGUSRPRF USRPRF (' || AUTHORIZATION_NAME || ') LMTDEVSSN (* YES)'; 
        call qcmdexc (cmd);
    end for;
end;

iAdmin-FAQ-023 Retrieve the IP of the IBM i partition and the client running an application

With IBM i services we can recover the IPs of the IBM i partition and also that of the client that is running an application in a very simple way:

Retrieve the system’s IP address (or IP addresses):

SELECT * FROM QSYS2.NETSTAT_INTERFACE_INFO 
  WHERE INTERFACE_LINE_TYPE = 'VETH';

or

SELECT LOCAL_BINDING_INTERFACE FROM QSYS2.NETSTAT_ROUTE_INFO where ROUTE_TYPE =
'DFTROUTE';

If you do not have IBM services available (old versions of the operating system), you can also get there with RPG via API:

List Network Interfaces (QtocLstNetIfc) API

Retrieve the IP address of the device that is running an application

select sysibm.client_ipaddr
from sysibm.sysdummy1;

iAdmin-FAQ-024 Name of the Relational Database (WRKRDBDIRE)

The database name (the one you see with STRSQL or SQL Script) is managed by WRKRDBDIRE and other ALIAS can be added to point to the DB * LOCAL … useful thing, for example, when changing systems we have to modify ODBC / JDBC accesses from outside.

iAdmin-FAQ-025 Attention to CHGCMDDFT

When changing defaults with CHGCMDDFT, updating the operating system or installing some PTFs we may have some problems. In this post on Geeky Ramblings we are offered an alternative:

Beware CHGCMDDFT

iAdmin-FAQ-026 Performance Data Collector and Performance Data Investigation (PDI)

When you want to analyze the performance of an IBM i (or a single partition), the operating system offers a series of tools that help identify and improve various performance problems:

• POI IBM i Performance Data Investigator
• Performance Data Collectors in IBM i

Always tied to the concepts of Performance there is an excellent post, even if still from 2013, of Dawn May: IBM i wait accounting that is worth reading.

iAdmin-FAQ-027 SAVSECDTA takes too long

The SAVSECDTA command saves all security information without requiring you to be in restricted mode (limited condition) and can be added to the data backup process. On some IBM i systems this process is very long … in this case there is a guide that explains the causes and also the way to speed it up: IBM Support – SAVSECDTA is Taking a Long Time, tricks such as creating a group user profile and assigning authorization of objects to this user -of-a group instead of every single one … with significant time savings (from hours to minutes !!!).

iAdmin-FAQ-028 List of objects of a UserProfile

It seems trivial … but the command “DSPUSRPRF USRPRF (MYUSER) TYPE (OBJOWN) OUTPUT (PRINT)” only prints the objects of the various IBM i libraries (QSYS.LIB / …) but ignores any IFS objects. If we also want those there is a tools not officially supported by IBM … but downloadable from their own site called QMGTOOLS.

In the QMGTOOLS library and also in the QSPTLIB, among other things, we find the way to print the complete list of the objects of the indicated user.

iAdmin-FAQ-028 Analyze the JOBLOGs of a “subset” of JOB

Using the “DB2 for” Services we can query and manage the system with a few simple SQL statements. For example JOBLOG_INFO allows to query with SQL the JOBLOG of the current job or of a specific JOB … but if we wanted to see the JOBLOGs of all JOBs of a certain type … for example of all JOB QZDASOINIT:

 select a.job_name, b.message_id, b.message_text
  from table (qsys2.joblog_info (
  JOB_USER_FILTER => 'QUSER')) to,
  lateral (select * from table (qsys2.joblog_info (a.job_name)) x) b
  where a.job_name like ('% QZDASOINIT%');

Other examples of use can be found in this post on IBM System Media: Analyzing Joblogs with IBM’s Db2 for i Services

iAdmin-FAQ-029 Bad Logon – Identify attempts to log in with incorrect password

If I activate the Audit Journal (see this post “Modernize your platform with IBM i 7.4″) we can analyze Logon attempts with incorrect password by querying the data detected by the Audit Journal itself with the control functions:

DSPAUDJRNE ENTTYP (PW) OUTPUT (* PRINT)
or
DSPJRN JRN (QAUDJRN) ENTTYP (PW) OUTPUT (* PRINT)

As indicated in this excellent ITJungle post from 2005: “Admin Alert: A Better Technique for Detecting Invalid Log-In Attempts”

Or query the same Audit Journal with the SQL features “Qsys2.Display_Journal” with an SQL statement as indicated in this Brian Dietz Gist: “Badlogons.sql“

iAdmin-FAQ-030 – Check JOBs in MSGW Message Waiting with SQL

See this Gist by Brian Dietz: Active Job in MSGW which uses a join between DB2 for i Service Qsys2.Joblog_Info and Qsys2.Active_Job_Info

iAdmin-FAQ-031 – Check JOBs in ODBC with DB2 for i Service

Also in Brian Dietz’s Gist repository we find this SQL to list the ODBC JOBs: “WRKOBDCJOB in SQL“

--- Roberto De Pedrini Faq400.com