DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 23, 2004 10:52 am Reply with quote    Back to top    

Before I hurt myself, I thought I would ask the experts....

I would like to create a job that looks at a specific category in a project and will send an email if there are any jobs that are not in run-able order. Is there an easy way to do this?

Detail:
I looked at DSGetProjectInfo and can get a list of all jobs in a project, but category information is not given. I then looked at DSGetJobInfo, it gives all the information I need (such as DSJ.JOBSTATUS), but it also does not seem to give the category information. So, I could get the job status info for all jobs in a project (by looping through the DSGetProjectInfo list of jobs, attaching each one, and getting the job status). But is there a way to limit this to only one category in a project?

Thanks,

Scott



_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Tue Mar 23, 2004 1:52 pm Reply with quote    Back to top    

Not sure if you can do this with just the functions within DataStage, but you can easily get a list of the job names in a category onto a Select List (= list of keys in memory) and process that list as follows:

Command = "SELECT DS_JOBS WITH CATEGORY = 'CategoryName' TO 9"
Call DSExecute("UV", Command, Output, Code)
If Selectinfo(9,1)
Then
Loop
While ReadNext JobName
GoSub ProcessJob
Repeat
End

Note that the executed command is NOT SQL, so does not have a trailing semi-colon. If you prefer SQL, the syntax is SELECT JOBNAME TO 9 FROM DS_JOBS WHERE CATEGORY = 'CategoryName';

----- Original Message -----
From: "Scott Brooks"
Date: Tue, 23 Mar 2004 11:52:03 -0500
To:
Subject: Job info for a specific category

> Before I hurt myself, I thought I would ask the experts....
>
> I would like to create a job that looks at a specific category in a project and will send an email if there are any jobs that are not in run-able order. Is there an easy way to do this?
>
> Detail:
> I looked at DSGetProjectInfo and can get a list of all jobs in a project, but category information is not given. I then looked at DSGetJobInfo, it gives all the information I need (such as DSJ.JOBSTATUS), but it also does not seem to give the category information. So, I could get the job status info for all jobs in a project (by looping through the DSGetProjectInfo list of jobs, attaching each one, and getting the job status). But is there a way to limit this to only one category in a project?
>
> Thanks,
>
> Scott
>
>
>
> _______________________________________________
> datastage-users mailing list
> datastage-users@oliver.com
> http://www.oliver.com/mailman/listinfo/datastage-users

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed Mar 24, 2004 7:53 am Reply with quote    Back to top    

Thanks Ray. This looks like just the thing I need.

My only problem is that I can't seem to get the JobName values out of the loop. Bolded are my modifications to your code. The first DSLogInfo gives me:
"Output = 33 record(s) selected to SELECT list #9."
So, I know the query found results. But the loop doesn't produce any results. So, I searched the DataStage archive for similar code and tried some out, but still struck out. I then looked through the helps files... but once again couldn't find help on Selectinfo, etc.
Do you (or anyone on this list) know why I'm not getting the list of jobs? Also, do you know where help is for Selectinfo, etc.?

Thanks,

Scott

>>> rayw@mindless.com 03/23/2004 2:52:38 PM >>>
Not sure if you can do this with just the functions within DataStage, but you can easily get a list of the job names in a category onto a Select List (= list of keys in memory) and process that list as follows:

Command = "SELECT DS_JOBS WITH CATEGORY = 'MyCategory' TO 9"
Call DSExecute("UV", Command, Output, Code)
OutMessage = "Output = " : Output
Call DSLogInfo(OutMessage, "JobControl")
If Selectinfo(9,1)
Then
Loop
While ReadNext JobName
* GoSub ProcessJob
OutMessage = "JobName = " : JobName
Call DSLogInfo(OutMessage, "JobControl")

Repeat
End

Note that the executed command is NOT SQL, so does not have a trailing semi-colon. If you prefer SQL, the syntax is SELECT JOBNAME TO 9 FROM DS_JOBS WHERE CATEGORY = 'CategoryName';


_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Wed Mar 24, 2004 1:46 pm Reply with quote    Back to top    

My error. I put the job names on Select List #9, but neglected to specify this list when processing in the loop. The corrected statement is
ReadNext JobName From 9

Sorry about that, Chief!

----- Original Message -----
From: "Scott Brooks"
Date: Wed, 24 Mar 2004 08:53:12 -0500
To:
Subject: Re: Job info for a specific category

