Displays version usage for the IQ main store.
Simplex and multiplex.
Requires the MONITOR system privilege. Users without the MONITOR system privilege must be granted EXECUTE permission to run the stored procedure.
The sp_iqversionuse system stored procedure helps troubleshoot situations where the databases uses excessive storage space due to multiple table versions.
If out-of-space conditions occur or sp_iqstatus shows a high percentage of main blocks in use on a multiplex server, run sp_iqversionuse to find out which versions are being used and the amount of space that can be recovered by releasing versions.
The procedure produces a row for each user of a version. Run sp_iqversionuse first on the coordinator to determine which versions should be released and the amount of space in KB to be released when the version is no longer in use. Connection IDs are displayed in the IQConn column for users connected to the coordinator. Version usage due to secondary servers is displayed as the secondary server name with connection ID 0.
The amount of space is expressed as a range because the actual amount typically depends on which other versions are released. The actual amount of space released can be anywhere between the values of MinKBRelease and MaxKBRelease. The oldest version always has MinKBRelease equal to MaxKBRelease.
The WasReported column is used in a multiplex setting. WasReported indicates whether version usage information has been sent from the secondary server to the coordinator. WasReported is 0 initially on a coordinator for new versions. WasReported changes to 1 once the database server replicates version usage information back to the coordinator.
Run sp_iqversionuse on multiplex secondary servers to determine individual connections to secondary servers. Users from other servers are not displayed on a secondary server.
Column Name |
Description |
|---|---|
VersionID |
In simplex databases, the VersionID is displayed as zero. For the multiplex coordinator, the VersionID is the same as the TxnID of the active transaction and VersionID is the same as the CmtID of a committed transaction. In multiplex secondary servers, the VersionID is the CmtID of the transaction that created the database version on the multiplex coordinator. It is used internally by the SAP Sybase IQ in-memory catalog and the SAP Sybase IQ transaction manager to uniquely identify a database version to all nodes within a multiplex database. |
Server |
The server to which users of this version are connected |
IQConnID |
The connection ID using this version |
WasReported |
Indicates whether the server has received usage information for this version |
MinKBRelease |
The minimum amount of space returned once this version is no longer in use |
MaxKBRelease |
The maximum amount of space returned once this version is no longer in use |
Sample output from the sp_iqversionuse system procedure:
VersionID Server IQConnID WasReported ========= ====== ======== =========== 0 ab2ab_iqdemo 9 0 MinKBRelease MaxKBRelease ============ ============ 0 0
The following examples show multiplex output. The oldest version 42648 is in use by connection 108 on the coordinator (mpxw). Committing or rolling back the transaction on connection 108 releases 7.9MB of space. Version 42686 is in use by secondary server (mpxq) according to output from the coordinator. Using the secondary server output, the actual connection is connection 31. The actual amount of space returned from releasing version 42686 depends on whether 42648 is released first.
WasReported is 0 for versions 42715 and 42728 on the coordinator because these are new versions that have not yet been replicated. Since version 42728 does not appear on the secondary server output, it has not yet been used by the secondary server.
call dbo.sp_iqversionuse
|
VersionID |
Server |
IQConn |
WasReported |
MinKBRelease |
MaxKBRelease |
|---|---|---|---|---|---|
|
42648 |
'mpxw' |
108 |
1 |
7920 |
7920 |
|
42686 |
'mpxq' |
0 |
1 |
7920 |
304 |
|
42702 |
'mpxq' |
0 |
1 |
0 |
688 |
|
42715 |
‘mpxq' |
0 |
0 |
0 |
688 |
|
42728 |
'mpxq' |
0 |
0 |
0 |
688 |
call dbo.sp_iqversionuse
|
VersionID |
Server |
IQConn |
WasReported |
MinKBRelease |
MaxKBRelease |
|---|---|---|---|---|---|
|
42686 |
'mpxq' |
31 |
1 |
0 |
0 |
|
42715 |
'mpxq' |
00 |
1 |
0 |
0 |