Identifying and changing object owners

Before I delete an user profile I always want to identify which objects it owns, and then transfer them to another profile or profiles. I can see which objects they own using the Work with Object by Owner command, WRKOBJOWWN. If there are a lot of objects in the results I do not find this user interface helpful.

Db2 for i offers us an alternative, the OBJECT_OWNERSHIP table function. I have written about the OBJECT_OWNERSHIP view before, it would appear I overlooked the table function.

The advantage of using a table function is that only the results for the parameters passed are found, like calling an API. With a view the rows are selected according to the selection criteria, which is like searching a file or table. In various situations, one may have an have advantages over the other. In this scenario, I only want the information for one user profile, the table function is more efficient.

The syntax for the OBJECT_OWNERSHIP is simple, as it only needs one parameter passed to it, the user profile:

01 SELECT * 02 FROM TABLE(QSYS2.OBJECT_OWNERSHIP(' < user profile > '))

In the scenario I am going to write about I want to identify all of the objects owned by the user profile OLD_USER, and then change the objects to be owned by my user profile, SIMON.

Let me start with identifying all of the non-IFS objects owned by OLD_USER.

01 SELECT OBJECT_TYPE AS "Type", 02 OBJECT_LIBRARY AS "Library", 03 OBJECT_NAME AS "Object", 04 TEXT_DESCRIPTION AS "Description" 05 FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER')) 06 WHERE PATH_NAME IS NULL 07 ORDER BY OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME 08 LIMIT 5

Lines 1 – 4: I think the column names describe the contents of each of these columns. I am adding column headings to these columns to make it easier to display on this page.

Line 5: I call the OBJECT_OWNERSHIP table function, passing to it the OLD_USER user profile.

Line 6: The path name column is not null for objects in the IFS. As I am interested only in non-IFS objects this column will always be null.

Line 7: I want the results to be ordered by object type, object library and name. I just prefer working with the returned results in that order.

Line 8: I have limited results to the first five, just for this article. If I was doing this for real I would want all the results.

The first five results returned were:

Type Library Object Description ------- ------- ---------- ------------------------ *BNDDIR MYLIB TESTBNDDIR <NULL> *CMD MYLIB WM WRKMBRPDM proxy *CMD MYLIB SP WRKSPLF proxy *CMD MYLIB GOTO3 Go to the library MYLIB3 *CMD MYLIB WO WRKOBJPDM proxy

If I want to list all of IFS objects owned by OLD_USER I would use the following:

01 SELECT OBJECT_TYPE AS "Type", 02 PATH_NAME AS "Path" 03 FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER')) 04 WHERE PATH_NAME IS NOT NULL 05 ORDER BY PATH_NAME

Lines 1 and 2: I want IFS object type and the path name of the IFS object.

Line 3: I call the OBJECT_OWNERSHIP table function, passing OLD_USER to it.

Line 4: The path name column is not null for IFS objects.

Line 5: Order the results by the path name.

I have two results, see below.

Type Path ----- ------------------------- *DIR /home/MyFolder *STMF /home/MyFolder/test.txt

Having identified all the objects that are owned by OLD_USER I need to change the owner. For non-IFS objects I will use the Change Object Owner command, CHGOBJOWN.

I always like to test I can build the command's string before I use it. My next SQL statement does that:

01 SELECT 'CHGOBJOWN OBJ(' || OBJECT_LIBRARY || '/' || 02 OBJECT_NAME || 03 ') OBJTYPE(' || OBJECT_TYPE || 04 ') NEWOWN(SIMON)' 05 FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER')) 06 WHERE PATH_NAME IS NULL 07 ORDER BY OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME 08 LIMIT 5

Lines 1 – 4: I build the CHGOBJOWN using the object library, name, and type. The double pipe ( || ) is the equivalent of the concatenate.

Line 5: I only want the non-IFS objects.

Line 7: This time I want the results ordered by object name.

The result looks like a valid CHGOBJOWN command string:

00001 ---------------------------------------------------------- CHGOBJOWN OBJ(MYLIB/ADVVIEW) OBJTYPE(*FILE) NEWOWN(SIMON) CHGOBJOWN OBJ(MYLIB/CL_DATE) OBJTYPE(*PGM) NEWOWN(SIMON) CHGOBJOWN OBJ(MYLIB/CL_DATE) OBJTYPE(*FILE) NEWOWN(SIMON) CHGOBJOWN OBJ(MYLIB/DEVSRC) OBJTYPE(*FILE) NEWOWN(SIMON) CHGOBJOWN OBJ(MYLIB/DEVSRC0) OBJTYPE(*FILE) NEWOWN(SIMON)