> Thanks Ray. This looks like just the thing I need.
>
> My only problem is that I can't seem to get the JobName values out of the loop. Bolded are my modifications to your code. The first DSLogInfo gives me:
> "Output = 33 record(s) selected to SELECT list #9."
> So, I know the query found results. But the loop doesn't produce any results. So, I searched the DataStage archive for similar code and tried some out, but still struck out. I then looked through the helps files... but once again couldn't find help on Selectinfo, etc.
> Do you (or anyone on this list) know why I'm not getting the list of jobs? Also, do you know where help is for Selectinfo, etc.?
>
> Thanks,
>
> Scott
>
> >>> rayw@mindless.com 03/23/2004 2:52:38 PM >>>
> Not sure if you can do this with just the functions within DataStage, but you can easily get a list of the job names in a category onto a Select List (= list of keys in memory) and process that list as follows:
>
> Command = "SELECT DS_JOBS WITH CATEGORY = 'MyCategory' TO 9"
> Call DSExecute("UV", Command, Output, Code)
> OutMessage = "Output = " : Output
> Call DSLogInfo(OutMessage, "JobControl")
> If Selectinfo(9,1)
> Then
> Loop
> While ReadNext JobName
> * GoSub ProcessJob
> OutMessage = "JobName = " : JobName
> Call DSLogInfo(OutMessage, "JobControl")
>
> Repeat
> End
>
> Note that the executed command is NOT SQL, so does not have a trailing semi-colon. If you prefer SQL, the syntax is SELECT JOBNAME TO 9 FROM DS_JOBS WHERE CATEGORY = 'CategoryName';
>
>
> _______________________________________________
> datastage-users mailing list
> datastage-users@oliver.com
> http://www.oliver.com/mailman/listinfo/datastage-users

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Thu Mar 25, 2004 8:33 am Reply with quote    Back to top    

Thanks Ray. Adding the "From 9" worked like a champ!

Scott

PS Is there help information somewhere that gives what can be queried from Universes as well as information on commands like:
"SELECT DS_JOBS WITH CATEGORY = 'MyCategory' TO 9"
If Selectinfo(9,1)
ReadNext JobName From 9
...... Or is this something that one would learn in a certain advanced DataStage class?

>>> rayw@mindless.com 03/24/2004 2:46:24 PM >>>
My error. I put the job names on Select List #9, but neglected to specify this list when processing in the loop. The corrected statement is
ReadNext JobName From 9

Sorry about that, Chief!


_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
admin
Site Admin



Joined: 12 Jan 2003
Posts: 8720

Points: 10

Post Posted: Fri Mar 26, 2004 2:13 pm Reply with quote    Back to top    

The UniVerse query information can be had from UniVerse manuals, which can be downloaded from IBM's website www-106.ibm.com/software/data/u2
You could as easily have used UniVerse SQL (same site for manuals).
SELECT NAME TO SLIST 9 FROM DS_JOBS WHERE CATEGORY = 'CategoryName' ORDER BY NAME;

The BASIC statements (Loop, ReadNext and so on) are in the DataStage BASIC manual.

----- Original Message -----
From: "Scott Brooks"
Date: Thu, 25 Mar 2004 09:33:40 -0500
To:
Subject: Re: Job info for a specific category

> Thanks Ray. Adding the "From 9" worked like a champ!
>
> Scott
>
> PS Is there help information somewhere that gives what can be queried from Universes as well as information on commands like:
> "SELECT DS_JOBS WITH CATEGORY = 'MyCategory' TO 9"
> If Selectinfo(9,1)
> ReadNext JobName From 9
> ..... Or is this something that one would learn in a certain advanced DataStage class?
>
> >>> rayw@mindless.com 03/24/2004 2:46:24 PM >>>
> My error. I put the job names on Select List #9, but neglected to specify this list when processing in the loop. The corrected statement is
> ReadNext JobName From 9
>
> Sorry about that, Chief!
>
>
> _______________________________________________
> datastage-users mailing list
> datastage-users@oliver.com
> http://www.oliver.com/mailman/listinfo/datastage-users
>

_______________________________________________
datastage-users mailing list
datastage-users@oliver.com
http://www.oliver.com/mailman/listinfo/datastage-users

_________________
PLEASE READ
Do not contact admin unless you have technical support or account questions. Do not send email or Private Messages about discussion topics to ADMIN. Contact the webmaster concerning abusive or offensive posts.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours