Surge of Services in DB2 for i, Part 2

Continuing where we left off in Part 1, a slew of new IBM i services (and enhancements to existing services) were released in IBM i 7.2 TR3 and IBM i 7.1 TR11. This article continues to detail some of the important new services.

As always, keep in mind that just an overview is given here and that the developerWorks documentation or IBM i Services in the Knowledge Center should be consulted in order to understand the full capability of each service.

SYSTOOLS.GROUP_PTF_DETAILS

This view runs a real-time query against the IBM Preventative Service Planning (PSP) website and can indicate which program temporary fixes (PTFs) within a group PTF have been applied and which ones are missing. (How cool is that?!) It goes without saying that the IBM i partition must have access to the Internet to do this.

This query shows all of the available PTFs that have not been loaded for the DB2 for i and Java product groups:

SELECT * 
  FROM SYSTOOLS.GROUP_PTF_DETAILS
 WHERE PTF_STATUS='PTF MISSING'
   AND PTF_GROUP_DESCRIPTION IN ('DB2 FOR IBM I','JAVA')

Because it retrieves data from IBM’s website in real time, this view can take a while to process. Its function is similar to the SYSTOOLS.GROUP_PTF_CURRENCY service, which compares the installed Group PTF levels against the available Group PTFs, but without going into detail about individual PTFs.

QSYS2.LICENSE_INFO

This view returns a list of installed license programs, similar to the Work with License Information (WRKLICINF) command. From a programmatic standpoint, it can be used to check to see if certain required software is installed before continuing with a certain operation. For instance, if you are writing an SQL script to validate that a partition has the licensed program 5770WDS ILE Compilers installed, you can include something like this in your validation script:

BEGIN

IF NOT EXISTS (
SELECT * 
  FROM QSYS2.LICENSE_INFO 
 WHERE PRODUCT_TEXT LIKE 'ILE COMPILERS%') THEN
    SIGNAL SQLSTATE '38I01'
           SET MESSAGE_TEXT = 'Compilers are not installed';
END IF;
 
END

The above statement will throw an error if the 5770WDS ILE Compilers product is not installed.

I wish this view returned all installed software inventory as shown on menu LICPGM option 10 “Display installed licensed programs.” But the documentation states that this view returns all “products or features that contain license information.” The Java (5770JV1) product does not have license info associated with it and it does not show up in this view, even though I might like to query what Java versions are installed. Likewise, it would be nice to use this service (or one like it) to check if, for example, the “IBM Advanced Data Security for i (5770SS1 option 47)” is installed before moving forward with an RCAC operation. Alas, there is too much of a disparity between installed programs and licensed programs!

QSYS2.OUTPUT_QUEUE_ENTRIES() and QSYS2.OUTPUT_QUEUE_ENTRIES

It’s not difficult to discern that this service pair (a table function and a view) is aimed at helping administrators deal with spool file entries within one or more output queues. In addition to the usual job and printer file name info, additional spool file attribute information can be mined from these services including a spool file’s size (KB), status, form type, orientation, front overlay name, page_length, cpi, device type (*SCS, *AFPDS, etc.), and much more.

Here are a few things you can do with these services.

Example 1: Use a dynamic compound statement to delete job log spool files over six weeks old.

BEGIN

DECLARE @CMD VARCHAR(2048) NOT NULL DEFAULT '';

FOR V1 AS C1 CURSOR FOR
SELECT JOB_NAME,SPOOLED_FILE_NAME,FILE_NUMBER
  FROM QSYS2.OUTPUT_QUEUE_ENTRIES 
 WHERE SPOOLED_FILE_NAME='QPJOBLOG' 
   AND CURRENT_TIMESTAMP - 42 DAYS > CREATE_TIMESTAMP
