DSXchange

Posted: **Tue Jul 21, 2009 10:30 pm**

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

Posted: **Tue Jul 21, 2009 10:42 pm**

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.

Posted: **Wed Jul 22, 2009 12:12 am**

If you compile all jobs in a category then same as reset. If what you want is to make sure all sequences run from start to finish.

It is pretty easy to take all the jobs in the select statement and reset them. You can do this in job control.

Posted: **Wed Jul 22, 2009 1:33 am**

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:

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

Then a server "wrapper" job:

Code: Select all

Universe --> xfm --> Seq File (for debugging)

The Universe SQL is:

Code: Select all

SELECT NAME, CATEGORY FROM DS_JOBS WHERE NAME NOT LIKE '\%' AND CATEGORY LIKE '#pCategory#%' AND NAME LIKE '#pJobName#%'

The xfm staging variable ReturnCode:

Code: Select all

If Jobs_List.NAME <> DSJobName Then ResetJob(Jobs_List.NAME) Else 0

(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

Posted: **Wed Jul 22, 2009 6:13 am**

Your question on "is there a better way" has a simple answer: no, not really.

Posted: **Wed Jul 22, 2009 5:24 pm**

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.

As an FYI in case someone finds this on a search: This is my final Universe SQL code:

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#%';

The job parameters allow filtering of the job list. "View <Universe Stage Data link name>" from Designer allows testing of the job parameters before running the job.

(P.S.: We keep all our test/throwaway/junk jobs in a category ZZ_<suffix>)

Posted: **Wed Jul 22, 2009 8:30 pm**

Nice.

For any "pick list" type parameters, like would help with "JobType", suggest you create that as a List parameter type. You can set the default to "none" to force a choice at runtime.

DSXchange

Resetting all sequences in a category

Resetting all sequences in a category