Alok
Why is your job aborting? Resetting a job takes time. It does not come back in a controlled way. At least I cannot control it when I do it because if you try to run a job right after resetting it then sometimes the reset is not done and it will not run the job. You have to wait on the reset and give it a few seconds extra.
If your job is failing because a file has not shown up or some other event which you could look for before running your job then change how you kick off this job. There are a lot cleaner ways to solve this problem. What is your trigger mechanism? You can even loop on a record set and wait for records to show up before starting your job. This is easy to setup. Tell us what you need. I am sure someone has done it. If you are on UNIX then shell scripts make great triggers to start jobs.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Running a job cyclically till it fails
Moderators: chulett, rschirm, roy
Alok
Are you waiting on all values to become "Y" before you start the sequence? Give us exactly what you are doing.
Here is my guess in shell:
while :
do
RESULT=`sqlplus "select count(MyField) from MyTable where MyField 'Y';"`
if [ $RESULT = 0 ]
then
dsjob ... start MySequence
exit 0
else
sleep 30
fi
done
The "while :" is a while forever. You need to fill in the exact syntax for dsjob. You may need to put the SQL in a file. If you are not using Oracle then you need something other than sqlplus. I can give you a BASIC version using ODBC if you want.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Are you waiting on all values to become "Y" before you start the sequence? Give us exactly what you are doing.
Here is my guess in shell:
while :
do
RESULT=`sqlplus "select count(MyField) from MyTable where MyField 'Y';"`
if [ $RESULT = 0 ]
then
dsjob ... start MySequence
exit 0
else
sleep 30
fi
done
The "while :" is a while forever. You need to fill in the exact syntax for dsjob. You may need to put the SQL in a file. If you are not using Oracle then you need something other than sqlplus. I can give you a BASIC version using ODBC if you want.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Alok
Does that work? You cannot run the job without a reset after a log fatal or can you?
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Does that work? You cannot run the job without a reset after a log fatal or can you?
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Alok
I'm wondering what your job looks like; you're talking about one or more tables where a particular field for all rows has to be 'Y' before you can continue. I assume that you get them all togther using a union statement, or like Ray suggested in your previous topic, a select distinct:
select distinct field1 from table1
union
select distinct field2 from table2
union
select distinct field3 from table3
and so on, depending on how many tables you have to check, and provided, of course, that all the queried fields are of the same data type. But doing it like that, you would have all distinct values of the field in the stream input. In the following transformer, you could define 2 output links, one for the records where field is 'Y', the other where field'Y' or field is null. You would have to direct the records using a constraint.
You could output to a sequential file stage, with the system trash file (/dev/null if you're on Unix) as targets in both cases. After you're job has completed (you wouldn't have to abort it), you can query, if there were records output to the link where the 'N's or nulls go, using the function DSGetLinkInfo. If yes wait a while and try again, otherwise kick off the load. You're code in the controlling job could look something like this:
Loop
hJob1 = DSAttachJob("yesnosuccfail", DSJ.ERRFATAL)
If NOT(hJob1) Then
Call DSLogFatal("Job Attach Failed: yesnosuccfail", "JobControl")
Abort
End
ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL)
ErrCode = DSWaitForJob(hJob1)
Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
If Status = DSJS.RUNFAILED Then
* Fatal Error - No Return
Call DSLogFatal("Job Failed: yesnosuccfail", "JobControl")
End
Check=DSGetLinkInfo(hJob1, ActiveStageName, LinkName, DSJ.LINKROWCOUNT)
DSDetachJob(hJob1)
* wait 5 minutes
sleep 300
Until Not(Check)
Repeat
You might want to query this forum for topics which critically discuss the usage of the DSJ.ERRFATAL option when attaching to a job or whether it is sensible to simply 'Abort' a controlling job[:)].
Kim,
when resetting a job, do you use DSPrepareJob(hJob)? I never noticed that this function returns control to the caller before the job, that hJob points to, isn't in a runnable state[?]?
Stephan
I'm wondering what your job looks like; you're talking about one or more tables where a particular field for all rows has to be 'Y' before you can continue. I assume that you get them all togther using a union statement, or like Ray suggested in your previous topic, a select distinct:
select distinct field1 from table1
union
select distinct field2 from table2
union
select distinct field3 from table3
and so on, depending on how many tables you have to check, and provided, of course, that all the queried fields are of the same data type. But doing it like that, you would have all distinct values of the field in the stream input. In the following transformer, you could define 2 output links, one for the records where field is 'Y', the other where field'Y' or field is null. You would have to direct the records using a constraint.
You could output to a sequential file stage, with the system trash file (/dev/null if you're on Unix) as targets in both cases. After you're job has completed (you wouldn't have to abort it), you can query, if there were records output to the link where the 'N's or nulls go, using the function DSGetLinkInfo. If yes wait a while and try again, otherwise kick off the load. You're code in the controlling job could look something like this:
Loop
hJob1 = DSAttachJob("yesnosuccfail", DSJ.ERRFATAL)
If NOT(hJob1) Then
Call DSLogFatal("Job Attach Failed: yesnosuccfail", "JobControl")
Abort
End
ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL)
ErrCode = DSWaitForJob(hJob1)
Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
If Status = DSJS.RUNFAILED Then
* Fatal Error - No Return
Call DSLogFatal("Job Failed: yesnosuccfail", "JobControl")
End
Check=DSGetLinkInfo(hJob1, ActiveStageName, LinkName, DSJ.LINKROWCOUNT)
DSDetachJob(hJob1)
* wait 5 minutes
sleep 300
Until Not(Check)
Repeat
You might want to query this forum for topics which critically discuss the usage of the DSJ.ERRFATAL option when attaching to a job or whether it is sensible to simply 'Abort' a controlling job[:)].
Kim,
when resetting a job, do you use DSPrepareJob(hJob)? I never noticed that this function returns control to the caller before the job, that hJob points to, isn't in a runnable state[?]?
Stephan
Stephan
I always execute it at TCL. I never had problem like this.
DSD.RUN Job -2
Will reset a job. I think the dsjob command can also reset. I had a job abort then I always had a serious problem which meant everything was shutdown until it was fixed. I never thought about aborting on purpose. This website brings out the most unusual solutions.
Alok, I think you are going against the design of the product. This is going to make you life difficult.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I always execute it at TCL. I never had problem like this.
DSD.RUN Job -2
Will reset a job. I think the dsjob command can also reset. I had a job abort then I always had a serious problem which meant everything was shutdown until it was fixed. I never thought about aborting on purpose. This website brings out the most unusual solutions.
Alok, I think you are going against the design of the product. This is going to make you life difficult.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Hi,
you can use user status and not abort the job.
if you still want to abort the job then you must reset it in order for it to run again,
so the thing you are missing is probably the, not well documented, DSPrepareJob():
hJob = DSAttachJob("x")
If Not(hJob) Then
...
End
hJob = DSPrepareJob(hJob)
If Not(hJob) Then
...
End
The DSPrepareJob() will reset the job for you if needed like the option you have in sequence jobs to reset if needed then run.
In my humble opinion, there is no need for the way you use the subroutines, I would use a simple loop.
Good Luck,
Roy R.
you can use user status and not abort the job.
if you still want to abort the job then you must reset it in order for it to run again,
so the thing you are missing is probably the, not well documented, DSPrepareJob():
hJob = DSAttachJob("x")
If Not(hJob) Then
...
End
hJob = DSPrepareJob(hJob)
If Not(hJob) Then
...
End
The DSPrepareJob() will reset the job for you if needed like the option you have in sequence jobs to reset if needed then run.
In my humble opinion, there is no need for the way you use the subroutines, I would use a simple loop.
Good Luck,
Roy R.
Alok
What part does not work? I don't think we want to do your job for you. We do not mind helping you figure out a syntax problem or conceptually how to solve a problem. I think this is a good approach. You could have a DataStage job do the exact thing and write it to a hash file or a sequential file and read the result from there. Force the key to be a specific value like "CountOfN". Learning how to call ODBC from BASIC is a lot more difficult than reading a hash file.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
What part does not work? I don't think we want to do your job for you. We do not mind helping you figure out a syntax problem or conceptually how to solve a problem. I think this is a good approach. You could have a DataStage job do the exact thing and write it to a hash file or a sequential file and read the result from there. Force the key to be a specific value like "CountOfN". Learning how to call ODBC from BASIC is a lot more difficult than reading a hash file.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com