Access System Directory information via SQL

I was asked for a list of all the enabled user profiles that are enrolled in the system directory on a partition. I know I could use the Display Directory Entry command, DSPDIRE, to create an outfile of the information:

DSPDIRE OUTPUT(*OUTFILE) OUTFILE(QTEMP/WOKFILE) DETAIL(*FULL) OUTFILFMT(*TYPE1)

Surely I would only want to retrieve the information for enabled profiles, or perhaps have a way to validate if a single user profile is enrolled in the system directory.

After some poking around I found a number of files in the library QUSRSYS that would give me the information I wanted. As the data is in files I can use SQL to get data and format the results in a way I would want, in real time.

I have searched in IBM's Documentation center and can find no mention of these files, therefore, it has been a case of comparing the data from these files with the WRKDIRE command that has allowed me to determine their functionality.

The first file I found useful was QAOKP01A. I found three fields here that would allow me to provide a list of the user ids and user profiles for the system directory entries. These fields are:

1. WOS1DDEN:  User id, not profile
2. WOS1DDGN:  System name
3. WOS1USRP:  Actual user profile

A sample of what I found on the partition I use for writing these posts can be generated using the following SQL statement:

SELECT WOS1DDEN AS "User id", WOS1DDGN AS "System", WOS1USRP AS "User profile" FROM QUSRSYS.QAOKP01A ORDER BY 1,2

I have given the three columns in the results column headings as the file's field names are at best unclear.

The results look like:

User id System User profile -------- -------- ------------ QBRMS THISSYS QBRMS QDFTOWN QDFTOWN QDFTOWN QDOC QDOC QDOC QLPAUTO QLPAUTO QLPAUTO QLPINSTL QLPINSTL QLPINSTALL QNETSPLF THISSYS QNETSPLF QPGMR THISSYS QPGMR QRMTCAL QRMTCAL QRMTCAL QSECOFR QSECOFR QSECOFR QSYS QSYS QSYS

When I looked at other files I found that the WOS1DDEN and WOS1DDGN are the keys that links all of this data together.

The next useful file I found the file that contains the descriptions, QAOKP08A. In this file there are only three fields I am interested in:

1. WOS8DDEN:  User id
2. WOS8DDGN:  System name
3. WOS8DESC:  Description

The statement I used to view the descriptions for the user ids was:

SELECT WOS8DDEN AS "User id", WOS8DDGN AS "System", WOS8DESC AS "Description" FROM QUSRSYS.QAOKP08A ORDER BY 1,2

Which gives me:

User id System User profile -------- -------- ------------------------------------------ QBRMS THISSYS QBRMS QDFTOWN QDFTOWN Default owner QDOC QDOC Internal Document Owner QLPAUTO QLPAUTO Licensed Program Automatic User QLPINSTL QLPINSTL Licensed program install QNETSPLF THISSYS Used for remote distribution of spool files QPGMR THISSYS QPGMR QRMTCAL QRMTCAL OV/400 Remote Calendar User QSECOFR QSECOFR Security Officer QSYS QSYS Internal System User Profile

Before I join these two files I need to know the user profile's status, whether it is enabled or disabled. The best place for me to get that information is from the USER_BASIC_INFO view. I only need to use two columns from this view:

1. AUTHORIZATION_NAME:  User profile
2. STATUS:  User profile status. Which will be either *ENABLED or *DISABLED

Now I can create one statement that will list all the user profiles, their descriptions from the system directory, user profile status, and user profile description:

01 SELECT A.WOS1USRP AS "User profile", 02 B.WOS8DESC AS "Description", 03 C.STATUS AS "Profile status", 04 C.TEXT_DESCRIPTION AS "Profile description" 05 FROM QUSRSYS.QAOKP01A A 06 JOIN QUSRSYS.QAOKP08A B 07 ON (A.WOS1DDEN,A.WOS1DDGN) = (B.WOS8DDEN,B.WOS8DDGN) 08 LEFT OUTER JOIN QSYS2.USER_INFO_BASIC C 09 ON A.WOS1USRP = C.AUTHORIZATION_NAME 10 ORDER BY 1

