DB2数据库常用语句

DB2数据库常⽤语句
1.快速清空⼤量数据表数据，但是还原不了
alter table rm_customer activate not logged initially with empty table
2.⼤量导出表语句
select 'db2 export from d:\exportdb\'||tabname||'.dat select * from '||tabname||';' from syscat.tables where TABSCHEMA='DB2ADMIN'
and tabname not in (select tabname from (select a.tabname, a.tbspace, a.npages*b.pagesize/1024.0/1024.0/1024.0 g , a.card --,a.* from syscat.tables a
left join syscat.tablespaces b
on a.tbspace = b.tbspace
where a.type = 'T'
and a.tabschema = (select current schema from sysibm.sysdummy1
)) a
where a.g>0.05);
3.单表导出语句
db2 "export to FTP_ADJUST_COA.del of del select * from FTP_ADJUST_COA" 当前路径下
4.⼤量导⼊表语句
select 'db2 import from d:\exportdb\'||tabname||'.dat of del replace into '||tabname||';' from syscat.tables where TABSCHEMA='DB2ADMIN' and tabname not in (select tabname from (select a.tabname, a.tbspace, a.npages*b.pagesize/1024.0/1024.0/1024.0 g , a.card --,a.* from syscat.tables a
left join syscat.tablespaces b
on a.tbspace = b.tbspace
where a.type = 'T'
and a.tabschema = (select current schema from sysibm.sysdummy1
)) a
where a.g>0.05);
5.单表导⼊语句
db2 import from sm_res_paractrl.del of del insert into sm_res_paractrl 当前路径下
4229 查看表空间
db2 => list tablespaces show detail
从上述错误来看，是表空间NNC_INDEX01表空间被占满了。

看下NNC_INDEX01表空间使⽤情况：
db2 => list tablespaces show detail
发现可⽤页数已经变成了0。

查看报错的地⽅，是⼀个绑定变量+Batch的操作。

insert into IC_ATP_F，这个表有⼀个主键索引，⽽批量插⼊的时候肯定会⽤到索引表空间。

添加⼀个容器，alter tablespace nnc_index01 add (file '\db2\nnc_index01_2' 204800)
再次执⾏，问题解决。

4229 可能⽇志⽂件太⼩
连接⽬标数据库，执⾏命令：
1.查看⽇志⼤⼩
db2 get db cfg|find /i "log"
2.修改⽇志⽂件⼤⼩
db2 update db cfg using LOGFILSIZ 16384
3.修改主⽇志⽂件个数
db2 update db cfg using LOGPRIMARY 24
4.修改辅助⽇志⽂件个数
db2 update db cfg using LOGSECOND 20
修改后的总⼤⼩为：16384*4K*(12+20)=2048M
5.重启数据库
db2stop
db2start
6.检查⽇志是否修改成功
db2 get db cfg|find /i "log"
db2 -tvf createtb.sql> createtb.log
db2move HKYH import -io replace_create -u db2admin -p db2admin
DB2 SQL Error: SQLCODE=-101, SQLSTATE=54001, SQLERRMC=null, DRIVER=3.63.123
1、命令查看你的bufferpool总⼤⼩（前提，连接数据库 connect to GXCSP）
db2 'select bpname,pagesize,npages from syscat.bufferpools'
2、在保证所有bufferpool总合不超过1G的情况下，尽量增加buffer的⼤⼩，调整bufferpool的⼤⼩。

db2 'alter bufferpool ibmdefaultbp size 51200'
这时会报错，其实缓冲区已经修改成功。

