导出ddl db2look -d  CESHI1 -e -o db.sql -i db2inst1 -w Coqais011备份db2 BACKUP DATABASE dbname恢复db2 RESTORE DATABASE dbnamea.服务端安装b.客户端安装1.建数据库create database HRA_GF 2.建信道catalog tcpip node gf remote 192.168.2.252 server 500003.1 别名catalog database HRA_GF as hServer at node gf3.2 数据库挂在信道上catalog database HRA_GF at node gf3.3 terminate 刷新3.4 db24.连接数据库connect to HRA_GF user db2admin using Hhgf20175.删除节点uncatalog node gf6. 获取序列下一个值     select nextval for BAS_MODULE_SEQ  from sysibm.sysdummy17.创建函数结合序列值获取     7.1 CREATE function BAS_SEQ ()            RETURNS BIGINT            LANGUAGE SQL            CONTAINS SQL            NO EXTERNAL ACTION            DETERMINISTIC            BEGIN ATOMIC               Declare cd BIGINT;   --时间             set cd=next value for BAS_MODULE_SEQ;            RETURN cd;              END ;  7.2CREATE  FUNCTION GET_DISCOUNT_FACTOR(    v_compute_date  date,    v_reference_date  date,    v_index_price_factor_id  bigint)RETURNS BIGINT          LANGUAGE SQL          NOT DETERMINISTIC          READS SQL DATA          STATIC DISPATCH          CALLED ON NULL INPUT          EXTERNAL ACTION          INHERIT SPECIAL REGISTERS            BEGIN ATOMIC            Declare RESULT bigint;    set  RESULT=(select count(1)                   from mkt_zero_rate_data                  where compute_date = v_compute_date                  and reference_date = v_reference_date                  and index_price_factor_id = v_index_price_factor_id);     IF RESULT=0       then        RETURN 0;       ELSE         SET RESULT=(                         select discount_factor   from mkt_zero_rate_data                                    where compute_date = v_compute_date                                    and reference_date = v_reference_date                                    and index_price_factor_id = v_index_price_factor_id);                                             END IF;                           return Result;END;CREATE  FUNCTION FN_GETFLOWURL(p_createUrl  varchar(255),p_nodeUrl  varchar(255),p_FormCode  varchar(255),p_FormType  varchar(255))RETURNS varchar(255)          LANGUAGE SQL          NOT DETERMINISTIC          READS SQL DATA          STATIC DISPATCH          CALLED ON NULL INPUT          EXTERNAL ACTION          INHERIT SPECIAL REGISTERS            BEGIN ATOMIC            declare v_pos bigint;            declare v_tmp bigint;            declare v_url varchar(255);         set  v_tmp=(select INSTR(p_nodeUrl,'.aspx') from SYSIBM.SYSDUMMY1);if v_tmp>0 then set v_url = p_nodeUrl;else --CreateUrl+??Url = ??Url ---??CreateUrl??Node??????(?,&) set v_pos=(select INSTR(p_createUrl,'Node=')  from  SYSIBM.SYSDUMMY1);if v_pos>0 then set v_url=SUBSTR(p_createUrl,1,v_pos-2);elseset v_url=p_createUrl;end if;---CreateUrl+??Url??????(?,&)set v_pos=(select INSTR(v_url,'?')  from  SYSIBM.SYSDUMMY1);if v_pos>0 thenset v_url= v_url || '&' || p_nodeUrl;elseset v_url= v_url || '?' || p_nodeUrl;end if;end if;set v_url=v_url || '&'||'FormCode=' || p_FormCode || '&'||'FormType=' || p_FormType;return v_url;end;8. 存储过程CREATE  PROCEDURE PR_INSERT(IN pr_sql  VARCHAR(550)) LANGUAGE SQL    SPECIFIC set_variables  begin    execute immediate  pr_sql;end ;9.参数化查询       var info = new QueryInfo();            info.CustomSQL = "select * from sys_user where user_id=?";            info.Parameters.Add("user_id", 1002);            var dt = Dao.ExcuteDataSet(info).Tables[0];10.load load from c:\AA20170828154805.del of del insert into aa  cmd = cn.CreateCommand();                cmd.CommandType = CommandType.StoredProcedure;                cmd.CommandText = @"load from c:\AA20170828154805.del of del insert into aa";                int ii = cmd.ExecuteNonQuery();11.非空约束alter table tableName alter columnName set not null;12.添加主键alter table tableName add constraint primary key(主键id)13.truncate table命令truncate table tableName immediate;14.reorg table tableName15.db2 数据库没有足够大的临时表空间，新建一个足够大的表空间，解决问题create BUFFERPOOL GFPOOL  SIZE 500 PAGESIZE 32K;CREATE TEMPORARY TABLESPACE TEMP PAGESIZE 32K MANAGED BY DATABASE USING(FILE 'STEALTH2/TEMP02' 128000) EXTENTSIZE 80 bufferpool GFPOOL;16.db2分页语法  select * from (select t.*,rownumber() over (order by id asc) as rowid from sys_office t) a where a.rowid>=1 and a.rowid<=3; 17. 不记录日志清空表alter table TRAN_STRUCTURE activate not logged initially with empty table;18.查看表id和表空间select * from syscat.tables where tableid=385219.日志设置update db cfg for ceshi1 using LOGFILSIZ 125000 20 查看表空间的使用情况 a.创建表空间 CREATE TEMPORARY TABLESPACE TEMP PAGESIZE 32K MANAGED BY DATABASE USING(FILE 'STEALTH2/TEMP02' 128000) EXTENTSIZE 80 bufferpool GFPOOL; b.list tablespace containers for 1 show detail c.扩展表空间 alter  TABLESPACE TEMP add (file '/home/db2inst1/db2inst1/NODE0000/CESHI1/004' 500m)20.不记录事务日志alter table     TRAN_TRANSACTION  --, RSK_BOOK_TRAN_SIMULATION, RSK_LEASE_TRAN_ADJUST, RSK_LEASE_TRANSACTOIN, RSK_TRANSACTION_LOG activate not logged initially;  21. 修改数据类型alter table  ETL_VERIFY_CHECK alter column IS_NOT_NULL set data type int ;reorg table ETL_VERIFY_CHECK;