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