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-2011 by Sybase, Inc. All rights reserved. Version: 15.3.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
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
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 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 Available and 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 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.