DB2学习资料

取数据库当前琐的快照
db2 get snapshot for locks on masa>zyl.out

杀死正在运行的进程
db2 "force appplications(进程号)"

整理表语句（优化表）:
reorg tablae masadw.tb_dw_sc_user;
runstats on table masadw.tb_dw_sc_user;
如何獲得表的排斥鎖?
LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE //解除表的鎖定： COMMIT
装载语句:
load client FROM /export/masaetl/work/data/dest_data/A0201120040802000000.AVL \
of DEL MODIFIED BY COLDEL0x09 insert into table_name \
NONRECOVERABLE CPU_PARALLELISM 4 PARTITIONED DB \
CONFIG PARTITIONING_DBPARTNUMS(0,1,2,3)
定长装载:
load client FROM /data/yjdata/tmp/a_11300_20040830_02008_00_001.tmp \
of asc method l(1 8,9 28,29 32) insert into tablename \
NONRECOVERABLE CPU_PARALLELISM 4 PARTITIONED DB \
CONFIG PARTITIONING_DBPARTNUMS(0,1,2,3)
装载语句:(消息文件)
load client FROM /export/masaetl/work/data/org_data/cond.avl \
of DEL MODIFIED BY COLDEL0x09 MESSAGES /export/masaetl/111 \
replace into MASAdw.TB_ods_promo_cond_BOSS15 \
NONRECOVERABLE CPU_PARALLELISM 4 PARTITIONED DB \
CONFIG PARTITIONING_DBPARTNUMS(0,1,2,3)

建索引
drop index MASADW.IDX_INTE_BILLACCT;
CREATE INDEX MASADW.IDX_INTE_BILLACCT
ON MASADW.TB_DW_AC_ACCT_INTEGRATE_BILL_BOSS15
(ACCOUNT_ID ASC
)
PCTFREE 10
DISALLOW REVERSE SCANS;

清空表
ALTER TABLE tb_name ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
1.
调试过程，第一步连数据库是怎样写的
db2 disconnect all;
db2 set client connect_dbpartitionnum 1;
db2 connect to masa user masamk using passwd;
db2 -td@ -f 文件名。
2.
load数据失败后,表处于“检查暂挂”状态。
执行以下语句以解除:
SET INTEGRITY FOR table_name IMMEDIATE CHECKED
解除暂挂状态:
load client from /export/masadw/test/dest_data/zhb.out of del \
terminate into tablename
load client from 'c:\aa.del' of del terminate into tablename
3.
导出过程
export to c:\proc.sql of del select text from syscat.procedures where
procname =''
4.
查找前N条记录
select * from table fetch first N rows only
5.
导出
db2 export to c:\pinpai.txt of del modified by coldel0x09 select * from table_name
db2 export to c:\pinpai.txt of del modified by nochardel select * from table_name
以TAB分隔,字符串不带双引号导出:
db2 export to c:\pinpai.txt of del modified by coldel0x09 nochardel select * from table_name
导出数据库分区2的数据
db2 "export to /smart/tmp/GS_DETAIL_DM_200411_2.txt of del modified by coldel0x0
9 select * from miner.GS_DETAIL_DM_200411 where dbpartitionnum(serv_number) in (
2)";
6.
导入
db2 import from d:\workspace\A0500120.AVL of del modified by coldel0x09 \
commitcount 1000 insert into table_name
选择字段导入
import from /data/bonson/parabak/P0401220040731000000.AVL \
of del modified by coldel0x09 method P(1,4,2,7,5,6) \
commitcount 5000 insert into TableName
定长导入
import from /data/yjdata/tmp/a_11300_20040827_0200

8_00_001.tmp of asc \
method l(1 8,9 28,29 32) commitcount 10000 insert into tablename
7.
编译脚本
db2 connect masa3 user chenyd
db2 -v -t -f c:\xxxx.sql
-v 显示正在执行的命令
-t 脚本文件中每个命令以 ；号结束
-f 指定文件路径
8.
启动数据库
db2start
9.
停止数据库
db2stop
10.
连接数据库
db2 connect to o_yd user db2 using pwd
11.
读数据库管理程序配置
db2 get dbm cfg
12.
写数据库管理程序配置
db2 update dbm cfg using 参数名 参数值
13.
读数据库的配置
db2 connect to o_yd user db2 using pwd
db2 get db cfg for o_yd
14.
写数据库的配置
db2 connect to o_yd user db2 using pwd
db2 update db cfg for o_yd using 参数名 参数值
15.
关闭所有应用连接
db2 force application all
db2 force application ID1,ID2,,,Idn MODE ASYNC
(db2 list application for db o_yd show detail)
16.
备份数据库
db2 force application all
db2 backup db o_yd to d:
(db2 initialize tape on \\.\tape0)
(db2 rewind tape on \\.\tape0)
db2 backup db o_yd to \\.\tape0
17.
恢复数据库
db2 restore db o_yd from d: to d:
db2 restore db o_yd from \\.\tape0 to d:
18.
绑定存储过程
db2 connect to o_yd user db2 using pwd
db2 bind c:\dfplus.bnd
拷贝存储过程到服务器上的C:\sqllib\function目录中
19.
整理表
db2 connect to o_yd user db2 using pwd
db2 reorg table ydd
db2 runstats on table ydd with distribution and indexes all
20.
执行一个批处理文件
db2 -tf 批处理文件名
（文件中每一条命令用 ；结束）
21.
自动生成建表（视图）语句
在服务器上：C:\sqllib\misc目录中
db2 connect to o_yd user db2 using pwd
db2look -d o_yd -u db2 -e -p -c c:\o_yd.txt
22.
显示当前用户所有表
db2look -d ylbx -u db2admin -w -asd -a -e -o a.txt21.
23.
授权
grant dbadm on database to user bb
24.
列出所有的系统表
list tables for system
25.
查看表结构
db2 describe select * from user.tables
27.
导出单个表结构到一个文件
db2look -d masa -e -u MASAMK -a -t PSC_MODE_SCORE_200503 -o aa.out
相当于informix
dbschema -d masa3 -t ods_bill_msisdn_ar ods_bill_msisdn_ar.sql -ss
导出所有表结构到一个文件
db2look -d masa3 -u administrator -a -e -p -c -o c:\db2bak.txt
运行条件在服务器上运行。
29.
查找过程内容
select text from syscat.procedures where procname='过程名'
查找function内容
select * from syscat.functions where funcschema like 'MASAMK%';
select body from syscat.functions where funcschema like 'MASAMK%';
30.
取当前时间
select current date from 任一表 fetch first 1 rows only
31。
DB2的虚拟表(如oracle的dual表)
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1

