DB2数据库的学习及总结

db2命令
给db2用赋权的命令
db2 grant dbadm on database to user dsp
#导出数据库结构会生成一个faldb20160224.sql文件
db2look -d udep -e -a -x -i dsp -w dsp -o dsp20160504.sql

导出所属scheme：dsp的所有表结构的命令如下
db2look -d tyfx -e -z dsp -o udep.ddl
语法: db2look -d DBname [-u Creator] [-s] [-g] [-a] [-t Tname1 Tname2...TnameN]

[-p] [-o Fname] [-i userID] [-w password]
db2look -d DBname [-u Creator] [-a] [-e] [-t Tname1 Tname2...TnameN]
[-m] [-c] [-r] [-x] [-l] [-f] [-o Fname] [-i userID] [
-w password]
db2look [-h]

-d: 数据库名：这必须指定

-a: 为所有创建者生成统计信息
-c: 不要生成模拟的 COMMIT 语句
-e: 抽取复制数据库所需要的 DDL 文件
-g: 使用图形来显示索引的页取装对
-h: 更详细的帮助信息
-m: 在模拟方式下运行 db2look 实用程序
-o: 将输出重定向到给定的文件名
-p: 使用简单文本格式
-r: 不要生成模拟的 RUNSTATS 语句
-s: 生成 postscript 文件
-t: 生成指定表的统计信息
-x: 生成“权限”语句 DDL
-l: 生成数据库布局：节点组、缓冲池和表空间
-f: 抽取配置参数和环境变量
-u: 创建者 ID：若 -u 和 -a 都未指定，则将使用 $USER
-i: 注册到数据库驻留的服务器时所使用的用户 ID
-w: 注册到数据库驻留的服务器时所使用的口令
#生成faldb20160224.sql文件之后要执行这个文件
db2 -tvf faldb20160224.sql

db2 [option ...] [db2-command | sql-statement |
[? [phrase | message | sqlstate | class-code]]]
选项：-a, -c, -e{c|s}, -finfile, -lhistfile, -n, -o, -p, -rreport, -s, -t,
-td;, -v, -w, -x, -zoutputfile。

选项 说明 缺省设置
-------------------------------------------------------------
-a 显示 SQLCA OFF
-c 自动落实 ON
-e 显示 SQLCODE/SQLSTATE OFF
-f 读取输入文件 OFF
-l 将命令记录到历史文件中 OFF
-n 除去换行字符 OFF
-o 显示输出 ON
-p 显示 db2 交互式提示 ON
-r 将输出报告保存到文件 OFF
-s 在命令出错时停止执行 OFF
-t 设置语句终止字符 OFF
-v 回送当前命令 OFF
-w 显示 FETCH/SELECT 警告信息 ON
-x 不打印列标题 OFF
-z 将所有输出保存到输出文件 OFF

注意：
使用 DB2OPTIONS 环境变量定制选项缺省值。
紧跟选项字母后的减号(-)使该选项关闭。
在交互方式或文件输入方式下使用 UPDATE COMMAND OPTIONS
更改选项设置。

select 'db2 "export to '||table_name||'.ixf of ixf select * from CUSTRISK.'||table_name||'"' from sysibm.TABLES where TABLE_SCHEMA='CUSTRISK'

select 'db2 "import from '||table_name||'.ixf of ixf commitcount 500 messages msg.out insert into CUSTRISK.'||table_name||'"' from sysibm.TABLES where TABLE_SCHEMA='CUSTRISK'

从数据库里在一张表中随机的抽取10条记录的db2SQL 语句是：
select * from moiamds.p_mon_dsm_cdt order by rand() FETCH FIRST 10 ROWS ONLY
启动db2数据库的命令是： db2start
关闭db2数据库的命令是： db2stop

命令提示符里连接db2数据库的命令是： db2 connect to moia user moiamds using moiamds

导出一张表里的数据的命令是：db2 "export to F:\IMP\p_mon_dsm_cdt.txt of del select * from MOIAMDS.P_MON_DSM_CDT"
IMPORT FROM employee.ixf OF IXF REPLACE_CREATE INTO employee_copy;
（REPLACE_CREATE 这个参数表示如果employee_copy数据表已经存在，则先清空数据表中的数据然后将ixf 中的数据导入，如果employee_copy 数据表不存在，则会先建立该表，然后将数据导入。）
导入数据到一张表中的命令式：db2 "import from tg.txt of del insert into MOIAMDS.P_MON_TAB_ONE"

