While running Point-in-time tablespace rollforward command, DB2
may consume all TEMPSPACE and results into DISK FULL condition.
It is due to one sql query, written inside db2 code.
db2diag.log contains entries like below. It will also documents
the query which cause this problem.
2009-11-24-11.09.26.099978+060 I16822A4034 LEVEL: Warning
PID : 1069112 TID : 1 PROC : db2bp
INSTANCE: db2inst NODE : 000
APPID : *LOCAL.db2inst.091124100917
EDUID : 1
FUNCTION: DB2 UDB, data protection services,
sqlpCheckConstraints, probe:50
MESSAGE : Rollforward query to check tables to be put in Set
Integrity Pending
DATA #1 : String, 3629 bytes
with dep_cnt(total_dep) as (select count(*) from
syscat.viewdep), intbspace(tabschema, tabname, count_star) as
(select tabsc
hema, tabname, count(*) from syscat.datapartitions where
tbspaceid in (5) group by tabschema, tabname),
notintbspace(tab
schema, tabname, count_star) as (select tabschema, tabname,
count(*) from syscat.datapartitions where tbspaceid not in (5)
group by tabschema, tabname), und_tab(schema, name, bschema,
bname, btype, bproperty, level) as (select tables.tabschema
, tables.tabname, tables.tabschema, tables.tabname,
tables.type, tables.property, 0 from syscat.tables tables,
intbspac
e where (tables.type = 'S' or (tables.type = 'T'
and substr(tables.property,2,1) = 'Y')) and (tables.
tabschema=intbspace.tabschema and
tables.tabname=intbspace.tabname) union all select
und_tab.schema, und_tab.name, viewd
ep.bschema, viewdep.bname, viewdep.btype, tables.property,
und_tab.level + 1 from syscat.viewdep viewdep, und_tab, sysc
at.tables tables where (und_tab.btype in ('S', 'V', 'W') or
(und_tab.btype = 'T' and substr(und_tab.bproperty,2
,1) = 'Y')) and viewdep.btype in ('S', 'T', 'V', 'W') and
viewdep.viewschema = und_tab.bschema and viewdep.viewname = u
nd_tab.bname and tables.tabschema = viewdep.bschema and
tables.tabname = viewdep.bname and und_tab.level < (select
dep_
cnt.total_dep from dep_cnt)), dep_tab(schema, name, type,
property, level) as (select tables.tabschema, tables.tabname,
tabl
es.type, tables.property, 0 from syscat.tables tables,
intbspace where tables.type in ('S', 'T', 'U') and
(t
ables.tabschema=intbspace.tabschema and
tables.tabname=intbspace.tabname) union all select
viewdep.viewschema, viewdep.v
iewname, viewdep.dtype, tables.property, dep_tab.level + 1
from syscat.viewdep viewdep, dep_tab, syscat.tables tables
where viewdep.dtype in ('S', 'V', 'W', 'T') and
viewdep.bschema = dep_tab.schema and viewdep.bname =
dep_tab.name and
tables.tabschema = viewdep.viewschema and tables.tabname =
viewdep.viewname and dep_tab.level < (select
dep_cnt.total_dep
from dep_cnt)), child_tab(schema, name) as (select
child.tabschema, child.tabname from syscat.tables child,
syscat.table
s parent, syscat.references references, syscat.tabconst
tabconst, intbspace, notintbspace where refere
nces.tabschema = child.tabschema and references.tabname =
child.tabname and parent.tabschema = references.reftabschema
and parent.tabname = references.reftabname and
references.tabname = tabconst.tabname and
references.tabschema = tab
const.tabschema and references.constname = tabconst.constname
and tabconst.enforced = 'Y' and ((child.tabschema=intb
space.tabschema and child.tabname=intbspace.tabname and
parent.tabschema=notintbspace.tabschema and parent.tabname=noti
ntbspace.tabname) or
(child.tabschema=notintbspace.tabschema and
child.tabname=notintbspace.tabname and parent.tab
schema=intbspace.tabschema and
parent.tabname=intbspace.tabname))) select distinct * from
(select und_tab.schema, und_tab.n
ame from und_tab, notintbspace where und_tab.btype in('S',
'T') and und_tab.bschema=notintbspace.tabschema and u
nd_tab.bname=notintbspace.tabname union all select
dep_tab.schema, dep_tab.name from dep_tab, notintbspace where
(dep
_tab.type = 'S' or (dep_tab.type = 'T' and
substr(dep_tab.property,2,1) = 'Y')) and
dep_tab.schema=notint
bspace.tabschema and dep_tab.name=notintbspace.tabname union
all select child_tab.schema, child_tab.name from child_tab
) x
2009-11-24-11.09.26.338923+060 I20857A337 LEVEL: Warning
PID : 1069112 TID : 1 PROC : db2bp
INSTANCE: db2inst NODE : 000
APPID : *LOCAL.db2inst.091124100917
EDUID : 1
FUNCTION: DB2 UDB, data protection services,
sqlpCheckConstraints, probe:85
MESSAGE : Warning sqlcode 347 is ignored
2009-11-24-11.32.39.357053+060 E21195A961 LEVEL: Error
(OS)
PID : 1736804 TID : 2572 PROC : db2sysc
0
INSTANCE: db2inst NODE : 000 DB : db2inst
APPHDL : 0-12 APPID:
*LOCAL.db2inst.091124100917
AUTHID : db2inst
EDUID : 2572 EDUNAME: db2agent (db2inst) 0
FUNCTION: DB2 UDB, oper system services, sqloseekwrite64,
probe:40
MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
DIA8312C Disk was full.
CALLED : OS, -, pwrite
OSERR : ENOSPC (28) "No space left on device"
DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes
0x070000000DBF49E0 : 0000 0230 0000 0200
...0....
DATA #2 : unsigned integer, 8 bytes
4096
DATA #3 : signed integer, 8 bytes
174071808
DATA #4 : signed integer, 8 bytes
-1
DATA #5 : String, 105 bytes
Search for ossError*Analysis probe point after this log entry
for further
self-diagnosis of this problem.
2009-11-24-11.32.39.404991+060 I22157A2718 LEVEL: Error
(OS)
PID : 1736804 TID : 2572 PROC : db2sysc
0
INSTANCE: db2inst NODE : 000 DB : db2inst
APPHDL : 0-12 APPID:
*LOCAL.db2inst.091124100917
AUTHID : db2inst
EDUID : 2572 EDUNAME: db2agent (db2inst) 0
FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100
CALLED : OS, -, pwrite
OSERR : ENOSPC (28) "No space left on device"
To confirm if you are hitting this issue, you can run the query
alone from command line. It should also results in "SQL0968C
The file system is full. SQLSTATE=57011" error. |
