Results 1 to 4 of 4

Thread: Tempspace issue

  1. #1
    Join Date
    Jul 2013
    Posts
    31

    Unanswered: Tempspace issue

    Is there a way/query to extract the dynamic/static query which is utilizing the tempspace the most?
    We are using DB2 9.7 Fp4 on Linux
    Last edited by db2j; 04-06-15 at 03:29.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    21
    Provided Answers: 1
    Not sure.. if this will help your question.. here is something which can be run.. for... identifying problematic SQL

    select substr(a.tabschema,1,20) as TABSCHEMA,
    substr(a.tabname,1,25) as TABNAME,
    a.rows_read as RowsRead,
    CAST((((a.rows_READ) * 100.0) / (select (sum(z.rows_read) + 1.0)
    from sysibmadm.snaptab z
    where a.DBPARTITIONNUM = Z.DBPARTITIONNUM)) as Decimal(5,2)) as pct_db_tb_rowsread,
    CAST((a.rows_read/ (b.commit_sql_stmts + b.rollback_sql_stmts +1.0)) as decimal(13,3)) as TBRRTX
    from SYSIBMADM.snaptab a,
    SYSIBMADM.snapdb b
    Where a.DBPARTITIONNUM = b.DBPARTITIONNUM
    order by a.rows_read desc fetch first 20 rows only;


    Surgeon

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Starting from 9.7 we have an ability to use new monitoring functions.
    I would recommend to use MON_GET_PKG_CACHE_STMT to get info not only for dynamic sql, but for static as well.

    If you wan to get info for running statements at the moment only, you can do it like this:
    Code:
    SELECT a.application_handle, a.uow_id, a.activity_id, a.dbpartitionnum, M.*
    FROM 
      TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97(null, -2)) A
    , TABLE(MON_GET_ACTIVITY_DETAILS(a.application_handle, a.uow_id, a.activity_id, a.dbpartitionnum)) D
    , XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'), 
     '$M/db2_activity_details' 
      PASSING XMLPARSE(DOCUMENT D.DETAILS) AS "M" COLUMNS 
      ACTIVITY_STATE           VARCHAR(17)  PATH 'activity_state'
    , ACTIVITY_TYPE            VARCHAR(9)   PATH 'activity_type'
    
    , pool_temp_data_p_reads   BIGINT       PATH 'activity_metrics/pool_temp_data_p_reads'
    , pool_temp_data_l_reads   BIGINT       PATH 'activity_metrics/pool_temp_data_l_reads'
    , pool_temp_index_p_reads  BIGINT       PATH 'activity_metrics/pool_temp_index_p_reads'
    , pool_temp_index_l_reads  BIGINT       PATH 'activity_metrics/pool_temp_index_l_reads'
    
    , STMT_TEXT                VARCHAR(1024) PATH 'stmt_text'
    ) M
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •