|
Problem IC69100
|
Status: Closed |
SQLTEMPSPACE THRESHOLD DOES NOT WORK FOR WITH STATEMENT |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* USERS AFFECTED: *
* DB2 UDB Version 9.7. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error description field for more information. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 FixPack 6. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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
|
| Solution |
Problem was first fixed in DB2 UDB Version 9.7 FixPack 6. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 08.06.2010
05.06.2012
05.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
