這隻是一種不太好的方法,也許還有更好、更簡便的方法。這種方法要求每次運作Job之前删除已有的日志資訊,否則無法統計出正确的記錄數。當然,在Job跑完之後,可以在shell備份本次Job運作的日志到伺服器磁盤。
DataStage通過分析日志擷取Job插入目标表的記錄數
這隻是一種不太好的方法,也許還有更好、更簡便的方法。這種方法要求每次運作Job之前删除已有的日志資訊,否則無法統計出正确的記錄數。當然,在Job跑完之後,可以在shell備份本次Job運作的日志到伺服器磁盤。
1 日志清理設定
登入Datastage Administrator,選擇對應項目,項目屬性->記錄,勾選“自動清除作業日志”,設定為自動清理上次及以前的日志。
Figure 1 Administrator日志清理設定
2 日志處理
2.1 日志備份(dsjob -logsum)
在shell裡用dsjob調起來Job,Job運作之後,将本次Job的日志備份到磁盤。
$DSHOME/bin/dsjob -logsum $projectName $jobName > $sysLogDir/$jobName.txt
2.2 記錄數分析(grep、awk)
注意到往目标表裡寫記錄的時候會有關鍵字“Number of rows inserted:”或“Number of rows rejected:”,後面跟記錄的數目。考慮到可能有多個節點,是以可以将各個節點的資料都加起來。
#inserted rows
insertedRows=`cat $sysLogDir/$jobName.txt | grep "Number of rows inserted:" | awk -F: '{print $3}' | sed 's/,/''/' | awk '{sum=sum+$1;} END {print sum}'`
#rejected rows
rejectedRows=`cat $sysLogDir/$jobName.txt | grep "Number of rows rejected:" | awk -F: '{print $3}' | sed 's/,/''/' | awk '{sum1=sum1+$1;} END {print sum1}'`
考慮到Job可能沒進數就終止了,需要在接下來做處理。例如為空的話指派0。
if [ ! -n "$insertedRows" ]; then
insertedRows=0
fi
2.3 其他監控資訊
監控可能還要些Job運作起止時間、結束狀态等等,可以一并加上,然後寫入一個日志檔案。
其中起止時間可以在Job運作前後記錄,Job的運作裝可以用dsjob –run –status擷取。
jobsta=$($DSHOME/bin/dsjob -run -mode NORMAL $jobParameters -warn 0 -jobstatus $projectName $jobName 2>&1 | awk -F= '/^Status code/{print $2}')
2.4 Job日志記錄
Job執行完畢之後,将該Job的一些監控資訊寫入日志。
echo $projectName $jobName $jobsta `date +%Y-%m-%d" "%H:%M:%S` $startTime $insertedRows $rejectedRows >> $logdir/job_run_` date +%Y%m%d`.log
3 其他(監控相關)
當所有的Job都執行完之後,可以建立一個Job,将記錄在$logdir/job_run_` date +%Y%m%d`.log中的資料抽取到一個表,用于檢視。
3.1 表設計
//日志表
create table DSLog
(
id INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 1, INCREMENT BY 1) primary key ,
prjName varchar (20),
jobName varchar (50),
state varchar (20),
rDate date ,
startTime time,
endTime time,
insertedRows integer,
rejectedRows integer
)
//日志狀态表
create table DSLogState
(
state varchar (20),
mark varchar (50 ),
des varchar (500)
)
3.2 Job狀态代碼
http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r7/topic/com.ibm.swg.im.iis.ds.cliapi.ref.doc/topics/r_dsvjbref_Error_Codes.html
https://www-304.ibm.com/support/docview.wss?uid=swg21469644
3.3 runJob.sh源碼
#!/bin/bash
########################################
#
# runJob.sh 2012-08-19
# run a job with parameters
#
#######################################
# if the number of input parameters is less than 2,then output the help document and exit
if [ $# -lt 2 ] ; then
cat << HELP
runJob --run a job UASGE: runJob projectName jobName jobParameters
EXAMPLE: runJob dsstage1 DD_Test '-param endDT=20120819'
HELP
exit 0
fi
projectName="$1"
jobName="$2"
jobParameters="$3"
#echo $projectName
#echo $jobName
echo $jobParameters
#exit 0
logdir=/DS/DSLogs #directory to store logs
workdate=`date +%Y%m%d`
sysLogDir=/DS/DSLogs/sysLogsBK/`date +%Y%m%d` #directory to back everyday datastage log.Datastage Administrator is setted to delete the logs before run a job.
#solve the problem of $DSHOME is null
source /mistel/IBM/InformationServer/Server/DSEngine/dsenv
#logdir processing.If log folder not exists,create folder.
if [ -d $logdir ]; then
echo "$logdir is exist,continue..."
else
echo "$logdir is not exist,creating $logdir..."
mkdir -p $logdir
fi
#create datastage logs backup direcotry
if [ ! -d $sysLogDir ]; then
mkdir -p $sysLogDir
fi
#job state processing.If job state is not finished ok,then reset the job
jobsta=$($DSHOME/bin/dsjob -jobinfo $projectName $jobName 2>&1 | awk -F: '/^Job Status/{print $2}')
echo 'last status: ' $jobsta
if [ "$jobsta" == " RUN FAILED (3)" -o "$jobsta" == " STOPPED (97)" ];then
echo "Reset before run job $jobname"
$DSHOME/bin/dsjob -run -mode RESET $projectName $jobName >>${logdir}/job_init_` date +%Y%m%d`.log
sleep 5
fi
#job start run time
startTime=`date +%H:%M:%S`
#run a job
jobsta=$($DSHOME/bin/dsjob -run -mode NORMAL $jobParameters -warn 0 -jobstatus $projectName $jobName 2>&1 | awk -F= '/^Status code/{print $2}')
#backup datastage logs
$DSHOME/bin/dsjob -logsum $projectName $jobName > $sysLogDir/$jobName.txt
#calculate the inserted rows and rejected rows from the back up log file
#inserted rows
insertedRows=`cat $sysLogDir/$jobName.txt | grep "Number of rows inserted:" | awk -F: '{print $3}' | sed 's/,/''/' | awk '{sum=sum+$1;} END {print sum}'`
#rejected rows
rejectedRows=`cat $sysLogDir/$jobName.txt | grep "Number of rows rejected:" | awk -F: '{print $3}' | sed 's/,/''/' | awk '{sum1=sum1+$1;} END {print sum1}'`
if [ ! -n "$insertedRows" ]; then
insertedRows=0
fi
if [ ! -n "$rejectedRows" ]; then
rejectedRows=0
fi
echo 'this run status code [1:Finished;2:Finished (see log);3:Aborted;97:Stopped] : ' $jobsta
#log
echo $projectName $jobName $jobsta `date +%Y-%m-%d" "%H:%M:%S` $startTime $insertedRows $rejectedRows >> $logdir/job_run_` date +%Y%m%d`.log