ITPub博客

首页 > 数据库 > 数据库开发技术 > db2查看表的索引的几种方式

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

原创 数据库开发技术 作者:dbSeeSee 时间:2016-06-24 14:14:13 0 删除 编辑
从视图看


  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. SCHEMA IDXNAME OWNER TABSCHEMA TABNAME INDEXTYPE
  3. -------------------- -------------------- ---------- -----------------
  4. DB2INST1 IDX_T1_01 DB2INST1 DB2INST1 T1 REG
  5. DB2INST1 IDX_T1_02 DB2INST1 DB2INST1 T1 REG

  6.   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. TABNAME TABSCHEMA INDNAME OWNER INDEXTYPE COLNAMES
  3. ------------ ---------- --------------- ---------- --------- --------
  4. T1 DB2INST1 IDX_T1_01 DB2INST1 REG +ID
  5. T1 DB2INST1 IDX_T1_02 DB2INST1 REG +ID+NAME

  6.   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. SCHEMA IDXNAME OWNER TABSCHEMA TABNAME
  3. -------------------- -------------------- ---------- ----------
  4. DB2CAE2 T1 DB2CAE2 DB2CAE2 T1
  5. DB2CAE2 T0 DB2CAE2 DB2CAE2 T0
  6. DB2CAE2 T2 DB2CAE2 DB2CAE2 T2
  7. DB2CAE2 T3 DB2CAE2 DB2CAE2 T3
  8. DB2CAE2 T4 DB2CAE2 DB2CAE2 T4
  9. DB2CAE2 T5 DB2CAE2 DB2CAE2 T5
  10. DB2CAE2 IXCDT1 DB2CP2CP DB2CAE2 CDT1
  11. DB2CAE2 IXCDT2 DB2CP2CP DB2CAE2 CDT2
  12. DB2CAE2 IXCDT3 DB2CP2CP DB2CAE2 CDT3
  13. DB2CAE2 IXCDT4 DB2CP2CP DB2CAE2 CDT4
  14. DB2CAE2 IXCDT5 DB2CP2CP DB2CAE2 CDT5
  15. DB2CAE2 IXCDT0 DB2CAE2 DB2CAE2 CDT0
  16. DB2CAE2 TEST01 DB2CAE2 DB2CAE2 TEST01
  17. DB2CAE2 IXCDTEST01 DB2CP2CP DB2CAE2 CDTEST01

  18.   14 record(s) selected.
第二种方式 db2 describe查看


  1. [db2inst1@kvm101 ~]$ db2 describe indexes for table db2inst1.t1

  2. Index Index Unique Number of Index Index
  3. schema name rule columns type partitioning
  4. ------------------------------- ------------------- -------------- --
  5. DB2INST1 IDX_T1_01 D 1 RELATIONAL DATA -
  6. DB2INST1 IDX_T1_02 D 2 RELATIONAL DATA -

  7.   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. -- Binding package automatically ...
  13. -- Bind is successful
  14. -- Binding package automatically ...
  15. -- Bind is successful

  16. CONNECT TO ASP;

  17. ------------------------------------------------
  18. -- DDL Statements for table "DB2INST1"."T1"
  19. ------------------------------------------------
  20. CREATE TABLE "DB2INST1"."T1" (
  21.                   "ID" INTEGER ,
  22.                   "NAME" CHAR(50) )
  23.                  IN "USERSPACE1" ;

  24. -- DDL Statements for indexes on Table "DB2INST1"."T1"
  25. CREATE INDEX "DB2INST1"."IDX_T1_01" ON "DB2INST1"."T1"
  26.                 ("ID" ASC)
  27.                 COMPRESS NO ALLOW REVERSE SCANS;
  28. -- DDL Statements for indexes on Table "DB2INST1"."T1"
  29. CREATE INDEX "DB2INST1"."IDX_T1_02" ON "DB2INST1"."T1"
  30.                 ("ID" ASC,
  31.                  "NAME" ASC)
  32.                 COMPRESS NO ALLOW REVERSE SCANS;

  33. --这里显示了索引的名称和索引对应的列
  34. COMMIT WORK;
  35. CONNECT RESET;

  36. TERMINATE

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29023300/viewspace-2120905/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-04

  • 博文量
    28
  • 访问量
    96231