Wednesday, October 14, 2020

Which libraries did Jane create?

get the libraries owned by a specific user profile

I was asked how can I identify the libraries a person, in this example we will call her Jane, created? The person who asked the question was a self-confessed “newbie operator" who had been given some information by his employer's programmers, but doubted it was the most efficient way to get the data.

If I need to find out who created one library I would use the Display Object Description command, DSPOBJD.

DSPOBJD OBJ(MYLIB) OBJTYPE(*LIB) DETAIL(*FULL)

The following screen would be shown, and I would just look at the “Created by user" field:

                 Display Object Description - Full
                                                      Library 1 of 1
Object . . . . . . . : MYLIB      Attribute  . . . . . : PROD
  Library  . . . . . :   QSYS     Owner  . . . . . . . : QPGMR
Library ASP device . : *SYSBAS    Library ASP group  . : *SYSBAS
Type . . . . . . . . : *LIB       Primary group  . . . : *NONE

User-defined information:
  Attribute  . . . . . . . . . . :
  Text . . . . . . . . . . . . . :  For Simon Hutchinson

Creation information:
  Creation date/time . . . . . . :  08/18/19  08:23:32
  Created by user  . . . . . . . :  QSECOFR  

The questioner was given the statement shown below, and told after the output file had generated to use Query, WRKQRY, to find the libraries:

01  DSPOBJD OBJ(*ALL/*ALL) OBJTYPE(*ALL) 
02            DETAIL(*FULL) OUTPUT(*OUTFILE)
03            OUTFILE(QTEMP/@DSPOBJD)

The above DSPOBJD is going to take an extremely long time to run, as it will output every object in the partition into the outfile.

To make this more efficient I can change the parameters used by this command. As all libraries reside in the library QSYS I can change the Object parameter to only use that library. I change the Object type to look for libraries only.

01  DSPOBJD OBJ(QSYS/*ALL) OBJTYPE(*LIB) 
02            DETAIL(*FULL) OUTPUT(*OUTFILE)
03            OUTFILE(QTEMP/@DSPOBJD)

Now I have a file that just contains the libraries. The fields from this file I am interested in are:

  • ODCRTU:  Created by user
  • ODOBNM:  Object name, in this case it will be the library name
  • ODCCEN:  Create date century
  • ODCDAT:  Create date
  • ODCTIM:  Created time
  • ODOBTX:  Object text, description

Rather than use Query, I use SQL instead:

01  SELECT ODCRTU,ODOBNM,ODCCEN,ODCDAT,ODCTIM,ODOBTX
02    FROM QTEMP.@DSPOBJD
03   WHERE ODOBNM LIKE 'MYLIB%'
04   ORDER BY ODOBNM

Line 3: I just want the results for all of my libraries. They all start with: 'MYLIB' so I need to use a wildcard symbol ( % ) to find them all.

Line 4: I want the results returned in library name order.

ODCRTU   ODOBNM ODCCEN ODCDAT ODCTIM ODOBTX
-------  ------ ------ ------ ------ --------------------------------
QSECOFR  MYLIB       1 081819 082332 For Simon Hutchinson
QSECOFR  MYLIB2      1 081819 082332 For Simon Hutchinson
QSECOFR  MYLIB3      1 081819 082333 For Simon Hutchinson
QSECOFR  MYLIB4      1 081819 082333 For Simon Hutchinson
SIMON    MYLIB5      1 062520 123730 temp COLLECTION - created by SQL

There is an easier way, without the need to build a work file and then query it for the information I want. The Db2 for i view SYSSCHEMAS gives us the basic information for all the schemas in the IBM i partition. Basically a schema in IBM i is a library.

To produce the same results as before I use these columns from the view:

  • SCHEMA_CREATOR:  Who created the schema/library
  • SYSTEM_SCHEMA_NAME:  The system schema name is the library name, rather than the schema name which can be up to 128 characters
  • CREATION_TIMESTAMP:  Timestamp of when library was created
  • SCHEMA_TEXT:  Library text

Which gives me the following SQL statement:

01  SELECT SCHEMA_CREATOR,SYSTEM_SCHEMA_NAME,
02         CREATION_TIMESTAMP,SCHEMA_TEXT 
03    FROM QSYS2.SYSSCHEMAS
04   WHERE SYSTEM_SCHEMA_NAME LIKE 'MYLIB%'
05   ORDER BY SYSTEM_SCHEMA_NAME

The results are pretty much the same as before:

         SYSTEM_
SCHEMA_  SCHEMA
CREATOR  _NAME   CREATION_TIMESTAMP         SCHEMA_TEXT
-------  ------- -------------------------- -------------------------
QSECOFR  MYLIB   2019-08-18 08:23:32.000000 For Simon Hutchinson
QSECOFR  MYLIB2  2019-08-18 08:23:32.000000 For Simon Hutchinson
QSECOFR  MYLIB3  2019-08-18 08:23:33.000000 For Simon Hutchinson
QSECOFR  MYLIB4  2019-08-18 08:23:33.000000 For Simon Hutchinson
SIMON    MYLIB5  2020-06-25 12:37:30.000000 temp COLLECTION - created

This is the preferred method as I am getting the results in one step. Unlike using DSPOBJD where I have to build the file, and then get the results from that.

Now to answer the question: This is the statement I would use to list all of the libraries created by Jane:

SELECT SCHEMA_CREATOR,SYSTEM_SCHEMA_NAME,
       CREATION_TIMESTAMP,SCHEMA_TEXT 
FROM QSYS2.SYSSCHEMAS
WHERE SCHEMA_CREATOR = 'JANE'
ORDER BY SYSTEM_SCHEMA_NAME

Which gives me:

SCHEMA_  SCHEMA
CREATOR  _NAME    CREATION_TIMESTAMP         SCHEMA_TEXT
-------  -------- -------------------------- -------------------
JANE     JANESLIB 2020-04-26 13:19:20.000000 Janes library
JANE     SQLTEST3 2019-12-20 02:00:48.000000 SQL test library 3

I could get the same results by using the DSPOBJD output file I generated, but why bother to take the time to generate the output file when I can get to the same information directly.

 

You can learn more about the SYSSCHEMAS SQL view from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

1 comment:

  1. This is very cool.
    The question is ?
    Wil this work on any platform.
    We as400 walking dinosaurs need to think about this.
    How could we eliminate the reference to qysy2 for example.
    Good stuff

    ReplyDelete

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.