Saturday, March 22, 2008

Mapping Application to Dynamic SQL

If you need to find out dynamic SQL statements in the database package cache, you can do so using Dynamic SQL Snapshot. If you need to check the details of your application, you can use Application snapshot. If you are accessing application snapshot through table function, you have to use Statement snapshot to find the executing statement by that application. However, as soon as the statement execution is finished, you would not find that statement text in the statement snapshot. Also, even though dynamic SQL snapshot will show you this statement, it does not have any info regarding the application that executed it. Hence, if you are trying to map an application with a dynamic SQL statement, the snapshot table functions mentioned above may not be very useful.

I had to suffer with this limitation of DB2 for quite some time. Interestingly, other competitive vendor databases (Oracle & SQL Server) provided the link between SQL in Cache and their sessions. However, I recently found that in version 9, we have the mapping info between application and dynamic SQL statements available through db2pd (DB2 Problem Diagnostic) tool. In fact, this db2pd enhancement was made in some fixpack after v8.2. Under this enhancement, the “applications” option of db2pd provides you following new columns

· C-AnchID : Current anchor ID

· C-StmtUID : Current statement unique ID

· L-AnchID : Last anchor ID

· L-StmtUID : Last statement unique ID

The first pair of Anchor ID and Statement Unique ID can be used to find the current SQL statement being executed by the application and the second pair can be used to find the last SQL statement executed by that application. The “dynamic” option of db2pd includes following columns to map with the Anchor ID and Statement Unique ID pair.

· AnchID : Anchor ID of the statement

· StmtUID : Statement unique ID

You may check the “Scenario 2” under Monitoring and troubleshooting using db2pd at DB2 Information Center to look at an example of mapping application to a dynamic SQL statement. You may also run the following command and verify it yourself.

db2pd –appl –dyn –db

However, the problem with db2pd is that you can’t run it through SQL. Hence, if you want to collect the above info on a regular basis and store in a table as part of your database monitoring, you need to parse the db2pd output. I wish if the above AnchID and StmtUID info could be included in Application and Dynamic SQL snapshots, so that it can be accessed through snapshot table functions using SQL. At least now we have the mapping available through db2pd, which is much better than no mapping available earlier.