Which libraries did Jane create?

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.