Restartability mechanisms
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A sequencer doesn't have that option either. The only option available to a sequencer is Any/All.
It may be that you are meaning to use the term "sequence".
A Search of DSXchange will tell you much about the "automatically handle activities that fail" and any gaps then remaining in your knowledge can be answered by reference to the Parallel Job Developer's Guide.
It may be that you are meaning to use the term "sequence".
A Search of DSXchange will tell you much about the "automatically handle activities that fail" and any gaps then remaining in your knowledge can be answered by reference to the Parallel Job Developer's Guide.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Can we accompilsh the below start of restartability?
For example one of my load job(which is loaded into oracle table) got aborted after 2000 records loaded.
In the restart i would like to load from 2001st records.. i dont want to load that already loaded 2000 records.
How to achieve this?
Whether those 2000 records will be loaded (got commited) even though the job aborts?
Thanks
For example one of my load job(which is loaded into oracle table) got aborted after 2000 records loaded.
In the restart i would like to load from 2001st records.. i dont want to load that already loaded 2000 records.
How to achieve this?
Whether those 2000 records will be loaded (got commited) even though the job aborts?
Thanks
pandeeswaran
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You will have to wait until the next time that U logs in (U is one of our members) or, perhaps, learn that the second person personal pronoun in English is spelled "you".
The design mechanism has to record, or determine on restart, how many records actually (successfully) made it into the target, and use that information in a filter of some kind during the restart. There are at least five ways to accomplish the recording; the most usual is identifying columns for the load job in the target table(s).
The design mechanism has to record, or determine on restart, how many records actually (successfully) made it into the target, and use that information in a filter of some kind during the restart. There are at least five ways to accomplish the recording; the most usual is identifying columns for the load job in the target table(s).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
i have tweaked the default runmodel for bringing restartability
But it doesn't work if the sequence is in Stopped/Restartable .
It seems the sequence needs to be reset before run if it's in the state 97.
The script is below:
But it doesn't work if the sequence is in Stopped/Restartable .
It seems the sequence needs to be reset before run if it's in the state 97.
The script is below:
Code: Select all
#!/usr/bin/ksh
###########################################################
# ATTENTION!!!
###########################################################
# This script assumes that the execution environment has been
# set up correctly.
# Failure to do so may result in incorrect execution, delays
# in delivery, or loss of data.
# The parmdefs are retrieved from the Datastage Server, the EDW_PARMS
# file, and the script command line. The parmdefs are stored with
# name as the key, and value as the data.
#
# 1) A call to dsjob is made to retrieve the set of parm names and
# default parmdefs from the Datastage job, and stored.
# 2) The file parmdefs for the specified Project and JS are extracted
# from the EDW_PARMS file, and stored.
# 3) The optional command line parmdefs are loaded.
#
# Any parmdefs with same name (key) will override the previous value
# that was stored.
#
# The precedence is controlled by the order in which the parmdefs are
# read and stored:
#
# Datastage job < EDW_PARMS file < command line
#
# A call to dsjob is made to get the job's status info.
#
# dsjob -run -jobinfo
# <project> <job>
#
# The status code is parsed out.
# If the status code is runnable, continue, else if the
# status code is resettable, reset, else return fail code.
#
# dsjob -run -mode RESET
# <project> <job>
#
# If the reset succeeds, continue, else return fail code.
#
# A call to dsjob is made to retrieve the set of parmdefs.
# A dsjob command string is created, using the stored
# set of parmdefs.
# The warn parm def is a special parm that will be passed
# to datastage as the -warn parameter rather than as a param.
# If no warn parm def is supplied then the -warn parameter
# will not be passed to datastage.
#
# The dsjob command string is executed
# to start the execution of the Job:
#
# dsjob -run -jobstatus
# -warn value
# -param name=value
# -param name=value
# -param name=value
# ...
# <project> <job>
#
# Set up return codes at top of script
# to make maintainence easier
#
NoParms=2 ; export NoParms
NotRunnable=4 ; export NotRunnable
OK=0 ; export OK
Warnings=1 ; export Warnings
ABORT=3 ; export ABORT
#DSBIN=`cat /.dshome`/bin
DSBIN=/opt/app/dstage/DataStage752/Ascential/DataStage/DSEngine/bin
DSCMDEXE="$DSBIN/dsjob -server :31539 "
ProjectNamesFile=$DSBIN/../../Projects/Project_Names.dat
export DSBIN
export DSCMDEXE
export ProjectNamesFile
#=====================================
# capture the pid of this invocation
THISPID=$$
ScriptPath=`echo $0 | sed -n 's/\(.*\/\)[^/]\{1,\}$/\1/p'`
export ScriptPath
if [ -e "$ScriptPath" ]
then
echo "Script Path is "$ScriptPath
else
ScriptPath=`pwd`
echo "Script Path is Home Directory "$ScriptPath
export ScriptPath
fi
ScriptName=${0##*/}
export ScriptName # this will grab the current name of this script
echo "script name is "$ScriptName
project_type=`echo $ScriptName | cut -d'_' -f1`
export project_type
echo "project name = " $project_type
#jobname=`echo $ScriptName | cut -d'_' -f2 |cut -d'.' -f1`
jobname=`echo ${ScriptName%.*} | cut -d'_' -f2`
echo "JOBNAME = "$jobname
# Added by Siva Tallapaneni on 2005-08-23
export jobname
# and ensure that the usage message remains correct
project=$project_type
export project
DateStarted=`date '+%Y%m%d%H:%M:%S'`
export DateStarted
#=====================================
# Source DSENV to set up environment
#=====================================
. $DSBIN/../dsenv
#=====================================
# Must declare all our functions first in ksh
#=====================================
#=====================================
function usage # display usage instructions
#=====================================
{
echo "Usage:"
echo "$ScriptName <project> <job> [<name1=value1> <name2=value2> ... ]"
echo "$project_type project - A valid Datastage Project name"
echo "$jobname job - A valid Datastage Job name in Project"
echo " name - A valid parameter name for Project and Job"
echo " value - A value to be assigned to parameter 'name'"
}
#=====================================
function do_log # write timestamp, pid, string to log
#=====================================
# arg1 - a string
{
echo `date '+%Y%m%d %H:%M:%S'` $THISPID $1
}
#=====================================
function in_list # returns 1 if arg1 found in list arg2, else 0
#=====================================
# arg1 - a token
# arg2 - a list of tokens
{
for token in $2
do
if [ "$1" = "$token" ]
then
return 1
fi
done
return 0
}
#=====================================
function get_jobstat # get job's status, return numeric code
#=====================================
# arg1 - project name
# arg2 - jobname
{
cmdstring="$DSCMDEXE -jobinfo $1 $2"
#do_log "get_jobstat: cmd=$cmdstring"
js=`$cmdstring | grep 'Job Status'`
echo "$js"
return 0
}
#=====================================
function jstat_action # return action based on text job status
#=====================================
# arg1 - text job status
{
# parse out the numeric job status
nstat=`echo $1 | sed 's/^.*(\([0-9][0-9]*\)).*$/\1/'`
case $nstat in
1|2|11|12|21|99|3|97)
print "RUN"
;;
13|98)
print "FAIL"
;;
*)
print "FAIL"
;;
esac
}
#=====================================
function get_dslog # read DataStage log for a given job
#=====================================
# arg1 - ProjectName
# arg2 - JobName
{
TEMPFIL1=/tmp/tmp_dstage1.$$
TEMPFIL2=/tmp/tmp_dstage2.$$
TEMPFIL3=/tmp/tmp_dstage3.$$
AbortProject=$1
AbortJob=$2
echo ""
echo "==== Checking datastage log for Project ${AbortProject} Job ${AbortJob}"
echo ""
$DSCMDEXE -logsum ${AbortProject} ${AbortJob} > ${TEMPFIL1}
StartLine=`grep -n STARTED ${TEMPFIL1} | cut -f1 -d: | tail -2 | head -1`
do_log StartLine:$StartLine
TotalLines=`wc -l ${TEMPFIL1} | awk '{print $1}'`
do_log TotalLines:$TotalLines
NumLines=`expr ${TotalLines} - ${StartLine} + 1`
do_log NumLines:$NumLines
tail -${NumLines} ${TEMPFIL1} > ${TEMPFIL2}
cat ${TEMPFIL2}
for AbortJob2 in `cat ${TEMPFIL2} | egrep "\(Aborted\)|\(Finished with warnings\)" | cut -f 4 -d " "`
do
echo ""
echo "==== Checking datastage log for Project ${AbortProject} NESTED Job ${AbortJob2}"
echo ""
$DSCMDEXE -logsum ${AbortProject} ${AbortJob2} > ${TEMPFIL3}
StartLine2=`grep -n STARTED ${TEMPFIL3} | cut -f1 -d: | tail -2 | head -1`
do_log startLine2:$StartLine2
TotalLines2=`wc -l ${TEMPFIL3} | awk '{print $1}'`
do_log TotalLines2:$TotalLines2
NumLines2=`expr ${TotalLines2} - ${StartLine2} + 1`
do_log NumLines2:$NumLines2
tail -${NumLines2} ${TEMPFIL3}
done
rm -f ${TEMPFIL1}
rm -f ${TEMPFIL2}
rm -f ${TEMPFIL3}
}
#===============================================
do_findParm() # Finds the value for a Name=Value pair
#===============================================
{
#
# Arg1 = fully qualified path and file name containing name=value pairs
# Arg2 = Name of parameter to find value for
#
ParameterFile=$1 ; export ParameterFile
ParameterName=$2 ; export ParameterName
if [ $# -lt 2 ]
then
do_log "do_findParm() requires exactly 2 parameters"
ValueOfParm=""
return 1
elif [ -r "$ParameterFile" ]
then
if [ "`egrep "^$ParameterName=" $ParameterFile 2>&1 > /dev/null ; echo $?`" -ne 0 ]
then
#do_log "Parameter: $ParameterName, does not exist in $ParameterFile"
ValueOfParm=""
return 2
#else
# do_log "Found parameter..."
fi
else
#do_log "ParameterFile: $ParameterFile, does not exist"
ValueOfParm=""
return 3
fi
#
# This sed expression grabs all characters after the first = on the lines
# beginning with the ParameterName followed by an =.
#
ValueOfParm=`egrep "^$ParameterName=" $ParameterFile | sed -n 's/^[^=]\{1,\}=\([^$]\{1,\}\)/\1/p'`
}
#================================
# If the job did not complete
# with normal status, retrieve
# the first warning/error
# message from the log
#================================
do_scanLog() # scan the Datastage log files
{
echo "#==============================================="
echo ""
echo ""
echo ""
echo ""
echo "#==============================================="
if [ ${RETCODE:-0} -ne 0 ]
then
FirstLineNumber=`cat $ScriptPath/../log/$jobname.$DateStarted.log | egrep "$[0-9]+[ ]+(FATAL|WARNING)" | head -1 | cut -f 1`
SecondLineNumber=`cat $ScriptPath/../log/$jobname.$DateStarted.log | egrep "$[0-9]+[ ]+(FATAL|WARNING)" | head -2 | tail -1 | cut -f 1`
echo 'sed -n "/^'$FirstLineNumber'/,/^'$SecondLineNumber'/p" $ScriptPath/../log/$jobname.$DateStarted.log | head -2 | tail -1' 2>&1 > $ScriptPath/../log/$jobname."LogContents".sh
LogMessage=$ScriptPath/../log/$jobname."LogContents".sh
export LogMessage
rm -r $ScriptPath/../log/$jobname."LogContents".sh $ScriptPath/../log/$jobname.$DateStarted.log
do_log "LogMessage=$LogMessage"
export FirstLineNumber
export SecondLineNumber
fi
}
#####################################
# #
# First executable line in script #
# #
#####################################
#=====================================
# Get the job's status from the DataStage server
jobstatus="$(get_jobstat $project_type $jobname)"
echo " After Job stat Function"
do_log "Project:$project JobName:$jobname ${jobstatus:-' '}."
#=====================================
# Get action based on the jobstatus
action=$(jstat_action "$jobstatus")
#=====================================
# RESET if needed
if [ "$action" = "RESET" ]
then
do_reset $project $jobname
# wait a bit
sleep 5
# check job status after reset
jobstatus="$(get_jobstat $project $jobname)"
do_log "Project:$project JobName:$jobname ${jobstatus:-' '}."
action=$(jstat_action "$jobstatus")
fi
# If action is still not RUN, then fail
if [ "$action" != "RUN" ]
then
do_log "Cannot run $project $jobname, $jobstatus"
export REW_CONSOLE=Y
exit $NotRunnable
fi
#=====================================
# Get parm names for this project and jobname from the Datastage server
cmdstring="$DSCMDEXE -lparams $project_type $jobname"
$cmdstring | while read parm
do
jobparmlist="$jobparmlist $parm"
done
echo jobparmlist
parmdefs1=$jobparmlist
#=====================================
# gather project level parms
# (and assign to parmdefs)
#=====================================
#
# This will assign to parmdefs all the lines in the format: Name=Value
# from the Project Parameters Files
#
if [ -e "$ScriptPath/../ini/$jobname.ini" ]
then
do_log "Using INI file found in: $ScriptPath/../ini/$jobname.ini"
parmdefs=$( sed -n 's/^\([^=]\{1,\}=[^$]\{1,\}\)/"\1" \\/p' $ScriptPath/../ini/$jobname.ini)
do_log "parmdefs set to: \"$parmdefs\""
if [ -e $parmdefs ]
then
parmdefs1=""
fi
else
do_log "Unable to find INI file in: $ScriptPath/../ini/$jobname.ini for the job $jobname"
status=-1
RETCODE=$"Couldn't find ini file"
export REW_CONSOLE=Y
date_variable=`date +'%b %e %H:%M:%S'`
TECString="$date_variable$TECDelim$THISPID$TECDelim$project_type$TECDelim$jobname$TECDelim$ABORT$TECDelim$TECSeverityCritical3"
do_log "Message for TEC Log Miner:$TECString"
# echo "$TECString" >> `cat /.dshome`/../Projects/TEC.log
exit 1
fi
#=====================================
# Then add on the optional parmdefs that may have been passed in
parmdefs="$parmdefs
#$remaining_parms" # 2004-01-28 dowen
if [ -e $parmdefs1 ]
then
echo continue
parmdefs=$jobparmlist
fi
do_log "parmdefs before validation: \"$parmdefs\""
#=====================================
# Make sure that each parmdef name is valid
echo for parmdef in "$parmdefs" > temp.$$
echo do >> temp.$$
echo 'do_log "Validating: \"$parmdef\""' >> temp.$$
echo "name=\`echo \$parmdef | cut -d"=" -f1 | cut -d'\"' -f2 \`" >> temp.$$
echo 'do_log "Attempting to set parm:\"$name\""' >> temp.$$
echo 'in_list "$name" "$jobparmlist"' >> temp.$$
echo 'found=$?' >> temp.$$
echo 'if [ $found -eq 1 ]' >> temp.$$
echo then >> temp.$$
echo "validdefs=\"\$validdefs '\$(echo \$parmdef | sed 's/ /#Space#/g')'\"">> temp.$$
echo 'do_log "validdefs=$validdefs"' >> temp.$$
echo 'do_log "Parameter \"$parmdef\", set."' >> temp.$$
echo 'elif [ `echo $name|tr [A-Z] [a-z]` = "warn" ]' >> temp.$$
echo then >> temp.$$
echo 'warnparm="-warn `echo $parmdef | cut -d"=" -f2"' >> temp.$$
echo 'do_log "Parameter \"$parmdef\", not found in job."' >> temp.$$
echo else >> temp.$$
echo 'do_log "Parameter \"$parmdef\", not found in job."' >> temp.$$
echo fi >> temp.$$
echo done >> temp.$$
#chmod 777 temp.$$
. ./temp.$$
if [ $? -ne 0 ]
then
do_log "Error building parameter list look in temp.$$ for details"
else
rm -f ./temp.$$
echo me
fi
#return
#=====================================
# Eliminate any duplicate parm definitions.
# (this is ugly, but we must use awk to get the associative arrays
# because the target HP box only has brain dead ksh88)
alldefs=`echo $validdefs |
awk '{count=split($0,foo," +"); # split line into parmdefs
for (i=1;i<=count;i++) # for(;;) insures order of parmdefs
{ # for each parmdef
cnt=split(foo[i],bar,"="); # split into name and value
parm[bar[1]]=bar[2]; # name is key, value is data
for (j=3;j<=cnt;j++) # loop if equal signs found in value
{
parm[bar[1]]=parm[bar[1]]"="bar[j]; # append additional value
}
} # cmdline parmdef overrides file parmdef
}
END { for (k in parm) printf(" -param %s=%s",k,parm[k]);
}'`
do_log "alldefs (before space fix):$alldefs"
alldefs=`echo $alldefs | sed 's/#Space#/ /g'`
do_log "alldefs (after space fix):$alldefs"
export alldefs
#====================================
# Add the complete command string
cmdstring="$DSCMDEXE -run -wait -jobstatus -warn 0 $warnparm $alldefs $project $jobname"
#=====================================
# Execute the command string, capture return code do_log "cmd=$cmdstring"
do_log "Jobrun command:$cmdstring"
echo $cmdstring > temp.$$
CommandResults=`. ./temp.$$ 2>&1 `
dsrc=$?
export CommandResults
rm -f temp.$$
do_log "Result from jobrun command: $CommandResults"
do_log "dsjob rc=$dsrc"
# Evaluate dsrc, to see if the job ran or not
# EG, job could be locked by other user, or
# been run with a bad parameter value.
if [[ $dsrc -gt 0 && $dsrc -lt 100 ]]
then
jobstatus="$(get_jobstat $project $jobname)"
do_log "Project:$project JobName:$jobname ${jobstatus:-' '}."
dsstatus=`echo $jobstatus | sed 's/^.*(\([0-9][0-9]*\)).*$/\1/'`
else
dsstatus=-1
fi
echo "dsrc = $dsrc dssstatus = $dsstatus"
#=====================================
# Evaluate dsstatus, return 0 (OK) or -1 (Fail)
# -1=Did not run,1=OK, 2=Warnings, 3=Abort, other=Abort
if [ $dsstatus -eq -1 ]
then
status=-1
RETCODE=$NotRunnable
elif [ $dsstatus -eq 1 ]
then
status=0
RETCODE=$OK
echo "#==============================================="
echo ""
echo ""
echo ""
echo ""
echo "#==============================================="
do_log "Getting Log contents..."
get_dslog $project $jobname 2>&1 | tee $ScriptPath/../log/$jobname.$DateStarted.log
do_scanLog
elif [ $dsstatus -eq 2 ]
then
status=0
RETCODE=$Warnings
export REW_CONSOLE=Y
echo "#==============================================="
echo ""
echo ""
echo ""
echo ""
echo "#==============================================="
do_log "Getting Log contents..."
get_dslog $project $jobname 2>&1 | tee $ScriptPath/../log/$jobname.$DateStarted.log
do_scanLog
else
status=-1
RETCODE=$ABORT
export REW_CONSOLE=Y
echo "#==============================================="
echo ""
echo ""
echo ""
echo ""
echo "#==============================================="
do_log "Getting Log contents..."
get_dslog $project $jobname 2>&1 | tee $ScriptPath/../log/$jobname.$DateStarted.log
do_scanLog
fi
find $ScriptPath/../log/*.log -mtime +4 -exec rm -f '{}' \;
TEC_RETCODE=$RETCODE ; export TEC_RETCODE
#==================================
# Determine if this job should
# report a completion with warnings
# as a normal completion to the
# calling program
#==================================
do_findParm "$ScriptPath/../ini/Ignore_Warnings.dat" "$project_type"
echo "ValueOfParm = $ValueOfParm"
if [ "$ValueOfParm" = "Yes" ]
do_log "This job is set (in $ScriptPath/../ini/Ignore_Warnings.dat) to ignore warnings for the calling program."
then if [ ${RETCODE:-0} -eq $Warnings ]
then
TEC_RETCODE=$OK
else
TEC_RETCODE=$RETCODE
fi
fi
export TEC_RETCODE
exit $TEC_RETCODE
pandeeswaran
In version 7.x I found the status of "Stopped/Restartable" to be basically a... lie. It isn't restartable. As you've seen, only "Aborted/Restartable" can actually be restarted without Resetting first.
Not sure if that was ever fixed...
Not sure if that was ever fixed...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
This is more of a logical design rather than using the tool to get the job done. Once you extract data from source, check what rows doesn't exist in target and only "insert" new rows.
In first run, when you extract 100 rows from source, and all are new, your code will insert all 100 in target.
In second run, (in case first aborted and you don't know what got inserted), if you check which rows are new in target, (if 20 were inserted out of 100), then your job should pick 80 remaining records that will be treated as new.
Ever heard of "delete > insert/update" logic. In most cases you won't delete any rows since job will finish end to end in first shot but in case job aborts "delete will come into play". You can either "delete" existing records or "Updates" existing rows...This is more of a design question and data scenario.
Basics of DBMS. This is tool independent.
In first run, when you extract 100 rows from source, and all are new, your code will insert all 100 in target.
In second run, (in case first aborted and you don't know what got inserted), if you check which rows are new in target, (if 20 were inserted out of 100), then your job should pick 80 remaining records that will be treated as new.
Ever heard of "delete > insert/update" logic. In most cases you won't delete any rows since job will finish end to end in first shot but in case job aborts "delete will come into play". You can either "delete" existing records or "Updates" existing rows...This is more of a design question and data scenario.
Basics of DBMS. This is tool independent.
Datawarehouse Consultant