IBM i System Management: FAQ and Howto (IT)

Last Updated on 23 April 2021 by Roberto De Pedrini

This is not intended to be a comprehensive guide to the management of the IBM System i but simply a collection of FAQs, tricks, tools (in absolutely random order) that relate in general to the MANAGEMENT of the IBM System i, collected by Forums or other websites in General.

Our intention is to keep it updated by occasionally adding new answers, new tricks etc. I also invite you to enrich this guide with your “tricks” or techniques regarding the Management of the System

iAdmin-FAQ-001 Is there a command to see all objects “in bulk” (WKROBJLCK on multiple objects?)

With the latest operating system version IBM i we have several DB2 for i Services: among them what we need is: QSYS2. OBJECT_LOCK_INFO

 SELECT - FROM QSYS2. OBJECT_LOCK_INFO WHERE SYSTEM_OBJECT_NAME - 'SALES' 

Insights on IBM DeveloperWorks : QSYS2. OBJECT_LOCK_INFO

iAdmin-FAQ-002 Can I use the QGPL to put my user objects?

The QGPL library and QUSRSYS are two system libraries that are updated during version or release changes, or sometimes even by installing PTFs. Any user objects are not touched so they can safely reside in these libraries.

Different speech for changes to system objects … in that case the changes can be lost during the release upgrade, version and even PTF installation.

In general, however, you prefer not to use the QGPL, rather better than the QUSRSYS or, even better, a real user library.

If they contain system objects, they must still be included in the daily backup libraries.

iAdmin-FAQ-003: What are the files (tables) that need an RGZPFM for deleted records?

By reading the System Catalog we can identify which tables (phisical files) need a RGZPFM … ordered by “deleted record space descendantind”:

SELECT
     FROM qsys2.netstat_info;
 select system_table_schema, 
        system_table_name, 
        system_table_member, 
        number_deleted_rows, 
        avgrowsize, 
        avgrowsize : number_deleted_rows as deleted_space
 from qsys2.syspartitionstat
 order by 6 desc;

In this case we limit the analysis to a single library and put in order of Number of deleted records

Select
    system_table_schema,
    system_table_name,
    system_table_member,
    number_deleted_rows,
    avgrowsize,
    avgrowsize : number_deleted_rows as deleted_space
from qsys2.syspartitionstat
where system_table_schema?'MYLIB'
order by number_deleted_rows desc;

iAdmin-FAQ-004: Clean up dns Cache?

Sometimes changes to DNS A/MX records are not caught on the fly by systems due to a “cache” issue that optimizes queries between DNS: To force a cache cleanup you can launch the following command without any parameters :

CHGTCPDMN 

or, easier to remember

GO CFGTCP, 
option 12, 
Enter 

To test the result, if you installed the product program 5770SS1 Option 31- Domain Name System you can do a verification with the NSLOOKUP command to the affected domain

NSLOOKUP HOSTNAME(faq400.com)

iAdmin-FAQ-005: Whatismyip from IBM i – From which network “I’m on the Internet”

The website https://www.whatismyip.com/ we know probably all … we can understand with what address we present ourselves on the Internet from our PC (useful in companies with multiple active Internet lines) … but if we want to know our IBM i from which network “comes out on the internet”?

We can use SQL HTTP functions and do with a query like this:

SELECT myip FROM
 (VALUES(SYSTOOLS. HTTPGETCLOB function
 ('https://api.ipify.org','')) WS(myip);

Or you can get your external-ip from a SSHD terminal

curl ifconfig.me

iAdmin-FAQ-006: Switching to a new Power … IFS migration and folder shares

When we change the system and move to a new IBM we have to worry about saving and restoring, in addition to libraries, users, configurations, permissions etc. Restoring the IFS with all its directories and files does not even restore the shares of the same … those are either remade by hand or you have to save and restore on the new system also these two files that generally reside in the directory /QIBM/UserData/OS400/NetServer… the first two files are always there while the third, there may be zero, one or more.

• Qazlscfg: The file contains configuration information.
• Qazlsshr: The file contains share information.
• Qazlsextxxx: The file contains text conversion information for a file share, where xxx is a file share name.

Insights at this link: Backup and recovery of configuration and share information

iAdmin-FAQ-007: Is it possible to manage IFS and share without iNavigator?

Yes, there is GO NETS … an IBM free tool that allows you to manage the functions of the IFS without the iNavigator GUI… installs simply by following the instructions at this link:

Manage IBM i NetServer without Navigator – GO NETS

iAdmin-FAQ-008: Monitor IFS space

If you need to know disk space used by your IFS directories and files and you are at least at 7.2 there is a great SQL Script Example in ACS ” “Analyze IFS storage consumption SQL Example Script” or you can choose for some other ways:

• “rtvdirinf” command and “prtdirinf”
• An old but great Joe Hertvik utility (IT Jungle): “Admin Alert: How Big is My IFS”
• Or this tool by Chris Pando : Printing IFS

iAdmin-FAQ-009: How can I get Users (active job) IP address?

You can get it with this simple SQL on the QSYS2.ACTIVE_JOB_INFO UDTF

SELECT job_name, JOB_USER_IDENTITY, CLIENT_PORT, CLIENT_HOST, CLIENT_IP_ADDRESS, job_type

FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) X

    WHERE client_ip_address is not null

    ORDER BY JOB_USER_IDENTITY;

iAdmin-FAQ-010: Configuring SSL/TSL on IBM i

Do you have problems with SSL or certificates … have a look at those links:

• IBM: Configuring Your IBM i System Secure Sockets Layer (SSL)/Transport Layer Security (TLS) Protocols and Cipher Suites
• BVS Tools: SSL Documentation
• BVS Tools: SSL FAQ

iAdmin-FAQ-011: Better control for JDBC/ODBC Jobs (QZDASOINIT JOBs)

More and more often we find ourselves having to manage and control the performance and activities of jobs accessing the DB2 for i via JDBC/ODBC: links from external applications or from open source languages such as PHP-Java-Node.js-Python, interactive SQL managed by ACS/Rdi RunSQL Script etc..

We know that all these jobs, by default, are managed in the QUSRWRK subsystem through jobs that go under the name of QZDASOINIT or QRWTSRVR and we don’t have great granular control possibilities.

In this excellent 2015 article on ITJungle “Tuning SQL Environments On i“ , Paul Tuohy explains how to bring some JOBs (depending on the execution user!) into an independent subsystem with dedicated priority and timeslice settings.

Also in this TechTip by Kent Milligan on McPressOnline “TechTip: Routing Away Your QZDASOINIT Troubles” the process is explained.

But already in 2011 Yiyu Jia had made a post in his blog “IBM i5/OS V5R4 prestart job and database connection tuning.” where he explained the concepts of Prestart Job for a better control of QZDASOINIT JOBs.

If you are on an old IBM i OS version you probably need this link : QZDASOINIT on the Wiki Midrange

If you need more information about Prestart JOBs have a look at this post on IBMSystemsMag about QSYS2.ACTIVE_JOB_INFO IBM i service: “Active_Job_Info() for Prestart Server Jobs”

--- Roberto De Pedrini Faq400.com