DB2 FOR LUW / DB2FORLUW / 970 - 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.7.                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 FixPack 6.                            * 
****************************************************************

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 9.7 FixPack 6.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

08.06.2010
05.06.2012
05.06.2012

9.7.FP6