DB2分区表相关~

1.如何知道一个索引是分区索引还是非分区索引？
2.分区表上一定要有非分区索引吗？
sycat.indexes和syscat.indexpartitions

1.创建表

1).首先以root权限执行下列命令

touch /tmp/tbsp01touch /tmp/tbsp02touch /tmp/tbsp03chown ba5oltp1 /tmp/tbsp*

2).创建分区要使用的表空间

[ba5oltp1@db2cls02 ~]$ db2 "create tablespace tbsp01 managed by database using (FILE '/tmp/tbsp01' 3000)"DB20000I  The SQL command completed successfully.[ba5oltp1@db2cls02 ~]$ db2 "create tablespace tbsp02 managed by database using (FILE '/tmp/tbsp02' 3000)" DB20000I  The SQL command completed successfully.[ba5oltp1@db2cls02 ~]$ db2 "create tablespace tbsp03 managed by database using (FILE '/tmp/tbsp03' 3000)" DB20000I  The SQL command completed successfully.

3).创建测试表

db2 "create table emp_tooo(emp_no INT,name varchar(20),sex int) partition by range(emp_no) (starting 0 ending 100000 in tbsp01,starting 100001 ending 200000 in tbsp02,starting 200001 ending 300000 in tbsp03)"

4).创建索引

db2 "create index pk_too_id on emp_tooo(emp_no) not partitioned " db2 "create index uni_too_name on emp_tooo(name) partitioned "             

5).查询syscat.indexes syscat.indexpartitions

[ba5oltp1@db2cls02 ~]$ db2 "select varchar(indname,30),iid,datapartitionid from syscat.indexpartitions where tabname='EMP_TOOO'"                            1                              IID    DATAPARTITIONID------------------------------ ------ ---------------UNI_TOO_NAME                        2               0UNI_TOO_NAME                        2               1UNI_TOO_NAME                        2               2  3 record(s) selected.[ba5oltp1@db2cls02 ~]$ db2 "select varchar(indname,30) from syscat.indexES where tabname='EMP_TOOO'"                             1                            ------------------------------PK_TOO_ID                    UNI_TOO_NAME                   2 record(s) selected.

结论

结论1：

INDEXES视图中每个索引对应一条数据，无论是分区索引还是非分区索引；
INDEXPARTITIONS 里面只包含分区索引；