Gimme, Gimme, Gimme! More Services And Global Variables Available For DB2 for i
April 7, 2015 Michael Sansoterra
To satiate the insatiable among us, IBM continues to provide new ways for DB2 for i SQL developers and database administrators to programmatically interrogate and interact with their operating environment through the use of “services” and global variables.
New “Services” Views
The IBM DB2 for i Services have been a great boon to developers and administrators because they give access to information about the IBM i OS environment via the simplicity of SQL. For a brief intro to services, see my article Make Your DB2 For i Apps Environment Aware. There are several new “services” available as of IBM i 7.2 TR3/IBM i 7.1 TR9 that I will describe for you.
View SYSTOOLS.GROUP_PTF_CURRENCY will be a system admin’s new best friend for determining whether the installed group PTFs are current. This service uses the SYSTOOLS.HTTPGETBLOB functions to talk to IBM in real-time (which you can see here) to retrieve the latest available group PTF levels. After gathering the latest available group PTF levels, the view compares the latest against the currently installed levels on the system provided by the GROUP_PTF_INFO (a.k.a. GRPPTFINFO) service view and reports the differences.
The view is easy to use:
SELECT * FROM SYSTOOLS.GROUP_PTF_CURRENCY
It will return results like this:
From the results, it’s easy to discern that the database group PTF level is up to date but there is an update available for Java and the HTTP Server products.
For the next IBM i Service showcase item, I’d like to present QSYS2.LIBRARY_LIST_INFO:
SELECT * FROM QSYS2.LIBRARY_LIST_INFO;
This view will return one row for each library in the current job’s library list and includes valuable information such as the schema name, system library name, ordinal position in the library list, and type of library (SYSTEM, PRODUCT, USER).
This view can be used programmatically to determine if expected libraries are in the library list, to record information about the state of the job when it crashed, etc.
Help is now present for those under the crushing task of managing journals. The new view QSYS2.JOURNAL_INFO shows the various options defined for a journal, the size of the receivers, the currently attached receiver, the number of objects journaled, etc.
SELECT * FROM QSYS2.JOURNAL_INFO WHERE JOURNAL_LIBRARY='CORPDATA';
QSYS2.REPLY_LIST_INFO and QSYS2.JOBLOG_INFO
The next two service objects fall under the category of “messaging service”. The QSYS2.REPLY_LIST_INFO view can be used to interrogate the currently defined system reply list entries via SQL:
SELECT * FROM QSYS2.REPLY_LIST_INFO
This is pretty much the read-only SQL equivalent of IBM i OS command Work with System Reply List Entries (WRKRPYLE). Having this information can be useful in an SQL environment, for instance, when you want to deploy an unattended database change script.
Consider the scenario where you have table APP_TABLE containing column MY_COLUMN that has a data type of REAL. After careful reflection you want to change the data type of this column to an INTEGER. APP_TABLE is very large so you decide to run an unattended script over the weekend to change the data type:
BEGIN IF EXISTS ( SELECT * FROM QSYS2.SYSCOLUMNS WHERE TABLE_NAME='APP_TABLE' AND COLUMN_NAME='MY_COLUMN' AND TABLE_SCHEMA='QGPL' AND DATA_TYPE<>'INTEGER') THEN ALTER TABLE QGPL.APP_TABLE ALTER COLUMN MY_COLUMN SET DATA TYPE INTEGER NOT NULL; END IF; END
However, when you issue an ALTER statement where the potential for data loss may occur, an inquiry message ID of CPA32B2 will automatically be sent to the job:
Change of file APPTABLE may cause data to be lost. (C I)
If the script is in an interactive or traditional batch job, the system will wait for a response. If you’re in a host server job such as QZDASOINIT then a reply of C will automatically be sent for you. This situation isn’t good because the point was for this to be an unattended script.
The new service view can help though, by checking if a system reply message is entered to automatically reply to CPA32B2. If a reply is not present, a system reply list entry can be added by the script to send an “Ignore” reply to let the script continue on its merry way. Here is an IF block that shows how to do this:
IF NOT EXISTS ( SELECT * FROM QSYS2.REPLY_LIST_INFO WHERE MESSAGE_ID='CPA32B2') THEN CALL QCMDEXC('ADDRPYLE SEQNBR(5000) MSGID(CPA32B2) RPY(I)'); END IF; /* Change the job to accept the system reply */ CALL QCMDEXC('CHGJOB INQMSGRPY(*SYSRPYL)');
With this reply list entry configured the script will run without waiting for a reply to CPA32B2. Of course, you may want to get fancy and change the job inquiry message reply back to *DFT, and remove the automatic reply to message CPA32B2.
Additionally, a new user-defined table function (UDTF) service called QSYS2.JOBLOG_INFO can be used to retrieve job log entries for an active job. The following example uses the asterisk (*) as the job parameter to return the job log for the current job:
SELECT * FROM TABLE(QSYS2.JOBLOG_INFO('*')) AS CURRENT_JOB;
As you know, many messages in the log are not helpful so they can be filtered by checking for a minimum severity or by a troublesome program known to have issues:
SELECT * FROM TABLE( QSYS2.JOBLOG_INFO('012345/MIKE/QPADEV0001')) THE_LOSERS_JOB WHERE SEVERITY>=30 OR FROM_PROGRAM='LAME_RPG';
Because it can look at any job on the system, this UDTF can also be used to inspect any active job log on the system (and SQL offers a better filtering capability than does the green screen F10=Job Log!) For developers, it can also be used programmatically to capture specific information from the job log if something goes awry in the job stream.
New Built-In Global Variables
Recall that IBM i 7.2 introduced something new called global variables. (This does not pertain to 7.1). These are similar to special registers and they contain information related to the execution environment.
Two new global variables are introduced in IBM i 7.2 TR3:JOB_NAME and SERVER_MODE_JOB_NAME.
Both of these registers return a formatted job name in the form JOBNO/USER/JOB_NAME with a data type of VARCHAR(28). An example is: 024654/QUSER/QZDASOINIT. Sample usage:
JOB_NAME returns the name of the current job and the SERVER_MODE_JOB_NAME returns the name of the host job that is controlling a QSQSRVR job. QSQSRVR jobs execute SQL statements on behalf of other jobs, typically those that access DB2 for i using SQL CLI or JDBC. If the SQL is statement is not being executed by a QSQSRVR job, then global variable SERVER_MODE_JOB_NAME will return null.
As with many of the new services, these global variables can easily relay important information about a job when a problem occurs.
Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.
Make Your DB2 For i Apps Environment Aware