IBM Support

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
20 March 2021