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
/ 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|
|IFS Access using|
System i Navigator
|Telnet (5250 Emulation)||telnet||23||992|
|Navigator for i (web)||as-nav||2004||2005|
|Management Central||as-mgtc>||5555 and 5544||5566 and 5577|
|DDM / DRDA||DDM / DRDA||446||448|
|Service Tools Server||as-sts||3000||—|
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