Datastage Routine示例---提取JOB错误信息录入到数据库.docVIP

Datastage Routine示例——使用Routine提取JOB错误信息录入数据库 环境：Red Hat Linux AS 4 + Datastage EE 7.5.2 + Oracle 10g ##在此感谢一下金证科技的Li Bin和Wang Jun，是他们带我学习Datastage的！ 目标：使用Routine将JOB运行后的错误信息录入数据库（暂时忽略警告信息） 方案：Server Routine调用Shell脚本，Shell脚本调用存储过程，存储过程把传递的参数信息写入数据库表 实现过程：1.创建Server Routine，命名DSJobLog，系统默认DSJ开头的Routine为内置(Built-in)Routine,类型为Before/After Subroutine 2.编写Routine的Code，代码及注释如下： ***************************************************************************ROUTINE名称：DSJobLog???????????????????????????????????????????????????**ROUTINE类型：Server Subroutine?????????????????????????????????????? **ROUTINE功能：提取JOB运行后的错误日志并写入数据库????**ROUTINE输入参数1：InputArg?????????????????????????????????????????? **ROUTINE返回参数1：ErrorCode?????????????????????????????????????????**ROUTINE作者：wangfei (wangfei04403@)????????**ROUTINE日期?????????????????????????????????????????????? ************************************************************************* $INCLUDE DSINCLUDE JOBCONTROL.H *设定返回值ErrorCode = 0 *设定当前运行的JOBJobHandle = DSJ.ME *设定变量cmdstr,存储shell命令及参数cmdstr= sh /home/dsadm/EDCETL/SH/PARA/etl_log.sh * 如果状态为失败，则产生错误日志 *设定变量LogList并初始化，存储JobLog信息?? LogList = *判断运行subroutine前JOB的执行状态(DSJ.JOBINTERIMSTATUS)*如果产生错误(代码为3)，则将error信息存储于动态数组Summary中(1为INFO,2为WARN,3为FATAL)?? if DSGetJobInfo(JobHandle,DSJ.JOBINTERIMSTATUS) = 3 then????? Summary=DSGetLogSummary(JobHandle , DSJ.LOGFATAL, DSGetJobInfo(JobHandle,DSJ.JOBSTARTTIMESTAMP), ???????????????????????????? DSGetJobInfo(JobHandle,DSJ.JOBLASTTIMESTAMP), 10) *通过循环先取到错误事件的ID(EvtID),然后依据EvtID获得错误信息?? *循环开始??? FOR I=1 TO 10??????? EvtID = Field(SummaryI,\,1)??????? if EvtID = then?????????? exit??????? end??????? ??????? LogList =LogList : DSGetLogEntry(JobHandle ,EvtID):ZhongTu??? NEXT I ??? *循环结束?? end*如果状态为失败，则产生错误日志 *开始拼接shell命令的参数*Char(34)在VB中是双引号，这里必须添加双引号以便Shell脚本能够正确的把带单引号的参数传递给存储过程cmdstr=cmdstr:Char(34) ::DSGetJobInfo(JobHandle,DSJ.JOBNAME)::Char(34): cmdstr=cmdstr:Char(34) ::DSGetJobInfo(JobHandle,DSJ.JOBSTAR