Object owner information from SQL View

Tucked within the latest round of Technology Refreshes for IBM i for 7.4, TR1,and 7.3, TR7, was the addition of a SQL View, OBJECT_OWNERSHIP. This View returns the same information as the Work Object Owner command, WRKOBJOWN. I do not use WRKOBJOWN frequently, but I do before deleting an user profile to see which objects are owned by the profile, and an intermittent check on the production libraries to make sure that all of the objects are owned by the generic profile created for this purpose.

Alas, I am restricted on the IBM i partition I use to write these posts to only being able to see objects I own. Therefore, my examples will only show these objects.

The WRKOBJOWN is one of the easier commands to use. I can either just type it at a command line and press Enter, or press F4 to prompt to view the command's parameters.

Work with Objects by Owner (WRKOBJOWN) Type choices, press Enter. User profile . . . . . . . USRPRF *CURRENT Object type . . . . . . . OBJTYPE *ALL + for more values

When the objects are displayed I get a full list of every object I own, by object type:

Work with Objects by Owner User profile . . . . . . . : SIMON Type options, press Enter. 2=Edit authority 4=Delete 5=Display authority 7=Rename 8=Display description 9=Change owner ASP Opt Object Library Type Attribute Device _ /MyFolder/list.txt *STMF *SYSBAS _ /MyFolder/report.c > *STMF *SYSBAS _ /MyFolder/test_fil > *STMF *SYSBAS _ /MyFolder/xmlfile. > *STMF *SYSBAS _ /home/MyFolder *DIR *SYSBAS _ /MyFolder *DIR *SYSBAS _ MYJOBQ QGPL *JOBQ *SYSBAS _ MYJOBQ2 QGPL *JOBQ *SYSBAS

So how can I get similar results to the above from this View?

01 SELECT AUTHORIZATION_NAME AS "User", 02 OBJECT_NAME AS "Object", 03 OBJECT_LIBRARY AS "Obj lib", 04 OBJECT_TYPE AS "Obj typ", 05 OBJECT_ATTRIBUTE AS "Attrib", 06 PATH_NAME AS "Path" 07 FROM QSYS2.OBJECT_OWNERSHIP 08 ORDER BY 4,3,2

Lines 1 - 6: I consider the long column names sufficient to explain the contents of the column, therefore, I do not see the point of describing what each column is. I am giving ever column a new column heading. The only reason I do this is so that when I show the results below the columns fit the width of this post.

Line 7: The View OBJECT_OWNERSHIP is found in the library (schema) QSYS2.

Line 8: I am being lazy here defining the sort order. Rather than list the columns to sort I am giving the position of the column in the results. Thus:

• 4 = OBJECT_TYPE
• 3 = OBJECT_LIBRARY
• 2 = OBJECT_NAME

I have pared the results to just a few rows of data as there were so many returned.

User Object Obj lib Obj typ Attrib Path ----- ---------- ------- ------- ------ ------------------- SIMON TEST MYLIB *BNDDIR - - SIMON LOOP MYLIB *CMD - - SIMON WM MYLIB *CMD PRX - SIMON - - *DIR - /home/MyFolder SIMON QSQ_S00001 QTEMP *FILE PF - SIMON PERSON MYLIB *FILE PF - SIMON L4001D MYLIB *FILE DSPF - SIMON MYMENU MYLIB *MENU UIM - SIMON QEEAD29B59 QRPLOBJ *MODULE RPGLE - SIMON MODULE1 MYLIB *MODULE RPGLE - SIMON QEC1A19FC3 QRPLOBJ *PGM RPGLE - SIMON PROGRAM1 MYLIB *PGM RPGLE - SIMON QF086B5E7F QRPLOBJ *SRVPGM RPGLE - SIMON SRVPGM1 MYLIB *SRVPGM RPGLE - SIMON - - *STMF - /MyFolder/report.csv

First thing I noticed in these results is it includes objects in the QRPLOBJ and QTEMP libraries. If you do not know what the QRPLOBJ library I have provided a link at the bottom of this post to its purpose. I don't want those libraries in my results. I also want to have the object name or path name in the second column, like the WRKOBJOWN command results.

My new Select statement looks like:

01 SELECT AUTHORIZATION_NAME AS "User", 02 CASE WHEN OBJECT_NAME IS NOT NULL THEN OBJECT_NAME 03 ELSE PATH_NAME 04 END AS "Object", 05 OBJECT_TYPE AS "Obj typ", 06 OBJECT_LIBRARY AS "Obj lib", 07 OBJECT_ATTRIBUTE AS "Attrib" 08 FROM QSYS2.OBJECT_OWNERSHIP 09 WHERE OBJECT_LIBRARY NOT IN ('QRPLOBJ','QTEMP') 10 OR OBJECT_LIBRARY IS NULL 11 ORDER BY 3,4,2

Lines 2 – 4: Here I am using a CASE clause. What this allows me to do is to create a new column that contains a value from another. In this case if the OBJECT_NAME contains a value, i.e. it is not null, the put that in the new column, otherwise put the value from the PATH_NAME column.

