This simple utility provides a practical example of tying together several IBM i features in a way you can use moving forward.

In recent articles, we've focused on APIs. Prior to that, we explored a lot of techniques involving embedded SQL. We've also looked at some of the new DB2 services that provide system information in tables. This article will tie all of those together with a mythical ERP system to make a quick and easy utility.

The Business Issue: Utility Terminals

Many businesses have dedicated workstations whose sole purpose is to allow users to log transaction data. These are often on production lines where employees use barcode scanners just to scan lots and other production information. These are not secured terminals, and in some shops they even manage to completely bypass the 5250 sign-on entirely. That requires a little extra setup, though, and the easy compromise is to simply set up a user profile for each workstation with an easy-to-remember password. Sometimes it's as simple as just using the user profile name as the password. On barcode lines, we can even set up a label with the barcode for the workstation: scan it twice, hit Enter, and you're in. And while I know that's not particularly secure, typically these are single-purpose profiles that can only access a single terminal and have a dedicated menu and no command line. Yes, user profiles with easy access provides a different security risk (think FTP, for example), but that's not the point of today's exercise.

Today we want easy access. Because no matter how easy we make the password process, we still have users who mess it up. And we don't want a third-shift operator locking their profile out and needing a 3:00 a.m. IT intervention to reset their password. Instead, we want to create a way for the shift supervisor to handle the problem. This does two things: It insulates IT from that 3:00 a.m. call, and it also gets the shift supervisor involved so that they can identify the process problem and decide how best to fix it.

What's the Best Solution?

I've addressed this particular business problem a lot of ways over the years. Options range from the no sign-on approach mentioned above to a user-friendly password reset utility to a message queue monitor that looks for the disabled profile message and re-enables the profile. Each has its pros and cons, but this article will focus on a more application-oriented approach. We're going to assume that we have an application file that we can use to identify the utility user profiles. The fix in this case will be simple: Re-enable every utility profile that is disabled. We'll put this program on the shift supervisor menu, and if an operator locks out the profile, the supervisor can just run this program.

Designing the Program

The program itself is very simple. But even as simple as it is, it still incorporates three pretty nifty technologies: embedded SQL, DB2 services, and APIs. The API is one of the simpler ones, but it's still an API (and maybe one you haven't seen yet!).

Here's the program:

ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no) actgrp(*new);      

This is my standard control header for free-format programs. When in doubt, I default to a new activation group. It adds a little overhead but reduces some of the debugging complexities.

dcl-pr system int(10) extproc('system');                              

command pointer value options(*string);                            

end-pr;                                                              

Technology feature 1: We're using the API system, which is a slightly simpler version of QCMDEXC, as you don't have to send it a command length. With this prototype, you can use a string or even an expression (which is what this program does). The API returns 0 if the command completes successfully.

dcl-s wUserID char(10);                                              

// Join user profile service table with user profile definition file  

exec sql declare c cursor for                                        

select USER_NAME from USER_INFO                                    

   join USRDEF on USER_NAME = UDUSER                                

     where UDTYPE = 'U' and STATUS = '*DISABLED';                    

exec sql open c;                                                      

This code defines the work variable we'll be loading and the SQL from which we'll be loading it. It also introduces the other two technologies: embedded SQL and DB2 services. The table USER_INFO is a DB2 service that provides information about all the user profiles on the system in an SQL table form. One of the columns in that table is STATUS, which returns that status, either *ENABLED or *DISABLED. We join this to the hypothetical user definition file, which uses the user profile name as a key and returns the field UDTYPE, which is set to U for utility profiles.

So this cursor enumerates all utility profiles that are disabled, which is exactly what we want.

exec sql fetch next from c into :wUserID;                            

dow SQLCOD = *zeros;                                                  

// Enable user profile                                              

if system('CHGUSRPRF USRPRF(' + wUserID + ') STATUS(*ENABLED)') = 0;

   // ... success                                                    

else;                                                                

   // ... failure                                                    

endif;                                                              

// Get next disabled user profile                                  

exec sql fetch next from c into :wUserID;                          

enddo;                                                                

This is the meat of the utility, although the meat is a little lean. The basic function is there: For every entry in the cursor, execute the CHGUSRPRF command to change the status back to *ENABLED. This is what you would do manually if you were going to re-enable a user profile. The way this logic is written, it will enable multiple user profiles if more than one are disabled.

You'll note a couple of things. First, the command is actually an expression. You can make the command as complex as you'd like. You can build it in a variable and pass the variable in. You can even write a subprocedure that returns a string and use that. In any case, the correct protocol is to test the result of the command. In this case, I don't have any logic in the two status test branches because that would be application-specific. You might log the results or notify the caller if an error occurs. That's entirely up to you.

exec sql close c;                                                    

*inlr = *on;                                                          

return;                                                              

This is just the final cleanup that you would do in any SQL program. Close your cursor, set on LR, and get out. I know it's fashionable these days to use NOMAIN in your procedure, but this is a simple solution for a simple problem. I may address the issue of NOMAIN and *INLR in another article, but until then, feel free to build on this code for your own utility.