DB2基本操作

1.  DB2 V9.7安装

下载安装包及license：

链接: https://pan.baidu.com/s/1B4OIVEMGu8J6ttdun3-GGQ

提取码: 2x6e

 1 #解压安装包
 2 tar -zxvf v9.7fp7_linuxx64_server-ok-db2v9.7ese.tar.gz
 3 
 4 #安装
 5 ./db2_install -f sysreq -b /opt/ibm/db2/V9.7
 6 
 7 #安装license
 8 db2licm -a /opt/ibm/db2/V9.7/license/db2ese_c.lic
 9 
10 #查看license信息
11 db2licm -l
12 
13 #创建DB2所需的用户组和用户
14 groupadd -g 2001 db2grp1
15 groupadd -g 2002 db2fgrp1
16 useradd -u 1002 -g db2fgrp1 -p db2fenc db2fenc
17 useradd -u 1001 -g db2grp1 -p db2admin db2admin
18 
19 #db2admin用户设置密码
20 passwd db2admin 
21 
22 #创建实例
23 cd /opt/IBM/db2/V9.7/instance
24 ./db2icrt -a SERVER -s ese -u db2fenc db2admin
25 
26 #修改/etc/bashrc文件,末尾添加. /home/db2admin/sqllib/db2profile
27 vi /etc/bashrc
28 . /home/db2admin/sqllib/db2profile
29 
30 #添加端口号，末尾添加db2c_db2admin 50000/tcp
31 vi /etc/services
32 db2c_db2admin 50000/tcp
33 
34 #修改db2的svcename
35 su - db2admin
36 db2 update dbm cfg using svcename db2c_db2admin
37 exit
38 
39 #关闭重启数据库
40 su - db2admin
41 db2stop
42 db2 force applications all
43 db2start
44 
45 #设置DB2自启动
46 cd /opt/ibm/db2/V9.7/instance
47 ./db2iauto -on db2admin
48 
49 #配置db2
50 #安装好的DB2,执行db2set
51 su - db2admin
52 db2set db2comm=tcpip
53 db2set db2codepage=1386
54 db2set db2country=86
55 db2set db2_parallel_io=*
56 db2set DB2MEMDISCLAIM=yes
57 db2set DB2MEMMAXFREE=8388608
58 db2set DB2_ANTIJOIN=no
59 db2set DB2_CORRELATED_PREDICATES=yes
60 db2set db2_hash_join=yes
61 db2set DB2_INLIST_TO_NLJN=yes
62 db2set DB2_NEW_CORR_SQ_FF=ON
63 db2set db2_binsort=yes
64 
65 #开启50000端口
66 firewall-cmd --permanent --zone=public --add-port=50000/tcp
67 systemctl stop firewalld.service
68 systemctl start firewalld.service
69 
70 #查看DB2配置
71 su - db2admin
72 db2 get dbm cfg
73 
74 #开启联邦
75 su - db2admin
76 db2 update dbm cfg using federated yes;
77 
78 #设置最大并发连接数，默认为8
79 su - db2admin
80 db2 update dbm cfg using NUMDB 20;

