If you have both Db2 Query Monitor and Db2 Administration Tool installed, you can now leverage yet another integration between these two tools. When viewing SQL activity (activity summaries, current activity, or exceptions) in the Db2 Query Monitor ISPF interface, you can now invoke Db2 Admin Tool directly from these panels to view stored procedure information.
This enhancement can be especially useful if you see a large number of CQM335I messages in place of SQL text on the Db2 Query Monitor panels. This message indicates internal logic within a native SQL procedure; the activity is procedural logic rather than a standard SQL statement and therefore cannot be displayed. With this enhancement, you can now quickly open Db2 Admin Tool to find more information about the native SQL procedure.
For example, suppose you are viewing a summary of SQL activity by plan (option 1 -> 1 from the main menu in Db2 Query Monitor). Assume you want to drill down on the SQL for particular plan. Enter the S line command next to that plan:
On the subsequent Activity by SQL Text panel, you notice message CQM335I (internal logic in a native SQL procedure) in the SQL Text column:
To find out more about the stored procedure associated with this message, use the SP line command:
Note: Using the SP line command assumes that you have OPTKEYS=SP defined in your monitoring profile.
On the resulting panel, you can see the name of the stored procedure:
Now, you can use the new AQ line command to invoke Db2 Admin Tool for that stored procedure:
When you press Enter, the Stored Procedures panel in Db2 Admin Tool is displayed with your stored procedure:
Notice the value in the Lang column: SQL. This value confirms that this procedure is a native SQL procedure.
Now, you can use Db2 Admin Tool line commands, such as I (Interpret) or DDL (generate DDL) to find out more information about this procedure. Enter the ? line command to see a list of all possible line commands for the row.
As with all of the new Ax line commands for invoking Db2 Admin Tool, make sure that you have specified the plan to use to invoke Db2 Admin Tool. You can specify this plan name in the Db2 Query Monitor settings (option 0 from the main Db2 Query Monitor menu):
Or you can specify this plan name in the new ADB_PRODUCT_PLAN parameter in CQMPARMS. (This parameter was added by APAR PH68257.)
This new functionality is delivered in APAR PH67869 for Db2 Query Monitor 3.4. Specifically, the new AQ line command is supported on the following Db2 Query Monitor panels:
- Current SQL Activity panel
- Operational Summaries panel
- Structural Summaries panel
- Display Exceptions panel
Note that APAR PH67869 also introduced message CQM335I to provide more useful information about why the SQL text cannot be displayed:
CQM335I - SQL PL Logic Execution
Previously, the generic CQM145I message was displayed for this scenario:
CQM145I – SQL TEXT NOT AVAILABLE
For more information about invoking Db2 Admin Tool from Db2 Query Monitor, see
https://www.ibm.com/docs/SSAURY_3.4.0/topics/cqmu_invokeadb.html.
For more information about viewing store procedure information in Db2 Admin Tool, see
https://www.ibm.com/docs/SSAUZ9_13.1.0/topics/adbu_cat-panels_option-o.html