Surge of Services in DB2 for i, Part 2
April 19, 2016 Michael Sansoterra
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.
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.
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;
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.
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.
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