db2查看表的索引的几种方式

从视图看

1. [db2inst1@kvm101 ~]$ db2 "select char(INDSCHEMA,20) SCHEMA,char(INDNAME,20) IDXNAME,char(OWNER,10) OWNER,char(TABSCHEMA,10) TABSCHEMA ,char(TABNAME,20) TABNAME,INDEXTYPE from syscat.indexes where tabname='T1'"
   
2. 
   
3. SCHEMA IDXNAME OWNER TABSCHEMA TABNAME INDEXTYPE
   
4. -------------------- -------------------- ---------- -----------------
   
5. DB2INST1 IDX_T1_01 DB2INST1 DB2INST1 T1 REG 
   
6. DB2INST1 IDX_T1_02 DB2INST1 DB2INST1 T1 REG 
   
7. 
   
8.   2 record(s) selected.

1. [db2inst1@kvm101 ~]$ db2 "select char(TABNAME,20) TABNAME,char(TABSCHEMA,10) TABSCHEMA,char(INDNAME,20) INDNAME,char(OWNER,10) OWNER,INDEXTYPE,char(COLNAMES,50) COLNAMES from syscat.indexes where tabname='T1'"
   
2. 
   
3. TABNAME TABSCHEMA INDNAME OWNER INDEXTYPE COLNAMES 
   
4. ------------ ---------- --------------- ---------- --------- --------
   
5. T1 DB2INST1 IDX_T1_01 DB2INST1 REG +ID 
   
6. T1 DB2INST1 IDX_T1_02 DB2INST1 REG +ID+NAME 
   
7. 
   
8.   2 record(s) selected.

1. [db2inst3@kvm101 ~]$ db2 "select char(INDSCHEMA,20) SCHEMA,char(INDNAME,20) IDXNAME,char(OWNER,10) OWNER,char(TABSCHEMA,10) TABSCHEMA ,char(TABNAME,20) TABNAME from syscat.indexes where INDSCHEMA='DB2CAE2'"
   
2. 
   
3. SCHEMA IDXNAME OWNER TABSCHEMA TABNAME 
   
4. -------------------- -------------------- ---------- ---------- 
   
5. DB2CAE2 T1 DB2CAE2 DB2CAE2 T1 
   
6. DB2CAE2 T0 DB2CAE2 DB2CAE2 T0 
   
7. DB2CAE2 T2 DB2CAE2 DB2CAE2 T2 
   
8. DB2CAE2 T3 DB2CAE2 DB2CAE2 T3 
   
9. DB2CAE2 T4 DB2CAE2 DB2CAE2 T4 
   
10. DB2CAE2 T5 DB2CAE2 DB2CAE2 T5 
    
11. DB2CAE2 IXCDT1 DB2CP2CP DB2CAE2 CDT1 
    
12. DB2CAE2 IXCDT2 DB2CP2CP DB2CAE2 CDT2 
    
13. DB2CAE2 IXCDT3 DB2CP2CP DB2CAE2 CDT3 
    
14. DB2CAE2 IXCDT4 DB2CP2CP DB2CAE2 CDT4 
    
15. DB2CAE2 IXCDT5 DB2CP2CP DB2CAE2 CDT5 
    
16. DB2CAE2 IXCDT0 DB2CAE2 DB2CAE2 CDT0 
    
17. DB2CAE2 TEST01 DB2CAE2 DB2CAE2 TEST01 
    
18. DB2CAE2 IXCDTEST01 DB2CP2CP DB2CAE2 CDTEST01 
    
19. 
    
20.   14 record(s) selected.

第二种方式 db2 describe查看

1. [db2inst1@kvm101 ~]$ db2 describe indexes for table db2inst1.t1
   
2. 
   
3. Index Index Unique Number of Index Index
   
4. schema name rule columns type partitioning
   
5. ------------------------------- ------------------- -------------- --
   
6. DB2INST1 IDX_T1_01 D 1 RELATIONAL DATA - 
   
7. DB2INST1 IDX_T1_02 D 2 RELATIONAL DATA - 
   
8. 
   
9.   2 record(s) selected.

第三种方式

使用db2look查看

1. [db2inst1@kvm101 ~]$ db2look -d asp -z db2inst1 -t "T1" -a -e -c
   
2. -- Generate statistics for all creators 
   
3. -- Schema name is ignored 
   
4. -- The db2look utility will consider only the specified tables 
   
5. -- Creating DDL for table(s)
   
6. -- This CLP file was created using DB2LOOK Version "9.7" 
   
7. -- Timestamp: Mon 29 Feb 2016 04:37:54 PM CST
   
8. -- Database Name: ASP 
   
9. -- Database Manager Version: DB2/LINUXX8664 Version 9.7.2 
   
10. -- Database Codepage: 1208
    
11. -- Database Collating Sequence is: IDENTITY
    
12. 
    
13. -- Binding package automatically ... 
    
14. -- Bind is successful
    
15. -- Binding package automatically ... 
    
16. -- Bind is successful
    
17. 
    
18. CONNECT TO ASP;
    
19. 
    
20. ------------------------------------------------
    
21. -- DDL Statements for table "DB2INST1"."T1"
    
22. ------------------------------------------------
    
23. CREATE TABLE "DB2INST1"."T1" (
    
24.                   "ID" INTEGER , 
    
25.                   "NAME" CHAR(50) ) 
    
26.                  IN "USERSPACE1" ; 
    
27. 
    
28. -- DDL Statements for indexes on Table "DB2INST1"."T1"
    
29. CREATE INDEX "DB2INST1"."IDX_T1_01" ON "DB2INST1"."T1" 
    
30.                 ("ID" ASC)
    
31.                 COMPRESS NO ALLOW REVERSE SCANS;
    
32. -- DDL Statements for indexes on Table "DB2INST1"."T1"
    
33. CREATE INDEX "DB2INST1"."IDX_T1_02" ON "DB2INST1"."T1" 
    
34.                 ("ID" ASC,
    
35.                  "NAME" ASC)
    
36.                 COMPRESS NO ALLOW REVERSE SCANS;
    
37. 
    
38. --这里显示了索引的名称和索引对应的列
    
39. COMMIT WORK;
    
40. CONNECT RESET;
    
41. 
    
42. TERMINATE