The sp_iqstatus Stored Procedure

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

Note: 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-2013 by Sybase, Inc. All rights reserved.
Version:	16.0.0.6552/110812/P/GA/Sun_Sparc/OS 5.10/64bit/2012-08-12 03:08:39
Time Now:	2012-09-13 10:33:19.979
Build Time:	2012-08-13 03:08:39
File Format:	23 on 03/18/1999
Server Mode:	IQ Multiplex Coordinator 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 Shared Temp DB Files:	0
Shared Temp Store Out Of Space:	N
Number of Local Temp DB Files:	1
Local Temp Store Out Of Space:	N
DB Blocks: 1-12800	IQ_SYSTEM_MAIN
DB Blocks: 1045440-1058239	iq_main
Local Temp Blocks: 1-3200	IQ_SYSTEM_TEMP
Create Time:	2013-08-17 11:31:03.313
Update Time:	2013-09-12 10:32:00.077
Main IQ Buffers:	510, 64Mb
Temporary IQ Buffers:	510, 64Mb
Main IQ Blocks Used:	8076 of 19200, 42%=63Mb, Max Block#: 1051107
Shared Temporary IQ Blocks Used:	0 of 0, 0%=0Mb, Max Block#: 0
Local Temporary IQ Blocks Used:	113 of 1600, 7%=0Mb, Max Block#: 834
Main Reserved Blocks Available:	6400 of 6400, 100%=50Mb
Shared Temporary Reserved Blocks Available:	0 of 0, 0%=0Mb
Local Temporary Reserved Blocks Available:	1600 of 1600, 100%=12Mb
IQ Dynamic Memory:	Current: 150mb, Max: 150mb
Main IQ Buffers:'	Used: 509, Locked: 0
Temporary IQ Buffers:'	Used: 8, Locked: 0
Main IQ I/O:'	I: L184357/P71 O: C18370/D25255/P20297 D:5613 C:51.8
Temporary IQ I/O:'	I: L248471/P0 O: C22502/D25269/P4896 D:22494 C:59.3
Other Versions:'	2 = 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
Main Tlvlog Size:	Pages: 2, Recs: 413, Replays: 0/0
DB File Encryption Status:	OFF

Key to 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 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, add a dbspace.
The Main IQ Blocks Used and Temporary IQ Blocks Used are calculated based on the line DB Blocks (Total Main IQ Blocks) minus Main Reserved Blocks Availableand the line Temp Blocks (Total Temp IQ Blocks) minus Temporary Reserved Blocks Available, since the Reserved Blocks cannot be used for user operations.
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 becomes an old version and eventually be dropped. If they roll back, the “created” data is freed.
Main Reserved Blocks Available and Temporary Reserved Blocks Available show the amount of available reserved space.
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.