If a Partitioned Table with NULLS FIRST clause on the
partitioning column is Altered to drop some columns, any
subsequent insert on the table with a NULL on the partitioning
column will fail with SQL0327N.
Example:
CREATE TABLE test ("OID" BIGINT NOT NULL,"CODEGROUPKEY"
VARCHAR(2) ,"PS_OID" BIGINT )
PARTITION BY RANGE("PS_OID" NULLS FIRST)
(PART "P00000000000000000000" STARTING(MINVALUE) ENDING(0),
PART "P00000000000000000300" STARTING(300) ENDING(300));
ALTER TABLE test DROP COLUMN CODEGROUPKEY;
db2 "INSERT INTO test VALUES(1,null)"
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0327N The row cannot be inserted into table "MARIAJ.TEST"
because it is
outside the bounds of the defined data partition ranges.
SQLSTATE=22525
SYSCAT.DATAPARTITIONEXPRESSION still shows that the partitioning
table has NULLSFIRST Y
SELECT SUBSTR( tabschema, 1, 20 ) AS tabschema , SUBSTR(
tabname, 1, 20 ) AS tabname , datapartitionkeyseq , SUBSTR(
datapartitionexpression, 1, 20 ) AS datapartitionexpression ,
nullsfirst FROM SYSCAT.DATAPARTITIONEXPRESSION WHERE tabschema =
'MARIAJ' AND tabname = 'TEST' ORDER BY tabschema , tabname
WITH UR FOR FETCH ONLY
TABSCHEMA TABNAME DATAPARTITIONKEYSEQ
DATAPARTITIONEXPRESSION NULLSFIRST
-------------------- -------------------- -------------------
----------------------- ----------
MARIAJ TEST 1
PS_OID Y
1 record(s) selected.
The issue is that the Packed descriptor of the table is corrupt
(after alter drop column) and does not have information for the
NULLS FIRST clause of the partitioning column. |
