The sp_iqstatus stored procedure

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

The following example shows output from the iqdemo sample database. The sample user dbspace iq_main may not be present in your own user-created databases.

The following output is from the sp_iqstatus stored procedure:

Sybase IQ (TM)              Copyright (c) 1992-2010 by Sybase, Inc.
                                     All rights reserved. 
Version:                             15.2.0.5027/0490416/P/GA/MS/
                                     Windows 2000/32bit/2010-04-16 02:11:41
Time Now:                            2010-04-27 14:09:00.648
Build Time:                          2010-04-16 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 Main DB Files:             2
Main Store Out of Space:             N
Number of Temp Files:                1
DB Blocks: 1-3200                    IQ_SYSTEM_MAIN
DB Blocks: 1045440-1055039           iq_main
Temp Blocks: 1-1600                  IQ_SYSTEM_TEMP
Create Time:                         2010-04-03 14:14:06.124
Update Time:                         2010-04-25 14:14:26.687
Main IQ Buffers:                     255, 32Mb
Temporary IQ Buffers:                191, 24Mb
Main IQ Blocks Used:                 5915 of 11200, 52%=46Mb, 
                                     Max Block#: 1051278
Temporary IQ Blocks Used:            81 of 800, 10%=0Mb, Max Block#: 161
Main Reserved Blocks Available:      1600 of 1600, 100%=12Mb
Temporary Reserved Blocks Available: 800 of 800, 100%=6Mb
IQ Dynamic Memory:                   Current: 69mb, Max: 70mb
Main IQ Buffers:                     Used: 6, Locked: 0
Temporary IQ Buffers:                Used: 5, Locked: 0
Main IQ I/O:                         I: L899/P3 O: C3/D91/P89 D:0 C:100.0
Temporary IQ I/O:                    I: L4043/P0 O:C674/D718/P47 D:669 C:100.0
Other Txn Versions:                  0 = 0Mb
Active Txn Versions:                 0 = C:0Mb/D:0Mb
Last Full Backup ID:                 0
Last Full Backup Time:               
Last Backup ID:                      0
Last Backup Type:                    None
Last Backup Time:                    
DB Updated:                          1
Blocks in next ISF Backup:           0 Blocks: =0Mb
Blocks in next ISI Backup:           0 Blocks: =0Mb
File Encryption Status:              OFF

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”.