如果对一张表进行操作的时候报因为表不活动，所以不能对表操作，解决方法：reorg table ---表名；

如果报事务日志已满的解决办法
DB2COPY2->命令行处理工具->命令行处理器—> db2 get db cfg for mds
db2 update db cfg for mds using logfilsiz 6000

db2显示表空间的语句：db2 list tablespaces show detail

去基线导出数据的方法：197.3.6.1上去导出数据的方法：
moiase@/home/mds/file/tmp> db2 connect to moia user moiamds using moiamds
moiase@/home/mds/file/tmp> db2 "export to P_DQM_SERVICE_CASE_TAB.ixf of ixf select * from moiamds.P_DQM_SERVICE_CASE_TAB"
导出成功！ 把它用在本地用E:JDK API ftp 197.3.6.1
输入user：moiase pwd：moiase
连接成功 ； ftp>get P_DQM_SERVICE_CASE_TAB.ixf 回车 现在自己的计算机e:盘JDK API 下就存在了P_DQM_SERVICE_CASE_TAB.ixf文件

查看db2的所有的连接：db2 list applications 断开所有的连接：db2 force applications all

db2 去执行配置文件的方法：
如：在D：盘建一个myscript.txt文件输入：
!db2start;
connect to moia user moiamds using moiamds;
select * from emp;
terminate;
就可以在命令提示符里输入db2cmd 然后输入：db2 -tf D:\myscript.txt

查看db2的数据库分区方法：
db2 list node directory //查询编目录（节点）
db2 list db directory //查询编目录下边具体有哪些库
db2 connnect to mds user mdsuser using mdsuser //连接数据库

创建备份表的sql语句：
create table new_table as (select * from old_table) definition only;

备份数据：insert into new_table select * from old_table;

excel 数据导入到DB2数据库的方法1（先把excel文件另存为.csv文件，放到d盘，然后执行下面的sql）
import from "d:/test.csv" of del messages "d:/msg.out" insert into M_CM_PROD_PRODUCT_ATTRIBUTE;
---------------------------------------------------------------------------------------------------------------------------------------------------------
查看db2数据库最大连接数的命令：
查看当前活动的实例的命令： db2ilist
查看当前活动的实例下的数据库的命令：db2 list db directory
创建一个新的实例下创建数据库的命令：db2icrt newinstance(实例);
删除实例的命令： db2idrop instancename;
set db2instance = 实例;
db2start;
db2 create database 数据库名
连接到数据库后，用db2 get db cfg for database查看一下maxappls和avg_appls的数值。
用db2 update db cfg for database using maxappls number试试把maxappls设置得更大些。
-----------------------------------------------------------------
db2给数据库更改表名的命令：rename table rep_data_04 to rep_data_04_20130528
------------------------------------------------------------------------------
linux系统下重启db2数据库的命令
db2 force applications all
db2stop
db2start
db2 backup db sample on all dbpartitionnums
重启成功---------------------------------------
---------db2只能增加字段的长度（varchar和另外两个）不能dorp掉字段，只能drop掉表，新建表导入数据
修改字段的命令：alter table log_data_11 ALTER nm_rep SET DATA TYPE varchar(200);
-----------------统计字段的出现相同值的行数的sql---------------
select count(formula) as sum, formula from code_formula group by formula

