Identifying IBM's libraries

I have always wanted the best way to identify which libraries, on the IBM i partition I was using, belong to IBM. I had come up with doing things like considering all libraries that start with "Q", "SYS", and "#". But that was never a guarantee to separate IBM's from others' libraries.

IBM gave me a way to identify their libraries, using the SQL OBJECT_STATISTICS table function. It allowed me to identify most of the IBM libraries, but not all. I have made some additions to what they told me to make it mostly work.

I need to use three columns from OBJECT_STATISTIC results:

• OBJNAME:  Name of the returned object, which in this case will be a library
• OBJDEFINER:  User profile who created the object
• OBJNAME:  User profile that owns the object

IBM told me that any libraries created by the profiles QLPINSTALL and *IBM would allow me to identify what should be considered IBM libraries. On further investigation on several IBM i partitions I have found what I would also consider to be IBM libraries that were created by the user profile QSYS, or are owned by QSYS.

The statement I came up with is:

01 SELECT OBJNAME,OBJDEFINER,OBJOWNER,TEXT 02 FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*LIB')) 03 WHERE OBJDEFINER IN ('QLPINSTALL','*IBM','QSYS') 04 OR OBJOWNER = 'QSYS' 05 ORDER BY OBJNAME

Line 1: I want the library name, object definer, object owner, and the text for the object in my results.

Line 2: I want OBJECT_STATISTICS to return all the objects that are libraries.

Line 3: Select objects (libraries) that were created by user profiles "*IBM", "QLPINSTALL", and "QSYS".

Line 4: Or objects (libraries) owned by the user profile "QSYS".

Line 5: To make the results easier to understand I ordered the results by library name.

I need to give you some words of caution: I ran the above statement on several partitions running different versions of IBM i all but one returned what I expected. The one partition that did not have some, of what I would consider to be IBM libraries, with the object definer of "QSECOFR". I do not know why this is so. I strongly recommend you run the above SQL statement on the partition you intend to use this on to ensure that it includes all the libraries you would have expected to be IBM's libraries. If it does not, you need to alter the statement to add or exclude other libraries.

How would I use this information? I would create a SQL view:

01 CREATE OR REPLACE VIEW MYLIB.NOT_IBM_LIBRARY 02 FOR SYSTEM NAME "NOTIBMLIB" 03 (LIBRARY,LIBDEFINER,LIBOWNER,LIBTEXT) AS 04 SELECT OBJNAME,OBJDEFINER,OBJOWNER,TEXT 05 FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL','*LIB')) 06 WHERE OBJDEFINER NOT IN ('QLPINSTALL','*IBM','QSYS') 07 AND OBJOWNER <> 'QSYS'

Line 1: I am calling my view NOT_IBM_LIBRARY.

Line 2: As that name is longer than ten characters, I am giving it a system (short) name too, NOTIBMLIB.

Line 3: I have a column list as I want to change the name of the columns from the names in OBJECT_STATISTICS to something more unique.

Lines 4 – 7: The Select that selects the rows to be returned from the view. The differences between this statement and the previous one are that the selections have been reversed. I want all the rows where the object definer, line 6, is not one of those values, and the object owner, line 7, is not "QSYS".

I can use this view to give me a list of all of the non-IBM libraries on this partition:

01 SELECT * FROM NOT_IBM_LIBRARY

I will not show the results as they will be different from what you would see, as I don't have the same libraries on my partition that you do.

 

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