Gain flexibility by using these IBM i Services to manage owned objects.

I recently had a client that had a set of profiles that shouldn’t have any owned objects but did. In this article, I describe how I used IBM i Services to get more information about these objects so we could determine what do to with them (that is, delete or reassign ownership).

Step 1: Discover the Scope of the Problem

We ran into this situation when we went to delete one of the profiles belonging to someone who had left the organization. We couldn’t delete the profile because it owned objects. Because this profile owned objects, I wondered if there were others. Rather than run the Work with Objects Owned (WRKOBJOWN) command for each profile, I decided to use the following SQL to find the list of profiles that owned objects.

SELECT * FROM QSYS2.OBJECT_OWNERSHIP

   WHERE AUTHORIZATION_NAME like 'CON%'

   AND OBJECT_TYPE <> '*MSGQ';

Things to note:

• Authorization_Name equals profile name
• In this case, the set of profiles that weren’t supposed to own objects started with a specific naming convention—in this case CON, but you can specify a specific name or even members of a group profile. To get the list of objects owned by members of a specific group (in this example, GROUP1), you’d use the following SQL:

SELECT * FROM QSYS2.OBJECT_OWNERSHIP

WHERE authorization_name

in (select user_profile_name

from qsys2.group_profile_entries

where group_profile_name = 'GROUP1' )

AND OBJECT_TYPE <> '*MSGQ';

• I’ve eliminated message queues. Profiles always own their own *MSGQ. The message queue is automatically deleted by the system when the profile is deleted, so I didn’t want those objects included in the list. I wanted the list of objects that would prevent the profile from being deleted.

I ran the SQL, hoping we had run into an exception, but as it turns out, several other profiles also owned objects.

To get this list of profiles and their owned objects into a format that I could more easily work with and analyze, I took one more step. With a couple of exceptions, I run my SQL in the Run SQL Scripts feature of Access Client Solutions (ACS). Rather than work with the list of profiles/objects in the output section Run SQL Scripts, I sent the list to a spreadsheet. To accomplish that, I chose Options from the toolbar, then used Enable Save Results > For This Session. I reran the last SQL statement, clicked on a cell in the output section, right-clicked, and chose Save Results…. From there, I was given various formats to choose from (.txt, .csv, .xlsx, etc.) as well as the ability to specify where the output was to be saved. Saving the results to a spreadsheet made our analysis much simpler.

Step 2: Get More Information

You may have more, but in my case, we had two options: Change the ownership of the objects to a more appropriate profile or delete the object. Personally, when I find myself in this situation, I like to take this opportunity to do a bit more investigation, and if the object isn’t being used, delete it rather than change the owner. To get the information needed for this investigation, I used the Object_Statistics table function. The information provided is similar to what you’d get by running Display Object Description (DSPOBJD). The following SQL provides information about the data area DATAAREA2 in library PROD_LIB1.

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('PROD_LIB1','*DTAARA'))

   WHERE OBJNAME = 'DATAAREA2'

I like this table function for its flexibility. For example, unlike DSPOBJD, I can customize the SQL to give me information on objects owned by a specific profile. The following example lists the objects owned by profile CONWOODCJ:

SELECT *

   FROM TABLE (

           QSYS2.OBJECT_STATISTICS('LIB_NAME', '*ALL')

       )

   WHERE OBJOWNER = 'CONWOODCJ';

Finally, I can add a WHERE clause that will allow me to see objects not used within the last year and would, therefore, be a candidate for deletion:

SELECT *

   FROM TABLE (

           QSYS2.OBJECT_STATISTICS('LIB_NAME', '*ALL')

       )

   WHERE OBJOWNER = 'CONWOODCJ'

        AND (LAST_USED_TIMESTAMP < CURRENT TIMESTAMP - 1 YEAR);

For IFS objects, you’ll need to use the IFS_Object_Statistics table function:

SELECT *

   FROM TABLE (

           QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/dir-name', SUBTREE_DIRECTORIES => 'YES')

       )

   WHERE OBJECT_OWNER = 'CONWOODCJ';