2.  创建数据库

  1 #创建NODE
  2 db2 catalog tcpip node [NODENAME] remote 127.0.0.1 server 50000
  3 
  4 #创建数据库
  5 db2 "create database [DATANAME] on /db2_tag alias [DATANAME] using codeset GBK territory cn"
  6 
  7 #创建别名编目
  8 db2 catalog db [DATANAME] as [ALIAS] at node [NODENAME]
  9 
 10 #创建缓冲池
 11 CREATE BUFFERPOOL [BUFFERPOOLNAME] SIZE 9600 PAGESIZE 32768;
 12 #创建表空间
 13 CREATE REGULAR TABLESPACE [TABLESPACENAME] IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM 
 14      USING ('[CONSTRAINTPATH]')
 15      EXTENTSIZE 32
 16      PREFETCHSIZE 32
 17      BUFFERPOOL IBMDEFAULTBP
 18      OVERHEAD 12.670000
 19      TRANSFERRATE 0.180000 
 20      FILE SYSTEM CACHING  
 21      DROPPED TABLE RECOVERY ON;
 22      
 23 CREATE REGULAR TABLESPACE [TABLESPACENAME] IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE 
 24      USING (FILE'[CONSTRAINTPATH]')
 25      EXTENTSIZE 32
 26      PREFETCHSIZE 32
 27      BUFFERPOOL RONEBP1
 28      OVERHEAD 24.100000
 29      TRANSFERRATE 0.900000 
 30      FILE SYSTEM CACHING  
 31      DROPPED TABLE RECOVERYON;
 32   
 33 #创建序列
 34 CREATE SEQUENCE [SEQUENCENAME] AS INTEGER
 35     MINVALUE 1 MAXVALUE 2147483647
 36     START WITH 1 INCREMENT BY 1
 37     CACHE 20 NO CYCLE NO ORDER;
 38 
 39 #创建函数
 40 SET CURRENT SCHEMA = "DB2ADMIN";
 41 SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN";
 42 CREATE FUNCTION [FUNCTIONNAME](T1 TIMESTAMP, T2 TIMESTAMP) RETURNS INT  RETURN
 43 (  
 44     (DAYS(t1) - DAYS(t2)) * 86400 +    
 45     (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))  
 46 );
 47 
 48 #创建存储过程
 49 CREATE OR REPLACE PROCEDURE [PROCEDURENAME] (
 50   IN ID INTEGER,
 51   IN NAME VARCHAR(50)
 52 )
 53 SPECIFIC SQL200512200013900
 54 BEGIN
 55   INSERT INTO PROC_TEST VALUES(ID,NAME);
 56 END
 57 
 58 #创建表
 59 CREATE TABLE [TABLENAME] (
 60     "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (  
 61       START WITH +1  
 62       INCREMENT BY +1  
 63       MINVALUE +1  
 64       MAXVALUE +2147483647  
 65       NO CYCLE  
 66       CACHE 20  
 67       NO ORDER ) , 
 68     "CAT_CODE" VARCHAR(30) , 
 69     "CAT_NAME" VARCHAR(100) , 
 70     "PARENT_ID" INTEGER , 
 71     "CAT_LEVEL" INTEGER , 
 72     "CAT_ORDER" INTEGER , 
 73     "REPORT_MODE" CHAR(1) , 
 74     "REPORT_INTERVAL" CHAR(1) , 
 75     "REPORT_LIMIT_TEXT" VARCHAR(200) , 
 76     "REPORT_LIMIT_TIME" INTEGER , 
 77     "PRESS_TYPE" CHAR(1) , 
 78     "PRESS_DATE" INTEGER , 
 79     "PRESS_COUNT" INTEGER , 
 80     "PRESS_LEADER_START" INTEGER , 
 81     "PRESS_STATUS" CHAR(1) , 
 82     "PRESS_SEND_TIME" VARCHAR(5) , 
 83     "PRESS_INTERVAL" INTEGER , 
 84     "PRESS_DATE_STATUS" CHAR(1) , 
 85     "PRESS_CONTENT" VARCHAR(500) , 
 86     "CAT_OWNER" VARCHAR(8) )   
 87     IN "USERSPACE" INDEX IN "IDXCSPACE" ; 
 88 
 89 #添加表说明和列说明
 90 COMMENT ON TABLE [TABLENAME] IS '表说明';
 91 COMMENT ON COLUMN [TABLENAME].[COLUMNNAME] IS '列说明';
 92 
 93 #增加主键
 94 ALTER TABLE [TABLENAME] ADD CONSTRAINT [PKNAME] PRIMARY KEY ("ID");
 95   
 96 #增加唯一约束
 97 ALTER TABLE [TABLENAME] ADD CONSTRAINT [UNIQUENAME] UNIQUE ("login_code");
 98 
 99 #创建视图
