Making a list of users with *ALLOBJ authority is much easier with SQL

The germ for this post comes from a series of conversations I had with a colleague. He had been asked to generate a Microsoft Excel spreadsheet that contained a list of all the current enabled user profiles with All Object, *ALLOBJ, authority. This was something that had been done for years as a series of manual steps, but he decided could do it more efficiently with a CL program.

He would come to me to show me what he had done, and to say he could do it without using any SQL. It is right there are many, many processes that can be done without using SQL, but is that the efficient and timely way to do it?

I am not showing his work, but what I would do if I could not use SQL.

Firstly he would need to use the Display User Profile command, DSPUSRPRF, to make a list of all the user profiles:

01 DLTF FILE(QTEMP/WORKFILE*) 02 MONMSG MSGID(CPF2105) 03 DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC) OUTPUT(*OUTFILE) + 04 OUTFILE(QTEMP/WORKFILE)

Line 1 and 2: If the work files already exist in QTEMP, delete them.

Lines 3 and 4: The command to create an output file of all the user profiles.

The output file, WORKFILE, contains the data for all the user profiles, and many more fields of information than he needs. He only needed the following fields:

• UPUPRF:  User name
• UPUSCL:  User class
• UPPWON:  Password in *NONE
• UPSPAU:  Special authority
• UPSTAT:  Status
• UPTEXT:  Text

And he would only needs user profiles that are active, and with the All Object authority.

We had several discussions about what was the best method to copy the data from WORKFILE into another that would have only the fields and records he desired. We eventually decided to create a second output file to contain the final selected data. In his DDS definition he coded the length of all of the fields. In this example I used the system template file for DSPUSRPRF, QADSPUPB, and REFFLD to define the fields.

01 A REF(QADSPUPB) 02 A R WORKFILE2R 03 A UPUPRF R 04 A UPUSCL R 05 A UPPWON R 06 A UPSTAT R 07 A UPTEXT R

A duplicate of this file would be created in QTEMP using the Create Duplicate Object command:

05 CRTDUPOBJ OBJ(HIS_FILE) FROMLIB(*LIBL) OBJTYPE(*FILE) + 06 TOLIB(QTEMP) NEWOBJ(WORKFILE2) + 07 CST(*NO) TRG(*NO)

Now we have a from file, WORKFILE, and a to file, WORKFILE2, we could use the Copy File command to copy the desired data into his new file:

08 CPYF FROMFILE(QTEMP/WORKFILE) + 09 TOFILE(QTEMP/WORKFILE2) MBROPT(*ADD) + 10 INCCHAR(UPSPAU 1 *CT 'ALLOBJ') + 11 INCREL((*IF UPSTAT *EQ '*ENABLED')) + 12 FMTOPT(*MAP *DROP)

Lines 8 and 9: Copying the contents from WORKFILE to WORKFILE2, as the to file is empty, I can just add to it.

Line 10: The Include Characters parameter allows me to select a string contained within a field. First parameter is the field name. Second is the starting position, "1" means that the search starts in the first position. Third, *CT means contains. And finally, the string I am searching for, "ALLOBJ".

Line 11: And I only want records where the status is enabled.

Line 12: As WORKFILE2 contains a subset of the fields in WORKFILE, I need to *MAP *DROP the additional fields.

The final step is to copy the contents of WORKFILE2 to the IFS:

13 DEL OBJLNK('/home/Simon/his_file.csv') 14 MONMSG MSGID(CPFA0A9) 15 CPYTOIMPF FROMFILE(QTEMP/WORKFILE2) + 16 TOSTMF('/home/Simon/his_file.csv') + 17 FROMCCSID(37) STMFCCSID(*PCASCII) + 18 RCDDLM(*CRLF) ADDCOLNAM(*SYS)

Lines 13 and 14: If a file exists with the name I am going to use exists, delete it.

