111企业年金基金监督管理系统系统（二期）维护指南(4).docx
中国人寿养老保险股份有限公司China Life Pension Company Limited企业年金基金监督管理系统系统维护指南修订页编号章节名称修订内容简述修订日期修订前版本号修订后版本号修订人核定人1所有章节方案整理2013-0541V1.0张战胜、兰伯、张东辉第一章系统日常检查、每日巡检1.1检查基金监督系统数据库备份情况10.3.229.14:d2期数据库备份1.2估值表挪动情况查看-东辉1、首先要缺表托管行都挪动2、其次查看“错误”、“警告”、“提示”三种提示。其中“错误”一般是文件错误，“警告” 一般是配置错误。select * from tgzb_log where l_type='提示'and t-date-z2013-01-0;.（这个里面日志分三种:警告，提示，错误）13登录基金监督系统，处理待运维业务用admin02登录，产看任务池1.4 检查已挪未导的估值表（执行时修改一下日期）-东辉这里可以直接看tgzb log的错误日志了，巡检有问题就看这个日志就行。his.path字段记录了未挪成功的路径。select obj planName, obj. prtName, obj. custName, a. * from v_obj_mapping obj,(SELECT d. *FROM (SELECTFROMa. *(SELECTo code,TO CHARTO CHAR(TO DATE (beg_date, ' yyyy-mrdd'),，yyyy-un-dd,)beg_date,(TO_DATE (end date, , yyyy-mm-dd,),，yyyy-mm-dd,)end_dateFROM(SELECTFROMtgzb_result where beg_date >= > 201304-101)a,a *(SELECTFROMa.port code, a. beg date AS gzlostdate(SELECT tp.port code, beg dateFROM (SELECTFROMWHEREANDbeg_datev trcide_daysxri skbeg_date >= >2013-04-10,beg_date <TO_CHAR (SYSDATE,，yyyymm-dd,UNIONSELECT begdateFROM lastdayofmonthWHERE begdate >= >2013-04-101AND begdate <T0J3HAR (SYSDATE,'yyyy-mm-dd,)vd,(SELECT opcode AS port_code,DECODE(trans_date,NULL, beg_date,trans_date)AS p dateFROM tprttpWHERE vd. beg date >= tp.p_date) aLEFT JOIN(SELECT port_code, beg_dateFROM (SELECT port code, beg dateFROM tcrp_hldxriskWHERE beg_date >= >2013-04-10,AND beg_date <TO_CHAR (SYSDATE,，yyyymm-dd,UNIONSELECT port_code, beg_dateFROM tcrp_navxriskWHERE beg date >= >2013-04-10,AND beg date <TO_CHAR (SYSDATE,，yyyy-un-dd,GROUP BY port_code, beg date) bON a. port_code = b. port_codeAND a.begdate = b.beg_dateLEFT JOIN v_obj_mapping objON a port_code = obj.prtcodeWHERE a. beg date >= '2013-04-10'-and a. beg date <= 92011-12-3ND ( SUBSTR (obj. prtcode, 2, 2) '07'OR obj. phncode = , T120l2010000000000005,-)AND b. beg,date IS NULL ) a,tprt bWHERE a. port code = b. o codeAND b. p_flag <> 2AND gzlostdate >=DECODE (b. trans_date,NLLL, b. beg date,b.transdate)bWHERE a o_code = b. port_codeAND b. gzlostdate < a end_dateAND b. gzlostdate >= a beg_date) dGROUP BY o code, beg date, end date) awhere obj. prtCode=a. o code1.5 检查组合现金流校验-东辉1、“新增计划组合任务”和“现金流校验”是同时执行的。可以通过TCASH_LOG来查看。select * from tcash_log tl order by tl. savedate desc1.6 检查未维护的现金流-东辉使用下面脚本，可以通过后台查看是否有未维护的现金流：select obj. planName, obj. custName, obj. prtname, a. * from(select *from (select a *,rank() over(partition by port code order by date asc) as seqidfrom (select a *, b. id1、从tcrp_size表中查份额变化数不为0的from (select *from (select port_code,p date,p_size -nvl (lag(p_size, 1)over (partition by port-codeorder by p_date),0) as ch sizefrom tcrp sizexriskwhere (port_code like ' S%' orport_code in(select ocodefrom tprtwhere ISSTPORT = '1')-and end_date'2010-01 -01' -add by xx 20110311)awhere a ch_size <> 0) a一2、左关联tcrp sgsh tzjg(现金流表里的数据)，条件组合代码一样,R期一left join tcr sgsh tzjgxrisk bon a. port_code = b. port_codecind a. p date = b. p date)a一3、查看是否有tcrp_size表中有数据,而tci、p_sgsh_tzjg没有数据,认为是现金流丢失where a. id is null)where seqid >1)aleft join v_obj_mapping objon a. port code=obj. rtcodewhere a. p-date>> 2012-08-01'order by obj. custCode, obj. prtCode, a. p date1.7 更新委托余额begin*删除所有非手工输入的委托余额数据*delete from tcrp_trust_acctinfoxrisk where pipe_id <> T;*重新生成所有委托余额数据*insert into tcrp trust acctinfoxrisk(beg_date,end_date,port_code,trust_amount,currency,impdate,pipe_id)select a. p_date as beg_date,lead(a. date, 1, ' 2050-12一31') over(partition by a. port code order by a. p date asc)as end date,a. port_code,nvl (b. trust_amount, 0) +sum(nvl(a. sg amount, 0) - nvl (a.sh amount, 0) over(partition by a.port_code order bya. p date asc) as t_amount,CNY' as currency,to_char (sysdate, ' yyyy-nm-dd') as imp_date,999 as pipe_idfrom tcrp_sgsh_tzjgxri sk aleft join (select trust amount, beg date, port codefrom tcrp_trus t_acc t i nfoxri skwhere pipe_id = -1) b on a. port_code = b. port_code;*截断所有手工输入的委托余额数据*update tcrp_trust_acctinfoxrisk aset a. end date = (select nvl(min(b. beg date), "2050-12-3)from tcrp trust acctinfoxrisk bwhere a. port_code = b. port_codeand b.pipe_id <> -1)where a. pipe id = 一1;commit;end;1.8 更新风控现金流日期begindelete from timp_chk_instancexrisk where rulecode = 'PRT CASH'insert into timp_chk_instancexrisk(rule_code, id name, port_code, maxdate, basedate)select ' P
