sp_iqversionuse procedure

Function

Displays version usage for the IQ main store.

Syntax

sp_iqversionuse

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

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. For information on multiplex capability, see Using Sybase IQ Multiplex.

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.

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 SQL Remote replicates version usage information back to the coordinator. If WasReported is 0 for an extended period, SQL Remote might be stopped.

NoteThe WasReported column is used in a multiplex setting. For more information on multiplex, see Using Sybase IQ Multiplex.

Table 7-66: sp_iqversionuse columns

Column name

Description

VersionID

The version identifier

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

Example

The following table illustrates sample output from the sp_iqversionuse system procedure:

VersionID Server        IQConnID WasReported 
========= ======        ======== =========== 
        0 ab2ab_iqdemo         9           0 

MinKBRelease  MaxKBRelease
============  ============
           0             0