sp_iqspaceused Procedure

Shows information about space available and space used in the IQ store, IQ temporary store, and IQ global and local shared temporary stores.

Syntax

sp_iqspaceused(out mainKB unsigned bigint,
                				 out mainKBUsed unsigned bigint,
                				 out tempKB unsigned bigint,
                  				 out tempKBUsed unsigned bigint,
out shTempTotalKB unsigned bigint,
                  				 out shTempTotalKBUsed unsigned bigint,
out shTempLocalKB unsigned bigint,
                  				 out shTempLocalKBUsed unsigned bigint)

Permissions

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

Usage

sp_iqspaceused returns eight 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.

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.

sp_iqspaceused columns
Column name	Description
mainKB	The total IQ main store space in kilobytes.
mainKBUsed	The number of kilobytes of IQ main store space used by the database.
tempKB	The total IQ temporary store space in kilobytes.
tempKBUsed	The total IQ temporary store space in kilobytes.
shTempTotalKB	The total IQ global shared temporary store space in kilobytes.
shTempTotalKBUsed	The total IQ global shared temporary store space in kilobytes. (Secondary multiplex nodes return '(Null)'.)
shTempLocalKB	The total IQ local shared temporary store space in kilobytes.
shTempLocalKBUsed	The number of kilobytes of IQ local shared temporary store space in use by the database.

Example

sp_iqspaceused requires eight output parameters. The following example shows the creation of a user-defined stored procedure myspace that declares the eight 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;
  declare gt unsigned bigint;
  declare gu unsigned bigint;
  declare lt unsigned bigint;
  declare lu unsigned bigint;
  call sp_iqspaceused(mt,mu,tt,tu,gt,gu,lt,lu);
  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;
         cast(gt/1024 as unsigned bigint) as shTempTotalKB,
         cast(gu/1024 as unsigned bigint) as shTempTotalKBUsed,
        gu*100/gt as globalshtempPerCent;
         cast(lt/1024 as unsigned bigint) as shTempLocalMB,
         cast(lu/1024 as unsigned bigint) as shTempLocalKBUsed,
        lu*100/lt as localshtempPerCent;
end

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

myspace