问题描述:db2, create unique index报错: SQL0270N Function not supported (Reason code = "1"),示意如下:
inst105@db2a:~$ db2 "create table t1(col1 int, col2 int, col3 varchar(20))"
DB20000I The SQL command completed successfully.
inst105@db2a:~$ db2 "create unique index idx1 on t1(col2)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0270N Function not supported (Reason code = "1"). SQLSTATE=42997
分析:
1. 先看报错的含义:The primary key, each unique constraint, and each unique index must contain all distribution columns of the table,即唯一性索引必须包含表的所有的分布列(distribution columns)
SQL0270N Function not supported (Reason code = "<reason-code>").
Explanation:
The statement cannot be processed because it violates a restriction as
indicated by the following reason code:
1
The primary key, each unique constraint, and each unique index
must contain all distribution columns of the table.
User response:
The action corresponding to the reason code is as follows:
1
Correct the CREATE TABLE, ALTER TABLE or CREATE UNIQUE INDEX
statement.
2. distribution columns只有DPF分区表才有,先看一下表的PARTITION_MODE是H,表示是partition by Hashing
------------------------------ --------------
T1 H
1 record(s) selected.
再查一下对应的分区列是COL1:
inst105@db2a:~$ db2 "select substr(COLNAME,1,30) as colname, PARTKEYSEQ from syscat.columns where tabname='T1' "
COLNAME PARTKEYSEQ
------------------------------ ----------
COL1 1
COL2 0
COL3 0
3 record(s) selected.
db2look查看表的定义,也能进一步确认:
inst105@db2a:~$ db2look -d test -a -e -t T1
..
CONNECT TO TEST;
------------------------------------------------
-- DDL Statements for Table "INST105 "."T1"
------------------------------------------------
CREATE TABLE "INST105 "."T1" (
"COL1" INTEGER ,
"COL2" INTEGER ,
"COL3" VARCHAR(20 OCTETS) )
DISTRIBUTE BY HASH("COL1")
IN "USERSPACE1"
ORGANIZE BY ROW;
3. 信息中心中create index的语法中也有如下介绍:
If the UNIQUE option is specified, and the table has a distribution key, the columns in the index key must be a superset of the distribution key. That is, the columns specified for a unique index key must include all the columns of the distribution key (SQLSTATE 42997).
4. 综上,原因是DPF环境,T1的distribution column是col1, 那么所有的unique index必须包含col1, 作为一种折衷方案,可以考虑如下语句
inst105@db2a:~$ db2 "create unique index idx1 on t1(col2,col1)"
DB20000I The SQL command completed successfully.
inst105@db2a:~$ db2 "create table t1(col1 int, col2 int, col3 varchar(20))"
DB20000I The SQL command completed successfully.
inst105@db2a:~$ db2 "create unique index idx1 on t1(col2)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0270N Function not supported (Reason code = "1"). SQLSTATE=42997
分析:
1. 先看报错的含义:The primary key, each unique constraint, and each unique index must contain all distribution columns of the table,即唯一性索引必须包含表的所有的分布列(distribution columns)
SQL0270N Function not supported (Reason code = "<reason-code>").
Explanation:
The statement cannot be processed because it violates a restriction as
indicated by the following reason code:
1
The primary key, each unique constraint, and each unique index
must contain all distribution columns of the table.
User response:
The action corresponding to the reason code is as follows:
1
Correct the CREATE TABLE, ALTER TABLE or CREATE UNIQUE INDEX
statement.
2. distribution columns只有DPF分区表才有,先看一下表的PARTITION_MODE是H,表示是partition by Hashing
inst105@db2a:~$ db2 "select substr(tabname,1,30) as tabname, PARTITION_MODE from syscat.tables where tabname='T1'"
------------------------------ --------------
T1 H
1 record(s) selected.
再查一下对应的分区列是COL1:
inst105@db2a:~$ db2 "select substr(COLNAME,1,30) as colname, PARTKEYSEQ from syscat.columns where tabname='T1' "
COLNAME PARTKEYSEQ
------------------------------ ----------
COL1 1
COL2 0
COL3 0
3 record(s) selected.
db2look查看表的定义,也能进一步确认:
inst105@db2a:~$ db2look -d test -a -e -t T1
..
CONNECT TO TEST;
------------------------------------------------
-- DDL Statements for Table "INST105 "."T1"
------------------------------------------------
CREATE TABLE "INST105 "."T1" (
"COL1" INTEGER ,
"COL2" INTEGER ,
"COL3" VARCHAR(20 OCTETS) )
DISTRIBUTE BY HASH("COL1")
IN "USERSPACE1"
ORGANIZE BY ROW;
3. 信息中心中create index的语法中也有如下介绍:
If the UNIQUE option is specified, and the table has a distribution key, the columns in the index key must be a superset of the distribution key. That is, the columns specified for a unique index key must include all the columns of the distribution key (SQLSTATE 42997).
4. 综上,原因是DPF环境,T1的distribution column是col1, 那么所有的unique index必须包含col1, 作为一种折衷方案,可以考虑如下语句
inst105@db2a:~$ db2 "create unique index idx1 on t1(col2,col1)"
DB20000I The SQL command completed successfully.