Lines 15 – 18: I am using the Copy to Import File command, CPYTOIMPF, to copy the contents of WORKFILE2 to the IFS. Alas, I cannot create a XLSX file, just a CSV.

IMHO his avoidance of using SQL made this a lot of work, unnecessary work.

I can get all the columns of data I need from the USER_INFO_BASIC SQL view. I use the basic view instead of the full, USER_INFO, as it returns the results faster than the full version as there are less columns returned.

The equivalent columns of data I need are:

• AUTHORIZATION_NAME
• USER_CLASS_NAME
• NO_PASSWORD_INDICATOR
• SPECIAL_AUTHORITIES
• STATUS
• TEXT

The statement I would use to get the information I need would be:

01 SELECT AUTHORIZATION_NAME AS "User", 02 USER_CLASS_NAME AS "User class", 03 NO_PASSWORD_INDICATOR AS "No password", 04 SPECIAL_AUTHORITIES AS "Special authorities", 05 STATUS AS "Status", 06 TEXT_DESCRIPTION AS "Text", 07 CASE USER_CLASS_NAME 08 WHEN '*SECOFR' THEN 1 09 WHEN '*SECADM' THEN 2 10 WHEN '*SYSOPR' THEN 3 11 WHEN '*PGMR' THEN 4 12 WHEN '*USER' THEN 5 13 ELSE 9 14 END AS SORT_CODE 15 FROM QSYS2.USER_INFO_BASIC 16 WHERE STATUS = '*ENABLED' 17 AND SPECIAL_AUTHORITIES LIKE '%ALLOBJ%' 18 ORDER BY SORT_CODE

Lines 1 – 6: These are the columns I need in my results.

Lines 7 – 14: I am going to add one more column, using a CASE statement, to contain a sort code that will allow me to sort by user class in its order of importance.

Lines 16 and 17: The selection criteria for the rows I need.

Line 18: I can sort the results in order of the user class importance.

The first five results returned were:

User User class No password Special authorities Text SORT_CODE --------- ---------- ----------- ---------------------- -------------- --------- ADMIN01 *SECOFR NO *ALLOBJ *SECADM... First Admin 1 ADMIN02 *SECOFR NO *ALLOBJ *SECADM... Second Admin 1 ADMIN03 *SECOFR NO *ALLOBJ *SECADM... Third Admin 1 ADMIN04 *SECOFR NO *ALLOBJ *SECADM... Fourth admin 1 ADMIN05 *SECOFR NO *ALLOBJ *SECADM... Fifth admin 1

I will be using the GENERATE_SPREADSHEET scalar function to generate an actual XLSX file.

I use the VALUES statement so that I can invoke the GENERATE_SPREADSHEET:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/Simon/allobj_users', 03 SPREADSHEET_TYPE => 'xlsx', 04 SPREADSHEET_QUERY => ' 05 SELECT AUTHORIZATION_NAME, 06 USER_CLASS_NAME, 07 NO_PASSWORD_INDICATOR, 08 SPECIAL_AUTHORITIES, 09 STATUS, 10 TEXT_DESCRIPTION, 11 CASE USER_CLASS_NAME 12 WHEN ''*SECOFR'' THEN 1 13 WHEN ''*SECADM'' THEN 2 14 WHEN ''*SYSOPR'' THEN 3 15 WHEN ''*PGMR'' THEN 4 16 WHEN ''*USER'' THEN 5 17 ELSE 9 18 END AS SORT_CODE 19 FROM QSYS2.USER_INFO_BASIC 20 WHERE STATUS = ''*ENABLED'' 21 AND SPECIAL_AUTHORITIES LIKE ''%ALLOBJ%'' 22 ORDER BY SORT_CODE', 23 COLUMN_HEADINGS => 'LABEL', 24 OVERWRITE => 'REPLACE', 25 KILL_DAEMON => 'YES') ;

I am using the GENERATE_SPREADSHEET from IBM i 7.6 TR1 and 7.5 TR7, this gives me some additional parameters that are not available for earlier releases and TRs.

