Gimme, Gimme, Gimme! More Services And Global Variables Available For DB2 for i

SELECT * FROM SYSTOOLS.GROUP_PTF_CURRENCY

PTF Group Currency	Group Id	Group Title	Level Inst.	Level Avail.	IBM Last Updated	Status
INSTALLED LEVEL IS CURRENT	SF99702	720 DB2 for IBM i	3	3	11/11/2014	INSTALLED
UPDATE AVAILABLE	SF99713	720 IBM HTTP Server for i	4	5	12/23/2014	INSTALLED
UPDATE AVAILABLE	SF99716	720 Java	3	4	12/10/2014	INSTALLED

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.

QSYS2.LIBRARY_LIST_INFO

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.

QSYS2.JOURNAL_INFO

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:

VALUES(JOB_NAME,SERVER_MODE_JOB_NAME);

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.

RELATED STORY

Make Your DB2 For i Apps Environment Aware