环境
- Ubuntu 22.04
- Db2 V11.5
概述
使用 db2look
命令,可以查看DB object的DDL语句,也可以生成 UPDATE DATABASE CONFIGURATION
, UPDATE DATABASE MANAGER CONFIGURATION
以及 db2set
语句。
本文主要介绍用 db2look
命令查看DB object的DDL语句。
常用选项
db2look
命令有很多选项,输入命令 db2look -h
可以查看详细的帮助信息:
$ db2look -h
db2look: Generates DDLs to recreate the objects defined in a database
Syntax: db2look -d DBname [-e] [-xs] [-xdir Path] [-u Creator] [-z Schema]
[-t Tname1 Tname2...TnameN] [-tw Tname] [-xdep] [-xddep]
[-n Nname1 Nname2...NnameN] [-nw Nname]
[-o Fname] [-a] [-m] [-c] [-r] [-l] [-x] [-xd] [-f] [-h]
[-fd] [-td x] [-noview] [-i userID] [-w password]
[-v Vname1 Vname2 ... VnameN] [-dp] [-ct]
[-wrapper WrapperName] [-server ServerName] [-nofed]
[-wlm] [-ap] [-mod] [-cor] [-wrap] [-noimplschema] [-nostatsclause]
[-wrapper WrapperName] [-server ServerName][-fedonly] [-nofed]
......
......
常用的选项有:
-d
:指定DB名字-e
:显示所有table、view、index等DB object的DDL-l
:创建DB partition group、bufferpool、tablespace的DDL-t
:显示指定table的DDL-v
:显示指定view的DDL-tw
:显示符合指定pattern的table的DDL,其中%
通配任意长度的字符,_
通配单个字符-z
:显示指定schema的object的DDL-td
:指定DDL语句的delimiter(默认是;
)-o
:把结果输出到指定文件
例子
例1:查看table T1
的DDL
$ db2look -d kai1022 -t T1
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- The db2look utility will consider only the specified tables
--
-- Using database KAI1022
-- Using userid DB2INST1
-- Database Manager Version DB2/LINUXX8664 Version 11.5.0
-- Database Codepage 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
--
--
--
--********************************************
TABLE T1
--********************************************
--
CREATOR DB2INST1
CARD -1
NPAGES -1
MPAGES -1
FPAGES -1
OVERFLOW -1
ACTIVE_BLOCKS -1
--
COLUMNS
--
NAME C1
COLNO 0
TYPE INTEGER
LENGTH 4
NULLS Y
COLCARD -1
NUMNULLS -1
SUB_COUNT -1
SUB_DELIM_LENGTH -1
PCTENCODED -1
AVGCOLLENCHAR -1
PAGEVARIANCERATIO -1
AVGENCODEDCOLLEN -1
NFRQ -1
NQUN -1
LOW2KEY
HIGH2KEY
AVGCOLLEN -1
--
NAME C2
COLNO 1
TYPE INTEGER
LENGTH 4
NULLS Y
COLCARD -1
NUMNULLS -1
SUB_COUNT -1
SUB_DELIM_LENGTH -1
PCTENCODED -1
AVGCOLLENCHAR -1
PAGEVARIANCERATIO -1
AVGENCODEDCOLLEN -1
NFRQ -1
NQUN -1
LOW2KEY
HIGH2KEY
AVGCOLLEN -1
--
COLUMN DISTRIBUTION
--
--
INDICES
例2:查看所有table,view,index等object的DDL
$ db2look -d kai1022 -e
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- This CLP file was created using DB2LOOK Version "11.1"
-- Timestamp: Thu 12 Jan 2023 07:33:01 PM PST
-- Database Name: KAI1022
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO KAI1022;
------------------------------------------------
-- DDL Statements for Schemas
------------------------------------------------
-- Running the DDL below will explicitly create a schema in the
-- new database that corresponds to an implicitly created schema
-- in the original database.
CREATE SCHEMA "IBM_RTMON";
CREATE SCHEMA "DB2INST1";
------------------------------------------------
-- DDL Statements for Table "DB2INST1"."T1"
------------------------------------------------
CREATE TABLE "DB2INST1"."T1" (
"C1" INTEGER ,
"C2" INTEGER )
IN "USERSPACE1"
ORGANIZE BY ROW;
......
......
例3:查看所有T开头的table的DDL
$ db2look -d kai1022 -e -tw T%
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Generate DDLs for tables whose names match the pattern criteria (wildcard characters) of the table name
-- Creating DDL for table(s)
-- This CLP file was created using DB2LOOK Version "11.1"
-- Timestamp: Thu 12 Jan 2023 07:35:09 PM PST
-- Database Name: KAI1022
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO KAI1022;
------------------------------------------------
-- DDL Statements for Table "DB2INST1"."T1"
------------------------------------------------
CREATE TABLE "DB2INST1"."T1" (
"C1" INTEGER ,
"C2" INTEGER )
IN "USERSPACE1"
ORGANIZE BY ROW;
------------------------------------------------
-- DDL Statements for Table "DB2INST1"."T2"
------------------------------------------------
CREATE TABLE "DB2INST1"."T2" (
"C1" INTEGER ,
"C2" INTEGER )
IN "USERSPACE1"
ORGANIZE BY ROW;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
例4:查看所有用户自定义的DB partition group,bufferpool,tablespace的DDL
$ db2look -d kai1022 -l
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- This CLP file was created using DB2LOOK Version "11.1"
-- Timestamp: Thu 12 Jan 2023 07:37:36 PM PST
-- Database Name: KAI1022
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO KAI1022;
-----------------------------------
-- DDL Statements for DATABASE PARTITION GROUPS --
-----------------------------------
CREATE DATABASE PARTITION GROUP "CONSOLEGROUP" ON DBPARTITIONNUMS
(0);
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------
CREATE BUFFERPOOL "CONSOLEPOOL" SIZE AUTOMATIC PAGESIZE 32768;
CONNECT RESET;
CONNECT TO KAI1022;
--------------------------
-- Mimic Storage groups --
--------------------------
ALTER STOGROUP "IBMSTOGROUP"
OVERHEAD 6.725000
DEVICE READ RATE 100.000000
DATA TAG NONE
SET AS DEFAULT;
------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------
CREATE LARGE TABLESPACE "SYSTOOLSPACE" IN DATABASE PARTITION GROUP IBMCATGROUP
PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE
USING STOGROUP "IBMSTOGROUP"
AUTORESIZE YES
INITIALSIZE 32 M
MAXSIZE NONE
EXTENTSIZE 4
PREFETCHSIZE AUTOMATIC
BUFFERPOOL "IBMDEFAULTBP"
DATA TAG INHERIT
OVERHEAD INHERIT
TRANSFERRATE INHERIT
DROPPED TABLE RECOVERY ON;
CREATE LARGE TABLESPACE "TS4MONITOR" IN DATABASE PARTITION GROUP CONSOLEGROUP
PAGESIZE 32768 MANAGED BY AUTOMATIC STORAGE
USING STOGROUP "IBMSTOGROUP"
AUTORESIZE NO
INITIALSIZE 2 G
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL "CONSOLEPOOL"
DATA TAG INHERIT
OVERHEAD INHERIT
TRANSFERRATE INHERIT
DROPPED TABLE RECOVERY OFF;
......
......
例5:查看view V1
的DDL
$ db2look -d kai1022 -e -v V1
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- This CLP file was created using DB2LOOK Version "11.1"
-- Timestamp: Thu 12 Jan 2023 07:52:31 PM PST
-- Database Name: KAI1022
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO KAI1022;
----------------------------
-- DDL Statements for Views
----------------------------
SET CURRENT SCHEMA = "DB2INST1";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2INST1";
create view v1 as select * from t1;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
注意,要指定 -e
选项,否则结果包含了巨多内容,但并不是指定view的信息。
例6:查看所有schema为 DB2INST1
的object的DDL
$ db2look -d kai1022 -z DB2INST1
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Specified SCHEMA is: DB2INST1
-- Schema name is ignored for the Federated Section
--
-- Using database KAI1022
-- Using userid DB2INST1
-- Database Manager Version DB2/LINUXX8664 Version 11.5.0
-- Database Codepage 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
--
--
--
--********************************************
TABLE T1
--********************************************
--
CREATOR DB2INST1
CARD -1
NPAGES -1
MPAGES -1
FPAGES -1
OVERFLOW -1
ACTIVE_BLOCKS -1
--
COLUMNS
--
NAME C1
COLNO 0
TYPE INTEGER
LENGTH 4
NULLS Y
COLCARD -1
NUMNULLS -1
SUB_COUNT -1
SUB_DELIM_LENGTH -1
PCTENCODED -1
AVGCOLLENCHAR -1
PAGEVARIANCERATIO -1
AVGENCODEDCOLLEN -1
NFRQ -1
NQUN -1
LOW2KEY
HIGH2KEY
AVGCOLLEN -1
--
NAME C2
COLNO 1
TYPE INTEGER
LENGTH 4
NULLS Y
COLCARD -1
NUMNULLS -1
SUB_COUNT -1
SUB_DELIM_LENGTH -1
PCTENCODED -1
AVGCOLLENCHAR -1
PAGEVARIANCERATIO -1
AVGENCODEDCOLLEN -1
NFRQ -1
NQUN -1
LOW2KEY
HIGH2KEY
AVGCOLLEN -1
--
COLUMN DISTRIBUTION
--
--
INDICES
--
--********************************************
TABLE T2
--********************************************
--
CREATOR DB2INST1
CARD -1
NPAGES -1
MPAGES -1
FPAGES -1
OVERFLOW -1
ACTIVE_BLOCKS -1
--
COLUMNS
--
NAME C1
COLNO 0
TYPE INTEGER
LENGTH 4
NULLS Y
COLCARD -1
NUMNULLS -1
SUB_COUNT -1
SUB_DELIM_LENGTH -1
PCTENCODED -1
AVGCOLLENCHAR -1
PAGEVARIANCERATIO -1
AVGENCODEDCOLLEN -1
NFRQ -1
NQUN -1
LOW2KEY
HIGH2KEY
AVGCOLLEN -1
--
NAME C2
COLNO 1
TYPE INTEGER
LENGTH 4
NULLS Y
COLCARD -1
NUMNULLS -1
SUB_COUNT -1
SUB_DELIM_LENGTH -1
PCTENCODED -1
AVGCOLLENCHAR -1
PAGEVARIANCERATIO -1
AVGENCODEDCOLLEN -1
NFRQ -1
NQUN -1
LOW2KEY
HIGH2KEY
AVGCOLLEN -1
--
COLUMN DISTRIBUTION
--
--
INDICES
参考
https://www.ibm.com/docs/en/db2/11.5?topic=commands-db2look-db2-statistics-ddl-extraction-tool