Enhanced OMEGAMON XE for DB2 Performance Expert filtering improves dynamic SQL cache problem diagnosis
zhouh 120000CTBC Comments (2) Visits (8401)
The enhanced 3270 User Interface (UI) of OMEGAMON® XE for DB2® Performance Expert on z/OS® V530 captures snapshot of statement level statistics for all statements in the dynamic SQL cache.
With potentially thousands of statements in the cache, locating the particular statements that are affecting application performance becomes a very time-consuming task. With the newly added filter options in V530, selectively displaying statements of interest becomes as simple as choosing the right filter options. In addition, using filters can also reduce the amount of data sent across the network, thus improving the OMEGAMON response time - especially when the dynamic SQL cache is heavily used.
The new Dynamic SQL Cache Filter Options panel is displayed when DSQL (Dynamic SQL) is selected from the System Statistics display. By default, the first 100 dynamic SQL cache statements are displayed in descending order of the accumulated CPU time.
The ‘Max Rows Returned’ option controls the total number of dynamic SQL statements returned to the DB2 Dynamic SQL Cache Statement Statistics workspace. The smaller the value, the better the OMEGAMON response time can be in returning the results. The ‘Filter Method’ option controls how DB2 data filtering is used. “DB2 data filtering” represents the qualifier OMEGAMON sends to DB2 to retrieve the requested metrics. In ‘Filter Method’ you can select to turn off data filtering (Option 0), sort the returned dynamic SQL statements (Option 1), or just return the dynamic SQL statements that match the filter criteria set by both, a value in ‘Value Higher than’ and a field in ‘Filter Field’(Option 2). The data filter options are applied at DB2 data retrieval time. In the example above, we’re filtering on the 100 dynamic SQL statements currently in the cache that have used the most CPU.
The dynamic SQL cache filter options are saved in the enhanced 3270 UI user profile. As a user you can tailor the display based upon your individual focus, so only those dynamic SQL statements that you are interested in will be selected to help you with the problem diagnosis. User profiles are saved across your logon sessions.
Select OK on the filter workspace to open the Dynamic SQL Cache summary display:
Note: The green symbol in the display next to the Auth ID field indicates that additional display filter is available to further reduce the number of statements in the display. Press the PF4 key to set these display filter(s).
With the display filter shown above, only 2 out of the top 100 CPU contributors are displayed. Note that the symbol next to Auth ID on the following panel is now yellow indicating that a display filter has been activated for the Auth ID field.
Here are some examples of how Database Administrators (DBA) might want to apply the new Filter Options to the Dynamic SQL Cache displays to help slice their dynamic SQL cache into manageable chunks:
1) To locate recently updated statements in the dynamic SQL cache, use ‘Filter Method’ 1 (Highest Value) and ‘Filter Field’ 8 (LastUpdTime).
2) To locate recently inserted statements in the dynamic SQL cache, use ‘Filter Method’ 1 (Highest Value) and ‘Filter Field’ 9 (LastInsTime)
3) To locate statements that are inserted/updated using parallelism, use ‘Filter Method’ 1 (Highest Value) and ‘Filter Field’ L (Groups)
4) To locate the top 10 statements with the most execution count, use ‘Filter Method’ 1 (Highest Value), ‘Filter Field’ E (Executions), and ‘Max Rows Returned’ 10.
5) To locate the first 20 statements with execution count higher than 1000, use ‘Filter Method’ 2 (Value Higher), ‘Value Higher than’ 1000, ‘Filter Field’ E (Executions), and ‘Max Rows Returned’ 20.
As you can see, with the new filtering displays of OMEGAMON DB2 the DBA can more quickly identify potential problems with dynamic SQL statements thereby saving time and reducing errors resulting from scrolling through thousands of SQL statements.
(Trademarks: IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. A current list of IBM trademarks is available on the Web at http