Hi,
Say I have Project --> Jobs --> 00_Sequences category.
What I want is a job that will reset all jobs in that category. I can build this as a separate sequence job, but it's always out of sync as soon as new sequences are added.
In pseudocode:
* List all sequence jobs in 00_Sequences category
* For each job in list
* Reset
* Rinse and repeat
Has anyone written a job to do this? Yes, I'm looking for the silver platter, but am hoping there's a perfectly round wheel already invented.
Otherwise, an approach to use is most welcome.
Thanks,
Scott
Resetting all sequences in a category
Moderators: chulett, rschirm, roy
Resetting all sequences in a category
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues
This post might be helpful. Add CATEGORY into the filter if you need to.
You should be able to build a job to dynamically pull an appropriate list of job names from the repository and then do whatever it is you need to do with them. Perhaps use a custom routine called for each row that takes the found job name, attaches to it and performs a DSPrepareJob. And lastly detaches the assigned handle.
You should be able to build a job to dynamically pull an appropriate list of job names from the repository and then do whatever it is you need to do with them. Perhaps use a custom routine called for each row that takes the found job name, attaches to it and performs a DSPrepareJob. And lastly detaches the assigned handle.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi Craig and Kim,
Craig's post led me to search on DSPrepare job, which led me to viewtopic.php?t=128186&highlight=dspreparejob.
My current approach:
ResetJob routine:
Then a server "wrapper" job:
The Universe SQL is:
The xfm staging variable ReturnCode:
(If there's a better way to keep the wrapper job from attaching to itself let me know, but this works)
The wrapper job has two parameters, pCategory and pJobName, which allow filtering of the jobs list. If blank then all jobs are selected.
I need to tweak the Universe SQL code to return more stuff, such as job type, but searching the forum should give me what I need.
Plus LOTS of great example code in ETLStats :D
Thanks for the help!
Cheers,
Scott
Craig's post led me to search on DSPrepare job, which led me to viewtopic.php?t=128186&highlight=dspreparejob.
My current approach:
ResetJob routine:
Code: Select all
$INCLUDE DSINCLUDE JOBCONTROL.H
JobName = Trim(pJobName)
JobHandle = DSAttachJob(JobName,DSJ.ERRFATAL)
JobHandle = DSPrepareJob(JobHandle)
ErrCode = DSWaitForJob(JobHandle)
ErrCode = DSDetachJob(JobHandle)
Ans = ErrCode
Code: Select all
Universe --> xfm --> Seq File (for debugging)
Code: Select all
SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME NOT LIKE '\%' AND CATEGORY LIKE '#pCategory#%' AND NAME LIKE '#pJobName#%'
Code: Select all
If Jobs_List.NAME <> DSJobName Then ResetJob(Jobs_List.NAME) Else 0
The wrapper job has two parameters, pCategory and pJobName, which allow filtering of the jobs list. If blank then all jobs are selected.
I need to tweak the Universe SQL code to return more stuff, such as job type, but searching the forum should give me what I need.
Plus LOTS of great example code in ETLStats :D
Thanks for the help!
Cheers,
Scott
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues
As an FYI in case someone finds this on a search: This is my final Universe SQL code:sbass1 wrote:I need to tweak the Universe SQL code to return more stuff, such as job type, but searching the forum should give me what I need.
Code: Select all
SELECT EVAL "FIELD('Server|Sequence|Mainframe|Parallel','|',JOBTYPE,1)" AS JOB_TYPE FMT '10L' COL.HDG 'Job Type', CATEGORY, NAME FMT '60T', READONLY COL.HDG 'ReadOnly', OLETYPE COL.HDG 'OLE Type', "DESC" FMT '60T', JOBNO COL.HDG 'Job#' FMT '4R', JOBID FROM DS_JOBS WHERE NAME NOT LIKE '\%' AND CATEGORY NOT LIKE 'ZZ_%' AND CATEGORY LIKE '#pCategory#%' AND NAME LIKE '#pJobName#%' AND JOB_TYPE LIKE '#pJobType#%';
(P.S.: We keep all our test/throwaway/junk jobs in a category ZZ_<suffix>)
Last edited by sbass1 on Thu Jul 23, 2009 1:24 am, edited 1 time in total.
Some people are the Michael Jordan's of Datastage. I'm more like Muggsy Bogues