DB2学习总结

.DB2学习整理笔记l 数据库软件安装（v9.7）l 软件下载IBM官方网站，需注册账号。l 解压db2安装包#tar -zxvf v9.7_linuxx64_server.tar.gz进入server目录下，执行安装检查root#cd server root#./db2prereqcheckl 运行安装程序root#./db2_install -no-ese-l 安装licensedb2licm -l命令可以查看到db2的license信息。可以找一个永久的license添加到db2数据 库即可,把db2ese_c.lic放到一目录下：/opt/ibm/db2/V9.7/license/db2ese_c.lic,在/opt/ibm/db2/V9.7/adm/目录下执行:db2licm -a /opt/ibm/db2/V9.7/license/db2ese_c.licl 创建DB2运行所需要的用户组和用户root#groupadd -g 901 db2iadmroot#groupadd -g 902 db2fadmroot#groupadd -g 903 dasadmroot#useradd -g db2iadm -u 801-d /home/db2inst -m db2inst (管理当前实例) root#useradd -g db2fadm -u 802 -d /home/db2fenc1 -m db2fenc root#useradd -g dasadm -u 803 -d /home/dasadm1 -m dasusr (管理所有实例)l 为用户创建密码passwd db2instl 创建实例root#cd /opt/ibm/db2/V9.7/instanceroot#./dascrt -u dasusr root#./db2icrt -u db2fenc db2inst (db2fenc表示将用来运行受防护用户定义的函数（UDF）和受防护存储过程的用户的名称,db2inst用户实例实例的名称必须与拥有实例的用户的名称相同)l 启动db2实例su - dasusrdasusr#. das/dasprofiledasusr#db2admin startsu - db2instdb2inst#. sqllib/db2profiledb2inst#db2startdb2inst#db2 get instancel 关闭、启动数据库db2inst#db2 force applications alldb2inst#db2stop db2inst#db2startl 创建样本库db2inst#cd /opt/ibm/db2/V9.7/bindb2inst#./db2sampll 设置DB2自启动root#cd /opt/ibm/db2/V9.7/instanceroot#./db2iauto -on db2instl 配置TCPIPsu - db2instdb2inst#db2set -alldb2inst#db2set DB2COMM=TCPIPdb2inst#db2set db2codepage=1386(简体中文)db2inst#db2 get dbm cfg |grep SVCENAME db2inst#tail /etc/servicesdb2inst#vim /etc/services (确保SVCENAME与/etc/services中端口保持一致。)db2inst#db2 update dbm cfg using SVCENAME 60000 （直接指定端口或使用名称与/etc/services中保持一致即可.linux端口默认为60000，windows端口默认为50000）db2inst#db2stop / db2stop forcedb2inst#db2startdb2inst#netstat -ano | grep 60000附：如果系统为CENTOS7，可能会因为防火墙问题导致50000端口被禁用解决方法：su - rootsystemctl stop firewalld.service或者将50000端口加入防火墙信任：firewall-cmd -permanent -zone=public -add-port=50000/tcp重启防火墙:systemctl stop firewalld.service systemctl start firewalld.servicel 数据库创建l 一、创建数据库db2inst#db2db2inst#create database develop 或db2createdbusingcodesetutf-8 territoryCNcollateusingidentitydb2inst#connect reset (连接复位)db2inst#terminate (编译)db2inst#connect to develop user db2inst using db2instdb2inst#list db directoryroot#useradd -g db2iadm db2test (db2iadm数据库用户组)root#passwd db2testdb2inst#db2db2inst#conenct to developdb2inst#grant connect on database to user db2testdb2inst#grant dbadm on database to user db2testdb2inst#db2get authorizations db2inst#revoke dbadm on database from user db2testdb2inst#db2grantuseoftablespacetouserdb2te详细权限说明见DB2权限说明.docx实例l 二、创建表空间db2inst#db2createbufferpoolbp32kallnodessize-1pagesize32k（新建缓冲池）db2inst#db2 create bufferpool tmppool size 500 pagesize 32k (临时缓冲区)db2inst#db2alterbufferpoolbp32ksize2g（调整缓冲池大小）db2inst#drop bufferpool（删除缓冲区）db2inst#db2 list tablespaces show detail （查看表空间）db2inst#db2createregulartablespacetablespace1pagesize32kmanagedbydatabaseusing(file/usr/yixiayizi/tablespace15g)bufferpoolbp32k （当指向外置盘时，file改为device）db2inst#db2createtemporary tablespacetmptablespacepagesize32kmanagedbydatabaseusing(file/usr/yixiayizi/tablespace15g)extentsize 80 bufferpool tmppool （临时表空间）db2inst#db2ALTERTABLESPACERESIZE(FILE/cstp/usr/db2ad/db2ad/5g)（调整表空间大小）db2inst#db2 DROP TABLESPACE db2inst#db2 alter database dbname add storage on 路径1，路径2 数据库支持自动存储v9.7以后支持该命令db2inst#db2 alter tablespace tbspname managed by automatic storge; 转自动存储第一步db2inst#db2 alter tablespace tbspname rebalance;转自动存储第二步l 三、编目系统数据库目录db2inst#db2catalogtcpipnode结点名字remote结点所在ip地址server服务端口db2inst#db2uncatalognode结点别名 db2inst#db2catalogdb远程数据库名字as数据库别名atnode结点名字db2inst#db2uncatalogdb数据库别名l 四、创建数据库SCHEMAdb2inst#db2 values current schemadb2inst#db2 select SCHEMANAME from syscat.SCHEMATAdb2inst#db2 create schema testdb2inst#db2db2inst#connect to develop user db2test using db2test db2inst#select current schema from sysibm.sysdummy1db2inst#set current schema schemanamedb2inst#drop schema schemaname restrictl 数据库参数修改db2inst#db2 update db config for develop using applheapsz 256 (修改数据库的应用程序内存堆大小)db2inst#db2 update db cfg for develop using AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON （修改数据表统计为自动统计，修改时get查看参数目录层次）db2inst#db2 get dbm cfg （查看实例设置）db2inst#db2 update dbm cfg using 参数名 新值 （修改实例设置）db2inst#db2 get db cfg for develop (查看数据库设置)db2inst#db2 update db cfg for using 参数名 新值（修改数据库参数)db2inst#db2set -all 查看数据参数db2inst#db2set DB2_COMPATIBILITY_VECTOR=ORA 设置兼容oracledb2inst#db2set DB2_COMPATIBILITY_VECTOR=回车 删除兼容oracle注意：修改参数后重启数据库l 数据库导入导出Contents- 使用db2 backup指令备份- 使用db2 restore指令恢复- 使用db2look提取数据库结构DDL- 用于数据移动的文件格式- 使用db2move导出全部数据- 使用db2 export指令导出数据- 使用db2move导入（import）数据- 使用db2 import指令导入数据l db2 backup指令备份首先关闭所有到数据库的连接，将数据库置为“静默”状态：$ db2 connect to testdb user db2inst1 using thepasswd$ db2 quiesce database immediate force connections$ db2 connect reset现在可以开始备份了：$ db2 backup database testdb to “/home/backup” user db2inst1 using thepasswd解除数据库的“静默”状态：$ db2 connect to testdb user db2inst1 using thepasswd$ db2 unquiesce database$ db2 connect reset说明：1， 以上指令将会把数据库testdb备份到指定目录 /home/backup下，所以请确保当前登陆用户（db2inst1）对该目录有读写的权限。题外话，若打算使用root用户来执行备份，请先编辑文件 /etc/group，将root用户加入到与DB2相关的几个组：db2grp1, db2fgrp1, dasadm1.2， 生成的备份文件名如下：TESTDB.0.db2inst1.NODE0000.CATN0000.20050131205259.001l 使用db2 restore指令恢复相同库名恢复：$ db2 restore database testdb from “/home/backup”说明：这将会从指定的位置恢复数据库testdb。不同库名恢复：$ db2 restore database testdb from “/home/backup” into testdb_new说明：这个指令将会建立一个新库，名为testdb_new，其结构、内容来自原先testdb的备份。l 使用db2look提取数据库结构DDL提取DDL$ db2look d testdb a e x o testdb.sql$db2 -tvf testdb.sql -z log.log参数的含义与用法请参考db2look的help.编辑得到的DDL文件使用db2look得到的DDL文件无法直接使用，因为其中存在一些与当前系统相关的特殊信息，所以需要对该文件进行编辑。共有几个方面：1， 去掉文件头、尾的指令：CONNECT TO TESTDB;COMMIT WORK;CONNECT RESET;TERMINATE;位于这几句指令之间的就是定义该数据库的DDL语句，去掉这几句话让这个文件变成一个纯粹的DDL文件，另外，有时候很容易发生DB2指令执行错误，将四句指令放到文件外手动执行更有助troubleshooting.2， 去掉schema name.在本案例中schema name是db2inst1。因为在进行异种平台的数据移动的时候会发生schema name的变化，最常见的就是从UNIX类系统迁移到Windows平台的时候，它缺省的schema name分别为db2inst1, db2admin.3， 去掉全部引号此经验主要来自Oracle，一个object，定义它的时候，使用了引号与没有使用引号是两个不同的object.4， 去掉create table语句中指定的tablespace这个原因与2类似，在数据移动的过程中，tablespace name发生变化是很经常的事情，所以不要指定，让（不同的）系统自己决定。有一点非常重要，需要强调，若存在比较大的字段，就必须创建pagesize比较大的bufferpool，在创建一个tablespace使用该bufferpool。用于数据移动的文件格式这个部分简单介绍一下用于DB2数据移动的文件格式，共有四种：1. ASC非定界ASCII文件，是一个ASCII字符流。数据流中的行由行定界符分隔，而行中的每一列则通过起始和结束位置来定义。例如：10 Head Office 160 Corporate New York15 New England 50 Eastern Boston20 Mid Atlantic 10 Eastern Washington38 South Atlantic 30 Eastern Atlanta42 Great Lakes 100 Midwest Chicago51 Plains 140 Midwest Dallas66 Pacific 270 Western San Francisco84 Mountain 290 Western Denver2. DEL定界ASCII文件，也是一个ASCII字符流。数据流中的行由行定界符分隔，行中的列值由列定界符分隔。文件类型修饰符可用于修改这些定界符的默认值。例如：10,Head Office,160,Corporate,New York15,New England,50,Eastern,Boston20,Mid Atlantic,10,Eastern,Washington38,South Atlantic,30,Eastern,Atlanta42,Great Lakes,100,Midwest,Chicago51,Plains,140,Midwest,Dallas66,Pacific,270,Western,San Francisco84,Mountain,290,Western,Denver3. WSFwork sheet format，工作表格式，用于与Lotus系列的软件进行数据交换。4. PC/IXF集成交换格式（Integration Exchange Format，IXF）数据交换体系结构的改编版本，由一些列可变长度的记录构成，包括头记录、表记录、表中每列的列描述符记录以及表中每行的一条或多条数据记录。PC/IXF 文件记录由包含了字符数据的字段组成。l db2move导出/导入全部数据db2move是一个集成式的数据移动工具，它支持导出（export）、导入（import）、装入（load）三种操作方式。其实db2move的这三种工作方式分别是通过简单使用db2 export, db2 import, db2 load指令来完成的。此部分仅仅介绍其export功能，import和load将在稍后的部分介绍。使用db2move导出的数据文件格式是IXF。建立并进入数据存放目录：$ mkdir /home/backup/mydata$ cd /home/backup/mydata导出指定的数据库中的全部数据：$ db2move testdb export u db2inst1 p thepasswd说明：1，这将会把数据库testdb中的全部数据提取到当前目录（/home/backup/mydata）中。每个表的内容都存储在一个.ixf文件中，每个.ixf文件都有一个与之相对应的.msg文件，.msg文件是描述从表中导出数据时的信息的。另外还有两个文件，db2move.lst用来记录.ixf文件、.msg文件与表的一一对应关系，EXPORT.out记录的是导出数据时的屏幕输出。2，有关db2move指令更多的细节，请直接执行该指令，将会打印出其帮助信息。使用db2 export指令导出数据与上面提到的db2move的export功能不同，db2 export是一个更加细致的导出工具，它支持三种数据文件格式：DEL, WSF, IXF.以下示范将数据库testdb中表mytbl的数据导出，存储在目录 /home/backup 下。建立到数据库的连接：$ db2db2 = connect to testdb user db2inst1 using thepasswd以DEL格式导出：db2 = export to /home/backup/mytbl.txt of del select * from mytbl以IXF格式导出：db2 = export to /home/backup/mytbl.ixf of ixf select * from mytbl注：若需要记录导出过程中的message，使用：db2 = export to /home/backup/mytbl.ixf of ixf messages /home/backup/mytbl.msg select * from mytbl断开连接：db2 = connect resetdb2 = quit$更多有关db2 export的帮助，请：$ db2 ? export使用db2move导入（import）数据以db2inst1用户身份登录到Host 2。创建数据库mytestdb：$ db2db2 = create database mytestdb on /home/db2inst1 using codeset UTF-8 territory CNdb2 = connect to mytestdb user db2inst1 using thepasswd创建一个pagesize为16K的bufferpool，名为mybigpool：db2 = create bufferpool mybigpool immediate size 1000 pagesize 16K创建一个tablespace使用上面创建的bufferpool，名为mybigspace：db2 = create regular tablespace mybigspace pagesize 16K managed by system using (/home/db2inst1/db2inst1/NODE0000/SQL00004/SQLT0003.0) extentsize 16 overhead 12.67 prefetchsize 16 transferrate 0.18 bufferpool mybigpool dropped table recovery off注：extentsize, overhead, prefetchsize, transferrate这几个参数值与所使用的服务器有关，我这里使用的值是基于普通的、使用SCSI硬盘的PC服务器的。完成空库的创建：db2 = commit workdb2 = connect resetdb2 = terminate导入（import）数据：使用import方式不需要先建表结构，即，准备好一个空库就行了。这一点与load方式不一样，load方式需要先建立表结构。我将从Host1上导出的全部数据文件（位于Host1的 /home/backup/mydata下）复制到Host2下某个目录下，假定为 /home/movedata$ cd /home/movedata$ db2move mytestdb import u db2inst1 p thepasswd此时屏幕上会显示有关导入数据的信息。存在的问题：db2move import方式只能导入“普通”的表，如果表中存在自增长的IDENTITY列，那么使用db2move import时，会出错。这是因为，如果IDENTITY列创建表的时候都是定义成always的话，那么在导入数据的时候该列数据是不能被赋值的，而是应该由系统生成，使用db2move无法导入这样的表。对于这种含有IDENTITY列的表，只能使用db2 import指令来进行导入，相关的参数是IDENTITYIGNORE，IDENTITYMISSING。我将在下一部分给出具体的指令操作。一句题外话：如果需要实现唯一主键，可以不必使用IDENTITY列，改而使用sequence，这样比较便于维护和管理。使用db2 import指令导入数据import和export是一对存在对应关系的指令，有一点不同的是，import支持四种格式：ASC, DEL, WSF, IXF, 而export只支持三种（见上面相应部分的描述）。以下示范将数据文件/home/movedata/mytbl.ixf导入到数据库mytestdb中。$ db2db2 = connect to mytestdb user db2inst1 using thepasswddb2 = import from /home/movedata/mytbl.ixf of ixf insert into mytbldb2 = commit workdb2 = connect resetdb2 = quit$在上一部分，我有提到，若表中存在自增长的IDENTITY列，需要使用相应的参数才能导入，比如：db2 = import from /home/movedata/mytbl2.ixf of ixf modified by identityignore insert into mytbl2更多有关db2 import的帮助，请：$ db2 ? importl 数据库常用函数l 列函数AVG，COUNT，MAX，MIN（用法与ORACLE一致），l 标量函数ABS，HEX（十六进制），LENGTH，YEARl 日期函数Current timestamp ,sysdate(当前系统时间 输出：2017-11-06 19:33:04) Current date(当前系统时间 输出：2017-11-06 19:33:04) Date(Current date/2017-11-06) (转换成日期格式 输出：2017-11-06) year(Current date)（输出：2017) Month(Current date/2017-11-06)（输出：11) day(Current date/2017-11-06)（输出：6) hour(Current timestamp )（输出：时) minute(Current timestamp )（输出：分) second(Current timestamp )（输出：秒) microsecond(Current timestamp )（输出：毫秒) Date + 1 (year,month,day,hour,minute,second,microsecond) / Date + 1 year +2 month +1 day 日期进行加减Days(date)-days(date) 计算两个日期间天数l 开发注意事项l 表结构修改1. alter table alter column set data type ; (如：varchar (20);字段为主键时不能转化类型)。2. alter table rename column to ;3. alter table add column ;4. alter table drop constraint ;5. alter table add constraint primary key(column_name) ;6. alter table alter column drop default;7. alter table alter column set default ;8. alter table alter column drop not null;9. alter table alter column set not null;10. Comment on column is 备注说明;说明：修改表结构以后需要对表进行重组（reorg）。l 数据库优化命令l Reorg(重组)Reorg table use ;（通过重构来消除“碎片”数据并压缩信息，对表进行重组）Reorg table index use ;（建议使用临时表空间进行重组，不使用use参数时在表对应表空间执行，存在多个索引时建议使用最为重要的表索引进行重组）Db2 update db cfg for develop using AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_REORG ON（修改表重组，修改时get查看参数目录层次）l Runstats(统计信息)Runstats on table ;(收集表统计信息，使DB2优化器使用最新的统计数据进行优化)参数自动执行参数配置：db2 update db cfg for develop using AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON （修改数据表统计为自动统计，修改时get查看参数目录层次）select stats_time,* from sysibm.systables 查看统计信息stats_time最后一次统计时间，以后系统每3小时触发auto_runstatsl Reorgchk(重组检查)Reorgchk on ；Update statistics :更新统计数据，根据统计数据判断是否需要重组Current statistics:根据当前统计数据判断是否需要重组Table table_name：对单表进行分析Table system：对数据库所有的表进行分析Table user ：对当前用户模式下的所有表进行分析l 优化常用语句1. select * from syscat.tablespaces where tbspacetype=D（查找数据库管理表空间）2. select *