Displays a variety of Sybase IQ status information about the current database.
sp_iqstatus
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
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. See “sp_iqspaceused procedure”.
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 does not show blocks that will be deallocated at the next checkpoint. These blocks do however, appear in sp_iqdbspace output as type X.
The following example shows objects in the iqdemo database
to better illustrate output. Note that iqdemo includes a sample
user dbspace named iq_main that may
not be present in your own databases.
The following output is from the sp_iqstatus stored procedure:
Sybase IQ (TM) Copyright (c) 1992-2009 by Sybase, Inc. All rights reserved. Version: 15.1.0/090416/P/MS/Windows/2003/ 32bit/2009-04-16 02:11:41 Time Now: 2009-04-21 13:48:22.319 Build Time: 2009-04-16 02:15:39 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 DB Files : 1 Temp Store Out Of Space: N DB Blocks: 1-3200 IQ_SYSTEM_MAIN DB Blocks: 1045440-1055039 iq_main Temp Blocks: 1-1600 IQ_SYSTEM_TEMP Create Time: 2009-04-03 11:30:20.674 Update Time: 2009-04-03 11:34:33.040 Main IQ Buffers: 255, 32Mb Temporary IQ Buffers: 191, 24Mb Main IQ Blocks Used: 5915 of 11200, 52%=46Mb, Max Block#:105278 Temporary IQ Blocks Used: 65 of 800, 8%=0Mb, Max Block#: 0 Main Reserved Blocks Available: 1600 of 1600, 100%=6Mb Temporary Reserved Blocks Available: 6400 of 6400, 100%=50Mb IQ Dynamic Memory: Current: 69mb, Max: 70mb Main IQ Buffers: Used: 17, Locked: 0 Temporary IQ Buffers: Used: 4, Locked: 0 Main IQ I/O: I: L1581/P14 O: C3/D163/P161 D:34 C:97.1 Temporary IQ I/O: I: L6627/P0 O: C1086/D1166/P83 D:1082 C:100.0 Other 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 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
sp_iqtransaction procedure and sp_iqversionuse procedure
“sp_iqstatus procedure” in Appendix A, “Multiplex Reference,” in Using Sybase IQ Multiplex.
