|TechTip: Using SQL Client Special Registers in DB2 Web Query|
|Tips & Techniques - SQL|
|Written by Gene Cobb|
|Friday, 08 April 2011 00:00|
Leverage DB2 Web Query and SQL client special registers.
In V6R1 of the IBM i operating system, support was added for the specification (and subsequent retrieval) of several new special registers that applications can use to identify what jobs, users, programs, and applications are issuing SQL statements to the DB2 for i engine. Currently, there are five such registers you can use:
The good news is that you can use these special registers for your own applications. The better news for customers using IBM DB2 Web Query for i is that this product now uses these registers to identify all SQL requests it submits to the database engine. The values specified by DB2 Web Query for each of these special registers is provided in the table below.
If you are using DB2 Web Query today, you may be thinking, "OK, sounds great. But how can I use this feature?" A couple of ideas jump to the forefront:
Selective Preprocessing of DB2 Web Query Requests
Once the registers are specified by DB2 Web Query, this information can be retrieved in your own programs to determine where the request came from. If you've read my previous articles, you may remember a couple that discuss the use of the QIBM_QSQ_CLI_CONNECT, the CLI connect exit point:
Registration of this exit point means that you can call a user-written program whenever a CLI connect event occurs in server mode (which happens to be every time DB2 Web Query submits an SQL request to DB2). This preprocessing gives you the ability to do some useful things before the report runs, such as dynamically changing the library list or adding a row to an audit table. One of the drawbacks of this exit point is that you can register only one program to it. This means that if you have multiple CLI applications, the exit point will be activated for all SQL requests coming from CLI applications running in server mode (not just DB2 Web Query). Consequently, it can be quite difficult to determine where each request is originating from. If you want to change the library list only for DB2 Web Query requests (not for an order entry application using CLI), how can you do this? Well, now you can use the information in the special registers! Here's an example snippet from an RPGLE program that uses embedded SQL to make this determination:
Analyzing Requests from DB2 Web Query
If you want to determine what applications are making SQL requests or even isolate those requests coming from DB2 Web Query to a gauge and/or analyze their impact on database resources, these special registers make it a breeze! You have a couple of options here:
Helpful hint: To reduce the strain on system resources, specify a pre-filter for the performance monitor so that it only collects information from a specific program ID. Depending on the IBM i OS level, use one of these commands to specify a pre-filter and only collect DB2 Web Query SQL requests.
Regardless of the method chosen, the SQL statement to query the results is basically the same. The following shows each register and corresponding column name of the table containing the collected data.
Use the following statement and specify the name of the table that contains the collected data:
qqtime AS time ,
qvc3002 AS client_userid ,
qvc3003 AS client_wkstnname ,
qvc3001 AS client_applname ,
qvc3005 AS client_acctng,
qvc3006 AS client_programid,
qq1000 AS statement
WHERE qqrid = 1000
AND qvc3006 = 'DB2WBQRY'
ORDER BY qqtime;
Note: At the risk of stating the obvious, if you started an SQL performance monitor using the pre-filter described previously, it would not be necessary to include this portion in the WHERE clause:
This is because the pre-filter would have only collected information from that program ID.
Using the above statement, here is an example result set:
Figure 1: This is the type of result set you can expect. (Click images to enlarge.)
If you really wanted to analyze a particular query by using the Visual Explain tool, simply show the statements in the snapshot or the SQL performance monitor, find the statement, and select Visual Explain. This process is shown in the examples below.
Figure 2: System i Navigator's Visual Explain tool helps you analyze queries.
Figure 3: You can drill down for details.
One last idea for querying this data: use DB2 Web Query and create a Web Query report of Web Query requests! An example report is shown below.
Figure 4: Get reports about queries that have been made.
As mentioned previously, support for the SQL special registers started with V6R1 of the IBM i operating system. The DB2 Web Query use of these special registers is supported only for version 1.1.2 of 5733QU2 and you must have level 2 or higher of the DB2 Web Query group PTF installed. The group PTFs are different for each IBM i operating system release. The specific information for which PTF you'll need is provided below.
Register Your Own Applications!
Keep in mind that there is no magic going on here: the use of these special registers is not exclusive to DB2 Web Query, IBM i products, or internal processes! If your applications use SQL to access the data and are running on V6R1 of the IBM i operating system, they too can use these special registers to identify where the SQL requests originated from! All you have to do is call the WLM_SET_CLIENT_INFO stored procedure (specifying the values of the registers as input parameters) in your application prior to the execution of your SQL statements (within the same job, of course). Simply follow the format provided in the following example:
CALL SYSPROC.WLM_SET_CLIENT_INFO (
Note: When using the WLM_SET_CLIENT_INFO stored procedure, you can allow any of the client registers to remain unchanged by specifying NULL for the appropriate parameter.
If you would like to learn more about SQL special registers, refer to these links:
as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1
|Last Updated on Wednesday, 06 April 2011 11:39|