DO
    /*
    Build command with form:
    DLTSPLF FILE(A) JOB(B) SPLNBR(1)    
    */

    SET @CMD='DLTSPLF FILE('||SPOOLED_FILE_NAME||
             ') JOB('||JOB_NAME||
             ') SPLNBR('||FILE_NUMBER||')';
    CALL QSYS2.QCMDEXC (@CMD);
END FOR;

END;

Example 2: Identify the spool file disk space hogs by user:

SELECT USER_NAME AS OFFENDER, SUM(SIZE)/1024.0 AS SIZE_MB, SUM(PAGES) AS PAGES
  FROM QSYS2.OUTPUT_QUEUE_ENTRIES 
 WHERE USER_NAME NOT IN ('QUSER','QTCP')
GROUP BY USER_NAME
ORDER BY SIZE_MB DESC

Example 3: Look through all saved spool files to find out if overlay FORM1 was used recently (this example is only useful if the pertinent spool files are not printed or saved):

SELECT *
  FROM QSYS2.OUTPUT_QUEUE_ENTRIES 
 WHERE FRONT_OVERLAY_NAME='FORM1'
   AND FRONT_OVERLAY_LIBRARY='MYLIB'
   AND CREATE_TIMESTAMP>='2016-01-01'

As with many other service views, it can take a while to materialize all of the data. Alternatively, use the OUTPUT_QUEUE_ENTRIES table function to focus on a single output queue:

SELECT *
  FROM TABLE(OUTPUT_QUEUE_ENTRIES(
  OUTQ_LIB=>'QGPL',
  OUTQ_NAME=>'QPRINT',
  DETAILED_INFO=>'*YES')) AS OUTQ
 ;

A long while back I wrote an RPG UDTF to return the content of an *SCS spool file and I don’t know what I did with the source. I’m hoping IBM comes out with such a beast soon to complement these fine Outq services (and save me the trouble of a rewrite).

QSYS2.SYSTEM_STATUS() and QSYS2.SYSTEM_STATUS_INFO

This table function returns a hybrid of information found in the Work with System Status (WRKSYSSTS) and Work with System Activity (WRKSYSACT) commands. This example queries the number of active jobs and sends a message to the system operator if over 120 jobs are active:

BEGIN
    IF EXISTS (
        SELECT * 
          FROM TABLE(QSYS2.SYSTEM_STATUS()) SysInfo 
         WHERE Active_Jobs_In_System>120) THEN
        CALL QSYS2.QCMDEXC (
        'SNDMSG TOUSR(*SYSOPR) MSG(''Warning: Partition Capacity Reached'')');
    END IF;
END;

Because this table function (like its interactive counterparts) returns aggregates based on accumulated data over time (such as AVERAGE_CPU_RATE, SQL_CPU_UTILIZATION, MAXIMUM_CPU_UTILIZATION), the aggregated data can become skewed by rare events or long periods of non-system use (such as overnight or holiday weekend). For instance, say a new RPG program in production has a bug and dominates the CPU for 10 minutes before the job is killed and the program is fixed. That incident will throw off the accumulators in the sense that they will be showing something out of the ordinary. The cumulative data can be reset by passing the value ‘YES’ to the reset parameter, at which time all prior data is discarded:

SELECT *
  FROM TABLE(QSYS2.SYSTEM_STATUS('YES')) INFO;

Don’t overlook the many riches in this service as it can even be used to test if the system is in a restricted state:

SELECT * 
  FROM TABLE(QSYS2.SYSTEM_STATUS()) SysInfo
 WHERE RESTRICTED_STATE='YES';

The SYSTEM_STATUS_INFO view is nothing more than a wrapper around the SYSTEM_STATUS table function that will also return default numeric values instead of NULLs where appropriate. Further, the view offers no mechanism to reset the statistics data like the table function does.

QSYS2.MEMORY_POOL() and QSYS2.MEMORY_POOL_INFO

The memory pool information provided by these services can be found using the Work with Shared Pools (WRKSHRPOOL) and Work with System Status (WRKSYSSTS using the intermediate assistance level) commands. Among other things, these services can be used to detect memory pressure in a given pool by monitoring the average page faults.