Timestamp arithmetic works here as well:

SELECT *

   FROM TABLE (

           QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/dir-name', SUBTREE_DIRECTORIES => 'YES')

       )

   WHERE OBJECT_OWNER = 'CONWOODCJ'

         AND (LAST_USED_TIMESTAMP < CURRENT TIMESTAMP – 6 months);

Note: Be careful when you’re evaluating *DIR objects. The last used date isn’t updated for directories.

By using the Object_Statistics and IFS_Object_Statistics services, I was able to provide my client with the list of objects that were potential candidates for deletion. I sent the information to a spreadsheet (as I described in Step 1), and the client submitted the output for review at their change management meeting prior to taking action.

Step 3: Resolving the Issue

It should go without saying that if you choose to delete the object, you should first back it up or verify that it’s on a recent backup. If you don’t, you’ll be sure to need it!

But what if you choose to change the ownership? I prefer to use the Change Owner (CHGOWN) command. Unless you have just one or two objects and can easily run Change Object Owner (CHGOBJOWN) or take option 9 from WRKOBJOWN, I find it easier to use CHGOWN regardless of whether the object is in the IFS or a library. This is especially true when there are many objects to change. CHGOBJOWN doesn’t allow for generics, so changing many or all objects in a library is very tedious. However, you can use CHGOWN and change the owner with one simple command. The following code changes all objects in the library CAROLNEW to be owned by CWOODBURY:

CHGOWN OBJ('/qsys.lib/carolnew.lib/*.*') NEWOWN(CWOODBURY) SUBTREE(*NONE)

If I want to be a bit more selective, I can further qualify the path to just include *FILE objects:

CHGOWN OBJ('/qsys.lib/carolnew.lib/*.file') NEWOWN(CWOODBURY) SUBTREE(*NONE)

When you go to change the ownership of objects in the IFS, you’ll want to be careful when specifying the SUBTREE parameter. If the path you’ve specified in the OBJ parameter has several subdirectories, specifying SUBTREE(*ALL) will go through all of the subdirectories and change the ownership of all of those objects. That’s fine if that’s your intent. But if you want to limit your change to just the objects in that specific path, be sure to specify SUBTREE(*NONE). The following command changes the ownership of all objects in /Prod Directory/App Directory1 but will not change the directory itself, nor will it change the ownership of any subdirectories or the contents of the subdirectories:

CHGOWN OBJ('/Prod Directory/App Directory1/*.*') NEWOWN(CWOODBURY) SUBTREE(*NONE)

How Do Profiles End Up Owning Objects?

The situation of profiles owning objects when you don’t think they should probably sounds familiar to you. I’m sure many of you have configured the profile so that the profile’s OWNER parameter is set to *GRPPRF, meaning that the ownership of any object the profile creates is transferred to the group profile specified in the Group profile (GRPPRF) parameter. Yet you’ve been prevented from deleting a profile because it owns objects. So the question is…how, with this being the profile’s configuration, can the profile end up owning objects? Let me explain. The most common case is IFS objects (directories, stream files, etc.). Profiles retain ownership of these objects because, when the object is created in the IFS, the system ignores the OWNER parameter. If the profile creates an object in the IFS, it will own it, pure and simple. So if the profile has created objects in the IFS, you will always have to take some action on those objects prior to deleting the profile. Perhaps, however, you have a profile configured with OWNER(*GRPPRF) and the profile owns objects in libraries. How can that happen? Well, one way I’ve found that to occur is if someone has blatantly run Change Object Own (CHGOBJOWN) on the object. In that situation, the profile owns the object and it’s not transferred to its group.

Summary

This was a relatively simple example of using IBM i Services to solve a real-world problem. I wanted to provide a simple example because I think a lot of people believe that SQL solves only complex problems, or it takes complex SQL to solve a simple problem. Obviously, that’s not the case. I encourage you to give these services a try. You can find the complete list here.