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
-------------------------------------------------- |
