|
Problem IC73805
|
Status: Closed |
REORG USING SMS TEMP TABLESPACE CAN FAIL WITH ARCHITECTURAL PAGE LIMIT REACHED |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Performing a reorg on a very large table using a SMS temporary
tablespace
can fail with SQL2216N.
eg.
db2 reorg table DB2.TAB1 index DB2."TAB1_IDX1" allow no access
use SMSTEMP0
SQL2216N SQLerror "-659" occurred while reorganizing
a database table or its indexes.
db2diag.log:
2010-11-19-19.32.15.187228+060 I2537104E554 LEVEL:
Warning
PID : 31593 TID : 47403776338240PROC :
db2sysc 0
INSTANCE: db2 NODE : 000 DB : DB2
APPHDL : 0-10667 APPID: *LOCAL.db2.101119180003
AUTHID : DB2
EDUID : 82 EDUNAME: db2agent (DB2) 0
FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary
logging fu, probe:0
MESSAGE : Reorg table failed.
DATA #1 : String, 73 bytes
Table(6:11023)=DB2.TAB1, Flags=x00000032, IID=1, Temp=16,
LongTemp=0
2010-11-19-19.32.15.230702+060 I2537659E561 LEVEL:
Warning
PID : 31593 TID : 47403776338240PROC :
db2sysc 0
INSTANCE: db2 NODE : 000 DB : DB2
APPHDL : 0-10667 APPID: *LOCAL.db2ebo.101119180003
AUTHID : DB2
EDUID : 82 EDUNAME: db2agent (DB2) 0
FUNCTION: DB2 UDB, data management, sqldTableReorgObject,
probe:4296
RETCODE : ZRC=0x8404000A=-2080112630=SQLD_RC_MAX_OBJ_SIZE
"ARCHITECTURAL PAGE LIMIT REACHED"
DIA8710E Maximum table size was reached.
2010-11-19-19.32.15.250738+060 I2538221E533 LEVEL: Severe
PID : 31593 TID : 47403776338240PROC :
db2sysc 0
INSTANCE: db2 NODE : 000 DB : DB2
APPHDL : 0-10667 APPID: *LOCAL.db2ebo.101119180003
AUTHID : DB2
EDUID : 82 EDUNAME: db2agent (DB2) 0
FUNCTION: DB2 UDB, relation data serv, sqlrreorg_table,
probe:550
MESSAGE : Reorg: DMS return rc =
DATA #1 : Hexdump, 4 bytes
0x00002B1D0D3F2EC0 : 0A00 0484
Workaround:
Use a DMS TEMP tablespace. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* Performing a reorg on a very large table using a SMS *
* temporary *
* tablespace *
* *
* can fail with SQL2216N. *
* *
* eg. *
* *
* *
* *
* db2 reorg table DB2.TAB1 index DB2."TAB1_IDX1" allow no *
* access *
* use SMSTEMP0 *
* *
* SQL2216N SQLerror "-659" occurred while reorganizing *
* *
* a database table or its indexes. *
* *
* *
* *
* db2diag.log: *
* *
* *
* *
* 2010-11-19-19.32.15.187228+060 I2537104E554 LEVEL: *
* *
* Warning *
* *
* PID : 31593 TID : 47403776338240PROC : *
* *
* db2sysc 0 *
* *
* INSTANCE: db2 NODE : 000 DB : DB2 *
* *
* APPHDL : 0-10667 APPID: *
* *LOCAL.db2.101119180003 *
* AUTHID : DB2 *
* *
* EDUID : 82 EDUNAME: db2agent (DB2) 0 *
* *
* FUNCTION: DB2 UDB, trace services, sqlt_logerr_string *
* (secondary *
* logging fu, probe:0 *
* *
* MESSAGE : Reorg table failed. *
* *
* DATA #1 : String, 73 bytes *
* *
* Table(6:11023)=DB2.TAB1, Flags=x00000032, IID=1, Temp=16, *
* *
* LongTemp=0 *
* *
* *
* *
* 2010-11-19-19.32.15.230702+060 I2537659E561 LEVEL: *
* *
* Warning *
* *
* PID : 31593 TID : 47403776338240PROC : *
* *
* db2sysc 0 *
* *
* INSTANCE: db2 NODE : 000 DB : DB2 *
* *
* APPHDL : 0-10667 APPID: *
* *LOCAL.db2ebo.101119180003 *
* AUTHID : DB2 *
* *
* EDUID : 82 EDUNAME: db2agent (DB2) 0 *
* *
* FUNCTION: DB2 UDB, data management, sqldTableReorgObject, *
* *
* probe:4296 *
* *
* RETCODE : ZRC=0x8404000A=-2080112630=SQLD_RC_MAX_OBJ_SIZE *
* *
* "ARCHITECTURAL PAGE LIMIT REACHED" *
* *
* DIA8710E Maximum table size was reached. *
* *
* *
* *
* 2010-11-19-19.32.15.250738+060 I2538221E533 LEVEL: *
* Severe *
* PID : 31593 TID : 47403776338240PROC : *
* *
* db2sysc 0 *
* *
* INSTANCE: db2 NODE : 000 DB : DB2 *
* *
* APPHDL : 0-10667 APPID: *
* *LOCAL.db2ebo.101119180003 *
* AUTHID : DB2 *
* *
* EDUID : 82 EDUNAME: db2agent (DB2) 0 *
* *
* FUNCTION: DB2 UDB, relation data serv, sqlrreorg_table, *
* *
* probe:550 *
* *
* MESSAGE : Reorg: DMS return rc = *
* *
* DATA #1 : Hexdump, 4 bytes *
* *
* 0x00002B1D0D3F2EC0 : 0A00 0484 *
* *
* *
* *
* Workaround: *
* *
* *
* *
* Use a DMS TEMP tablespace. *
****************************************************************
* RECOMMENDATION: *
* upgrade to version 9.07.004 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
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 |
Corrected in V97 fixpack4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.01.2011
17.01.2011
17.01.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.,
9.7.7.004
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
