IQ UTILITIES Statement

Quick Links:

Syntax

IQ UTILITIES { MAIN | PRIVATE }
   [ INTO ] table-name
   { START MONITOR ['monitor-options']
   | STOP MONITOR }

monitor-options
   { -summary 
   | {-append | -truncate } -bufalloc 
   | -cache 
   | -cache_by_type 
   | -contention 
   | -debug 
   | -file_suffix suffix
   | -io 
   | -interval seconds 
   | -threads }...

Parameters

(back to top)

START MONITOR – starts the IQ buffer cache monitor.

MAIN – monitors all tables in the main buffer cache of the IQ Store.

PRIVATE – monitors all tables in the temp buffer cache of the temporary Store.

dummy_table_name – can be any SAP Sybase IQ base or temporary table. The table name is required for syntactic compatibility with other IQ UTILITIES commands. It is best to have a table that you use only for monitoring.

monitor_options – controls buffer cache monitor output. You can specify more than one, and they must be enclosed with quotation marks.

Option	Description
-summary	Displays summary information for both the main and temp buffer caches. If you do not specify any monitor options, you receive a summary report. Usage: monitor_options -summary
-cache	Displays main or temp buffer cache activity in detail. Critical fields are `Finds`, `HR%`, and `BWaits`. Usage: monitor_options -cache
-cache_by_type	Breaks -cache results down by IQ page type. (An exception is the `Bwaits` column, which shows a total only.) This format is most useful when you need to supply information to Technical Support. Usage: monitor_options -cache_by_type
-file_suffix	Creates a monitor output file named <dbname>.<connid>-<main_or_temp>-<suffix>. If you do not specify an optional file extension, the file extension defaults to .iqmon. Usage: monitor_options -file_suffix {extension}
-io	Displays main or temp (private) buffer cache I/O rates and compression ratios during the specified interval. These counters represent all activity for the server; the information is not broken out by device. Usage: monitor_options -io
-bufalloc	Displays information on the main or temp buffer allocator, which reserves space in the buffer cache for objects like sorts, hashes, and bitmaps. Usage: monitor_options -bufalloc
-contention	Displays many key buffer cache and memory manager locks. These lock and mutex counters show the activity within the buffer cache and heap memory and how quickly these locks were resolved. Timeout numbers that exceed 20% indicate a problem. Usage: monitor_options -contention
-threads	Displays the processing thread manager counts. Values are server-wide (i.e., it does not matter whether you select this option for main or private). Usage: monitor_options -threads
-interval	Specifies the reporting interval in seconds. The default is every 60 seconds. The minimum is every 2 seconds. You can usually get useful results by running the monitor at the default interval during a query or time of day with performance problems. Short intervals may not give meaningful results. Intervals should be proportional to the job time; one minute is generally more than enough. Usage: monitor_options -interval
-append \| -truncate	Append or truncate output to existing output file. Truncate is the default. Usage: monitor_options -append \| -truncate
-debug	Displays all information available to the performance monitor, whether or not there is a standard display mode that covers the same information. -debug is used mainly to supply information to Technical Support. Usage: monitor_options -debug

STOP MONITOR – similar to START MONITOR except that you do not need to specify any options:
Note:
- To simplify monitor use, create a stored procedure to declare the dummy table, specify its output location, and start the monitor.
- The interval, with two exceptions, applies to each line of output, not to each page. The exceptions are the -cache_by_type and -debug clauses, where a new page begins for each display.

Examples

(back to top)

Example 1 – start the buffer cache monitor and record activity for the IQ temp buffer cache:
```
IQ UTILITIES PRIVATE INTO monitor START MONITOR '-cache -interval 20'
```

Usage

(back to top)

Issue separate commands to monitor each buffer cache. Keep each sessions open while the monitor collects results; a monitor run stops when you close its connection. A connection can run up to a maximum of two monitor runs, one for the main and one for the temp buffer cache.

To control the directory placement of monitor output files, set the MONITOR_OUTPUT_DIRECTORY option. If this option is not set, the monitor sends output to the same directory as the database. All monitor output files are used for the duration of the monitor runs. They remain after a monitor run has stopped.

Either declare a temporary table for use in monitoring, or create a permanent dummy table when you create a new database, before creating any multiplex query servers. These solutions avoid DDL changes, so that data stays up on query servers during production runs.

On UNIX-like operating systems, you can watch monitor output as queries are running. For example:

Starting the monitor with this command:

iq utilities main into monitor_tab 
start monitor “-cache -interval 2 -file_suffix iqmon”

sends the output to an ASCII file with the name dbname.conn#-[main|temp]-iqmon. So, for the iqdemo database, the buffer monitor would send the results to iqdemo.2-main-iqmon

The buffer cache monitor writes the results of each run to these logs:

dbname.connection#-main-iqmon //for main buffer cache results
dbname.connection#-temp-iqmon //for temp buffer cache results

The prefix dbname.connection# represents your database name and connection number. If you see more than one connection number and are uncertain which is yours, you can run the Catalog stored procedure sa_conn_info. This procedure displays the connection number, user ID, and other information for each active connection to the database. The -file_suffic clause to change the suffix iqmon to a suffix of your choice. Use a text editor to display or print a file. Running the monitor again from the same database and connection number, overwrites the previous results. To save the results of a monitor run, copy the file to another location or use the -append option.

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Not supported in Adaptive Server.

Permissions

(back to top)

None