导出ddl db2look -d CESHI1 -e -o db.sql -i db2inst1 -w Coqais011 db2look -d GFL -e -o E:db.sql -i db2admin -w 123 系统存储过程 QueryInfo info = new QueryInfo(); info.NamedQuery = "SYSPROC.ADMIN_CMD"; info.Parameters.Add("PR_SQL", "reorg table " + tableName); Dao.ExecuteNonQuery(info); 备份 db2 BACKUP DATABASE dbname 恢复 db2 RESTORE DATABASE dbname a.服务端安装 b.客户端安装 1.建数据库 create database HRA_GF 2.建信道 catalog tcpip node gf remote 192.168.2.252 server 50000 3.1 别名 catalog database HRA_GF as hServer at node gf 3.2 数据库挂在信道上 catalog database HRA_GF at node gf 3.3 terminate 刷新 3.4 db2 4.连接数据库 connect to HRA_GF user db2admin using Hhgf2017 5.删除节点 uncatalog node gf 6. 获取序列下一个值 select nextval for BAS_MODULE_SEQ from sysibm.sysdummy1 7.创建函数结合序列值获取 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.2 CREATE 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); else set v_url=p_createUrl; end if; ---CreateUrl+??Url??????(?,&) set v_pos=(select INSTR(v_url,'?') from SYSIBM.SYSDUMMY1); if v_pos>0 then set v_url= v_url || '&' || p_nodeUrl; else set 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 tableName 15.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=3852 19.日志设置 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; 建库 --重启数据库 FORCE APPLICATION ALL DB2STOP DB2START --创建数据库 CREATE DATABASE GFL USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM USER TABLESPACE MANAGED BY DATABASE USING (FILE 'd:DB2dataGFLsdetbsp' 51200) CONNECT TO GFL --创建缓冲池(使用32k的pagesize) create bufferpool GFPOOL size 12800 pagesize 32K create bufferpool GFPOOL1 size 12800 pagesize 32K --创建表空间并使用32k的pagesize和自定义的缓冲池 CREATE REGULAR TABLESPACE regtbs PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'D:DB2NODE0000GFL egtbs' 2g) bufferpool GFPOOL CREATE REGULAR TABLESPACE idxtbs PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'D:DB2NODE0000GFLidxtbs' 1g) bufferpool GFPOOL CREATE LONG TABLESPACE lobtbs PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'D:DB2NODE0000GFLlobtbs' 1g) bufferpool GFPOOL1 CREATE USER TEMPORARY TABLESPACE sdespace PAGESIZE 32 K MANAGED BY SYSTEM USING ('D:DB2NODE0000GFLsdespace' ) bufferpool GFPOOL1 表空间自增长 alter tablespace lobtbs autoresize yes increasesize 10240K maxsize none; --授权表空间给用户 grant use of tablespace --授权表空间 GRANT USE OF TABLESPACE regtbs TO PUBLIC GRANT USE OF TABLESPACE lobtbs TO PUBLIC GRANT USE OF TABLESPACE idxtbs TO PUBLIC GRANT USE OF TABLESPACE sdespace TO PUBLIC COMMENT ON TABLESPACE sdespace IS '' --优化数据库配置 update db cfg for GFL using APPLHEAPSZ 2048 update db cfg for GFL using APP_CTL_HEAP_SZ 2048 update db cfg for GFL using LOGPRIMARY 10 update db cfg for GFL using LOGFILSIZ 1000 --重启数据库 FORCE APPLICATION ALL DB2STOP FORCE DB2START --授予sde用户DBADM权限 grant DBADM on database to user db2admin --重启数据库 FORCE APPLICATION ALL DB2STOP FORCE DB2START
3.去重
long count = 0;
//删除的时候，保留最老的记录
var delsql1 = $@" delete from
(
select {repetColumns}, row_number() over
(PARTITION by
{repetColumns} order
by
{repetColumns}
) as rn from {importTable} where 1=1 {where}
) t1 where t1.rn > 1";
//删除的时候，保留最新的记录
var delsql2 = $@"DELETE from
(
select {repetColumns}, row_number() over
(PARTITION by {repetColumns}
order by {repetColumns} asc
) as rn from {importTable}
) t1 where rn<
(
select max(rn) from
(
select {repetColumns}, row_number() over
(PARTITION by {repetColumns}
order by {repetColumns} asc
) as rn from {importTable} ) t1
)
";
string sql = style == DeleteStyle.KeepNew ? delsql2 : delsql1;
count = Dao.ExecuteNonQuery(new Framework.QueryInfo() { CustomSQL = sql });
return count;