IBM i system management: FAQ and Howto (Part 2 – EN)

Last Updated on 1 May 2020 by Roberto De Pedrini

This post is a continuation of the post “Managing the IBM i system: FAQ and Howto (Part 1 – IT)“.

iAdmin-FAQ-011: Better control of JDBC / ODBC jobs (QZDASOINIT JOBs)

Increasingly we find ourselves having to manage and control the performance and activities of the jobs that access DB2 for i via JDBC / ODBC: connections from external applications or from open source languages such as PHP-Java-Node.js-Python, interactive SQL managed by RunSQL Script by ACS / Rdi 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 user of execution!) Into an independent subsystem with dedicated priority and timeslice settings.

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

If you have an old operating system you probably have to look here: QZDASOINIT on the Wiki Midrange, if instead you are in step with the times there are some excellent IBM i Service to see and check the Prestart-jobs, as explained in this post on IBMSystemsMag: “Active_Job_Info () for Prestart Server Jobs”

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 better control of the JOB QZDASOINIT.

iAdmin-FAQ-012: Convert the charset of a file to the IFS

To convert the encoding of the character set of a file in the IFS we can use iconv:

Example convert from plain ASCII to EBCDIC CCSID 037 USA:

 / QOpenSys / pkgs / bin / iconv -f ISO8859-1-t IBM-037 myfile.ascii> myfile.ebcdic 

and viceversa

 / QOpenSys / pkgs / bin / iconv -f IBM-037 -t ISO8859-1 /home/me/myfile.ebcdic> /home/me/myfile.txt 

To get the list of supported charsets:

   / QOpenSys / pkgs / bin / iconv -l 

iAdmin-FAQ-013: Reading and writing files in the IFS

There are several ways to read and write files in the IFS, here are some links to posts from other sites / blogs where they are discussed:

• RPGPGM.COM: “A better way to read a file in the IFS with RPG”
• Worksofbarry.com: “Writing and reading IFS files”
• Scottklement.com: “Working with the IFS in RPG IV”

iAdmin-FAQ-014: What are the IBM ACS Access Client Solution TCP ports?

The ACS ports are the same as the “old” Iseries Access / Client Access

PC Function	Server Name	Non-SSL port	SSL port
Server Mapper	as-svrmap	449	—
License Management	as-central	8470	9470
Access database	as-database	8471	9471
Data Queues	as-DTAQ	8472	9472
IFS Access using System i Navigator	as-files	8473	9473
Network Printers	as-netprt	8474	9474
Remote Command	as-RMTCMD	8475	9475
Signon Verification	as-signon	8476	9476
Telnet (5250 Emulation)	telnet	23	992
Navigator for i (web)	as-nav	2004	2005
HTTP Administration	as-admin	2001	2010
POP3 (MAPI)	pop3	5010	—
Management Central	as-mgtc>	5555 and 5544	5566 and 5577
Ultimedia Services	as-usf	8480	9480
DDM / DRDA	DDM / DRDA	446	448
NetServer	netbios>	137	—
NetServer	CIFS	445	—
NetServer	netbios>	139	—
Service Tools Server	as-sts	3000	—
DHCP Monitor	—	—	942
RUNRMTCMD	REXEC	512	—

As can be seen from the IBM Support site “TCP / IP Ports Required for IBM i Access and Related Functions” or from the ACS manual: “https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajr/rzajrpdf.pdf”

iAdmin-FAQ-015: Check the status of IPv4 and IPv6 network connections

If I want to quickly check network connections to my IBM i, I can do it with a simple SQL statement using DB2 for i Services:

select local_port, remote_address, count (*) as nbrconnections
 from QSYS2.NETSTAT_INFO
 group by local_port, remote_address
 order by remote_address;

If you just wanted to control 5250 access on port 23:

select local_port, remote_address, count (*) as nbrconnections
 from QSYS2.NETSTAT_INFO
 where local_port = 23
 group by local_port, remote_address
 order by remote_address;

iAdmin-FAQ-016: Which files have set the “Reuse deleted records” flag?

This question was asked on the Midrange.com mailing list, given that this information is not present in the system catalog?

The answer comes from Bruce Vinings, with a small RPG program that uses the QDBRTVFD API to retrieve information … then create a UDF that calls the RPG program:

 ctl-opt dftactgrp (* no);

  dcl-pr RtvFD2;
    fileIn char (10) const;
    libIn char (10) const;
    reuseSts char (10);
    fileInd int (5) const;
    libInd int (5) const;
    reuseStsInd int (5);
    sqlState char (5);
    sqlName varchar (517) const;
    sqlSpecific varchar (128) const;
    sqlDiag varchar (1000);
  end-pr;
  dcl-pi RtvFD2;
    fileIn char (10) const;
    libIn char (10) const;
    reuseSts char (10);
    fileInd int (5) const;
    libInd int (5) const;
    reuseStsInd int (5);
    sqlState char (5);
    sqlName varchar (517) const;
    sqlSpecific varchar (128) const;
    sqlDiag varchar (1000);
  end-pi;


  dcl-pr RtvReuse extpgm ('QDBRTVFD');
    rcvVar char (65535) options (* varsize);
    lenRcvVar int (10) const;
    qualRtnFN char (20);
    formatAPI char (8) const;
    qualFileName char (20) const;
    rcdFmtName char (10) const;
    ovrPcs char (1) const;
    system char (10) const;
    formatType char (10) const;
    errCde likeds (qusec);
  end-pr;

