Object statistics wildcard makes it easier to list objects

A small enhancement to the Object Statistics table function in the latest round of Technology Refreshes is going to make it easier for me to get the results I want. Previously if I wanted to get the results for a group of objects I would have to ask the table function for a list of all the objects in the library, and then limit the returned results only to the ones I want using a where clause.

SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE FROM TABLE(QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => 'MYLIB', OBJTYPELIST => '*ALL', OBJECT_NAME => '*ALL')) WHERE OBJNAME LIKE 'TEST%' ORDER BY OBJTYPE,OBJNAME ;

I am sure you can appreciate that this is not the most efficient way to get the results I want, a list of objects that start with the letters: TEST. I have had to return the results of all the objects in the library MYLIB, and then select from that list the rows I want, where the name start with TEST.

It is now possible to use a wildcard in the object name parameter to just have the objects that start with TEST returned in the table function's results.

01 SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE, 02 DATE(LAST_USED_TIMESTAMP) AS "Last date", 03 LAST_USED_OBJECT AS "LU valid" 04 FROM TABLE(QSYS2.OBJECT_STATISTICS( 05 OBJECT_SCHEMA => 'MYLIB', 06 OBJTYPELIST => '*ALL', 07 OBJECT_NAME => 'TEST*')) ;

Line 2 and 3: I have added these columns as a reminder. In an earlier release this column was added to help us determine if the object's last used date is updated. If the value in the LAST_USED_UPDATE column is "YES" the last used timestamp is updated when the object is used.

Line 7: Here is the wildcard selection. Notice that the asterisk ( * ) is used, rather than the percent character ( % ) if I was to use a LIKE in a where clause.

My results are as follows:

OBJNAME OBJTYPE OBJATRRIBUTE Last date LU valid --------- ------- ------------ ---------- -------- TESTCL *PGM CLLE <NULL> YES TESTRPG *PGM RPGLE 2021-06-24 YES TESTDTAQ *DTAQ 2020-11-25 YES TESTFILE *FILE PF 2021-06-24 YES TESTFILE1 *FILE LF 2021-06-25 YES TEST *USRSPC 2021-06-15 YES TEST *BNDDIR 2020-11-14 YES

I don't have to use the table function's parameter names, the following statement will return the same results:

01 SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE, 02 DATE(LAST_USED_TIMESTAMP) AS "Last date", 03 LAST_USED_OBJECT AS "LU valid" 04 FROM TABLE(QSYS2.OBJECT_STATISTICS( 05 'MYLIB','*ALL','TEST*')) ;

A few TRs ago another cool feature was added to OBJECT_STATISTICS, it is the ability just to make a list of objects, without all the other information. I know I have written about it before, but I want to repeat myself as this is such a useful feature. For example, I just want to return a list of all the objects in my library. I could use this statement:

01 SELECT OBJNAME,OBJTYPE 02 FROM TABLE(QSYS2.OBJECT_STATISTICS( 03 'RPGPGM1','*ALL','*ALL')) ;

Yes it works as it returns to me the list I want. But when the results are generated the table function still does all of the work to fill the other columns, even though they are not in my results.

If I use *ALLSIMPLE in the object name parameter most of the columns are not calculated, returning a null value.

01 SELECT * 02 FROM TABLE(QSYS2.OBJECT_STATISTICS( 03 OBJECT_SCHEMA => 'MYLIB', 04 OBJTYPELIST => 'ALL', 05 OBJECT_NAME => '*ALLSIMPLE')) ;

The first five of the columns in the results look like:

OBJNAME OBJTYPE OBJOWNER OBJDEFINER OBJCREATED -------- ------- -------- ---------- ---------- CPY2IMPF *PGM <NULL> <NULL> <NULL> EG001 *PGM <NULL> <NULL> <NULL> EG002 *PGM <NULL> <NULL> <NULL>

This makes the returning of the results a lot faster.

There are only six columns that return data in the results:

1. OBJNAME:  Name of the object
2. OBJTYPE:  Type of the object
3. OBJLIB:  Name of library
4. OBJLONGSCHEMA:  Basically the library name
5. IASP_NUMBER:  IASP number the object is in
6. IASP_NAME:  Name of the IASP – new column added in these TRs

01 SELECT OBJNAME,OBJTYPE,OBJLIB,IASP_NUMBER,IASP_NAME 02 FROM TABLE(QSYS2.OBJECT_STATISTICS( 03 'MYLIB','ALL','*ALLSIMPLE')) ;

The first few results are:

IASP_ ISAP_ OBJNAME OBJTYPE OBJLIB NUMBER NAME -------- ------- ------ ------ ------- CPY2IMPF *PGM MYLIB 0 *SYSBAS EG001 *PGM MYLIB 0 *SYSBAS EG002 *PGM MYLIB 0 *SYSBAS

 

I use OBJECT_STATISTICS in quite a few of my programs. The wildcard functionality is going to allow me to make the statements easier to understand and faster to execute.

 

You can learn more about the changes to the OBJECT_STATISTICS SQL table function from the IBM website here.

 

This article was written for IBM i 7.4 TR4 and 7.3 TR10.