sp_iqstatus Procedure

Displays a variety of SAP Sybase IQ status information about the current database.

Syntax

sp_iqstatus

Applies to

Simplex and multiplex.

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following system privileges:
  • ALTER DATABASE
  • MANAGE ANY DBSPACE
  • MONITOR
  • SERVER OPERATOR

Remarks

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.

Memory used by direct-attached storage devices in the cache dbspace can be monitored with sp_iqstatus:
Measurement Description
Number of Cache Dbspace Files Number of cache dbspace dbfiles in the database.
Cache Dbspace Block Identifies the cache dbspace blocks and the corresponding storage device dbfile name.
Cache Dbspace IQ Blocks Used Number of IQ blocks used, compared to the total number of IQ blocks. Usage is also shown as a percentage. If the percentage is high, consider adding additional storage.

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.

Example

Note: This example 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-2013 by SAP AG or an SAP affiliate company. All rights reserved.
 Version: 16.0.0.562/130821/P/Mainline/Sun_x64/OS 5.10/64bit/2013-08-21 06:15:41
 Time Now: 2013-08-21 06:27:14.150
 Build Time: 2013-08-21 06:15:41
 File Format: 23 on 03/18/1999 
 Server mode: IQ Server
 Catalog Format: 2
 Stored Procedure Revision: 1
 Page Size: 65536/4096blksz/16bpp
 Number of Main DB Files: 2
 Main Store Out Of Space: N
 Number of Cache Dbspace Files: 5
 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-25600 IQ_SYSTEM_MAIN
 DB Blocks: 522208-547807 MainUser
 Cache Dbspace Blocks: 1-5120 ssd_dev_1
 Cache Dbspace Blocks: 522208-527327 ssd_dev_2
 Cache Dbspace Blocks: 1044416-1049535 ssd_dev_3
 Cache Dbspace Blocks: 1566624-1571743 ssd_dev_4
 Cache Dbspace Blocks: 2088832-2093951 ssd_dev_5
 Local Temp Blocks: 1-25600 IQ_SYSTEM_TEMP
 Create Time: 2013-08-21 06:27:05.444
 Update Time: 2013-08-21 06:27:14.035
 Main IQ Buffers: 1588, 100Mb
 Temporary IQ Buffers: 1588, 100Mb
 Main IQ Blocks Used: 5250 of 38400, 13%=20Mb, Max Block#: 5313
 Cache Dbspace IQ Blocks Used: 197 of 25600, 0%=0Mb, Max Block#: 0
 Shared Temporary IQ Blocks Used: 0 of 0, 0%=0Mb, Max Block#: 0
 Local Temporary IQ Blocks Used: 65 of 12800, 0%=0Mb, Max Block#: 0
 Main Reserved Blocks Available: 12800 of 12800, 100%=50Mb
 Shared Temporary Reserved Blocks Available: 0 of 0, 0%=0Mb
 Local Temporary Reserved Blocks Available: 12800 of 12800, 100%=50Mb
 IQ Dynamic Memory: Current: 292mb, Max: 308mb
 Main IQ Buffers: Used: 18, Locked: 0
 Temporary IQ Buffers: Used: 4, Locked: 0
 Main IQ I/O: I: L459/P9 O: C21/D33/P22 D:1 C:100.0
 Temporary IQ I/O: I: L320/P0 O: C54/D59/P8 D:50 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: 0
 Blocks in next ISF Backup: 0 Blocks: =0Mb
 Blocks in next ISI Backup: 0 Blocks: =0Mb
 IQ large memory space: 2048Mb
 IQ large memory flexible percentage: 50
 IQ large memory flexible used: 0Mb
 IQ large memory inflexible percentage: 90
 IQ large memory inflexible used: 0Mb
 IQ large memory anti-starvation percentage: 50
DB File Encryption Status: OFF
 RLV memory limit: 2048Mb
 RLV memory used: 0Mb

The following is a key to understanding the Main IQ I/O and Temporary IQ I/O output codes:

Parent topic: System Procedures