Get Creative Using the SQL Database Exit Point

The System i platform has a powerful operating system, i5/OS. One example of that power is the fact that developers can tap into many features of the OS within homegrown applications by using the provided Application Programming Interfaces (APIs). Further, developers are allowed, via exit points, to create exit programs that interact with the system by responding to certain events that occur, such as when an FTP, SQL or Telnet request is made by a client application. This kind of versatility allows a developer to have fine-tuned control over a wide array of events on the system.

In this article, I first want to discuss one particular exit point–the SQL database server exit–and illustrate how it can be used to make your System i more secure. You can take the principles applied in the given example and apply them to a wide range of exit points available on the System i. Second, I’ll relate an experience of how the ability to use these exits creatively allowed me to avoid hours of time that would have been required to do some major re-coding of an application.

For those familiar with event driven programming, think of an exit point as a programmable event that occurs on the System i and an exit program as the code that is optionally defined to run when a specific event occurs.

The SQL database server exit point provides information to a user defined program about incoming SQL requests. (All SQL requests incoming from another computer using JDBC, ODBC, OLE DB, .NET, etc.) It provides information such as the user making the request, the SQL statement text, the package name, the default SQL collection (library), and so forth. Typically exit programs are used to implement various security restrictions based on this information. (For example, perhaps only certain users are allowed to make ODBC or JDBC requests to the iSeries.) Based on the information passed, an exit program can decide whether or not the request should be allowed to run. Sometimes, therefore, these exit programs are referred to as security exits.

There are actually two SQL database server exit points: QIBM_QZDA_SQL1 and QIBM_QZDA_SQL2. They are essentially the same with the exception that exit point QIBM_QZDA_SQL2 provides a little more information. We’ll be using exit QIBM_QZDA_SQL2.

