IBM Support

IT30568: AGGSQLTEMPSPACE THRESHOLD DOES NOT LIMIT TEMPORARY TABLE SPACE SIZE.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • If you follow below steps, AGGSQLTEMPSPACE THRESHOLD will not
    limit temporary table space TEMPSPACE1 size.
    
    
    Problem Reproducible Steps:
    --------------------------------------------------
    db2sampl
    db2 connect to sample
    
    db2 "CREATE SERVICE CLASS "MY_USER"
        AGENT PRIORITY 10
        PREFETCH PRIORITY LOW"
    
    db2 "CREATE WORKLOAD W_USER SESSION_USER ('DBUSER')
        SERVICE CLASS MY_USER"
    
    db2 "create threshold MY_AGGSQLTEMPSPACE_THRES FOR service class
    SYSDEFAULTSUBCLASS
        under MY_USER
        activities enforcement member enable
        WHEN AGGSQLTEMPSPACE > 10 M
        COLLECT ACTIVITY DATA on all STOP EXECUTION"
    
    db2 "ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER MY_USER
    COLLECT AGGREGATE ACTIVITY DATA EXTENDED"
    db2 "ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER MY_USER
    COLLECT AGGREGATE REQUEST DATA BASE"
    db2 "ALTER WORKLOAD W_USER COLLECT AGGREGATE ACTIVITY DATA
    EXTENDED"
    
    db2 "GRANT USAGE ON WORKLOAD W_USER TO PUBLIC"
    
    
    db2 "with temp1 as (
    select * from SYSCAT.COLUMNS
    ),
    temp2 as (
    select * from SYSCAT.COLUMNS
    ),
    temp3 as (
    select * from SYSCAT.COLUMNS
    ),
    temp4 as (
    select * from SYSCAT.COLUMNS
    )
    select * from temp1 full join temp2 ON 1=1 full join temp3 ON
    1=1
    full join temp4 ON 1=1 ORDER BY 2"
    
    ...
    ...
    
    For the query above AGGSQLTEMPSPACE threshold is not triggered
    at all.
    From the db2pd -db sample -tablespace one case see (before
    interrupt the query) output similar to the one below:
    
    
    Tablespace Configuration:
    Address            Id    Type Content PageSz ExtentSz Auto
    Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg
    RSE  Name
    ...
    0x00007FD7E43D28A0 6     SMS  SysTmp  32768  32       Yes  32
    2     2         On  1        0          31           No
    TMP_TBSP32
    
    Tablespace Statistics:
    Address            Id    TotalPgs   UsablePgs  UsedPgs
    PndFreePgs FreePgs    HWM        Max HWM    State
    MinRecTime NQuiescers PathsDropped TrackmodState
    ...
    0x00007FD7E43D28A0 6     403024     403024     403024     0
    0          -          -          0x00000000 0          0
    No           n/a
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2_v111m4fp6 or later                            *
    ****************************************************************
    

Problem conclusion

  • Upgrade to db2_v111m4fp6 or later
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT30568

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-10-10

  • Closed date

    2021-03-19

  • Last modified date

    2021-03-19

  • APAR is sysrouted FROM one or more of the following:

    IT22862

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 May 2022