100 CREATE VIEW [VIEWNAME] AS SELECT * FROM [TABLENAME];
101 
102 #创建wrapper
103 CREATE WRAPPER [WRAPPERNAME] LIBRARY 'libdb2drda.so' OPTIONS (DB2_FENCED 'N' );
104 
105 #创建server
106 CREATE SERVER [SERVERNAME]
107    TYPE DB2/UDB 
108    VERSION '9.7' 
109    WRAPPER [WRAPPER]  
110    AUTHORIZATION $USERNAME
111    PASSWORD #PASSWORD
112    OPTIONS (DBNAME  '[ALIAS]');
113 
114 #创建user mapping
115 CREATE USER MAPPING FOR DB2ADMIN 
116    SERVER [SERVERNAME]
117    OPTIONS (
118          REMOTE_AUTHID  $USERNAME,
119          REMOTE_PASSWORD  #PASSWORD
120  );
121 
122 #创建nickname
123 CREATE NICKNAME [NICKNAME] FOR [SERVERNAME].[TABLENAME|VIEWNAME];
124 
125 #创建索引
126 CREATE INDEX [INDEXNAME] ON [TABLENAME] ("PARENT_ID" ASC,   "CODE" ASC)  COMPRESS NO ALLOW REVERSE SCANS;
127 
128 #创建外键
129 ALTER TABLE [TABLENAME] ADD CONSTRAINT [FKNAME] FOREIGN KEY  ("PARENT_ID") REFERENCES [REFTABLENAME]  ("ID") ON DELETE CASCADE ON UPDATE RESTRICT ENFORCED ENABLE QUERY OPTIMIZATION;
130 
131 #创建触发器
132 CREATE TRIGGER [TRIGGERNAME]
133         AFTER UPDATE OF PERSONEXT1 ON RO_PERSON
134         Referencing New As NewData
135         FOR EACH ROW MODE DB2SQL
136         UPDATE RO_PERSON SET PERSONCODE = NewData.PERSONEXT1 WHERE PERSONUUID = NewData.PERSONUUID;
137 
138 #重置自增列
139 SELECT MAX(ID) FROM [TABLENAME];
140 ALTER TABLE [TABLENAME] ALTER COLUMN ID RESTART WITH 100;

3.  基本Shell

 1  #查询node
 2  db2 list node directory
 3  
 4  #查询数据库
 5  db2 list db directory
 6  
 7  #删除数据库
 8  db2 drop db [DBNAME]
 9  
10  #删除编目
11  db2 uncatalog db [ALIAS]
12  
13  #删除node
14  db2 uncatalog node [NODENAME]
15  
16  #查询数据库版本
17  su - db2admin -c db2level|grep Informational|awk '{print $5}'|sed 's#\\\"##g'|sed 's#\\,##g'
18 
19  #连接数据库
20  su - db2admin
21  db2 connect to [DBNAME]
22  
23  #执行sql
24  db2 "select * from [TABLENAME]"
25  db2 -t
26  select * from [TABLENAME];
27  
28  #重置连接
29  db2 connect to reset
30  
31  #断开连接，释放进程
32  db2 terminate
33  
34  #查询所有应用连接
35  db2 list applications
36  
37  #断开所有应用链接
38  db2 force applications all
39  
40  #停用数据库连接
41  db2 deactivate database [DBNAME]
42  
43  #查询字符集
44  su - db2admin -c \"db2 get db cfg for %s|grep -i 'Database code page'\"|awk '{print $5}'
45  
46  #备份与还原
47  su - db2admin
48 #断开数据库连接
49 db2 force applicatons all
50 #备份数据库(返回时间戳)
51 db2 backup db [DBNAME] to /db2back
52 #检测备份文件
53 db2ckbkp [DB_BACKUP_FILE_NAME]
54 #查看备份记录
55 db2 list history backup all for [DBNAME]
56 #还原数据库
57 db2 restore db [DBNAME] from . taken at [备份返回的时间戳] into [DBNAME]
58 
59  #导出
60  db2move [DBNAME] export -u db2admin -p db2admin
61  db2 export to /dbdata/export_data/[TABLENAME].del of del modified by codepage=1386 "select * from [TABLENAME]"
62  db2 "export to /dbdata/export_data/[TABLENAME].del of del lobs to /dbdata/export_data lobfile [TABLENAME] modified by codepage=1386  lobsinfile messages msg.out select * from DB2AMDIN.[TABLENAME]" 
63  
64  #导入
65  db2move [DBNAME] import -u db2admin -p db2admin
66  db2 import from /dbdata/export_data/[TABLENAME].del of del "insert into [TABLENAME]"
67  db2 load from /dbdata/export_data/[TABLENAME].del of del "replace into [TABLENAME]"
68  
69  #查询数据库实例
70  db2ilist
71  
72  #查询数据库配置
73  db2set
74  
75  #监控表状态
76  db2 "load query table [TABLENAME]"
77  
78  #当表除于挂起状态
79  db2 "set integrity for [TABLENAME] immediate checked"
80  
81  #查询所有的表空间
82  db2 list tablespaces

