The sp_iqstatus stored procedure

The sp_iqstatus stored procedure provides a variety of IQ status information.

The following output is from the sp_iqstatus stored procedure:

Adaptive Server IQ (TM)              Copyright (c) 1992-2006 by Sybase, Inc.
                                     All rights reserved. 
Version:                             12.7.0/040810/P/GA/MS/
                                     Windows 2000/32bit/2006-06-10 09:54:19
Time Now:                            2006-06-11 18:53:34.274
Build Time:                          2006-06-10 09:54:19
File Format:                         23 on 03/18/1999
Server mode:                         IQ Server
Catalog Format:                      2
Stored Procedure Revision:           1
Page Size:                           131072/8192blksz/16bpp
Number of DB Spaces:                 1
Number of Temp Spaces:               1
DB Blocks: 1-5632                    IQ_SYSTEM_MAIN
Temp Blocks: 1-2816                  IQ_SYSTEM_TEMP
Create Time:                         2006-06-03 14:14:06.124
Update Time:                         2006-06-03 14:14:26.687
Main IQ Buffers:                     127, 16Mb
Temporary IQ Buffers:                95, 12Mb
Main IQ Blocks Used:                 4541 of 5632, 80%=35Mb, Max Block#: 5120
Temporary IQ Blocks Used:            65 of 2816, 2%=0Mb, Max Block#: 0
Main Reserved Blocks Available:      512 of 512, 100%=4Mb
Temporary Reserved Blocks Available: 256 of 256, 100%=2Mb
IQ Dynamic Memory:                   Current: 41mb, Max: 41mb
Main IQ Buffers:                     Used: 4, Locked: 0
Temporary IQ Buffers:                Used: 4, Locked: 0
Main IQ I/O:                         I: L168/P2 O: C2/D16/P15 D:0 C:100.0
Temporary IQ I/O:                    I: L862/P0 O: C136/D150/P17 D:132 C:100.0
Other Versions:                      0 = 0Mb
Active Txn Versions:                 0 = C:0Mb/D:0Mb

The following is a key to understanding the Main IQ I/O and Temporary IQ I/O output codes:

I: Input
L: Logical pages read (“Finds”)
P: Physical pages read
O: Output
C Pages Created
D Pages Dirtied
P: Physically Written
D: Pages Destroyed
C: Compression Ratio

Check the following information:

The lines Main IQ Blocks Used and Temporary IQ Blocks used tell you what portion of your dbspaces is in use. If the percentage of blocks in use (the middle statistic on these lines) is in the high nineties, you need to add a dbspace.

The lines Main IQ Buffers and Temporary IQ Buffers tell you the current sizes of your main and temp buffer caches.

Other Versions shows other db versions and the total space consumed. These versions will eventually be dropped when they are no longer referenced or referencable by active transactions.

Active Txn Versions shows the number of active write transactions and the amount of data they have created and destroyed. If these transactions commit, the “destroyed” data will become an old version and eventually be dropped. If they rollback, the “created” data will be freed.

Main Reserved Blocks Available and Temporary Reserved Blocks Available show the amount of reserved space that is available.

The lines Main IQ I/O and Temporary IQ I/O display I/O status in the same format as in the IQ message log. For an explanation of these statistics, see the section “Main buffer cache activity message” in Chapter 7, “Moving Data In and Out of Databases” of the Sybase IQ System Administration Guide.