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