DatastageRoutine示例提取JOB錯(cuò)誤信息錄入到數(shù)據(jù)庫

1、Datastage Routine 示例使用Routine提取JOB錯(cuò)誤信息錄入數(shù)據(jù)庫環(huán)境：Red Hat Linux AS 4 + Datastage EE + Oracle 10g#在此感謝一下金證科技的 Li Bin和 WangJun,是他們帶我學(xué)習(xí)Datastage的!目標(biāo)：使用Routine將JOB運(yùn)行后的錯(cuò)誤信息錄入數(shù)據(jù)庫(暫時(shí)忽略警告信息)方案：Server Routine調(diào)用Shell腳本，Shell腳本調(diào)用存儲(chǔ)過程，存儲(chǔ)過程把傳遞的參數(shù)信息寫入數(shù)據(jù)庫表實(shí)現(xiàn)過程：1. 創(chuàng)建Server Routine，命名DSJobLog系統(tǒng)默認(rèn) DSJ開頭的Routine為內(nèi)置 (Built

2、-in)Routine,類型為 Before/After Subroutine2. 編寫Routine的Code,代碼及注釋如下：*ROUTINE名稱: DSJobLog*ROUTINE類型：Server Subrouti ne*ROUTINE功能：提取JOB運(yùn)行后的錯(cuò)誤日志并寫入數(shù)據(jù)庫*ROUTINE俞入?yún)?shù)1:InputArg *ROUTINE返 回參數(shù) 1:ErrorCode*ROUTINE乍者：wangfei ()*ROUTINE 日期:*'k'k'k'k$INCLUDE DSINCLUDE* 設(shè)定返回值ErrorCode = 0* 設(shè)定當(dāng)前運(yùn)行的 JO

3、BJobHandle =* 設(shè)定變量 cmdstr, 存儲(chǔ) shell 命令及參數(shù) cmdstr=" sh /home/dsadm/EDCETL/SH/PARA/ "* 如果狀態(tài)為失敗，則產(chǎn)生錯(cuò)誤日志* 設(shè)定變量 LogList 并初始化，存儲(chǔ) JobLog 信息LogList =""Summary中 (1 為*判斷運(yùn)行subroutine前JOB的執(zhí)行狀態(tài)* 如果產(chǎn)生錯(cuò)誤 ( 代碼為 3) ，則將 error 信息存儲(chǔ)于動(dòng)態(tài)數(shù)組INFO,2 為 WARN,為 FATAL)if DSGetJobInfo(JobHandle, = 3 thenSummar

4、y=DSGetLogSummary(JobHandle , ,DSGetJobInfo(JobHandle,DSGetJobInfo(JobHandle, 10) * 通過循環(huán)先取到錯(cuò)誤事件的 ID(EvtID), 然后依據(jù) EvtID 獲得錯(cuò)誤信息* 循環(huán)開始FOR I=1 TO 10EvtID = Field(Summary<I>,'',1) if EvtID = "" thenexit endLogList =LogList :DSGetLogEntry(JobHandle ,EvtID):"ZhongTu"NEXT I

5、* 循環(huán)結(jié)束end*如果狀態(tài)為失敗，則產(chǎn)生錯(cuò)誤日志* 開始拼接 shell 命令的參數(shù)*Char(34)在VB中是雙引號，這里必須添加雙引號以便Shell腳本能夠正確的把 帶單引號的參數(shù)傳遞給存儲(chǔ)過程cmdstr=cmdstr:Char(34) :"'":DSGetJobInfo(JobHandle,:"'":Char(34):" " cmdstr=cmdstr:Char(34) :"'":DSGetJobInfo(JobHandle,:"'":Char(34):

6、" " cmdstr=cmdstr:Char(34) :"'":DSGetJobInfo(JobHandle,:"'":Char(34):" " cmdstr=cmdstr:Char(34) :"'":DSGetJobInfo(JobHandle,:"'":Char(34):" " cmdstr=cmdstr:"1":" "cmdstr=cmdstr:Char(34) :"&

7、#39;":LogList:"'":Char(34)結(jié)束拼接shell命令的參數(shù)執(zhí)行Shell腳本Call DSExecute("UNIX", cmdstr, Ans, SystemReturnCode)3. 其他標(biāo)簽頁的內(nèi)容默認(rèn)即可，保存并編譯！4.在Job中調(diào)用該Routine，打開一個(gè)JOB在JOB屬性中選擇Routine即可。General |ccritrQl R即und&nci寺 | NL? E賀口ition眉CategoryBefore-job subroutine;Input Value:| (none |Aft&

8、amp;T-joti subroutine'"OSJobLog-Input Value.Cnfl.y run after-job subrouHne on successfuljob cofnpl&tionE n-able hashed fie cache shaiing ：-Ata Multiple Instdnae系統(tǒng)下編寫Shell腳本，命名，腳本代碼如下:# *#程序名：# 執(zhí)行環(huán)境：/home/dsadm/EDCETL/SH 目錄下#程序描述：調(diào)用存儲(chǔ)過程將JOB日志信息寫到數(shù)據(jù)庫中#輸入?yún)?shù)：#輸出參數(shù)：#編寫人員：wan gfei()#創(chuàng)建日期:# 代碼

9、版本：# *if $# -ne 6 thenexit 2fi#參數(shù)文件、日志文件、臨時(shí)文件存放路徑PARAPATH=/home/dsadm/EDCETL/SH/PARA#參數(shù)文件ETLCFG=$PARAPATH/#獲取控制ETL運(yùn)行的數(shù)據(jù)庫服務(wù)器信息DBNAME=$(awk -F= "DBNAME_CTL/pri nt $2' $ETLCFG)DBUSR=$(awk -F= "DBUSR_CTL/pri nt $2' $ETLCFG)DBPWD=$(awk -F= "DBPWD_CTL/pri nt $2' $ETLCFG)VALUE='sqlplus -s <<ENDset pagesize 0 feedback off verify off heading off