sp_iqspaceused procedure

Function

Shows information about space available and space used in the IQ Store and IQ Temporary Store.

Syntax

sp_iqspaceused(out mainKB unsigned bigint,
                				 out mainKBUsed unsigned bigint,
                				 out tempKB unsigned bigint,
                  				 out tempKBUsed unsigned bigint)

Usage

sp_iqspaceused returns four values as unsigned bigint out parameters. This system stored procedure can be called by user-defined stored procedures to determine the amount of Main and Temporary IQ Store space in use. In a multiplex, this procedure applies to the server on which it runs. If a query server has no IQ Local Store, it returns 0 in the first two out parameters.

Description

sp_iqspaceused returns a subset of the information provided by sp_iqstatus, but allows the user to return the information in SQL variables to be used in calculations.

Table 10-41: sp_iqspaceused columns

Column name

Description

mainKB

The total Main IQ Store space in kilobytes.

mainKBUsed

The number of kilobytes of Main IQ Store space used by the database.

tempKB

The total Temporary IQ Store space in kilobytes.

tempKBUsed

The number of kilobytes of Temporary IQ Store space in use by the database.

Example

sp_iqspaceused requires four output parameters. The following example shows the creation of a user-defined stored procedure myspace that declares the four output parameters and then calls sp_iqspaceused:

create procedure dbo.myspace()
begin
  declare mt unsigned bigint;
  declare mu unsigned bigint;
  declare tt unsigned bigint;
  declare tu unsigned bigint;
  call sp_iqspaceused(mt,mu,tt,tu);
  select cast(mt/1024 as unsigned bigint) as mainMB,
         cast(mu/1024 as unsigned bigint) as mainusedMB,
        mu*100/mt as mainPerCent,
        cast(tt/1024 as unsigned bigint) as tempMB,
         cast(tu/1024 as unsigned bigint) as tempusedMB,
        tu*100/tt as tempPerCent;
end

To display the output of sp_iqspaceused, run the procedure myspace:

myspace