Displays a variety of SAP Sybase IQ status information about the current database.
Simplex and multiplex.
Requires the ALTER DATABASE, MANAGE ANY DBSPACE, MONITOR, or SERVER OPERATOR system privilege. Users without one of these system privileges must be granted EXECUTE permission.
Shows status information about the current database, including the database name, creation date, page size, number of dbspace segments, block usage, buffer usage, I/O, backup information, and so on.
sp_iqstatus displays an out-of-space status for main and temporary stores. If a store runs into an out-of-space condition, sp_iqstatus shows Y in the store’s out-of-space status display value.
Memory used by the row-level versioning (RLV) store can be monitored with sp_iqstatus. The RLV memory limit row displays the memory limit as specified by the -iqrlvmem server option, or the sa_server_option rlv_memory_mb. The RLV memory used row displays the amount of memory used by the RLV store.
sp_iqspaceused returns a subset of the same information as provided by sp_iqstatus, but allows the user to return the information in SQL variables to be used in calculations.
To display space that can be reclaimed by dropping connections, use sp_iqstatus and add the results from the two returned rows:
(DBA)> select * from sp_iqstatus() where name like '%Versions:%' Execution time: 6.25 seconds Name Value ---------------------------- Other Versions: 2 = 1968Mb Active Txn Versions: 1 = C:2175Mb/D:2850Mb (First 2 rows)
The above example output shows that one active write transaction created 2175MB and destroyed 2850 MB of data. The total data consumed in transactions and not yet released is 4818MB, or 1968MB + 2850MB = 4818MB.
sp_iqstatus omits blocks that will be deallocated at the next checkpoint. These blocks do however, appear in sp_iqdbspace output as type X.
In a multiplex, this procedure also lists information about the shared IQ store and IQ temporary store. If sp_iqstatusshows a high percentage of main blocks in use on a multiplex server, run sp_iqversionuse to see which versions are being used and the amount of space that can be recovered by releasing versions.
The following output is from the sp_iqstatus stored procedure:
SAP Sybase IQ (TM) Copyright (c) 1992-2013 by Sybase, Inc. All rights reserved. Version: 16.0.0.160/120507/D/ELAN/Sun_x64/OS 5.10/ 64bit/2012-05-07 17:36:36 Time Now: 2013-05-16 09:53:13.590 Build Time: 2013-05-07 17:36:36 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 : 3 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-640000 IQ_SYSTEM_MAIN DB Blocks: 1045440-130101439 iqmain1 DB Blocks: 2090880-2346879 iqmain2 Local Temp Blocks: 1-384000 IQ_SYSTEM_TEMP Create Time: 2013-05-08 15:54:15.549 Update Time: 2013-05-16 09:53:00.077 Local Temp Blocks: 1-1600 IQ_SYSTEM_TEMP Create Time: 2013-05-08 15:54:15.549 Update Time: 2013-05-16 09:53:00.077 Main IQ Buffers: 510, 64Mb Temporary IQ Buffers: 510, 64Mb Main IQ Blocks Used: 157379 of 1126400, 13%=1229Mb, Max Block#: 2128363 Shared Temporary IQ Blocks Used: 0 of 0, 0%=0Mb, Max Block#: 0 Local Temporary IQ Blocks Used: 81 of 358400, 0%=0Mb, Max Block#: 81 Main Reserved Blocks Available: 25600 of 25600, 100%=200Mb Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb Local Temporary Reserved Blocks Available: 25600 of 25600, 100%=200Mb IQ Dynamic Memory: Current: 178mb, Max: 178mb Main IQ Buffers: Used: 99, Locked: 0 Temporary IQ Buffers: Used: 5, Locked: 0 Main IQ I/O: I: L60904/P29 O: C5463/D11343/P9486 D:5450 C:51.3 Temporary IQ I/O: I: L12526/P0 O: C165/D319/P157 D:160 C:100.0 Other Versions: 6 = 0Mb Active Txn Versions: 0 = C:0Mb/D:0Mb Last Full Backup ID: 0 Last Full Backup Time: Last Backup ID: 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: 1, Recs: 193, Replays: 0/0 DB 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