Lines 9 and 10: The WHERE clause may seem a bit strange, but trust me it works. In line 9 I want to exclude objects in QRPLOBJ and QTEMP. If I do not check the OBJECT_LIBRARY is null then all the directory, *DIR, and stream file, *STMF, are omitted from the results.

Again I am only showing a few rows from the results to show what the output looks like:

User Object Obj typ Obj lib Attrib ----- ------------------ ------- ------- ------ SIMON TEST *BNDDIR MYLIB - SIMON /home/MyFolder *DIR - - SIMON PROGRAM1 *PGM MYLIB RPGLE SIMON /MyFolder/report.csv *STMF - -

When using the WRKOBJOWN command I can "drill down" into a screen that shows me the authorities the user profile has to the selected object. I can generate results that look pretty much the same as that screen, but list the authorities for more than one object.

01 SELECT AUTHORIZATION_NAME AS "User", 02 CASE WHEN OBJECT_NAME IS NOT NULL THEN OBJECT_NAME 03 ELSE PATH_NAME 04 END AS "Object", 05 OBJECT_TYPE AS "Obj typ", 06 OBJECT_LIBRARY AS "Obj lib", 07 OBJECT_AUTHORITY AS "Auth", 08 CASE WHEN OBJECT_OPERATIONAL = 'YES' THEN 'X' 09 ELSE ' ' 10 END AS "Opr", 11 CASE WHEN OBJECT_MANAGEMENT = 'YES' THEN 'X' 12 ELSE ' ' 13 END AS "Mgt", 14 CASE WHEN OBJECT_EXISTENCE = 'YES' THEN 'X' 15 ELSE ' ' 16 END AS "Exist", 17 CASE WHEN OBJECT_ALTER = 'YES' THEN 'X' 18 ELSE ' ' 19 END AS "Alter", 20 CASE WHEN OBJECT_REFERENCE = 'YES' THEN 'X' 21 ELSE ' ' 22 END AS "Ref" 23 FROM QSYS2.OBJECT_OWNERSHIP 24 WHERE OBJECT_LIBRARY NOT IN ('QRPLOBJ','QTEMP') 25 OR OBJECT_LIBRARY IS NULL 26 ORDER BY 3,4,2

Lines 8 – 22: I am using CASE clauses to "translate" the YES and NO values returned in this columns to a value of X or space so that the results for these columns will only occupy one character of space, like the WRKOBJOWN screen:

User Object Obj typ Obj lib Auth Opr Mgt Exist Alter Ref ----- ------ ------- ------- ---- --- --- ----- ----- --- SIMON L4001 *PGM MYLIB *ALL X X X X X SIMON L4002 *PGM MYLIB *ALL X X X X X SIMON L4003 *PGM MYLIB *ALL X X X X X

I can display the data authorities the same way:

01 SELECT AUTHORIZATION_NAME AS "User", 02 CASE WHEN OBJECT_NAME IS NOT NULL THEN OBJECT_NAME 03 ELSE PATH_NAME 04 END AS "Object", 05 OBJECT_TYPE AS "Obj typ", 06 OBJECT_LIBRARY AS "Obj lib", 07 OBJECT_AUTHORITY AS "Auth", 08 CASE WHEN DATA_READ = 'YES' THEN 'X' 09 ELSE ' ' 10 END AS "Read", 11 CASE WHEN DATA_ADD = 'YES' THEN 'X' 12 ELSE ' ' 13 END AS "Add", 14 CASE WHEN DATA_UPDATE = 'YES' THEN 'X' 15 ELSE ' ' 16 END AS "Upd", 17 CASE WHEN DATA_DELETE = 'YES' THEN 'X' 18 ELSE ' ' 19 END AS "Del", 20 CASE WHEN DATA_EXECUTE = 'YES' THEN 'X' 21 ELSE ' ' 22 END AS "Exe" 23 FROM QSYS2.OBJECT_OWNERSHIP 24 WHERE OBJECT_LIBRARY NOT IN ('QRPLOBJ','QTEMP') 25 OR OBJECT_LIBRARY IS NULL 26 ORDER BY 3,4,2

Lines 8 – 22: Using the CASE to return either X or space to denote the authority.

My results for the same programs I showed before are:

User Object Obj typ Obj lib Auth Read Add Upd Del Exe ----- ------ ------- ------- ---- ---- --- --- --- --- SIMON L4001 *PGM MYLIB *ALL X X X X X SIMON L4002 *PGM MYLIB *ALL X X X X X SIMON L4003 *PGM MYLIB *ALL X X X X X

I can see myself writing a program that uses this View to check if all the objects in the production libraries are owned by the appropriate user profile, and send, via email, the list of objects not owned by the desired profile.

 

You can learn more about this from the IBM website:

• OBJECT_OWNERSHIP SQL View
• What is the QRPLOBJ library?

 

This article was written for IBM i 7.4 TR1 and 7.3 TR7.