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:
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.
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:
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:
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:
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.
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