Issue/Introduction:
Error Message :
N/A
After upgrading to v9.1.1, staged Process Flows/Jobs a stay in a status of DATE PENDING past the “Start Date”.
The jobs do not run until the final staged Job’s “Start Date” has passed, at which time all jobs execute at once.
Cause:
Cause type:
Defect
Root Cause: Staged jobs or process flows stay in Date Pending status past their eligible Start Date. When submitting a batch of staged jobs, they will not execute until the last job is eligible to run. This occurred after an upgrade from 9.1.0 to 9.1.1.
Environment:
OS Version: N/A
Resolution:
Update to a fix version listed below or a newer version if available.
Fix Status: Released
Fix Version(s):
Application Manager 9.1.3 - Available
Additional Information:
Workaround :
- Create a sql script in the $AW_HOME/sql directory named “reset_date_pending.sql” on the master which contains the following:
REM Temporary Workaround script which selects all date pending jobids
REM that are past their start date.
REM Note this does not reset the jobs, only populates a spool output list
REM Use in conjunction with a COMPLETION script to reset jobs.
REM
set verify off
set feedback off
set term off
set heading off
set echo off
spool &1
select so_jobid from so_job_queue where so_status = 5 and so_start_date < sysdate;
spool off
- Next create a completion script in in the $AW_HOME/exec directory on the master called “COMPLETION.RESET_DATE_PENDING” which contains the following:
#Temporary workaround script to reset date pending jobs
#Note this script was tested on linux and may not be optimized for other operating systems
#Script verifies that the sql results returned value (filesize) and then resets jobs
tmpfile=$AW_HOME/out/$file
filesize=`ls -l $tmpfile | awk '{print $5}'`
if [ $filesize < 1 ]; then
echo $tmpfile is empty no jobs to process
else
echo resetting the following jobids
echo `$SQLOPER_HOME/exec/ONELINE $SQLOPER_HOME/sql/test.lst`
arg="awexe aw_quemgr tranmode=R jobids='`$SQLOPER_HOME/exec/ONELINE $tmpfile`' so_user_name=SQLOPER password=s0pass"
eval $arg
fi
*** awexe aw_quemgr tranmode=R jobids='`$SQLOPER_HOME/exec/ONELINE $tmpfile`' so_user_name=SQLOPER password=s0pass
You will want to ensure that the user name and password are changed for a valid user in your env.
If you don't want the password hardcoded/clear text in the Completion script, you can always create an environment variable and pass it to the awexe call.
- Next create a job in Applications Manager named “RESET_DATE_PENDING” which calls the reset_date_pending.sql. Every time the job is run it does a query to find jobs which have so_start_date < sysdate in the job queue. This information is then passed to the completion script which makes the awexe call to reset jobs returned from the sql. You can set the job to run a scheduled which best suites your needs in regards to releasing the date pending jobs.