DB2常用

主要整理于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 --添加别名