DB2数据库

创建表

create table sample.upps_pay_transdtl_epcc(  --都有not null 但是ddl语句已经miss
payserno int,
paydate varchar(8),
paytime varchar(8),
amount varchar(100),
payername varchar(100),
payeracctno varchar(100),
payeraccttype varchar(100),
payeename varchar(100),
payeeacctno varchar(100),
payeeaccttype varchar(100),
pathprocstatus varchar(1),
pathprocmsg varchar(100),
coreprocstatus varchar(1),
coreprocmsg varchar(100),
trxstatus varchar(1),
trxmsg varchar(100),
payorganno varchar(100),
payorganname varchar(100)
)
comment  on  table sample.upps_pay_transdtl_epcc is  '登记簿';
comment  on  column sample.upps_pay_transdtl_epcc.payserno   is  '流水号';
comment  on  column sample.upps_pay_transdtl_epcc.paydate   is  '支付日期';
comment  on  column sample.upps_pay_transdtl_epcc.paytime   is  '支付时间';
comment  on  column sample.upps_pay_transdtl_epcc.amount   is  '交易金额';
comment  on  column sample.upps_pay_transdtl_epcc.payername   is  '付款人名称';
comment  on  column sample.upps_pay_transdtl_epcc.payeracctno   is  '付款人账户账号';
comment  on  column sample.upps_pay_transdtl_epcc.payeraccttype   is  '付款人账户类型';
comment  on  column sample.upps_pay_transdtl_epcc.payeename   is  '收款人名称';
comment  on  column sample.upps_pay_transdtl_epcc.payeeacctno   is  '收款人账户账号';
comment  on  column sample.upps_pay_transdtl_epcc.payeeaccttype   is  '收款人账户类型';
comment  on  column sample.upps_pay_transdtl_epcc.pathprocstatus   is  '通道状态';
comment  on  column sample.upps_pay_transdtl_epcc.pathprocmsg   is  '通道描述';
comment  on  column sample.upps_pay_transdtl_epcc.coreprocstatus   is  '核心状态';
comment  on  column sample.upps_pay_transdtl_epcc.coreprocmsg   is  '核心描述';
comment  on  column sample.upps_pay_transdtl_epcc.trxstatus   is  '业务状态';
comment  on  column sample.upps_pay_transdtl_epcc.trxmsg   is  '业务描述';
comment  on  column sample.upps_pay_transdtl_epcc.payorganno   is  '机构号';
comment  on  column sample.upps_pay_transdtl_epcc.payorganname   is  '机构名称';

创建索引

	create index sample.UPPS_PAY_TRANSDTL_EPCC_5 on 
	sample.UPPS_PAY_TRANSDTL_EPCC
	(paydate)
	collect sampled detailed statistics compress no allow reverse scans;
	
	聚合索引
	create index sample.UPPS_PAY_TRANSDTL_EPCC_3 on 
	sample.UPPS_PAY_TRANSDTL_EPCC
	(paydate,payserno,payorganno)
	collect sampled detailed statistics compress no allow reverse scans;

创建序列

create sequence paySerNo start with 1 increment by 1 minvalue 1 maxvalue 999999999

使用序列

next value for  paySerNo

删除序列

drop sequence serNo

创建存储过程

create or REPLACE procedure db2Test_insert(
   IN IN_PAYDATE VARCHAR(8),
   IN IN_INSERT_NUMBER int
) language sql
BEGIN
declare V_PAYDATE VARCHAR(8);
declare V_number int;
declare V_i int;
set V_PAYDATE = IN_PAYDATE;
set V_i = 1;
set V_number = IN_INSERT_NUMBER;
--if V_i <=  V_number then 
 WHILE V_i <= V_number DO INSERT
   INTO sample.UPPS_PAY_TRANSDTL_EPCC(
       payserno ,
       paydate ,
       paytime ,
       amount ,
       payername ,
       payeracctno ,
       payeraccttype ,
       payeename ,
       payeeacctno ,
       payeeaccttype ,
       pathprocstatus ,
       pathprocmsg ,
       coreprocstatus ,
       coreprocmsg ,
       trxstatus ,
       trxmsg ,
       payorganno ,
       payorganname
   ) values(
       next value for sample.paySerNo ,
       IN_PAYDATE ,
       '215100' ,
       '50000' ,
       '付款人-胡波涛' ,
       '付款账号-6228483950229' ,
       '付款类别-借记卡' ,
       '付款人-支付宝' ,
       '付款账号-支付宝的备付金账号' ,
       '付款类别-借记卡' ,
       '1' ,
       '通道成功' ,
       '1' ,
       '核心成功' ,
       '1' ,
       '业务成功' ,
       '机构号-Z2440000035' ,
       '支付宝'
   );
commit;
set V_i = V_i + 1;
--end if;

 end WHILE;
END

使用存储过程

call sample.db2Test_insert('20190103',100000)

删除存储过程

drop procedure "db2Test_insert"

创建视图

create view sample.v_transdtl_pecc
	(paydate1,paytime1,payoganno1)
	as select paydate,paytime,payorganno from sample.upps_pay_transdtl_epcc 
	where paydate='20190121'

使用视图

select * from sample.v_transdtl_pecc

创建大批量的表数据之后需要做：

-- reorg表
CALL SYSPROC.ADMIN_CMD('REORG TABLE SAMPLE.UPPS_PAY_TRANSDTL_EPCC');
-- runstats 表
CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE SAMPLE.UPPS_PAY_TRANSDTL_EPCC ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL');

刚才用存储过程新增了五百万的数据，这个时候需要reorg表了。然后我想看一下reorg前和reorg后执行sql的时间变化。

reorg前：
  select paydate,count(*) from sample.UPPS_PAY_TRANSDTL_EPCC group by paydate  
  --查看执行计划：总成本：30,267.756  
  --11 已获取行 - 52921毫秒  
reorg后：
   select paydate,count(*) from sample.UPPS_PAY_TRANSDTL_EPCC group by paydate   
   --查看执行计划：总成本：30,267.756 （执行计划不受reorg影响）
   -- 11 已获取行 - 5290毫秒  
   然后执行runstate
      select paydate,count(*) from sample.UPPS_PAY_TRANSDTL_EPCC group by paydate   
   --查看执行计划：总成本：21,972.877（执行计划受runstate影响）
   --11 已获取行 - 5459毫秒(可是执行计划降低之后，执行时间并没有减少。。这是为什么？120190126 
   10点09分)

结论：reorg确实能大大提升查询效率。

db2 的一些命令 :（待研究。20190121-23点07分）
db2top命令
db2advis 命令
db2expln命令
db2reorg命令
db2 runstatus命令