/ copy qsysinc / qrpglesrc, qusec
/ copy qsysinc / qrpglesrc, qdbrtvfd

 dcl-ds baseFD likeds (QDBQ25) based (ptrRcvVar);
 dcl-ds pFAtrs likeds (QDBQ26) based (ptrPFAtrs);
 dcl-ds errCde qualified;
   hdr likeds (qusec);
   errDta char (256);
 end-ds;

 dcl-s ptrPFAtrs pointer;
 dcl-s ptrRcvVar pointer inz (% addr (rcvVar));
 dcl-s qualRtnFN char (20);
 dcl-s rcvVar char (4096);
 dcl-s theFile char (10) inz ('MYFILE');

  errCde.hdr.QUSBPrv = 0;
  monitor;
    RtvReuse (rcvVar:% size (rcvVar): qualRtnFN
             : 'FILD0100': (fileIn + libIn): '* FIRST'
             : '0': '* LCL': '* INT'
             : ErrCde);
    if% bitand (% subst (baseFD.QDBBits27: 1: 1): x'20 ') = x'00';
      ptrPFAtrs = ptrRcvVar + baseFD.QDBPFOf;
      if% bitand (pFAtrs.QDBBits33: x'80 ') = x'80';
        reuseSts = 'YES';
      else;
        reuseSts = 'NO';
      endif;
    else;
      reuseSts = 'N / A';
    endif;
  on-error;
    reuseStsInd = -1;
  endmon;
  * inlr = * on;
  return; 

And here is the Function SQL instead

 create or replace function Reuse_Deleted_Records
                          (tableName char (10)
                           , schemaName char (10)
                          )
                  returns char (10)
                  external name 'MYLIB / RTVFD2'
                  program type main
                  language rpgle
                  parameter style sql
                  returns null on null input
                  no external action
                  not fenced 

To then use the UDF by taking the list of files from the System Catalog

 Select system_table_name, system_table_schema,
       Reuse_Deleted_Records (system_table_name, system_Table_schema)
From systables
where table_type in ('P', 'T')
 and system_table_schema = 'MYLIB';
 

iAdmin-FAQ-017: WRKOBJLCK with SQL (QSYS2.OBJECT_LOCK_INFO)

The result of the command “WRKOBJLCK OBJ (MYLIB / TESTFILE) OBJTYPE (* FILE)” can be obtained with SQL by querying the view QSYS2.IBJECT_LOCK_INFO, one of the many “DB2 for i Services” made available by the IBM laboratory.

In this post by Simon Hutchinson on RPGPGM.COM “Using SQL to look for record locks” we see a concrete example of using this view with a small utility that ends all the jobs that lock an object

iAdmin-FAQ-018: WRKACTJOB with SQL (QSYS2.ACTIVE_JOB_INFO)

DB2 for i Service are a great way to query the system from SQL without launching commands or System API: let’s take for example a WRKACTJOB … we can do it with a simple SQL statement like the one below:

select * from table (qsys2.active_job_info (
RESET_STATISTICS => 'NO',
SUBSYSTEM_LIST_FILTER => '',
JOB_NAME_FILTER => '* ALL',
CURRENT_USER_LIST_FILTER => '',
DETAILED_INFO => 'NONE'
)) TO ; 

If instead we wanted to query all the JOBs, even those closed in OUTQ we can use this other view on the JOBs:

SELECT * FROM TABLE (QSYS2.JOB_INFO (JOB_USER_FILTER => '* ALL'));

iAdmin-FAQ-019 WRKSYVAL with SQL (QSYS2.SYSTEM_VALUE_INFO)

Making the change from a POWER7 with 7.2 to a POWER9 with 7.4 I wanted to make sure that I set all the system value settings correctly … to see the difference between the settings of POWER7 and those of POWER9, I created a table with the values of power7, moved with FTP on POWER9 and made a comparison SQL:

- Create a table with system values on the POWER7 system

create table MYLIB.SYSVALP7 as (
 SELECT * FROM QSYS2.SYSTEM_VALUE_INFO
 order by system_value_name) with data;

- Save the table in a SAVF and move it on the POWER9 system ... and then, on my new POWER9 System

SELECT a.system_value_name, a.current_numeric_value as NumP9, 
 b.current_numeric_value as Num_P7, 
 a.current_character_value as Alf_p9,
 b.current_character_value as Alf_p7
 FROM SYSTEM_VALUE_INFO a
 left join MYLIB.SYSVALP7 b on b.system_value_name = a.system_value_name
 where a.current_numeric_value <> b.current_numeric_value or
 a.current_character_value <> b.current_character_value
 order by system_value_name;

iAdmin-FAQ-020 Take control of your Subsystems and Memory Pools (WRKACTJOB join WRKSYSSTS)!

If you need to know in which memory pool are running your subsystem do a JOIN from WRKACTJOB and WRKSYSSTS … by DB2 for i service, of course!

SELECT DISTINCT CAST(A.Objlongschema AS CHAR(10)) AS "Library",
                 A.Objname AS "Subsystem",
                 b.memory_pool,
                 CASE
                     WHEN B.Subsystem IS Null THEN 'Inactive'
                     ELSE 'Active'
                 END AS "Status"
     FROM TABLE (
              Qsys2.Object_Statistics('*ALL', 'SBSD')
          ) A
          LEFT OUTER JOIN TABLE (
                  Qsys2.Active_Job_Info()
              ) B
              ON A.Objname = B.Subsystem
                  AND A.Objlongschema = B.Subsystem_Library_Name
     ORDER BY 4,
              2,
              1 ; 

--- Roberto De Pedrini Faq400.com