Line 1: I am using the VALUES to invoke GENERATE_SPREADSHEET.

Line 2: Where I want the spreadsheet to be put in the IFS.

Line 3: Type of spreadsheet.

Lines 4 – 22: This is the same statement I showed previously. Notice that for the string values I need two apostrophes ( '' ), rather than one.

Line 23: I want the column labels be the headings in the spreadsheet.

Line 24: I want to overwrite the file that may already exists in the destination.

Line 25: This will end the Java daemon, in the past it would remain active in background of the job.

When I run the statement GENERATE_SPREADSHEET returns a code. In this case it was "1", which means the statement executed successfully.

I can use the IFS_OBJECT_STATISTICS table function to confirm that the file has been created:

01 SELECT PATH_NAME 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/Simon','NO'))

This returns:

PATH_NAME ----------------------------- /home/Simon/allobj_users.xlsx

The final version of this consists of a CL program and a source member that contains a couple of SQL statements.

The CL program looks like:

01 CHGJOB CCSID(37) 02 RUNSQLSTM SRCFILE(DEVSRC) SRCMBR(SQL_MBR) COMMIT(*NC) + 03 MARGINS(*SRCFILE)

Line 1: If your IBM i partition is using CCSID 65535 you will experience a CCSID mismatch error if you execute the GENERATE_SPREADSHEET. As I am in the USA, I am changing the CCSID to 37, US English.

Lines 2 and 3. I am using the RUNSQLSTM command to execute the SQL in the source file SQL_MBR.

That was simple. Now onto the SQL:

01 DROP TABLE IF EXISTS QTEMP.WORKFILE ; 02 CREATE TABLE QTEMP.WORKFILE (RTNCODE) AS 03 (VALUES SYSTOOLS.GENERATE_SPREADSHEET( 04 PATH_NAME => '/home/Simon/allobj_users', 05 SPREADSHEET_TYPE => 'xlsx', 06 SPREADSHEET_QUERY => ' 07 SELECT AUTHORIZATION_NAME, 08 USER_CLASS_NAME, 09 NO_PASSWORD_INDICATOR, 10 SPECIAL_AUTHORITIES, 11 STATUS, 12 TEXT_DESCRIPTION, 13 CASE USER_CLASS_NAME 14 WHEN ''*SECOFR'' THEN 1 15 WHEN ''*SECADM'' THEN 2 16 WHEN ''*SYSOPR'' THEN 3 17 WHEN ''*PGMR'' THEN 4 18 WHEN ''*USER'' THEN 5 19 ELSE 9 20 END AS SORT_CODE 21 FROM QSYS2.USER_INFO_BASIC 22 WHERE STATUS = ''*ENABLED'' 23 AND SPECIAL_AUTHORITIES LIKE ''%ALLOBJ%'' 24 ORDER BY SORT_CODE', 25 COLUMN_HEADINGS => 'LABEL', 26 OVERWRITE => 'REPLACE', 27 KILL_DAEMON => 'YES')) 28 WITH DATA ;

I need somewhere to put the return code from GENERATE_SPREADSHEET, therefore, my VALUES statement needs to be within a CREATE TABLE statement.

Line 1: I delete (DROP) the table I will be using for the return code.

Line 2: This is the start of the statement to create the table. I am calling my table WORKFILE. I also need to give a "column list" for the columns that will be in this table.

Lines 3 – 27: Are the same as the previous statement.

Line 28: I need to give this for the file it is creating to contain data.

I have to be honest I do not really care about the contents of the file.

When I called the CL program it created the XLSX file in the IFS, in less time than the prior approach. Another win for SQL!

If you running an older version of GENERATE_SPREADSHEET you will need to handle the system output yourself.

This shows that with using SQL just one program and source member I could make a XLSX. Rather one program and two files to make something is similar but is not a XLSX.

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.