04 - System Administration (EN)04g - System Admin miscellanea

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:

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 FunctionServer NameNon-SSL portSSL port
Server Mapperas-svrmap449
License Managementas-central84709470
Access databaseas-database84719471
Data Queuesas-DTAQ84729472
IFS Access using
System i Navigator
as-files84739473
Network Printersas-netprt84749474
Remote Commandas-RMTCMD84759475
Signon Verificationas-signon84769476
Telnet (5250 Emulation)telnet23992
Navigator for i (web)as-nav20042005
HTTP Administrationas-admin20012010
POP3 (MAPI)pop35010
Management Centralas-mgtc>5555 and 55445566 and 5577
Ultimedia Servicesas-usf84809480
DDM / DRDADDM / DRDA446448
NetServernetbios>137
NetServerCIFS445
NetServernetbios>139
Service Tools Serveras-sts3000
DHCP Monitor942
RUNRMTCMDREXEC512

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 ; 
Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

Leave a Reply

Your email address will not be published. Required fields are marked *