DB2基本操作
1. DB2 V9.7安装
下载安装包及license：
提取码: 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';