1. 从命令窗口创建一个名为newinst 新实例
db2icrt newinst
2. 在这个新实例newinst 中，使用默认值创建一个名为newdb 的数据库
set db2instance=newinst
db2start
db2 create database newdb
3. 列出服务器上所有的实例
db2ilist
4. 转换到DB2 实例，并确认转换成功
set db2instance=db2
db2 get instance
5. 把dbm cfg 的FEDERATED 参数值由NO 改为YES 并验证修改的结果。
db2 update dbm cfg using FEDERATED YES
db2 force applications all
db2 terminate
db2stop
db2start
db2 get dbm cfg
6. 使用登录操作系统的用户名和密码连接SAMPLE 数据库
db2 connect to sample user <userID> using <psw>
7. 查看当前实例上有多少应用程序在运行
db2 list applications show detail
8. 打开另一个DB2 命令窗口，不指明用户名和密码连接到SAMPLE 数据库。然后查看当前有
多少应用连接到该实例上。
db2 connect to sample
db2 list applications
9. 强制关闭一个DB2 命令窗口
db2 force <application> (<application> 填入程序“b2bp.exe”的句柄，该值由db2 list applications命令获得)
10. 删除实例newins
db2idrop newinst
11. 删除并重新创建DAS，然后启动DAS。
db2admin stop
db2admin drop
db2admin create
db2admin start
12. 在您的实例中设置DB2 注册变量DB2COMM，使其值为tcpip，npipe。
db2set db2comm=tcpip，npipe
db2stop
db2start
13. 置空DB2COMM 注册变量
db2set db2comm=
db2stop
db2start
14. 检查当前db cfg 的LOGSECOND 参数，然后将其值设置成5 并验证新值
db2 connect to sample
db2 get db cfg
db2 update db cfg using LOGSECOND 5
db2 get db cf
------------------db2（标识列）中创建一张表，这张表的一个字段从100开始并且以100增加----------
CREATE TABLE subscriber(subscriberID INTEGER GENERATED ALWAYS AS
IDENTITY (START WITH 100
INCREMENT BY 100)，
firstname VARCHAR(50)，
lastname VARCHAR(50) )
在这个例子中，列subscriberID 是INTEGER 类型，被定义为标识列并且是常规定义。其值将
从100 开始，按增量100 增加
-------序列对象产生整个数据库的一个唯一值。跟标识列不一样的是，序列独立于数据表。下面提供一个例子：
CREATE TABLE t1 (salary int)
CREATE SEQUENCE myseq
START WITH 10
INCREMENT BY 1
NO CYCLE
INSERT INTO t1 VALUES (nextval for myseq)
INSERT INTO t1 VALUES (nextval for myseq)
INSERT INTO t1 VALUES (nextval for myseq)
SELECT * FROM t1
SALARY
------------------------------------------------------
10
11
12
3 record(s) selected.
SELECT prevval for myseq FROM sysibm.sysdummy1
1
----------------------------------------------------------------------------------------------------------------------------------
db2move 工具
Export，Import 和Load 每次都只对一个表进行操作。但是您若使用它们来写一段脚本，就可对
一个数据库中的所有的表进行操作。另一个工具db2move 可以更方便地完成同样的工作。db2move
工具只兼容IXF 格式的文件，而且IXF 文件的名字由db2move 自动生成。下面的例子展示怎么样使
用db2move 并结合export 和import 选项来对SAMPLE 数据库进行整体操作。
导出
db2move faldb export -u slsadmin -p slsadmin
导入
db2move faldb import -u slsadmin -p slsadmin
导出固定schema的语句如下
db2move tyfx(db) export -sn dsp(schema) -u dsp -p dsp
----------------------------------------------------db2load----------------------------------------------------------------
下面的例子将IXF 格式的employee.ixf 文件里面的所有数据导入到表employee_copy。
REPLACE 是LOAD 工具所提供的众多选项之一。它表示将替换employee_copy 表中的所有数据。
LOAD FROM employee.ixf OF IXF REPLACE INTO employee_copy
执行完上面的命令后，该表进入检查暂挂状态。这时您必须运行SET INTEGRITY 命令来检查数据的一致性，下面是执行SET INTEGRITY 的例子：
SET INTEGRITY FOR employee_copy ALL IMMEDIATE UNCHECKE
----------------------------------------------------给用户赋权的命令--------------------------------------------------------
DBADM（DataBase ADMinistrator）是数据库的超级用户。它不是实例层次的权限，所以它没有在前一节中列出，要想授予DBADM 权限，使用GRANT 语句，如下面的例子所示：
connect to sample
grant DBADM on database to user <userid>
--------------如果您想要备份udep 数据库到路径/home/db2inst1/backup，您可以在DB2 Window/Linux 命令行解释器中执行这条命令------
db2 BACKUP DB sample TO C:\BACKUPS;
例如： db2 backup db udep to /home/db2inst1/backup/ compress
备份的文件结构如下

备份完成之后，如果要发生重大灾难，如：DB2 DROP DATABASE UDEP
然后数据库就不存在了，进行数据恢复如下：

命令如下：db2 restore db udep incremental from /home/db2inst1/backup taken at 20160525204710
----------查询的db2的版本的命令------------------------------------------------------------
SELECT service_level, fixpack_num FROM TABLE(sysproc.env_get_inst_info())as INSTANCEINFO
-------------------------------linux上新安装的DB2---------------------------------------------------
db2查看修改端口号

db2安装完成后，tcp/ip连接端口默认为50000，可通过下面的方法确认：

