温州异常处理


异常分析ß处理

1.先统计应报量

表:xmldb.t_sb_dbxx

--isdbcg 0未登簿或者登簿失败:699 1登簿成功:130   2除了登簿其他操作成功:6
select to_char(xx.dbsj,'yyyy'),xx.isdbcg,count(*) from xmldb.t_sb_dbxx xx where xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null
group by to_char(xx.dbsj,'yyyy'),xx.isdbcg
order by to_char(xx.dbsj,'yyyy');

附记:xmldb.t_sb_dbxx.tszt 统计推送省厅的t_sb_dbl

--tszt  0未推送:1  1已经推送省厅:833 3需要重推:1    这个代表是推送省厅T_sb_dbl表的推送状态
select to_char(xx.dbsj,'yyyy')xx.tszt,count(*) from xmldb.t_sb_dbxx xx where xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null 
group by to_char(xx.dbsj,'yyyy'),xx.tszt
order by to_char(xx.dbsj,'yyyy');

2.统计已经从xmldb.t_sb_dbxx推送到索引表的数据推送状态

处理过的数据

--索引表数据 150条没有推送成功 1条推送成功 
select to_char(xx.dbsj,'yyyy'),sy.sybtszt,count(*) from xmldb.t_sb_syb sy,xmldb.t_sb_dbxx xx where xx.djshbsm=sy.djshbsm and
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null  and sy.receiverbsm=2
group by to_char(xx.dbsj,'yyyy'),sy.sybtszt
order by to_char(xx.dbsj,'yyyy');

没有推送成功的量

-- 索引表推送异常量
select to_char(xx.dbsj,'yyyy'),count(*) from xmldb.t_sb_syb sy,xmldb.t_sb_dbxx xx ,xmldb.log_exception e
where xx.djshbsm=sy.djshbsm and sy.logecpbsm=e.bsm(+) /*and sy.logecpbsm>0 */and sy.sybtszt='0' and
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null  and sy.receiverbsm=2
group by to_char(xx.dbsj,'yyyy')
order by to_char(xx.dbsj,'yyyy');

没有推送成功的明细

--索引表异常明细
select to_char(e.loginfo) as loginfo,sy.pushtype,to_char(xx.dbsj,'yyyy'),xx.* 
from xmldb.t_sb_syb sy,xmldb.t_sb_dbxx xx ,xmldb.log_exception e
where xx.djshbsm=sy.djshbsm and sy.logecpbsm=e.bsm(+) /*and sy.logecpbsm>0 */and sy.sybtszt='0' 
and xx.qxdm='330183' and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null  and sy.receiverbsm=2
order by to_char(xx.dbsj,'yyyy-mm-dd');

数据还在xmldb.t_sb_dbxx,没推送到t_sb_syb的数据量

--还有684条信息没有推送xmldb.t_sb_syb
select to_char(xx.dbsj,'yyyy'),count(*) from xmldb.t_sb_dbxx xx where 
xx.qxdm='330183' and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and xx.djshbsm is not null and xx.qlbsm is not null 
and not exists(
select * from xmldb.t_sb_syb sy where xx.djshbsm=sy.djshbsm  and sy.receiverbsm=2)
group by to_char(xx.dbsj,'yyyy')
order by to_char(xx.dbsj,'yyyy');

3.统计xml数据推送到登记簿/省厅的数据量

3.1省厅receiverbsm=2 、登记簿receiverbsm=1

附记:t_sb_result记录的是推送登记簿和省厅的成功状态

-- 148失败 3成功 推送省厅的    iscg是2的话 代表省厅异步接受xml成功,是否解析入库不知道
--iscg是1的话代表同步接收解析是确定成功入库了的
select to_char(xx.dbsj,'yyyy'),r.iscg,count(*) 
from xmldb.t_sb_dbxx xx,xmldb.t_sb_result r
where xx.djshbsm=r.djshbsm and xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0 and r.receiverbsm=2 
group by  to_char(xx.dbsj,'yyyy'),r.iscg
order by  to_char(xx.dbsj,'yyyy');

推送失败明细

--148条失败的原因的明细  (xml数据包推送登记簿或者省厅)
select xx.qlbm,xx.qlbsm,xx.djshbsm,xx.ywh,xx.lcdm,xx.djlx,xx.qxdm,xx.dbsj,
e.logtype,to_char(substr(e.loginfo,1,3000))/*,count(*)*/
 from xmldb.t_sb_dbxx xx,xmldb.t_sb_result r,xmldb.log_exception e
where xx.djshbsm=r.djshbsm and xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0
and r.receiverbsm=2 and r.iscg=0
and r.logecpbsm=e.bsm(+)
order by  to_char(xx.dbsj,'yyyy-mm-dd');
/*group by xx.qlbm*/
select to_char(xx.dbsj,'yyyy'),count(*)
 from xmldb.t_sb_dbxx xx,xmldb.t_sb_result r,xmldb.log_exception e
where xx.djshbsm=r.djshbsm and xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0
and r.receiverbsm=2 and r.iscg=0
and r.logecpbsm=e.bsm(+)
group by  to_char(xx.dbsj,'yyyy')
order by  to_char(xx.dbsj,'yyyy');

还在t_sb_ywxx的数据量

--684条还在t_sb_ywxx
select to_char(xx.dbsj,'yyyy'),count(*) from xmldb.t_sb_dbxx xx where  xx.qxdm='330183' 
and to_char(xx.dbsj,'yyyy')<='2022' and to_char(xx.dbsj,'yyyy')>='2010'
and xx.qszt>0
and not exists (
select * from xmldb.t_sb_result r where xx.djshbsm=r.djshbsm and r.receiverbsm=2)
group by  to_char(xx.dbsj,'yyyy')
order by  to_char(xx.dbsj,'yyyy');

文章作者: fejxc
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 fejxc !
评论
  目录