To perform the changing of the non-IFS objects' owner my statement would be:

01 SELECT OBJECT_TYPE AS "Type", 02 OBJECT_LIBRARY AS "Library", 03 OBJECT_NAME AS "Object", 04 TEXT_DESCRIPTION AS "Description", 05 QSYS2.QCMDEXC('CHGOBJOWN OBJ(' || OBJECT_LIBRARY || '/' || 06 OBJECT_NAME || 07 ') OBJTYPE(' || OBJECT_TYPE || 08 ') NEWOWN(SIMON)') 09 AS "Rtn code" 10 FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER')) 11 WHERE PATH_NAME IS NULL 12 ORDER BY OBJECT_TYPE,OBJECT_LIBRARY,OBJECT_NAME

Lines 1 – 4: I want to display the columns that make up the CHGOBJOWN command.

Lines 5 – 9: I am using the QCMDEXC scalar function to execute the CHGOBJOWN command. The QCMDEXC scalar function returns a value for every row of results. If the returned value is '1' then the command contained within the scalar function completed successfully. If the value is '-1' the command failed.

Line 11: Non-IFS objects only.

This statement returned many results, too many to show here. Therefore, I have taken one from each object type it changed:

Type Library Object Description Rtn code ------- ------- ---------- ------------------- -------- *BNDDIR MYLIB TESTBNDDIR <NULL> 1 *CMD MYLIB WM WRKMBRPDM proxy 1 *FILE MYLIB FCUST Customer 1 *JOBD MYLIB RPGPGM Simon Hutchinson... 1 *LIB QSYS MYLIB Simon Hutchinson... 1 *MODULE MYLIB EG068SRVP <NULL> 1 *MSGQ QUSRSYS SIMON Simon Hutchinson... 1 *OUTQ MYLIB RPGPGM <NULL> 1 *PGM MYLIB DLTRCDS Add data to FDLT... 1 *SRVPGM MYLIB MYVARIABLE SQL VARIABLE MYV... 1

To change the owner for IFS objects I need to use the Change Owner command, CHGOWN.

This is my example code showing how I would format CHGOWN's command string:

