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. 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/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/29023300/viewspace-2120905/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值