Starting and stopping an SQL Viewer

Use this task to start recording SQL information for a database. Stop an SQL Viewer to stop recording SQL statement data, or before purging a viewer or deleting a viewer.

Before you begin

An SQL Viewer collects a maximum of 1,000 SQL statements. Although still active, the viewer is no longer recording information when this limit is reached. You should stop the SQL Viewer at this point to avoid using unnecessary processing time.

To start an SQL Viewer

1. Right-click on the SQL Viewer instance icon and select Start SQL Viewer.

2.  In the Detail Options for SQL Viewer window, select one or more of the following options to indicate the details that you want to record:

3. Click OK.

   Warning

   Note

   If you selected the FILTERS (Filter out specific SQL statements) option in Step 2, the SQL STATEMENT FILTERS window appears. For information about setting up SQL statement filters, see To set up SQL statement filters.

If a problem occurs

The following problems could occur while recording SQL information.

• A problem could occur when a SQL Viewer is collecting large volumes of SQL statements. An error message stating that you have run out of application heapsize might appear. If this error occurs, increase the applheapsize value in your DB2 database configuration.
• Heavy loads of SQL data being processed by a SQL Viewer could cause a decrease in system performance. This problem occurs after you receive the PATROL error message that contains the following phrase:
  PatrolAgent-W-EUSER; PSL script 'DB2_SQL_VIEWER_INST. [instance name][database name][sqlviewer name] Command' may be in an infinite loop...
   To resolve this problem, perform the following steps:

1. From the PATROL menu of the host that you are monitoring, select Development > Agent Configuration.
   
   The PATROL configuration window appears.
2. select AgentSetup > AgentTuning.
3. Increase the value for the maximum number of PSL instructions (pslInstructionMax).
4. select Tools > Apply Configuration.
5. Click File > Exit.
   
   In addition, check for active viewers that might have reached the 1000- statement limit and stop the viewer to reduce processing time.

To set up SQL statement filters

The SQL STATEMENT FILTERS window appears if you select the FILTERS (Filter out specific SQL statements) option on the Detail Options for SQL Viewer window. (Refer step 2 in To start an SQL Viewer for more information about this option.)

1. Enter at least one of the following supported filters:
   • Agent Id Enter a valid agent ID. The SQL Viewer collects data for this agent ID.

   • CPU Time Enter CPU time using the format secs.msecs. For example, 0.9, 1.0, 8.05.

     Warning

     Note

     The SQL CPU time must exceed this CPU time for the SQL Viewer to collect information about it.

2. Click OK.

   Warning

   Note

   You can terminate the Start SQL Viewer command by clicking Cancel on this window.

To stop an SQL Viewer

From the SQL Viewer instance icon menu, select Stop SQL Viewer.