33.
关于多分区存储
在主机上创建了一个数据库
CREATE DATABASE masa
创建了第一个表空间
CREATE TABLESPACE ARGTBS
MANAGED BY DATABASE
USING (device '/dev/vx/rdsk/DWDBDG/arg_1g_01lv' 1G) O

N DBPARTITIONNUM(0)
USING (device '/dev/vx/rdsk/DWDBDG/db2_2g_01lv' 2G) ON DBPARTITIONNUM(1)
USING (device '/dev/vx/rdsk/DWDBDG/db2_2g_02lv' 2G) ON DBPARTITIONNUM(2)
USING (device '/dev/vx/rdsk/DWDBDG/db2_2g_03lv' 2G) ON DBPARTITIONNUM(3);
创建表
CREATE TABLE partest
(
iid integer not null
) in argtbs;
装入以下数据
IID
-----------
400
500
800
600
300
700
100
200

8 record(s) selected.
使用以下语句
select dbpartitionnum(iid),iid from gsbiinst.partest
1 IID
----------- -----------
0 600
3 300
3 700
1 100
1 200
2 400
2 500
2 800

其中第一列就是该行数据所在的PARTITION NUM
34。
连接远程数据库
在本机"DB2命令行窗口"下执行
下面两条命令
catalog tcpip node nde239 remote 135.129.22.239 server 50000
db2 catalog database masa as masa at node nde239 authentication server
然后尝试连接数据库
db2 connect to masa user masadw
35.
显示表空间状态
db2 list tablespaces show detail
创建表空间
CREATE TABLESPACE MINDBS_FILE1 \
PAGESIZE 32K \
MANAGED BY DATABASE \
USING(FILE '/minerdbs1/MINDBS_FILE1/MINDBS_FILE1000' 30G) ON DBPARTITIONNUM(0) \
USING(FILE '/minerdbs1/MINDBS_FILE1/MINDBS_FILE1001' 30G) ON DBPARTITIONNUM(1) \
USING(FILE '/minerdbs1/MINDBS_FILE1/MINDBS_FILE1002' 30G) ON DBPARTITIONNUM(2) \
USING(FILE '/minerdbs1/MINDBS_FILE1/MINDBS_FILE1003' 30G) ON DBPARTITIONNUM(3) \
BUFFERPOOL masabp
怎么看db2,一个表占了多大空间？
Select * from SYSIBM.SYSTABLES;
npages fpages
之前要runstats
大概是这样
再次提醒大家在装载数据使用load时，一定要加上nonrecoverable选项，否则会导致表空间处于backup panding状态。
LOAD CLIENT FROM /export/masamk/zhbbak/pps.txt \
OF DEL MODIFIED BY COLDEL0x09 INSERT INTO \
ZHBTEST.TB_DW_SC_PPS_CUST nonrecoverable CPU_PARALLELISM 4 \
PARTITIONED DB CONFIG \
PARTITIONING_DBPARTNUMS (0,1,2,3)

Select name ,tbname from SYSIBM.SYSCOLUMNS where partkeyseq =2 order by tbname
可以查找某表对应的partitioning key 为哪个字段。
看自建涵数(function)语句
select body from syscat.functions where lower(funcschema) like '%masamk%' and lower(funcname) like '%last_day%'
except 运算符
except运算符通过包括所有在TB1中但不在TB2中的行消
除所有重复行而派生出一个结果表.用EXCEPT ALL不消除重复行.
select serv_number from masaimei.tb_mk_cust_imei_cur_BOSS15
except
select serv_number from masaimei.tb_t_cust_imei_cur_BOSS15_tmp1;
=============================================
update MASAMIS.TB_FAT_BRAND_MIS_USER
set (mis_para_brand_id,upp_para_brand_id)=
(select mis_para_brand_id,upp_para_brand_id
from MA

SAMK.TB_FAT_MONTH_FEE_ALL_INFO A
where A.statis_month=l_month and
serv_number=MASAMIS.TB_FAT_BRAND_MIS_USER.serv_number)
where serv_number in (select serv_number from
MASAMK.TB_FAT_MONTH_FEE_ALL_INFO);
================================================
生成数据库信息命令(将产生/tmp/db2support.zip文件):
db2support /tmp -f -d masa -c -s
================================================
表空间处于backup pending状态是用表空间在线备份操作解除:
db2_all "db2 backup db masa tablespace listdbs1 online incremental use tsm"
================================================

36.
删除过程
db2 "drop procedure 过程名"
37.
调用过程
db2 "call 过程名("",?)"