sp_iqsysmon Procedure

Batch Mode Syntax

sp_iqsysmon start_monitor
sp_iqsysmon stop_monitor [, 'section(s)' ]
or
sp_iqsysmon 'time-period' [, 'section(s)' ]

File Mode Syntax

sp_iqsysmon start_monitor, 'filemode' [, 'monitor-options' ]
sp_iqsysmon stop_monitor

Batch Mode Parameters

start_monitor – Starts monitoring.
stop_monitor – Stops monitoring and displays the report.
time-period – The time period for monitoring, in the form HH:MM:SS.
section(s) – The abbreviation for one or more sections to be shown by sp_iqsysmon.
See the Remarks section for a complete list of abbreviations.

If you specify more than one section, separate the section abbreviations using spaces, and enclose the list in single or double quotes. The default is to display all sections.

For sections related to the IQ main store, you can specify main or temporary store by prefixing the section abbreviation with 'm' or 't', respectively. Without the prefix, both stores are monitored. For example, if you specify 'mbufman', only the IQ main store buffer manager is monitored. If you specify 'mbufman tbufman' or 'bufman', both the main and temporary store buffer managers are monitored.

Note: The SAP Sybase IQ components Disk I/O and Lock Manager are not currently supported by sp_iqsysmon.

File Mode Parameters

start_monitor – Starts monitoring.
stop_monitor – Stops monitoring and writes the remaining output to the log file.
filemode – Specifies that sp_iqsysmon is running in file mode. In file mode, a sample of statistics appear for every interval in the monitoring period. By default, the output is written to a log file named dbname.connid-iqmon. Use the file_suffix option to change the suffix of the output file. See the monitor_options parameter for a description of the file_suffix option.
monitor_options – The monitor _options string can include one or more options:
- -interval seconds – Specifies the reporting interval, in seconds. A sample of monitor statistics is output to the log file after every interval. The default is every 60 seconds, if the -interval option is not specified. The minimum reporting interval is 2 seconds. If the interval specified for this option is invalid or less than 2 seconds, the interval is set to 2 seconds.
  The first display shows the counters from the start of the server. Subsequent displays show the difference from the previous display. You can usually obtain useful results by running the monitor at the default interval of 60 seconds during a query with performance problems or during a time of day that generally has performance problems. A very short interval may not provide meaningful results. The interval should be proportional to the job time; 60 seconds is usually more than enough time.
- -file_suffix suffix – Creates a monitor output file named dbname.connid-suffix. If you do not specify the -file_suffix option, the suffix defaults to iqmon. If you specify the -file_suffix option and do not provide a suffix or provide a blank string as a suffix, no suffix is used.
- -append or -truncate – Directs sp_iqsysmon to append to the existing output file or truncate the existing output file, respectively. Truncate is the default. If both options are specified, the option specified later in the string takes precedence.
- -section section(s) – Specifies the abbreviation of one or more sections to write to the monitor log file.
  See the Remarks section for a complete list of abbreviations.
  
  The default is to write all sections. The abbreviations specified in the sections list in file mode are the same abbreviations used in batch mode. When more than one section is specified, spaces must separate the section abbreviations.
  
  If the -section option is specified with no sections, none of the sections are monitored. An invalid section abbreviation is ignored and a warning is written to the IQ message file.

Privileges

You must have EXECUTE privilege on the system procedure, as well as the MONITOR system privilege.

Remarks

Report Sections or IQ Components to be Reported On	Abbreviation to Type
Buffer allocation	(main) – mbufalloc (temporary) – tbufalloc
Buffer manager	(main) – mbufman (temporary) – tbufman
Buffer pool	(main) – mbufpool (temporary) – tbufpool
Catalog statistics	catalog
CPU utilization	cpu
Free list management	(main)– mfreelist (temporary) – tfreelist
Memory management	memory
Prefetch management	(main)– mprefetch (temporary)– tprefetch
IQ RLV In-Memory Store statistics	rlv
Large Memory Allocator (LMA) statistics	lma
Server context statistics	server
Thread management	threads
Transaction management	txn

The sp_iqsysmon stored procedure monitors multiple components of SAP Sybase IQ, including the management of buffer cache, memory, threads, locks, I/O functions, and CPU utilization.

The sp_iqsysmon procedure supports two modes of monitoring:

Batch mode – sp_iqsysmon collects the monitor statistics for the period between starting and stopping the monitor or for the time period specified in the time-period parameter. At the end of the monitoring period, sp_iqsysmon displays a list of consolidated statistics.
sp_iqsysmon in batch mode is similar to the SAP Adaptive Server® Enterprise procedure sp_sysmon.
File mode – sp_iqsysmon writes the sample statistics in a log file for every interval period between starting and stopping the monitor.
The first display in file mode shows the counters from the start of the server. Subsequent displays show the difference from the previous display.

sp_iqsysmon in file mode is similar to the IQ UTILITIES command START MONITOR and STOP MONITOR interface.

Batch Mode Syntax Example

Example 1:

Starts the monitor in batch mode and displays all sections for the main and temporary stores:

sp_iqsysmon start_monitor
sp_iqsysmon stop_monitor

Example 2:

Starts the monitor in batch mode and displays the Buffer Manager and Buffer Pool statistics for the main store:

sp_iqsysmon start_monitor
sp_iqsysmon stop_monitor 'mbufman mbufpool'

Example 3:

Prints monitor information after 10 minutes:

sp_iqsysmon '00:10:00'

Example 4:

Prints only the Memory Manager section of the sp_iqsysmon report after 5 minutes:

sp_iqsysmon '00:05:00', memory

Example 5:

Starts the monitor, executes two procedures and a query, stops the monitor, then prints only the Buffer Manager section of the report:

sp_iqsysmon start_monitor
     go
     execute proc1
     go
     execute proc2
     go
     select sum(total_sales) from titles
     go
     sp_iqsysmon stop_monitor, bufman
     go

Example 6:

Prints only the Main Buffer Manager and Main Buffer Pool sections of the report after 2 minutes:

sp_iqsysmon '00:02:00', 'mbufman mbufpool'

Example 7:

Prints only the RLV sections of the report after 1 hour:

sp_iqsysmon '01:00:00','rlv'

Example 8:

Prints only the LMA sections of the report after 5 seconds:

sp_iqsysmon '00:00:05', 'lma'

Example 9:

Runs the monitor in batch mode for 10 seconds and displays the consolidated statistics at the end of the time period:

sp_iqsysmon '00:00:10', 'mbufpool memory'

File Mode Syntax Example

Example 1:

Truncates and writes information to the log file every 2 seconds between starting the monitor and stopping the monitor:

sp_iqsysmon start_monitor, 'filemode', '-interval 2'
     .
     .
     .
     sp_iqsysmon stop_monitor

Example 2:

Appends output for only the Main Buffer Manager and Memory Manager sections to an ASCII file with the name dbname.connid-testmon. For the database iqdemo, writes results in the file iqdemo.2-testmon:

sp_iqsysmon start_monitor, 'filemode',
     '-file_suffix testmon -append -section mbufman memory'
     .
     .
     .
     sp_iqsysmon stop_monitor

Example 3:

Prints only the RLV and LMA sections of the report:

sp_iqsysmon start_monitor,'filemode','-section rlv lma'
     sp_iqsysmon stop_monitor

Example 4:

Starts the monitor in file mode and writes statistics for Main Buffer Pool and Memory Manager to the log file every 5 seconds:

sp_iqsysmon start_monitor, ‘filemode’, '-interval 5 -section mbufpool memory'
sp_iqsysmon stop_monitor