Wednesday, January 20, 2016

Using SQL for object's statistics

object statistics dspobjd

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.

9 comments:

  1. Greetings.
    An earlier post identified ways to determine last used date for objects.
    Some SQL "stuff" doesn't show up as objects (some user-defined functions for example).
    Is there a way to find out if a function in a specified library has been used, and if so, when?
    Thanks!

    Regards,
    Michael Regan

    ReplyDelete
  2. trying to determine a good way to look at objects in a library and determine if a library should be removed due to inactivity. I am trying your info above, but am finding that it takes a long time for it to perform an sql retrieval of *allusr objects - is this common

    ReplyDelete
    Replies
    1. The time this would take to run depends upon how many non-IBM libraries and objects you have.
      I would create a program to create an output table, submit it to batch, but not the one used by your users, etc. (QPGMR?) and just let it run.
      It would take just as long if you were using DSPOBJD command.

      Delete
  3. This is a fantastic table function, thank you for taking the time to share your experiences and usable examples. I found an immediate use so my development team can verify their enhancements are deployed to our test and production systems.

    Never hurts to double check everything, and this tool gives use the ability to do so easily.

    ReplyDelete
    Replies
    1. The Db2 for i team is really spoiling us with a lot of really cool stuff!

      And thank you for the compliment.

      Delete
  4. Is there some reason that you don't use the table function for finding the source? When I compared the table function results with DSPOBJD, I get the same values.

    select objname, objtype, objattribute, source_library, source_file, source_member, source_timestamp, created_system
    from table(qsys2.object_statistics('RDIEDRICH','*ALL'));

    ReplyDelete
  5. Thank you for this, how would you use a variable for library name? I would like to read from a file, and get the oldest used object date from each library that is defined as a variable.

    ReplyDelete
    Replies
    1. Are you meaning the date and time when an object was first used?
      If so, I don't think that information is stored. Although last used date and time is available.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.