主要整理于2010年,DB2 9.1
1、连接数据库命令
db2 CATALOG TCPIP NODE R_PTDB REMOTE 10.4.144.36 SERVER 60010
db2 CATALOG DATABASE portaldb AS R_PTDB AT NODE R_PTDB AUTHENTICATION SERVER
db2 uncatalog db R_PTDB
db2 uncatalog node R_PTDB
db2 catalog tcpip node R_PTDB remote 10.4.144.36 server 60010
db2 catalog db R_PTDB at node R_PTDB
db2 connect to vnrdb user vnrapp using Vg09@bmb
db2 connect to r_ptdb user vportal using vp*49al
2、导入导出
export to T_A of del select c1,c2 from T_A
import from T_A of del insert into T_A(c1,c2)
LOAD CLIENT FROM '/dp/data/T_A.del' OF DEL INSERT INTO T_A(c1,c2) NONRECOVERABLE
db2 load from /dev/null of del terminate into T_B NONRECOVERABLE
3、查看instance
db2 get instance
db2 list database directory
4、前N行
select * from T_PRD_R_RING order by CYCLE_TIME desc fetch first 1 rows only
5、行号:rownumber() OVER (ORDER BY svcsubid) AS ROW_NUM
6、查看被锁的表:
db2 list node directory
db2 attach to VNRNODE user vnrapp using kR36*app
db2 get snapshot for locks on vnrdb global | more
db2pd -db vnrdb -app -trans -locks -dynamic showlocks | more
db2 list application show detail | grep Lock
1)db2 list utilities show detail
2)db2 load query table vnrapp.T_G_PRD_ML_MAILING_LOG
3)db2 list applications show detail
4)db2pd -stacks
5)db2 update monitor switches using lock on
db2 get snapshot for locks on db <dbname>
get snapshot for application agentid 5671
db2 "force application()"
db2 ? sql952
7、时间格式转换
select to_char(current timestamp + 1 days, 'yyyymmdd') tomorrow, to_char(current timestamp + 2 days, 'yyyymmdd') after_tomorrow, to_char(current timestamp + 2 days, 'yyyymm') next_month from sysibm.sysdummy1
8、DB2 reorg:
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE T_A ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS ALLOW READ ACCESS') ;
RUNSTATS ON TABLE T_A ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL ALLOW READ ACCESS;
REORG TABLE T_A ALLOW READ ACCESS;
REORG INDEXES ALL FOR TABLE T_A READ ACCESS;
ALTER TABLESPACE TIDX AUTORESIZE YES;
9、DB2日志:
/home/db2inst1/sqllib/db2dump/db2diag.log
alter table cuixz_T ACTIVATE NOT LOGGED INITIALLY
10、alter table T_C alter column begin_time set data type varchar(20)
11、truncate table
ALTER TABLE T_C activate NOT LOGGED initially WITH EMPTY TABLE;
12、绑定存储过程
select a.routineschema, a.routinename, b.bname as packagename from syscat.routines a, syscat.routinedep b where a.specificname=b.routinename and a.routinetype='P' AND A.SPECIFICNAME='P_ETL';
REBIND PACKAGE "P8350099" REOPT ALWAYS;
13、loop: LOOP
DELETE FROM (SELECT 1 FROM Table WHERE InvDate <= dt FETCH FIRST
1000 ROWS ONLY) AS D;
IF SQLCODE = 100 THEN LEAVE loop;
END IF;
COMMIT;
END LOOP loop;
14、declare c1 cursor for ……;
load from c1 of cursor insert into ...... nonrecoverable;
15、procedure used table
SELECT c.procname , a.BNAME as tblname
FROM SYSCAT.PACKAGEDEP A,SYSIBM.SYSDEPENDENCIES B,SYSCAT.PROCEDURES C
WHERE a.BTYPE = 'T' AND a.PKGNAME=b.bname and b.dname=c.SPECIFICNAME
and c.procname = 'P_ANA'
order by procname,tblname
16、索引添加情况:
select tbc.tabname, idx.COLNAME
from ( select * from
SYSCAT.COLUMNS
where colname = 'CYCLE_TIME') tbc
left join
(select idx.TABNAME, idc.COLNAME from SYSCAT.INDEXES idx, SYSCAT.INDEXCOLUSE idc
where idx.INDNAME = idc.INDNAME
and idc.COLNAME = 'CYCLE_TIME'
) idx
on tbc.TABNAME = idx.tabname
order by tabname
17、DB2分区
CREATE TABLE hole (c1 int) PARTITION BY RANGE (c1)
(STARTING FROM 1 ENDING AT 10, STARTING FROM 20 ENDING AT 30);
CREATE TABLE hole (CYCLE_TIME timestamp) PARTITION BY RANGE(CYCLE_TIME)(STARTING FROM '2009-02-01 00:00:00' ENDING AT '2009-11-01 00:00:00' EXCLUSIVE every 1 days)
ALTER TABLE hole ADD PARTITION STARTING 15;
ALTER TABLE stock DETACH PART dec01 INTO junk;
DROP TABLE junk;
select * from SYSCAT.TABLES where owner = 'APP'
select * from SYSCAT.DATAPARTITIONS
18、create table as
create table cuixz as (select * from tables) definition only;
19、db2 -td@ -vf 文件名 --(文件以@结尾)
20、去掉回车
replace(second_biz_id, x'0D', '')
21、create alias t_a1 for app2.t_a --添加别名