SQL20169W?The buffer pool is not started.?SQLSTATE=01654
3、重启数据库即可（关闭数据库，启动数据库）
db2 get db cfg|find /i "locklist"
db2 update db cfg using MAXLOCKS AUTOMATIC
db2 update db cfg using LOCKLIST 524288 AUTOMATIC
db2 alter tablespace nnc_index01 add (file 'D:\DB2\NODE0000\FTP63TE\nnc_index01_2' 1G)
db2 alter tablespace nnc_index01 extend (file 'D:\DB2\NODE0000\FTP63TE\nnc_index01_2' 1G)
db2 alter tablespace nnc_index01 reduce (file 'D:\DB2\NODE0000\FTP63TE\nnc_index01_2' 1G)
-- 表分区------
--1 创建具有多个分区键列的分区表.
drop table tab_partitions
create table tab_partitions (
acct_no varchar(20),
data_date char(10) ,
pk_run char(20) ,
busi_type varchar(3)
)
partition by range (data_date,pk_run)
(
part tab_partition_20150301_1001H610000000000KU3 STARTING ('2015-03-01','1001H610000000000KU3') INCLUSIVE ENDING ('2015-03-01','1001H610000000000KU3') INCLUSIVE,
part tab_partition_20150302_1001H610000000000KU3 STARTING ('2015-03-02','1001H610000000000KU3') INCLUSIVE ENDING ('2015-03-02','1001H610000000000KU3') INCLUSIVE,
part tab_partition_20150301_1001H610000000000KU4 STARTING ('2015-03-01','1001H610000000000KU4') INCLUSIVE ENDING ('2015-03-01','1001H610000000000KU4') INCLUSIVE,
part tab_partition_20150302_1001H610000000000KU4 STARTING ('2015-03-02','1001H610000000000KU4') INCLUSIVE ENDING ('2015-03-02','1001H610000000000KU4') INCLUSIVE
)
-- 2 查看分区表
select *
from syscat.datapartitions a
where a.tabname = upper('tab_partitions')
-- 3 添加分区
alter table tab_partitions add part tab_partition_20150303_1001H610000000000KU3 STARTING ('2015-03-
03','1001H610000000000KU3') INCLUSIVE ENDING ('2015-03-03','1001H610000000000KU3') INCLUSIVE
-- 4 拆离分区（注，分区的拆离是个异步的过程）
alter table tab_partitions detach part tab_partition_20150301_1001H610000000000KU3 into
tab_partition_20150301_1001H610000000000KU3
-- 4 拆离分区（注，分区的拆离是个异步的过程）//db2 97 以后版本，异步处理机制规避，先删除ftp_res_acct_his_bk,然后把
ftp_res_acct_his剥离分区成ftp_res_acct_his_bk，下次再删除此表。

alter table tab_partitions detach partition tab_partition_20150301_1001H610000000000KU3 into new_table
drop table new_table
-- SELECT * FROM tab_partition_20150301_1001H610000000000KU3
-- 5 将⼀个独⽴的表添加到分区表中
create table tab_partition_01 (
acct_no varchar(20),
data_date char(10) ,
pk_run char(20) ,
busi_type varchar(3)
)
-- 往表⾥插⼊数据
insert into tab_partition_01(acct_no,data_date,pk_run,busi_type)
values('20160621122332000001','2015-03-04','1001H610000000000KU3','3')
-- 查看插⼊的数据
select *
from tab_partition_01
-- 合并表到分区表
ALTER TABLE
tab_partitions ATTACH PARTITION tab_partition_20150304_1001H610000000000KU3 STARTING ('2015-03-04','1001H610000000000KU3') INCLUSIVE
ENDING ('2015-03-04','1001H610000000000KU3') INCLUSIVE
FROM
tab_partition_01
-- 查看分区表
-- 会发现新增加的分区的数据，在分区表中不可见。

⽤最后的语句，执⾏完整性检查。

select *
from tab_partitions
-- 查看分区表字典
select b.datapartitionname,b.tabname,b.access_mode,b.status,b.lowvalue,b.highvalue
from syscat.DATAPARTITIONS b
where b.TABNAME = upper('tab_partitions')
-- 执⾏设置完整性
set integrity for tab_partitions allow write access immediate checked
>db2 "set integrity for tab_partitions allow write access immediate checked"
-- 6、分区表索引
from t_student m
select *
from syscat.indexpartitions
create index idx1_tab_partitions on tab_partitions (acct_no)。