Wednesday, February 5, 2020

Object owner information from SQL View

object_ownership sql view rather than wrkobjown

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:

 

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

3 comments:

  1. weird, I get *POINTER in the PATH_NAME for type=*STMF

    ReplyDelete
    Replies
    1. 1. Stop using STRSQL. It is old and outdated as you have found. use ACS's Run SQL Scripts instead. Better tool and comes with built in examples.

      2. CAST the column to something like CHAR(2500).

      Delete
    2. yeah! much better
      thanks for the info 👍

      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.