Latest versionsfixlist
11.1.0.6 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
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC65165 Status: Closed

POINT-IN-TIME TABLESPACE ROLLFORWARD COMMAND MAY CONSUME ALL TEMPSPACES
AND HITS DISK FULL CONDITION.

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users prior to DB2 V95 FP6                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* POINT-IN-TIME TABLESPACE ROLLFORWARD COMMAND MAY CONSUME     * 
* ALLTEMPSPACES AND HITS DISK FULL CONDITION.                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V95 FP6.                                      * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Problem is first fixed in DB2 V95 FP6.
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC66475 IC66476 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
16.12.2009
22.06.2010
22.06.2010
Problem solved at the following versions (IBM BugInfos)
9.5.FP6
Problem solved according to the fixlist(s) of the following version(s)
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.