Skip to Content
SAP IQ

IQ Shared Temp

Tags:

IQ Shared Temporary Store Shared dbspace is available from  SAP IQ 15.3 and above. IQ Shared Temporary Store is automatically

created when new database is created or database is upgraded to IQ 15.3 or above. Newly created IQ_SHARED_TEMP does not

include any dbfiles.  Administrator will need to add dbfiles to IQ_SHARED_TEMP  using:

                           ‘Alter dbspace  IQ_SHARED_TEMP add file’

Shared raw device storage is required for IQ Shared Temporary Store, which should be accessible  and writeable by all Nodes in
the IQ Multiplex. IQ Shared Temporary Store(IQ_SHARED_TEMP) is used to store intermediate results between IQ Multiplex Nodes

during Distributed Query Processing(DQP).

The IQ Shared System Temporary (IQ_SHARED_TEMP) dbspace cannot be dropped. The last  dbfile in the IQ_SHARED_TEMP
dbspace can be dropped or made in READONLY in the multiplex single node mode(-iqmpx_sn 1). The IQ_SHARED_TEMP dbspace

cannot be set to READONLY. Temporary user tables cannot be created in IQ_SHARED_TEMP using the ‘IN IQ_SHARED_TEMP’
clause.

  • To add dbfiles to the Shared Temporary Store:

                         alter dbspace IQ_SHARED_TEMP add file <logical file name> ‘<physical file path>’

  • To drop dbfiles from the Shared Temporary Store:

                         alter dbspace IQ_SHARED_TEMP drop file <logical file name>

  • To alter Shared Temporary Store dbfile read/write status:

      alter dbspace IQ_SHARED_TEMP alter file <logical file name> [READONLY | READWRITE]

For complete syntax see:

                 http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1602/doc/html/san1281564739089.html

IQ Shared Temporary dbspace dbfiles are added as READONLY file status Once dbfile is added to IQ Shared Temporary dbspace,

all Secondary nodes in the multiplex attempt to access the dbfile and send the file status to the Coordinator. Once all Secondary Nodes

send valid file status to the Coordinator, dbfile can be altered to READWRITE file status and can be used for DQP. Attempt to alter file status to  READWRITE without a VALID file status from all Multiplex Secondary Nodes will result in an SQL error.

         
(DBA)> alter dbspace IQ_SHARED_TEMP alter  file iqsharedtmpdsp2 READWRITE

     
      Could not execute statement.

    
      File may not be accessible from one or more multiplex servers. Use   sp_iqmpxfilestatus for further diagnosis.

  
      -- (slib/s_db.cxx 3026)

  
     SQLCODE=-1009192, ODBC 3 State="HY000"

   Line 1, column 1   Press ENTER to continue...

The sp_iqmpxfilestatus stored procedure can be used to check file status. If executed on Coordinator node, it displays file status for Coordinator and for
every shared dbspace dbfile on every included Secondary Node.  If executed on Secondary Node, it only displays file status for only that Node.

  • File Status States:

            - VALID:  file path access and permissions are correct

            - INVALID_PATH: file path name access problem

            - INVALID_PERM – Operating System file permissions are incorrect.

In certain scenarios when one of the Secondary Node is down,  DBA can bring IQ SHARED TEMPORARY dbspace dbfile online by bypassing

file status check with the  FORCE READWRITE clause


           alter dbspace IQ_SHARED_TEMP alter file <logical file name> FORCE READWRITE


SAPIQ database options MAX_TEMP_SPACE_PER_CONNECTION and QUERY_TEMP_SPACE_LIMIT limits the sum of local and shared temporary space used by a user connection.

  • If the total of local and shared temporary space exceeds the quota set for user connection, DQP query will fail with “Temporary space quota

        exceeded” error.

  • In case for DQP query, temporary space used is total temporary space used by all nodes participating in the DQP query.
  • The IQ Shared Temporary Store(IQ_SHARED_TEMP) and Local temporary Store(IQ_SYSTEM_TEMP) uses temporary cache configured

        using –iqtc startup switch

IQ 16 allows temporary data in IQ_SHARED_TEMP from both temporary and global temporary tables. On Multiplex Systems logical server policy option TEMP_DATA_IN_SHARED_TEMP governs temporary tables creation in IQ_SHARED_TEMP,  default value is ‘off’. Changing this option

requires IQ server to be restarted. ‘with stop server’ clause can be used to automatically shutdown the affected nodes in logical server for

whom ls policy option is changed.

  •   If ‘TEMP_DATA_IN_SHARED_TEMP’ login policy server option is set to ‘off’, temporary objects are created in IQ_SYSTEM_TEMP.
  •   If ‘TEMP_DATA_IN_SHARED_TEMP’ login policy server option is set to ‘on’, temporary objects are created in IQ_SHARED_TEMP.


          Example:


                         - alter ls policy lp1 TEMP_DATA_IN_SHARED_TEMP=on with stop server

                         - alter logical server ls1 policy lp1 with stop server

                         - drop logical server ls1 with stop server


sp_iqsharedtempdistrib:


           This system stored  procedure reports the current shared temp space usage distribution. When sp_iqsharedtempdistrib is executed from

           Coordinator, it reports shared temp space distribution for all nodes. When sp_iqsharedtempdistrib is executed from Secondary nodes,

           it reports shared temp space usage for only that node.  Shared temporary space is reserved on each node in the multiplex on demand by

           the  Coordinator. Shared temporary space is reserved for a node in an allocation unit. Nodes can request and hold multiple allocation units

          based on their current demand. These allocation units are leased by the nodes to use more space as needed and return the space to a global

          pool when they don’t need it. Allocation units expire when the shared  temporary space usage decreases, or lease expires or when nodes

          are shutdown.


sp_iqstatus and sp_iqspaceused:


       When these system stored procedures are executed on Coordinator node, they report total space in use on IQ_SHARED_TEMP. When
      executed on Secondary nodes, these system stored procedures do not report space used by shared dbspace ie. IQ_SYSTEM_MAIN,

      IQ_SHARED_TEMP and user dbspaces. sp_iqspaceused takes following arguments:


         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)

User-defined stored procedure myspace can be created that declares all seven sp_iqspaceused output parameters and then call sp_iqspaceused.

For myspace stored procedure code see:

        

            http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1602/doc/html/san1278453328180.html

sp_iqdbspace and sp_iqfile:


   sp_iqdbspace and sp_iqfile system stored procedure report include information for the IQ_SHARED_TEMP.


sp_iqcheckdb

     sp_iqcheckdb system stored procedure reports allocation information for IQ_SHARED_TEMP


                     sp_iqcheckdb ‘allocation dbspaceIQ_SHARED_TEMP’


      When executed from Coordinator Node, displays global block usage:


                 -  Blocks Total

                  -  Blocks Used

      When executed from any secondary node, sp_iqcheckdb reports blocks reserved for that secondary node:


                - Blocks Reserved for this Server Total

No comments