Line 1: The user profile from the file QAOKP01A.

Line 2: The system directory description from the file QAOKP08A.

Lines 3 and 4: User profile status and description from the USER_INFO_BASIC view.

Line 5 – 7: Defining the first two files and the type of join to combine the data, and on line 7 the fields to join the data with. This is an easier way to enter the join fields, rather than:

07 ON A.WOS1DDEN = B.WOS8DDEN 07 AND A.WOS1DDGN = B.WOS8DDGN

Either way is acceptable.

Lines 8 and 9: I am using a left outer join to join the system directory data to the USER_INFO_BASIC. If there is not a matching user profile in USER_INFO_BASIC, the profile has been deleted but the system directory entry still exists, then the last two columns will be null.

Line 10: And I am sorting my results by the first column, WOS1USRP, user profile.

This is a sample of the results produced when I run the SQL statement:

User profile Description Profile status Profile description ------------ -------------- -------------- ------------------- ACOX Alexander Cox *DISABLED Cox, Alexander CEVANS Craig Evans *ENABLED Evans, Craig DCOLLINS Daniel Collins <NULL> <NULL> DMITCHELL David Mitchell *ENABLED Mitchell, David RGUNN Rob Gunn *ENABLED Gunn, Robert

As I said before Daniel Collin's user profile status and description are null as there is no longer a profile of DCOLLINS in this partition.

There was one more file I found that might be useful, QAOKPSRA. This file appears to contain the details of the system directory entry. If I wanted to see my system directory entry I would use the following command:

DSPDIRE USRID(SIMON THISSYS)

This shows all of my detailed information, over several screens:

Display Directory Entry Details User ID/Address . . . . : SIMON THISSYS Description . . . . . . : Hutchinson, Simon System name/Group . . . : THISSYS User profile . . . . . : SIMON Network user ID . . . . : SIMON THISSYS Name: Last . . . . . . . . : Simon First . . . . . . . . : Hutchinson Middle . . . . . . . : D Preferred . . . . . . : Simon Full . . . . . . . . : Simon Hutchinson Department . . . . . . : IT Job title . . . . . . . : Author Company . . . . . . . . : RPGPGM.COM Telephone numbers . . . : 512-000-0000 FAX telephone number . : N/A Location . . . . . . . : Headquarters Building . . . . . . . : Main Office . . . . . . . . : Main Mailing address . . . . : 123 Main Street Austin, TX 78700

I only need three fields from the file QAOKPSRA:

1. WOKSDEN:  User id
2. WOKSFLD:  Field name
3. WOKSVAL:  Field value

With the following statement, containing those three columns, I can get the same information as from the DSPDIRE command:

SELECT WOKSDEN AS "User id", WOKSFLD AS "Field", WOKSVAL AS "Value" FROM QUSRSYS.QAOKPSRA WHERE WOKSDEN = 'SIMON'

The results are:

User id Field Value ------- --------- ------ U4142SH USER SIMON U4142SH USRID SIMON U4142SH USRADDR THISSYS U4142SH SYSNAME *LCL U4142SH USRD HUTCHISON,SIMON U4142SH FSTNAM HUTCHINSON U4142SH PREFNAM SIMON U4142SH MIDNAM D U4142SH NETUSRID SIMONTHISSYS U4142SH ALWSYNC 0 U4142SH DLOOWN 0 U4142SH LSTNAM SIMON U4142SH FULNAM SIMONHUTCHINSON U4142SH TITLE AUTHOR U4142SH CMPNY RPGPGM.COM U4142SH TELNBR1 512-000-0000 U4142SH FAXTELNBR N/A U4142SH LOC HEADQUARTERS U4142SH BLDG MAIN U4142SH OFC MAIN U4142SH ADDR1 123MAINSTREET U4142SH ADDR2 AUSTIN,TX78700

I am not sure when I would want this level of detail, but if I ever do I now know where to find it.

 

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