01 SELECT 'CHGOWN OBJ(''' || PATH_NAME || ''') NEWOWN(SIMON)' 02 FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER')) 03 WHERE PATH_NAME IS NOT NULL 04 ORDER BY PATH_NAME

Line 1: Make the CHGOWN command strings.

The results look good:

00001 -------------------------------------------------------------------- CHGOWN OBJ('/home/RPGPGM') NEWOWN(SIMON) SYMLINK(*YES) CHGOWN OBJ('/home/RPGPGM/test.txt') NEWOWN(SIMON) SYMLINK(*YES)

Now to change the owner for these IFS objects:

01 SELECT OBJECT_TYPE AS "Type", 02 PATH_NAME AS "Path", 03 QSYS2.QCMDEXC('CHGOWN OBJ(''' || PATH_NAME || 04 ''') NEWOWN(SIMON)') 05 AS "Rtn code" 06 FROM TABLE(QSYS2.OBJECT_OWNERSHIP('OLD_USER')) 07 WHERE PATH_NAME IS NOT NULL 08 ORDER BY PATH_NAME

Lines 1 and 2: I want to display the IFS object type and the path name in the results.

Lines 3 and 4: Using the QCMDEXC scalar function to execute the CHGOWN command string.

As the return code is '1' for both rows, the IFS objects have had their owner changed.

00001 Rtn code --------------------------------------------------------------- -------- CHGOWN OBJ('/home/RPGPGM') NEWOWN(SIMON) SYMLINK(*YES) 1 CHGOWN OBJ('/home/RPGPGM/test.txt') NEWOWN(SIMON) SYMLINK(*YES) 1

To make the changes the way I have described above is too complicated to do more than once. Therefore, I have made a RPG program to change the owner.

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ccsid(*char : 1208) ; 03 dcl-proc Main ; 04 dcl-s OldUser varchar(10) inz('OLD_USER') ; 05 dcl-s NewUser like(OldUser) inz('SIMON') ; 06 exec sql DROP TABLE IF EXISTS QTEMP.MY_CHANGE_OWNERS ; 07 exec sql CREATE TABLE QTEMP.MY_CHANGE_OWNERS 08 FOR SYSTEM NAME "MYCHGOWN" 09 (OBJECT VARCHAR(100), 10 RETURN_CODE NUMERIC(1)) ; 11 exec sql 12 INSERT INTO MY_CHANGE_OWNERS 13 SELECT OBJECT_LIBRARY concat '/' concat OBJECT_NAME concat 14 ' (' concat OBJECT_TYPE concat ')', 15 QSYS2.QCMDEXC( 16 'CHGOBJOWN OBJ(' concat OBJECT_LIBRARY concat 17 '/' concat OBJECT_NAME concat 18 ') OBJTYPE(' concat OBJECT_TYPE concat 19 ') NEWOWN( ' concat :NewUser concat ')') 20 FROM TABLE(QSYS2.OBJECT_OWNERSHIP(:OldUser)) 21 WHERE PATH_NAME IS NULL 22 ORDER BY OBJECT_TYPE ; 23 exec sql 24 INSERT INTO MY_CHANGE_OWNERS 25 SELECT PATH_NAME, 26 QSYS2.QCMDEXC( 27 'CHGOWN OBJ(''' concat PATH_NAME concat ''') ' concat 28 'NEWOWN(' concat :NewUser concat ') ') 29 FROM TABLE(QSYS2.OBJECT_OWNERSHIP(:OldUser)) 30 WHERE PATH_NAME IS NOT NULL 31 ORDER BY PATH_NAME ; 32 on-exit ; 33 end-proc ;

Line 2: I am using a Main procedure in this program, to not use the RPG cycle. I also have my favorite control option, *SRCSTMT, and I am not running the program in the default activation group. This is something I have only used a few times, the CCSID. I am using it here to convert the program's default CCSID to 1208. Why am I doing that? This partition's default CCSID is 65535. This is not compatible with the path name returned by OBJECT_OWNERSHIP, which is CCSID 1208. By using CCSID to change the program's character set to 1208 this is no longer an issue.

Line 3: Start of the Main procedure.

Line 4 and 5: The two variables contain the two user profiles. If this was not an example I would have passed the two user names to this program.

Line 6: I am deleting the output file, if it exists, from the library QTEMP.

Lines 7 - 10: As the QCMDEXC scalar function returns a return code I need to capture that into a table, giving me the ability to check if all the ownership changes were successful. I have given the table a short system name, MYCHOWN, to make it easy to find with IBM i commands. The file has just two columns:

1. OBJECT:  This field will contain the object library, name, and type for non-IFS objects, and the path name for IFS objects.
2. RETURN_CODE:  This will contain the return code from QCMDEXC.

Lines 11 – 22: This statement inserts the data from changing the object ownership for the non-IFS objects.

Lines 13 and 14: I am making data for the OBJECT column. For non-IFS objects I am using the following format:

< object library >/< object name > ( < object type > )

Lines 15 – 19: This is the QCMDEXC scalar function to change the objects' owner. I was disappointed to find that I could not use the double pipe ( || ) to denote concatenation, therefore, I have had to use 'concat' instead.

Lines 23 – 31: This is the statement that changes the owner of the IFS objects.

Line 29: The tripe apostrophes ( ''' ) are needed so that a single apostrophe ( ' ) will surround the path name.

After running the program I can look to see what changed:

01 SELECT * FROM QTEMP.MY_CHANGE_OWNERS LIMIT 5

This returns:

OBJECT RETURN_CODE -------------------------- ----------- MYLIB/TESTBNDDIR (*BNDDIR) 1 MYLIB/WM (*CMD) 1 MYLIB/SP (*CMD) 1 MYLIB/GOTO3 (*CMD) 1 MYLIB/WO (*CMD) 1 /home/MyFolder 1 /home/MyFolder/test.txt 1

If I wanted to check for anything that was not changed I would use the following statement:

01 SELECT * FROM QTEMP.MY_CHANGE_OWNERS WHERE RETURN_CODE = -1

With my work there were no errors:

OBJECT RETURN_CODE -------------------------- -----------

I hope you will find this program as helpful as I do. I know I will be using this in the next weeks.

 

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