If you issue the ALTER TABLE ... DROP DISTRIBUTION on a table
that has 5 or more columns in the partitioning key, it will
cause corruption in the internal table packed descriptor. The
corruption may not seem apparent until the next time you attempt
to alter or drop the table.
When you try to alter or drop the table, you will receive a
SQL0901N error with the following message:
2010-12-10-08.41.35.746777-480 I10075022A859 LEVEL: Severe
PID : 966724 TID : 25960 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-294 APPID:
128.09.208.34.38231.10121012203
AUTHID : SAPTST
EDUID : 25960 EDUNAME: db2agent (WQ1) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
probe:300
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 36
sqlerrmc: Unexpected missing data partition ID
sqlerrp : SQLRL4BF
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFE0C (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Additionally, you will find the following db2diag.log entries
that will help identify this problem.
2010-12-10-08.41.35.940680-480 E10130905A718 LEVEL: Info
(Origin)
PID : 966724 TID : 25960 PROC : db2sysc
0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-294 APPID:
128.09.208.34.38231.10121012203
AUTHID : SAPTST
EDUID : 25960 EDUNAME: db2agent (WQ1) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
"unexpected error but state is OK"
DATA #1 : String, 185 bytes
Compiler error stack for rc = -2144272209:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[330]
sqlnp_smactn[100]
sqlnq_alter_table[120]
sqlnq_table_old[127]
sqlnq_ftb::ftb_privatize[555]
This problem solely affects the table which has been altered and
altering the table requires ALTER, CONTROL or ALTERIN privilege.
Contact DB2 Support if you have already experienced this issue
and require assistance in fixing your environment. |
Current issue doesn't allow a complete drop for the partitioning
key.
Workaround to the issue if possible is to run ADMIN_TABLE_MOVE
to a new table with the new definition as appropriate. Here's
an example:
Assuming a given table T1, with 9 distribution key columns. The
objective is to:
a) Change the type a column part of the distribution key
b) Change the set of distribution key columns from 9 to 5.
This will be possible in a single partition database partition
group, or this on a serial (non-DPF) environment
Dropping this distribution is not an option, since it contains
more than 5 columns.
Instead, do the following:
CREATE TABLE PARTKEY.T1
(C1 INT, C2 INT, C3 INT, C4 INT, C5 INT, C6 INT,
C7 INT, C8 INT, C9 INT, C10 INT, C11 INT, C12 INT)
IN PK_TS1 DISTRIBUTE BY HASH (C1, C2, C3, C4, C5, C6, C7, C8,
C9);
CALL SYSPROC.ADMIN_MOVE_TABLE ('PARTKEY', 'T1', NULL, NULL,
NULL, NULL,
'C5, C6, C7, C8, C9', NULL,
'C1 INT, C2 INT, C3 INT, C4 INT, C5 INT, C6 SMALLINT,
C7 INT, C8 INT, C9 INT, C10 INT, C11 INT, C12 INT',
NULL, 'MOVE');
The ADMIN_MOVE_TABLE specified will both set new partitioning
keys as well as new definitions for the columns themselves.
Please evaluate this option along your Software Vendor or DBA
team about
this altenative process when planning to change table's
partitioning with 5+ columns
definition. Please verify your scripts to add the necessary
changes
as described above when changing table distribution keys. |
