New Feature SQL Text in Enhanced 3270 UI
Paul Kenney 12000097KJ Visits (9804)
Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS V5.3.0
New Feature: SQL Text in Enhanced 3270 UI
New features are being added to the Enhanced 3270 User Interface in each release of Tivoli® OMEGAMON® XE for DB2® Performance Expert on z/OS®. One of the most important new features for the recently released version 5.3.0 is to display SQL text.
DB2 for z/OS application performance problems can quite often be traced back to poorly written SQL. Lack of WHERE clauses or improper WHERE clauses can lead to tablespace scans which can cause long running SQL Statements. Being able to see the SQL statement text is key to diagnosing these types of application problems. In addition to simple visualization, the SQL text can be copied into Explain/Explain tools to determine full details about DB2 access path selection which can provide hints in tuning individual SQL Statements.
In OMEGAMON XE for DB2 Performance Expert, there are three different places that SQL text is displayed.
Active Thread Detail
The Active Thread Detail SQL displays the SQL text of the active SQL statement being executed by a thread.
From the Enterprise Summary workspace the ‘T’ selection will display the summary of active threads for the selected DB2 subsystem. On the thread summary workspace the ‘T’ select is used to display the Thread Detail SQL Text workspace for a specific thread. The Thread Detail SQL Text workspace can also be reached by clicking the SQLT tab from most of the other Thread Detail workspaces.
Here is an example of what the Thread Detail SQL text workspace can look like for an active thread.
The width of the SQL text varies according to the width of the screen you are using. Here is an example of SQL text displayed on a 3270 Emulator using 160 width and 62 height.
Dynamic SQL Cache
Another enhancement in version 5.3.0 is the addition of three new workspaces for the Dynamic SQL Cache.
Here is an example of SQL text being displayed from the Dynamic SQL Cache.
Static SQL Cache
Another enhancement to version 5.3.0: Three new workspaces were added to display Static SQL Cache.
Here is an example of SQL text being displayed from the Static SQL Cache.
Being able to display SQL statements on the Enhanced 3270 UI will assist in being able to locate and tune badly performing SQL. Locating SQL that is long running or using large amounts of CPU time can assist in improving application and overall DB2 system performance.
(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