问题描述:
使用db2look导出表定义,然后再使用该定义创建表报错 SQL1860N RC=5,导出的表定义如下:
db2 "CREATE TABLE mqs20201208 (
STORETIME DATE NOT NULL ,
IMAGEOBJECT BLOB(10485760) LOGGED NOT COMPACT NOT NULL ,
UUID VARCHAR(36) )
PARTITION BY RANGE(STORETIME)
(PART PART20140101 STARTING('2014-01-01') ENDING('2014-01-01') IN TSP_MIBSSE LONG IN TSP_BLOB,
PART PART201704 STARTING('2017-04-13') ENDING('2017-04-30') IN TSP_MIBSSE LONG IN TSP_BLOB,
PART PART202011 STARTING('2020-01-01') ENDING('2020-12-31') IN TSP_BLOB LONG IN TSP_BLOB)
"
问题重现:
如果直接上面的表定义创建表,会报错 SQL1860N Table space "TSP_BLOB" is not compatible with table space "TSP_BLOB". Reason code = "5". SQLSTATE=42838
如果先创建表,只包含前两个分区:
db2 "CREATE TABLE mqs20201208 (
STORETIME DATE NOT NULL ,
IMAGEOBJECT BLOB(10485760) LOGGED NOT COMPACT NOT NULL ,
UUID VARCHAR(36) )
PARTITION BY RANGE(STORETIME)
(PART PART20140101 STARTING('2014-01-01') ENDING('2014-01-01') IN TSP_MIBSSE LONG IN TSP_BLOB,
PART PART201704 STARTING('2017-04-13') ENDING('2017-04-30') IN TSP_MIBSSE LONG IN TSP_BLOB)
"
然后再使用alter table语句增加一个分区,则是可行的
db2 "alter table mqs20201208 add partition PART202011 STARTING('2020-01-01') ENDING('2020-12-31') IN TSP_BLOB LONG IN TSP_BLOB"
解决方案:
应该是DB2产品的缺陷,建议修改表的DDL之后重新执行