1、使用命令 db2 get dbm cfg
找到"SVCENAME" 查找到TCP/IP 服务名
www.2cto.com
2、到系统配置文件里找到service name 对应的 port number
windows：查看 c:\windows\system32\drivers\etc\services
aix： 查看 /etc/services
如下：
DB2_DB2 60000/tcp
DB2_DB2_1 60001/tcp
DB2_DB2_2 60002/tcp
DB2_DB2_END 60003/tcp
db2c_DB2 50000/tcp
-----------------------------------------------------------
1,先切换到db2inst1用户下，
su - db2inst1
2,查看当前活动的实例
db2ilist
3,切换到当前实例下，
set db2instance = 实例
4，在该实例下创建数据库
----DB2 数据库一旦创建就无法再修改字符集的编码方式了。
----可以在创建的时候指定字符集，如下指定为GBK:
----create db SRCDB using codeset GBK territory CN
db2 create database custrisk
5,为该数据库创建用户和密码（用户名和密码不要超过8个字符，用户名不能大写）
useradd custrisk
passwd custrisk 输入密码：custrisk
创建完成后创建schema，命令如下
CONNECT TO UDEP USER dsp;
------------------------------------------------
-- DDL Statements for Schemas
------------------------------------------------
CREATE SCHEMA "DSP " AUTHORIZATION "DSP ";
---------------创建完成之后必须从起server才能够用创建的用户连接该数据库
6，创建缓冲池
---先连接db2
db2 connect to custrisk
db2 create bufferpool udeptmp32k all nodes size 9060 pagesize 32k //100*1024%32
7，创建表空间
db2 "create regular tablespace eastsp pagesize 32k managed by database using(file '/home/db2inst1/east' 5g) bufferpool udeptmp32k"
@命令创建临时表空间
db2 "create USER TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k managed by database using(file '/home/db2inst2/faldbtemp' 2g) bufferpool faltmp32k"
@命令创建系统表空间
db2 "create system TEMPORARY TABLESPACE TEMPSPACE3 pagesize 32k managed by database using(file '/home/db2inst2/faldbtemp3' 2g) bufferpool faltmp32k"

8，把表空间赋给用户
db2 grant use of tablespace TEMPSPACE3 to user slsadmin
9，没有什么默认的表空间
创建表的时候必须指定如：
create table test(userid varchar(5),username varchar(20)) in custrisk
-------------------------------------------------------------------------------------------------------------------

创建缓冲池：create bufferpool dsppool size 1000 pagesize 32k;
create tablespace newtbs pagesize 32k managed by system using (file '/home/db2' 300M) bufferpool dsppool
db2 list tablespaces;
db2 list tablespaces contraners for 4 {4为上边查出的id}

=================================================DB2创建触发器的例子===================================================
-- 建表语句
CREATE TABLE WANGZSTABLE (
ID INTEGER NOT NULL,
NAME VARCHAR(20) NOT NULL,
AGE VARCHAR(20) NOT NULL,
STATUS VARCHAR(10),
CREATETIME TIMESTAMP,
UPDATETIME TIMESTAMP,
SENDTIME TIMESTAMP
)

select * from wangzsTable;
insert into WANGZSTABLE(id,name,age) values(1,'wangzs1','26');
update WANGZSTABLE set status='4' where id=1;

--update 更新特定状态 status=4
DROP TRIGGER "WANGZS_UPDATE";

CREATE TRIGGER WANGZS_UPDATE
AFTER UPDATE OF STATUS ON WANGZSTABLE
REFERENCING OLD AS OLDROW NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF(NEWROW.STATUS='4')
THEN
update wangzsTable set UPDATETIME=CURRENT TIMESTAMP where id=NEWROW.id;
END IF;
END;

--update 更新特定id
DROP TRIGGER "WANGZS_UPDATE";

CREATE TRIGGER WANGZS_UPDATE
AFTER UPDATE OF STATUS ON WANGZSTABLE
REFERENCING OLD AS OLDROW NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
update wangzsTable set UPDATETIME=CURRENT TIMESTAMP where id=NEWROW.id;
END;

--insert
DROP TRIGGER "WANGZS_INSERT";

CREATE TRIGGER WANGZS_INSERT
AFTER INSERT ON WANGZSTABLE
REFERENCING NEW AS NEWROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
update wangzsTable set CREATETIME=CURRENT TIMESTAMP where id=NEWROW.id;
END;

=======================================================================================================================