sp_iqspaceused Procedure

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,    
               out rlvLogKB          unsigned bigint,     
               out rlvLogKBUsed      unsigned bigint)

Applies to

Simplex and multiplex.

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following system privilege:

ALTER DATABASE
MANAGE ANY DBSPACE
MONITOR

Remarks

sp_iqspaceused returns several values as unsigned bigint out parameters. This system stored procedure can be called by user-defined stored procedures to determine the amount of main, temporary, and RLV store space in use.

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.

If run on a multiplex database, this procedure applies to the server on which it runs. Also returns space used on IQ_SHARED_TEMP.

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. Secondary multiplex nodes return '(Null)'.
tempKB	The total IQ temporary store space, in kilobytes.
tempKBUsed	The number of kilobytes of total IQ temporary store space in use by the database.
shTempTotalKB	The total IQ global shared temporary store space, in kilobytes.
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.
rlvLogKB	The total RLV store space, in kilobytes.
rlvLogKBUsed	The number of kilobytes of RLV store space in use by the database.

Example

sp_iqspaceused requires seven output parameters. Create a user-defined stored procedure myspace that declares the seven output parameters, then calls sp_iqspaceused:

  create or replace 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; 
     declare tt_t unsigned bigint;
     declare mt_t unsigned bigint;
     declare gt_t unsigned bigint;
     declare lt_t unsigned bigint;     
     call sp_iqspaceused(mt,mu,tt,tu,gt,gu,lt,lu);       
     if (tt = 0) then
        set tt_t = 0;
     else
        set tt_t = tu*100/tt;
     end if;
     if (mt = 0) then
        set mt_t = 0;
     else
        set mt_t = mu*100/mt;
     end if;
     if (gt = 0) then
        set gt_t = 0;
     else
        set gt_t = gu*100/gt;
     end if;
     if (lt = 0) then
        set lt_t = 0;
     else
        set lt_t = lu*100/lt;
     end if;
  select cast(mt/1024 as unsigned bigint) as mainMB,
         cast(mu/1024 as unsigned bigint) as mainusedMB, mt_t as mainPerCent,
         cast(tt/1024 as unsigned bigint) as tempMB,
         cast(tu/1024 as unsigned bigint) as tempusedMB, tt_t as tempPerCent,
         cast(gt/1024 as unsigned bigint) as shTempTotalKB,
         cast(gu/1024 as unsigned bigint) as shTempTotalKBUsed, gt_t as globalshtempPerCent,
         cast(lt/1024 as unsigned bigint) as shTempLocalMB,
         cast(lu/1024 as unsigned bigint) as shTempLocalKBUsed, lt_t as localshtempPerCent;
end

To display the output of sp_iqspaceused, execute myspace:

myspace