home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC69099 Status: Geschlossen

SQLTEMPSPACE THRESHOLD DOES NOT WORK FOR WITH STATEMENT

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
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 
--------------------------------------------------
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.5                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.5 FixPack 9.                            * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in DB2 UDB Version 9.5 FixPack 9.
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC69100 IC69101 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
08.06.2010
08.03.2012
08.03.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.FP9
Problem behoben lt. FixList in der Version
9.5.0.9 FixList