4.  查询系统表

 1 #查询数据库版本
 2 SELECT INST_NAME , SERVICE_LEVEL FROM SYSIBMADM.ENV_INST_INFO;
 3 
 4 #查询数据库名使用情况
 5 SELECT
 6     SUM(TOTAL) AS TOTALSIZE,
 7     SUM(USED)  AS USEDSIZE,
 8     SUM(FREE)  AS FREESIZE
 9 FROM
10     (
11         SELECT
12             SUBSTR(TABLESPACE_NAME,1,20)              AS TBSPC_NAME,
13             BIGINT(TOTAL_PAGES * PAGE_SIZE)/1024/1024 AS TOTAL,
14             USED_PAGES*PAGE_SIZE/1024/1024            AS USED,
15             FREE_PAGES*PAGE_SIZE/1024/1024            AS FREE
16         FROM
17             TABLE(SNAPSHOT_TBS_CFG('[DBNAME]', -2)) AS SNAPSHOT_TBS_CFG) AS CFG
18 FETCH
19     FIRST 1 ROWS ONLY;
20     
21 #查询缓冲池
22 SELECT BUFFERPOOLID, BPNAME, NPAGES, PAGESIZE FROM SYSCAT.BUFFERPOOLS WHERE BPNAME != 'IBMDEFAULTBP'
23 
24 #查询表空间
25 SELECT T.TBSPACE, T.TBSPACEID, ( CASE WHEN T.TBSPACETYPE = 'D' THEN 'DATABASE' ELSE 'SYSTEM' END ) AS TBSPACETYPE, ( CASE WHEN T.DATATYPE = 'A' THEN 'REGULAR' WHEN T.DATATYPE = 'T' THEN 'TEMPORARY' WHEN T.DATATYPE = 'U' THEN 'USER TEMPORARY' WHEN T.DATATYPE = 'L' THEN 'LARGE' END ) AS DATATYPE, C.CONTAINER_NAME, C.TOTAL_PAGES, C.CONTAINER_TYPE, T.EXTENTSIZE, T.PREFETCHSIZE, T.OVERHEAD, T.TRANSFERRATE, T.PAGESIZE, T.DBPGNAME, B.BPNAME, T.DROP_RECOVERY FROM SYSCAT.TABLESPACES T LEFT JOIN SYSCAT.BUFFERPOOLS B ON T.BUFFERPOOLID = B.BUFFERPOOLID LEFT JOIN SYSIBMADM.CONTAINER_UTILIZATION C ON T.TBSPACEID = C.TBSP_ID WHERE T.OWNERTYPE = 'U';
26 
27 #查询序列
28 SELECT SEQNAME, DEFINER, MINVALUE, MAXVALUE, START, INCREMENT, NEXTCACHEFIRSTVALUE, CACHE, CYCLE, ORDER FROM SYSCAT.SEQUENCES WHERE SEQSCHEMA = 'DB2ADMIN' AND SEQTYPE = 'S' WITH UR;
29 
30 #查询函数
31 SELECT FUNCNAME,DEFINER,PARM_COUNT,FUNC_PATH,BODY from SYSCAT.FUNCTIONS where FUNCSCHEMA='DB2ADMIN';
32 
33 #查询存储过程
34 SELECT PROCNAME,DEFINER,PARM_COUNT,TEXT FROM  SYSCAT.PROCEDURES WHERE PROCSCHEMA='DB2ADMIN';
35 
36 #查询Schema
37 SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE DEFINER <> 'SYSIBM' AND SCHEMANAME NOT IN ('NULLID','SQLJ','SYSTOOLS');
38 
39 #查询表
40 SELECT TABNAME,TYPE,COLCOUNT,REMARKS FROM SYSCAT.TABLES WHERE TABSCHEMA='DB2ADMIN';
41 
42 #查询列
43 SELECT SC.COLNO, SC.COLNAME, SC.TYPENAME, SC.LENGTH, SC.SCALE, SC. DEFAULT, SC.NULLS, SC.IDENTITY, SC.GENERATED, SC.REMARKS, A.CONSTNAME, A.TYPE FROM SYSCAT. COLUMNS SC LEFT JOIN ( SELECT C.CONSTNAME AS CONSTNAME, C.TYPE AS TYPE, K.TABSCHEMA AS TABSCHEMA, K.TABNAME AS TABNAME, K.COLNAME AS COLNAME FROM SYSCAT.KEYCOLUSE K INNER JOIN SYSCAT.TABCONST C ON K.CONSTNAME = C.CONSTNAME AND K.TABSCHEMA = C.TABSCHEMA AND K.TABNAME = C.TABNAME AND C.TYPE <> 'F' ) A ON SC.COLNAME = A.COLNAME AND SC.TABSCHEMA = A.TABSCHEMA AND SC.TABNAME = A.TABNAME WHERE SC.TABSCHEMA = 'DB2ADMIN' AND SC.TABNAME =[ TABLENAME ] ORDER BY SC.COLNO ASC;
44 
45 #查询视图关系
46 SELECT BSCHEMA,BNAME,BTYPE FROM SYSCAT.VIEWDEP WHERE  VIEWNAME='V_RO_ORG' WITH UR;
47 
48 #查询视图DDL
49 SELECT VIEWSCHEMA,VIEWNAME,TEXT FROM SYSCAT.VIEWS WHERE VIEWSCHEMA='DB2INST1' AND VIEWNAME=[VIEWNAME];
50 
51 #查询Nickname关系
52 SELECT OPTION,SETTING FROM  SYSCAT.TABOPTIONS WHERE TABSCHEMA='DB2ADMIN' AND TABNAME=[NICKNAME];
53 
54 #查询Wrapper
55 SELECT WRAPNAME, WRAPTYPE, WRAPVERSION, LIBRARY, REMARKS FROM SYSCAT.WRAPPERS
56 
57 #查询Server
58 SELECT WRAPNAME, SERVERNAME, SERVERTYPE, SERVERVERSION FROM  SYSIBM.SYSSERVERS;
59 
60 #查询Server Option
61 SELECT SERVERNAME AS FEDERATEDNAME,SETTING AS DBALIAS FROM SYSCAT.SERVEROPTIONS WHERE OPTION='DBNAME';
62 
63 #查询约束(主键、约束、索引)
64 SELECT TABSCHEMA,TABNAME,INDSCHEMA,INDNAME,COLNAMES,UNIQUERULE FROM SYSCAT.INDEXES WHERE TABSCHEMA='DB2ADMIN' AND TABNAME=[TABLENAME];
65 
66 #查询外键
67 SELECT CONSTNAME,TABSCHEMA,TABNAME,REFKEYNAME,REFTABSCHEMA,REFTABNAME,FK_COLNAMES,PK_COLNAMES FROM SYSCAT.REFERENCES WHERE TABSCHEMA='DB2ADMIN';
68 
69 #查询触发器
70 SELECT TRIGNAME, TABSCHEMA, TABNAME, FUNC_PATH, VALID, TEXT FROM SYSCAT. TRIGGERS WHERE TRIGSCHEMA = 'DB2ADMIN';