db2部署与数据仓库应用

• 概念特性
• 安装
• 基础命令
  • 连接
  • 监控
• 存储过程

####数据合并 Merge Into是增量备份 ####结果集分组 row_number() OVER (PARTITION BY COL1 ORDER BY COL2) ``` seletc row_number() over(partition by log_user_id order by id desc) as tmpid, o.* from ope_match_his_log o ``` 表示根据COL1分组，在分组内部根据 COL2排序

tmpid	id	log_user_id
1	5616	111122222
2	5613	111122222
3	5611	111122222
1	5681	345678900
2	5654	345678900

syscat.routines

存储所有的存储过程和用户自定义函数（UDF）信息
sysproc

call

调用存储过程

分区特性

DISTRIBUTE BY HASH
原则上，尽量将大表分布在所有的分区上，提高并行处理能力；将小表放置在尽量少的分区上，一般是建议放在单一分区上；尽量减少分区间的通信

create or replace procedure

load from insert into PARTITIONED

专用寄存器（special register）

专用寄存器是 DBA 定义的一个存储块，供一个应用程序过程使用，寄存器中的值可以在 SQL 语句或 SQL PL 语句中访问和引用
最常用的专用寄存器有：

• CURRENT DATE
• CURRENT TIME
• CURRENT TIMESTAMP
• CURRENT USER
• CURRENT PATH

###多节点多分区数据库配置的几个要点 ####节点认证文件 ```bash [root@sjck-node00 ~]# cat /etc/hosts.equiv sjck-node00 sjck sjck-node01 sjck ``` ####实例用户的实例目录下添加.rhosts文件 ```bash [sjck@sjck-node00 sjck]# cat .rhosts sjck-node00 sjck sjck-node01 sjck ``` ####节点文件 ```bash [root@sjck-node00 sqllib]# cat db2nodes.cfg 0 sjck-node00 0 1 sjck-node00 1 2 sjck-node01 0 3 sjck-node01 1 ``` ####启动 ```bash [sjck@sjck-node00 root]$ db2start 01/20/2019 17:32:01 0 0 SQL1063N DB2START processing was successful. 01/20/2019 17:32:01 1 0 SQL1063N DB2START processing was successful. 01/20/2019 17:32:08 3 0 SQL1063N DB2START processing was successful. 01/20/2019 17:32:09 2 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. ``` ####连接数据库 ``` [sjck@sjck-node00 ~]$ db2 connect to sjck

Database Connection Information

Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = SJCK
Local database alias = SJCK

<span id="base">
####连接db2
su sjck   
db2 connect to sjckdb user sjck using sjck
db2
####退出db2
quit
####显示所有数据库
db2 list db directory 
####显示所有表名
db2 list tables
####列出当前数据库下所有的表
db2 list tables for all   
db2 list tables for schema sjck
####查看表结构
db2 describe table sjck.ccrd_card
####查看当前数据库表空间分配状况
db2 list tablespaces show detail
####查看数据库配置参数
db2 get dbm cfg
####查看当前连接到数据库的应用 
db2 list application
####查询分页
db2 "select * from sjck.CORE_BHFMCMRM FETCH FIRST 501 ROWS ONLY"

<span id="server">

<span id="process">
####存储过程
增量表和历史拉链表，生成新的拉链表     

```bash
create or replace procedure tbbak.CREDITOR_ACCREDIT_LIMIT_to_his_proc(in dt_etl varchar(8))   
begin     
declare etldt date;     
declare sql varchar(1000);     
declare ct integer;     
declare del_ct integer;     
set etldt = to_date(dt_etl,'YYYYMMDD');   
select count(1) into del_ct from tbbak.XDZX_CREDITOR_ACCREDIT_LIMIT_his where bg_dt_test=etldt;   
if del_ct > 0 then  delete from tbbak.XDZX_CREDITOR_ACCREDIT_LIMIT_his where bg_dt_test>=etldt;   
update tbbak.XDZX_CREDITOR_ACCREDIT_LIMIT_his set ed_dt_test = '9999-12-31' 
where ed_dt_test >= etldt - 1 days and ed_dt_test < '9999-12-31'and etl_flag <> 'D';   
commit;   
end if;   
select count(1) into ct from tmp.XDZX_CREDITOR_ACCREDIT_LIMIT;    
 if ct > 0 then   
merge into  tbbak.XDZX_CREDITOR_ACCREDIT_LIMIT_his a   
using tmp.XDZX_CREDITOR_ACCREDIT_LIMIT b on a.ID=b.ID and    a.ed_dt_test='9999-12-31' when MATCHED then update set a.ed_dt_test=etldt -1 DAYS;   
commit;   
insert into tbbak.XDZX_CREDITOR_ACCREDIT_LIMIT_his select etldt, '9999-12-31', a.* from tmp.XDZX_CREDITOR_ACCREDIT_LIMIT a;   
commit;   
update tbbak.XDZX_CREDITOR_ACCREDIT_LIMIT_his set ed_dt_test = etldt where etl_flag = 'D' and bg_dt_test = etldt;   
commit;   
end if ;   
end