The STRSQL (Start SQL Interactive Session) command can bring good news and bad news. The good side of the STRSQL command is that it provides an interface that lets users quickly execute SQL statements. Often STRSQL is used to browse the data in the database and cleanup any data errors that might have been injected by a program bug or user error. The downside? STRSQL can be a bearer of bad news when an auditor reviews your system and wants to see the list of database changes made from the STRSQL interface and who performed them. For most IT departments, STRSQL activity is just a part of running your business--the problem is effectively tracking STRSQL usage.

Prior to IBM i 6.1, there were no good solutions. The only real solution was starting a database monitor for all jobs running on the system. This type of database monitor collection will capture all the SQL requests executed from STRSQL. However, this means the monitor will also capture data for every SQL statement running on your system. That's not an acceptable solution for those systems with heavy SQL activity because the DB2 database monitor would generate too much disk activity and consume large amounts of disk storage.

DB2 for i 6.1 provides a tailored solution for this problem with the usage of client special registers and database monitor filters. Special registers are not new to SQL--in fact, there's a good chance you already use special registers such as CURRENT DATE or USER. IBM introduced client special registers to enable applications to provide tags (or attributes) to describe the client or application environment that is running SQL statements. In addition, IBM tools such as the database monitor collect the client register values along with the SQL statement text--this makes it very easy to identify which interface or application submitted an SQL statement. Here are the client special registers available in 6.1:

When IBM i 6.1 was first released, the IBM i operating system assigned default values to these client registers for "remote" database client interfaces such as the Toolbox JDBC driver and System i Navigator. With recent PTFs on IBM i 6.1, DB2 server-side interfaces such as STRSQL and RUNSQLSTM also now set the client register values. The value of these special registers can be retrieved at any time using a simple SQL statement like the following from any SQL interface:

If this SQL statement was run from the STRSQL interface, the value returned would be the string 'STRSQL'. Thus, you can see how the DB2 for i 6.1 client register support makes it easy to flag those SQL statements executed with the STRSQL command.

As mentioned earlier, the client register values are also collected by the database monitor. This collection of client register values is the other enabling component of the IBM i 6.1 solution for tracking STRSQL usage. The client register values are assigned to the following columns in a database monitor output table:

Here’s an example to help you understand how the database monitor tracks the values of the client registers. The following STRDBMON (Start Database Monitor) command is used to initiate the monitor of all jobs on the system.

The database monitor is ended (ENDDBMON JOB(*ALL) ) after a 10 minute collection period. Now it's time to determine if anyone was using the STRSQL interface during the monitor collection period. That can be done by running the query in Figure 1 against the database monitor output table from any SQL interface.

Notice that this query requests only those monitor records collected from the STRSQL interface by specifying a search string of 'STRSQL' for the qvc3006. The search criteria of qqrid=1000 retrieves only those monitor records containing the text of collected SQL statements.

The output of this monitor analysis query is found in Figure 2. The QVC102 and QQ1000 columns contain the user profile and SQL statement submitted from the STRSQL interface during the database monitor collection period.

You may be thinking that the monitor collection of client special register values does not solve the STRSQL tracking problem because the previous example still requires a database monitor collecting data for all jobs on the system. That thinking is correct since the final component of the complete solution has not yet been discussed. The final piece to the solution is database monitor filters.

Enter Database Monitor Filters

Database monitor filters were first introduced in V5R3 and have been expanded upon in every release since that time. Some of the filters available are a user filter (FTRUSER) to restrict data collection to only SQL statements executed by a specified user and a table filter (FTRFILE) to only capture records for SQL statements referencing a specific DB2 table. Monitor filters enable a smaller set of data to be collected by only collecting database monitor data for SQL statements that meet the specified filter criteria. Less data being collected means that an active database monitor will have less impact on your system in terms of disk activity and disk storage requirements.

DB2 for i 6.1 provides the ability to specify monitor filters for the client register values. These new client register filters enable a system-wide database monitor to be active and running all the time--but only collecting data for a specific interface (i.e., STRSQL). Here is a list of the new client register filters supported by the database monitor:

Unfortunately, these filters were delivered after availability of IBM i 6.1, which prevented IBM from adding new parameters to the STRDBMON command. Thus, the client register filters have to be specified indirectly using the COMMENT parameter as shown in Figure 3. IBM plans to support these filter registers as formal command parameters in a future release.

Since STRDBMON is a CL command, it could be easily added to the QSTRUP program on your system to ensure that a database monitor for STRSQL is always started and active on your system. The *JOBCTL special authority is required when using the STRDBMON command to monitor jobs other than the current job.

As mentioned earlier, the DB2 enhancements in this article were delivered after the GA of IBM i 6.1. Thus, additional PTFs must be applied on your system before using the techniques discussed in this article. The required PTFs are: SI33593, SI33596, and SI33597. These PTFs are included in Database Group PTF SF99601 Level 7.

The STRSQL interface is not the only IBM interface that assigns values to the client register values. Here are the client ProgramID register values for other IBM interfaces that you may be interested in tracking and auditing: See Table 1.

Controlling the Client Special Registers

In addition, you can have your own application programs assign values to the special client registers. This would enable you to easily collect database monitor data for specific programs or modules within your application instead of collecting monitor data for the entire application.

The client special registers are not just for IBM's usage, there are programmatic interfaces available to your application as SQL. Some programmers have found that they can speed up debug and performance tuning processes by having their applications assign values to the client special registers.

For instance, consider a system that is experiencing performance problems caused by a long-running SQL statement that was identified with a DB2 performance tool such as the database monitor or visual explain. These tools identify the text of the SQL statement, but you may not know what application is responsible for executing the SQL statement. If the client special registers had been set prior to your application submitting any SQL statements, the client register values can be used to determine if the SQL is from your application, third-party software, or an IBM product. Having the client registers supply this type of environment information can definitely jump start your problem determination process.

Below you will find a list of the programmable interfaces that are available with IBM i 6.1 to assign values to the client special registers. These interfaces allow a client register value to be assigned a character string up to 255 bytes in length. If your goal is to use the database monitor client register filters, then you will want to use shorter client register values--because client register filters are not formal parameters on the STRDBMON command. Since the parameters are passed indirectly thought the COMMENT parameter, the client register filter are limited to 50 bytes in length. Here’s the list of programmable interfaces:

• IBM i SQLESETI API
• CLI SQLSetConnectAttr() function
• JDBC setClientInfo connection method
• OLE DB Provider (IBMDASQL & IBMDA400) Connection String Keywords
  • Application Name
  • Client Accounting
  • Client Program ID
  • Client User ID
  • Client WorkStation Name
• ADO.NET Provider Connect String Keywords
  • ApplicationName
  • ClientAccounting
  • ClientProgramId
  • ClientUserId
  • ClientWorkstation
• SYSIBM.WLM_SET_CLIENT_INFO stored procedure
  • First Parameter: Client User ID
  • Second Parameter: Client WorkStation Name
  • Third Parameter: Client Application Name
  • Fourth Parameter: Client Accounting
  • Fifth Parameter: Client Program ID

The WLM_SET_CLIENT_INFO stored procedure will only change the value of a client register when a non-null value is passed as a parameter. If a null input parameter value is passed on the procedure call, the client register associated with that null parameter value remains unchanged. Let's review the following stored procedure call as an example.

Notice that three of the five input values on the stored procedure call are null. As a result, the CLIENT USERID, CLIENT WRKSTNNAME, and CLIENT ACCTG register values are not changed on the procedure call. This procedure call results in only the CLIENT APPLNAME register being changed to 'MYAPP' and the CLIENT PROGRAMID register being changed to 'MYPGM1'.

There are several tools available to help you examine the current value of the client special registers and determine if they have been set correctly by your application. In addition to the Database Monitor and Visual Explain, the "SQL Details for Job" tool in System i Navigator can also be used to examine the client register values associated with an SQL request. The “SQL Details” tool can be accessed by right-clicking on the Databases icon in the System i Navigator tree and selecting that task. An example of the "SQL Details for Job" output is found in Figure 4.

All-in-All, No More Bad News

Hopefully, it is now clear how the client special registers and database monitor filters can be used together to put tracking in place for STRSQL--and any other SQL interface on your system. This should be real good news for auditors that need to know exactly what database change was made and who did it. Happy tracking!