PATROL for DB2 UDB provides extensive SQL monitoring ability through the SQL Viewer facility which support DB2 Explain. With the SQL Viewer facility, you monitor SQL statements in a database. You can record SQL statement activity by creating and managing SQL Viewers as needed. You can also write all the data collected to a report.
Warning
If you are running DB2 UDB EEE on Solaris, you must have the following DB2 fixpacks installed to run the SQL Viewer facility: DB2 UDB EEE Version 5, Fixpack 17 DB2 UDB EEE Version 6, Fixpack 11 DB2 UDB EEE Version 7, Fixpack 7 Failure to install these fixpacks will cause the database manager to crash if you attempt to start an SQL Viewer. Also, note the warning about fixpack requirements before attempting to run the SQL Snapshot facility.
Note
The SQL Viewer facility is not available when you use a remote database.
In addition to viewing SQL text, you can invoke the DB2 Explain facility from an SQL Viewer. See the IBM DB2 Universal Database Administration Guide for a complete description of the Explain facility.
To begin monitoring SQL statements, you first create an SQL Viewer, and then start the viewer to begin recording data. You view the collected data in the viewer instance output window.
Note
The SQL Viewer facility uses DB2 Event Monitoring which produces a large amount of output and can impact performance. BMC recommends that you only run an SQL Viewer during the time that you want to monitor SQL activities. In addition, select only the detail options that you need and consider using the filtering features to reduce the amount of data collected. A SQL Viewer instance will collect a maximum of 1000 SQL statements. After this limit is reached, the SQL Viewer stops recording data and displays a message in the system output window. Although the SQL Viewer stops recording data, it is still active. You should stop the SQL Viewer at this point to avoid using unnecessary processing time. SQL statements issued through the PATROL for DB2 UDB SQL Processor command, DB2 EXPLAIN, the EXPLAIN formatter tool (db2exfmt), and the DB2 Benchmark Tool (db2batch) are not recorded by the SQL Viewer facility.
Warning
One exception exists to the preceding note. If you are running DB2 version 5 on Windows NT without fixpack 10 installed or DB2 version 6 on Windows NT without fixpack 1 installed, PATROL for DB2 UDB will attempt to record all SQL statements issued by the SQL Processor, DB2 EXPLAIN, db2exfmt and db2batch. This activity can produce unwanted data and impact system performance.
The following tasks related to SQL Viewers are described in this section:The [confluence_table-plus] macro is a standalone macro and it cannot be used inline.