Using SQL for object's statistics

I have mentioned in a previous post how you could find out pretty much anything you wanted to know about a file or SQL table by using the SQL View SYSTABLES. But what about other object types?

I have found the OBJECT_STATISTICS table function useful for most of the information I want to know about objects in a library. Table functions return columns in way that resembles getting columns from a View or Table.

OBJECT_STATISTICS has three parameters:

1. Library name. There are three special values: *ALL, *ALLUSR for all the user libraries in the current job's ASP group, *ALLUSRAVL for all user libraries in all ASP groups.
2. Object types. List of the object types, more than one can be given. The object types do not have to have the leading "*". I can use either "*ALL" or "ALL" to list all of the objects in the library.
3. Object name (optional). If given then only rows are returned where the object name is equal to that given here. I am fortunate to be working on an IBM i with 7.2 TR3. I have tested this on other IBM i also with 7.2 but not with TR3 and I have been unable to use this parameter.

If this is placed in a Select statement it could look like:

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('library_name', 'object_type_list', OBJECT_NAME => 'object_name')) A

At the end of the From part of the statement there must be an alphabetic character, in this example "A". The examples in the documentation all show "AS A", but I have found that the alphabetic character is enough. Failure to take either approach will result in an error.

I am not going to list all the columns available, just the ones I am going to use in these examples. If you want a full list of the columns go to the IBM Knowledge Center here.

Column	Description
OBJNAME	Object name
OBJATTRIBUTE	Object type's attribute
OBJCREATED	When object created as timestamp
OBJDEFINER	User profile that created object
LAST_USED_TIMESTAMP	When the object was last used
DAYS_USED_COUNT	Number of days the object was used
OBJLONGSCEMA	Library name.

Let me start with something simple: a list of all the libraries on the IBM i with who created them:

SELECT OBJNAME,OBJCREATED,OBJATTRIBUTE,OBJDEFINER FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','LIB')) A

I have used "*ALL" when I could have used QSYS (all libraries reside in QSYS) just to give an example of how the *ALL works. If I did the *ALL with let's say "PGM" the result set would be enormous. Therefore, I recommend you think hard before using *ALL.

A few snippets from the results set for the libraries looks like:

OBJNAME OBJCREATED OBJAT00001 OBJDEFINER #CGULIB 2014-05-04-00.54.23.000000 PROD QLPINSTALL #COBLIB 2014-05-04-01.44.22.000000 PROD QSECOFR #DFULIB 2014-05-04-00.54.23.000000 PROD QLPINSTALL MYLIB 2014-05-30-13.26.12.000000 PROD SIMON QADVSEC 2014-05-04-01.19.14.000000 PROD QSECOFR QAFP 2014-05-04-01.19.16.000000 PROD QSECOFR QAFPLIB1 2014-05-04-01.18.21.000000 PROD QSECOFR SOMELIB - - -

Notice that there are no values returned for the library SOMELIB. The dashes indicate a value of null. I assume this is because I am not authorized to the library.

An annual task I perform is to delete objects in the libraries I am responsible for that are no longer used. This table function allows me to quickly get a list of all eligible objects:

01 SELECT OBJNAME,OBJTYPE, 02 CAST(OBJCREATED AS DATE) AS CREATED_DATE, 03 CAST(LAST_USED_TIMESTAMP AS DATE) AS LAST_USED, 04 DAYS_USED_COUNT 05 FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','ALL')) A 06 WHERE LAST_USED_TIMESTAMP < '2014-04-01' 07 OR (OBJCREATED < '2014-04-01' AND DAYS_USED_COUNT = 0)

I do not really care what time an object was created, just the date. Therefore, I have used CAST to convert the two timestamp columns into dates, lines 2 and 3. If an object has never been used its Days Used Count will be zero, and I have incorporated this into line 7 to capture objects that were created before my cutoff date and never used.

OBJNAME OBJTYPE CREATED_DATE LAST_USED DAYS_00001 TESTMSGF *MSGF 03/14/14 03/14/14 1 BIGFILE *FILE 04/12/13 02/15/14 141 DATE *PGM 09/12/12 09/12/12 1 TESTRPG99 *PGM 11/29/13 - 0

As I mentioned previously the object type can contain a list of values. In this example I want to have a list of all programs and service programs in my library. All I have to is to list the object types, either with or without the leading asterisk, separated by a space.

SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE,OBJDEFINER FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','SRVPGM PGM')) A

Which gives me:

OBJNAME OBJTYPE OBJAT00001 OBJDEFINER SRVPGM001 *SRVPGM RPGLE SIMON TESTCLLE *PGM CLLE SIMON TESTRPG *PGM RPGLE SIMON

Now to the mysterious parameter, that only appears to work when I use this table function in IBM i 7.2 TR3. I assume the same is true with IBM i 7.1 TR11, but I do not have access to a server running that release and TR I cannot be sure. In this example I am looking for all libraries where the program TESTRPG is in. I could make a Select statement in two ways. Firstly without using the third parameter:

SELECT OBJNAME,CAST(OBJLONGSCHEMA AS CHAR (10)) AS OBJLIBRARY, OBJTYPE,OBJATTRIBUTE FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','PGM')) A WHERE OBJNAME = 'TESTRPG'

Using the third parameter the Select could be:

SELECT OBJNAME,CAST(OBJLONGSCHEMA AS CHAR (10)) AS OBJLIBRARY, OBJTYPE,OBJATTRIBUTE FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','PGM', OBJECT_NAME => 'TESTRPG')) A

I presume that using the third parameter makes the returning of the results faster, and take less systems resources gathering the results. When I ran the two statements there was no real difference in time taken to return the results.

OBJNAME OBJLIBRARY OBJTYPE OBJAT00001 TESTRPG MYLIB *PGM RPGLE

I see OBJECT_STATISTICS being another reason why I will stop using the Display Object Description command, DSPOBJD, for everything except for finding which source an object was created from.

 

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

 

This article was written for IBM i 7.2, and should work for later 7.1 Technology Refreshes.