Restartability mechanisms

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Thanks!! i ll try my own script.. :lol: :lol: :lol:
pandeeswaran
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

i have one more query.


Actually there is an option "Reset if required, then run " in sequencer whats the use of that option?

Can anyone give example where we can check that option.

I have never used that one. i have only used the checkpoint so that restartable option.

Thanks..
pandeeswaran
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

A sequencer doesn't have that option. The only option available to a sequencer is Any/All.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Sorry!!

Actually i ama sking about the option, "Automatically handle activities that fail " in jobcontrol of a sequencer..

Can you explain abt that?

thanks
pandeeswaran
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Thanks ray!

Got it!!
pandeeswaran
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

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
pandeeswaran
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You can do this, but you have to design it in to your own jobs - there is no automatic mechanism for keeping track of successfully committed transactions.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
akroy2011
Participant
Posts: 5
Joined: Mon Jul 18, 2011 12:46 pm

Post by akroy2011 »

Can u tell us the logic so that I can try to design my job?
We are extracting from SAP.
How can I design so that job will start extracting data from 1001 records once it aborted after 1000 records.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

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:

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

"doesn't work"?
-craig

"You can never have too many knives" -- Logan Nine Fingers
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Yes. If the sequence state is stopped/restartable ,I can't run the sequence without resetting.in that case if I trigger the script,it says the job should be reset before run .
In that case I am not able to maintain the restart ability.
In the case of aborted/restart able it works fine.
pandeeswaran
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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...
-craig

"You can never have too many knives" -- Logan Nine Fingers
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

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.
Datawarehouse Consultant
Post Reply