DB2 FOR LUW / DB2FORLUW / 950 - DB2

If you follow below steps, SQLTEMPSPACE THRESHOLD will not 
limit temporary tablespace TEMPSPACE1 data size for WITH 
statement. 
 
Problem Reproducible Steps: 
-------------------------------------------------- 
db2 create db sample 
db2 connect to sample 
db2 -tvf create_workload.sql 
db2 -tvf workload.sql2.txt 
   ==>> while this running, 'db2pd -tablespaces -db sample' 
shows 
   ==>> TEMPSPACE1 is having more than definded threshold 10 M 
as below. 
   ==>> (142996x4K/page=570MB ) 
-------------------------------------------------- 
$ db2pd -tablespaces -db sample 
 
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 
00:15... 
 
Tablespace Configuration: 
Address            Id    Type Content PageSz ExtentSz Auto 
Prefetch B... 
0x00002B83C4A275E0 0     DMS  Regular 4096   4        Yes  4 
   1... 
0x00002B83C4A27E60 1     SMS  SysTmp  4096   32       Yes  32 
<<<--- 
0x00002B83C4A28680 2     DMS  Large   4096   32       Yes  32 
   1... 
 
Tablespace Statistics: 
Address            Id    TotalPgs   UsablePgs  UsedPgs 
PndFreePgs ... 
0x00002B83C4A275E0 0     16384      16380      12288      0 
    ... 
0x00002B83C4A27E60 1     142996     142996     142996     0 
<<<--- 
0x00002B83C4A28680 2     8192       8160       96         0 
    ... 
    : 
-------------------------------------------------- 
 
-------------------------------------------------- 
$ cat create_workload.sql 
CREATE SERVICE CLASS "SC_USER" 
AGENT PRIORITY 10 
PREFETCH PRIORITY LOW 
; 
 
CREATE WORKLOAD "WL_USER" SESSION_USER GROUP('DASADM1') 
SERVICE CLASS "SC_USER" 
; 
 
CREATE THRESHOLD "TH_USER_LARGETMP" 
FOR SERVICE CLASS "SC_USER" ACTIVITIES 
ENFORCEMENT DATABASE PARTITION 
WHEN SQLTEMPSPACE > 10 M 
COLLECT ACTIVITY DATA 
ON COORDINATOR DATABASE PARTITION 
WITH DETAILS 
STOP EXECUTION 
; 
 
GRANT USAGE ON WORKLOAD WL_USER TO PUBLIC 
; 
 
-------------------------------------------------- 
$ cat workload.sql2.txt 
WITH X( 
        NUM 
        , tabname 
        , depth 
) AS ( 
        SELECT 
                ROW_NUMBER() OVER() NUM 
                , TABNAME 
                , 1 AS DEPTH 
        FROM 
                SYSCAT.TABLES 
        WHERE 
                TABSCHEMA='SYSCAT' 
        UNION ALL 
        SELECT 
                X.NUM + 1 
                , X.TABNAME 
                , X.DEPTH + 1 
        FROM 
                X 
                , SYSCAT.TABLES 
        WHERE 
                SYSCAT.TABLES.TABSCHEMA='SYSCAT' 
                and X.TABNAME=SYSCAT.TABLES.TABNAME 
) 
 
 
select 
        count(*) 
from 
        X 
--------------------------------------------------

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.5                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.5 FixPack 9.                            * 
****************************************************************

DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 9.5 FixPack 9.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC69100 IC69101 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.06.2010
08.03.2012
08.03.2012

9.5.FP9