Writing an exit program is extremely easy. For exit QIBM_QZDA_SQL2, one only needs to code two parameters: a one-character field defining whether the program should accept or reject a database request; and a data structure containing information about the request. The table below (taken from IBM’s Web site shows the format of this data structure, which is referred to as ZDAQ0200.

Figure 1: Exit point QIBM_QZDA_SQL2 format ZDAQ0200

Thankfully, source file QSYSINC/QRPGLESRC has member EZDAEP which contains the parameter structure definitions for many exit programs including QIBM_QZDA_SQL2. Within this member, structure EZDSQLF2 contains the parameter list for format ZDAQ0200 so that we don’t have to type it. With that in mind, we can quickly construct a simple program to accept or reject any external SQL database request based on criteria such as the requesting user profile. Below is sample RPG program DBSEXIT:

 //
 // DBSEXIT - Database Server Exit Example
 //
 // This sample program demonstrates how to restrict access to
 // external SQL database calls based on user profile.
 //
 // QSYSINC is installed with the SYSTEM OPENESS INCLUDE
 // licensed program (part of the base OS).
 //
 /Copy QSYSINC/QRPGLESRC,EZDAEP 

DAcceptRequest    S              1 

C     *Entry        PList 
C                   Parm                    AcceptRequest            
C                   Parm                    EZDSQLF2                 

C                   Eval      AcceptRequest=*On                      
 //                                                                  
 // Only allow database server SQL statements from user ODBCUSER or a
 // profile beginning with Q                                         
 //                                                                  
C                   If            EZDUP05='ODBCUSER'                 
C                             Or %Subst(EZDUP05:1:1)='Q'             
C                   Eval      AcceptRequest=*On                      
C                   Else                                             
C                   Eval      AcceptRequest=*Off                     
C                   EndIf                                            
C                   Return                                           

That’s all there is to it. Field EZDUP05 (OK, IBM doesn’t come up with the greatest field names) contains the name of the requesting user. The AcceptRequest should return a ‘1’ (*On) for SQL requests that should run and a ‘0’ (*Off) for those requests that should be cancelled.

Judging by the information available in ZDAQ0200 we could also OK or cancel SQL requests based on other criteria such as characteristics of the SQL statement itself or whether the statement is a DRDA request.

Figure 2: How database SQL requests are routed to an exit program

Once the exit program is written, how do we tell the System i to use it? The answer is by registering the program with the database server exit point. Use the “Work with Registration Information” (WRKREGINF) command to see a list of all exit points on the system. There are many indeed! Take a minute to review the available options; you just might find they will come in handy some day. To secure everything on the System i requires a lot of developer time (or to simply buy a security package that has written these routines already along with a user interface to hide the complexity).

In the list of exit points, position to QIBM_QZDA_SQL2 (Database Server SQL Access). Put an 8 next to this entry to work with the exit programs. When the work with exit program screen appears, put an option 1 to “add” followed by the exit program name you just wrote and its library followed by Enter. You’ll be prompted to fill in the remaining pieces of optional information in the “Add Exit Program” (ADDEXITPGM) command. A sample program registration entry may look like this:

ADDEXITPGM EXITPNT(QIBM_QZDA_SQL2) 
           FORMAT(ZDAQ0200)
           PGMNBR(1)
           PGM(I3/DBSEXIT)
           TEXT('Validate Database Server SQL Req by User') 

It is important to note that some exit points allow for several registered programs. Notice the program number parameter (PGMNBR) on this command. This parameter allows the developer to specify the order of execution when an exit point has multiple programs registered to run. If you want to remove your program from the exit program registration, use the Remove Exit Program (RMVEXITPGM) command.

Once the program is registered with a particular exit, it may not run right away. This is because existing database server jobs (QZDASOINIT) were not instructed to run an exit program when they started. Likewise, when removing an exit program, its usage may not cease right away until the current set of database server jobs are ended. Of course, you can always force the existing set of jobs to end.

Incidentally, when the user exit program rejects a request, a PWS9801 error is thrown: “Function rejected by user exit program DBSEXIT in I3.”

Another Use for the SQL Database User Exit

While exit programs are normally used for security, they can be useful in other areas. I’ll now relate a story of how the database user exit saved me from great pain in a recent project.

There is nothing worse than getting to the end of a big application development project and learning there is a technical flaw in the heart of the data access layer. I wrote an application that heavily interacted with the System i through SQL Server 2005 using ODBC. So end users wouldn’t have to bother installing and configuring iSeries Access and the iSeries .NET managed provider on each PC using the application, I directed all DB2 requests from the application through SQL Server. (The ability to talk to SQL Server comes with the .NET framework.) SQL Server would then query the DB2 via ODBC and return the results to the client application as though the data came from SQL Server itself. Figure 3 below illustrates this scenario.

Figure 3: Making DB2/400 Look Like SQL Server

Because the library list for this application could change on the fly, I had to make sure dynamic queries were submitted using the system naming convention and also issue a CHGLIBL command before executing every query. I accomplished this library list change from within SQL Server 2005 by executing a CL stored procedure with a library list parameter.

The clincher came as I was nearing the end of the project and I was still having intermittent problems with some of the database calls. Every once in a while I would receive an SQL0204 error, which means that SQL couldn’t find an object referenced in a Select statement. After receiving the error I would re-run the statement and everything would run fine. Since I was always changing the library list before every query submission I couldn’t deduce why I was getting an SQL0204 error. Then it dawned on me that there is no guarantee that SQL Server 2005 will always execute the stored procedure to change the library list followed by the desired query in the same host database server job. I realized too late in the game that it is possible for SQL Server to change the host database job’s library list, close the connection and then open a new connection to run the query under a different database server job that no longer had the required library list. Ouch! This didn’t happen very often, but I knew I couldn’t ignore the situation.

“What should I do?” This was a vexing question because there wasn’t an easy way to re-engineer the whole data access layer of the application. It was then in a moment of divine mercy that I remembered that there are database exit points available to do processing before each external SQL statement is executed. Since I had only thought about these exit points in terms of security restrictions, it hadn’t dawned on me to use them for something else.

I quickly put a code sample together for the QIBM_QZDA_SQL2 exit that would change the host job’s library list whenever a SQL statement was submitted by a specific pre-defined application user id and with a specific comment containing the desired library list code.

For example, I checked for SQL statements prefixed with a comment similar to this:

/* LIBL:01 */

When the incoming statement contained such a comment, I would then take the library list code (01), clean up any open activation groups (because there was heavy use of service programs leaving open resources) and reset the library list. Wouldn’t you know, everything worked and my life was spared much grief (until the next big project.)

Thanks to the ability to use the QIBM_QZDA_SQL2 database exit point, a tough data access issue was solved. Available for securing your system or doing some other tricks, the ability to create exit programs is definitely a great tool to have.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.