Zero amounts for SQL statements in the DB2View screen of Mainframe Application Tuner
Article ID: 10218
Updated On:
Products
Issue/Introduction
When looking at the DB2View screen you find a lot of SQL statements have zero amounts:
CA MAT ---------------------- DB2View ---------------------- Row 1 to 24 of 66
COMMAND ===> SCROLL ===> CSR
Primary Commands: SQL (all/sampled), SEQ (sort), ADDHelp Profile: CA
Options: NORMAL
Line commands: S - Select SQL E - Explain SQL SQL: All
SD - Show Declare I - Explain Information Sort: Sequence
C - Code Details D - Statement Detail DB2 SSID: CADB
DB2 Rel: 10.1.0
DBRM or D Data Call Total
LC Package S From Stmt Num Type Count Total CPU CPU-P-Call Resp Time
________ _ ____ ________ ________ _________ __________ __________ __________
>------------------------------------------------------------------------------ __ AAAAA85 S S 1751 OPEN 0 0.000000 0.000000 0.000000 __ AAAAA84 S S 1109 OPEN 0 0.000000 0.000000 0.000000 __ AAAAA83 S S 1419 FETCH 0 0.000000 0.000000 0.000000 __ AAAAA82 S S 3472 OPEN 0 0.000000 0.000000 0.000000 __ AAAAA81 S S 2209 FETCH 0 0.000000 0.000000 0.000000 __ AAAAA80 S S 1581 OPEN 0 0.000000 0.000000 0.000000 __ BBBBB41 S S 1341 OPEN 0 0.000000 0.000000 0.000000 __ BBBBB42 S S 1462 OPEN 0 0.000000 0.000000 0.000000 __ BBBBB43 S S 1436 OPEN 0 0.000000 0.000000 0.000000 __ BBBBB44 S S 2133 FETCH 0 0.000000 0.000000 0.000000 __ BBBBB45 S S 1818 OPEN 0 0.000000 0.000000 0.000000
Mainframe Application Tuner (MAT) collects SQL data using two different methods.
One is based on observing the target address space and the other is based on the harvester.
The harvester is the more exact method of the two because it monitors every SQL statement that gets processed in the DB2 subsystem.
The observation and sampling method is the less accurate of the two. But both are done depending on what is specified for DB2 options.
After the data is collected for both options and put it into the monitor file during analysis the data will be merged based on things like DBRM name, CRC value, statement number, etc., and DB2view will then show the merged data.
If on the DB2View screen only SQL from the sample process is, there will be an S in the 'Data From' column.
If it is only from the Harvester there will be an H in the 'Data From' column and if there is SQL in both sample and harvester there will be a B in the 'Data From' column.
Environment
Resolution
There are any number of reasons why you may see the SQL collected from one method and not the other. If only seen during sampling, with 0 counts then it is possible that an application was monitored that used a DB2 subsystem that was not included in DB2HVSE* parameter. That means MAT will not set the Harvester in the DB2 region until after it observes and determines the DB2 subsystem needs to have the harvester implanted. What that means that MAT will have seen some sampled SQL before it is able to see the harvested SQL. There will be only Sample SQL to report.
MAT also knows if there is DB2 sharing. If the SQL request is shipped to a DB2 subsystem other than the one used by the application, it will only see sampled data.
If the 'Data From' column contains an H, means it only found harvested SQL, which means sample collection simply didn't see a sample of the SQL.
The best way to get a complete picture of the SQL is to filter the 'Data From' column to show only SQL found by both processes by putting a B under the column heading.
Feedback