In this example, the memory pools are shown where the number of average database related page faults/second (i.e., the system tries to access a portion of database data that is not in main storage) is greater than 100. This condition could indicate that the pool needs more memory (or perhaps the queries should be tuned to run more efficiently):

SELECT SYSTEM_POOL_ID,POOL_NAME,RESERVED_SIZE,
       CURRENT_SIZE,DEFINED_SIZE,CURRENT_THREADS,
       DESCRIPTION,ELAPSED_DATABASE_FAULTS,ELAPSED_NON_DATABASE_FAULTS 
  FROM QSYS2.MEMORY_POOL_INFO mp
 WHERE ELAPSED_DATABASE_FAULTS>100

I’m not a system tuning kind of guy, so I can’t offer advice on a particular number of faults to look for. However, if I was in charge of keeping a system tuned (without all of the fancy performance capturing tools that the highly paid guys use), I would create a performance baseline by recording and reviewing the various values offered in these services while the system is running smoothly. Thereafter, if a performance storm hits (due to increased database data, more users, new applications, etc.) I would compare the current values with prior “baseline” values to see what has changed.

This query uses the service to show inactive memory pools that have a defined size:

SELECT SYSTEM_POOL_ID,POOL_NAME,RESERVED_SIZE,
       CURRENT_SIZE,DEFINED_SIZE,CURRENT_THREADS,
       DESCRIPTION,ELAPSED_DATABASE_FAULTS,ELAPSED_NON_DATABASE_FAULTS 
  FROM QSYS2.MEMORY_POOL_INFO mp
 WHERE DEFINED_SIZE>0
   AND STATUS='INACTIVE'

Many of the values returned by these services represent values aggregated over time. The ELAPSED_TIME column indicates the duration of the aggregation period. Sometimes it is useful to reset the metrics such as the beginning of the day when users start hitting the system. The function MEMORY_POOL() offers the same columns as the view, but allows for a parameter (valid values are ‘YES’ and ‘NO’) that can be used to reset the aggregated metrics:

SELECT * FROM TABLE(QSYS2.MEMORY_POOL('YES')) MP;

QSYS2.MEDIA_LIBRARY_INFO

DB2 for i is leaving no area of IBM i administration untouched. This view is the SQL companion of the Work with Media Library Status (WRKMLBSTS) command. In this example, the query returns the list of devices that are varied off:

SELECT DEVICE_NAME,DEVICE_MODEL,DEVICE_DESCRIPTION
  FROM QSYS2.MEDIA_LIBRARY_INFO
 WHERE DEVICE_STATUS='VARIED OFF'

Modified Existing Services

While space doesn’t permit to go over all of these in detail, follow the links to review the enhancements to these existing services.

• QSYS2.OBJECT_STATISTICS()–Now returns info for more object types including *USRPRF
• QSYS2.GET_JOB_INFO()–includes a new column for the client IP address.
• QSYS2.ACTIVE_JOB_INFO()— a new Elapsed_Time column has been added which represents the number of seconds from the last time the function’s aggregate information was reset until the current time (in other words, the aggregation’s duration).
• SYSIBMADM.ENV_SYS_INFO— new columns for configured partition memory.
• QSYS2.SERVER_SBS_ROUTING / QSYS2.SET_SERVER_SBS_ROUTING()–The Remote command server (QZRCSRVS) is a new configuration option.

The Cat’s Meow

Whenever IBM released a technology refresh for DB2 for i, I used to look forward to enhancements in the functional and performance areas. However, these services have certainly become the “cat’s meow” (i.e. something big to anticipate with each technology refresh) by allowing easy access to important system data without the technical API programming headaches.

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 STORIES

Surge Of Services In DB2 For i, Part 1

Make Your DB2 For i Apps Environment Aware

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