I need to get a list of all the jobs under a category. I have done this before using "SELECT" on "DS_JOBS" table. However, this time, I am not getting any results...
user@SERVER:/opt/IBM/InformationServer/Server/DSEngine>./bin/uvsh
DataStage Command Language 8.0 Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 1997, 2006 All Rights Reserved.
DSEngine logged on: Monday, May 02, 2011 11:57
>SELECT NAME FMT '60L' FROM DS_JOBS WHERE CATEGORY = 'coverage_performance';
0 records listed.
>PTERM CASE NOINVERT
>SELECT NAME FMT '60L' FROM DS_JOBS;
Job name....................................................
\\NLS
1 records listed.
>
I am 100% sure that there are jobs in the specified category. What is more perplexing to me is, this doesn't display any rows even without the filter condition. I probably am doing something wrong, so please do guide me...
Is there any alternate approach to get this list?
Thanks.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
>LOGTO BI_W1_DS_DW1
>SELECT NAME FROM DS_JOBS WHERE CATEGORY = 'coverage_performance';
0 records listed.
>SELECT NAME FROM DS_JOBS WHERE CATEGORY = "coverage_performance";
DataStage/SQL: syntax error. Unexpected symbol. Token was "coverage_performance".
Scanned command was FROM DS_JOBS SELECT NAME WHERE CATEGORY = "coverage_performance"
>
I tried 2 variations of the WHERE clause literal (one with ' and one with "). What am I doing wrong now?
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Thanks for all your replies. The command I was providing worked now - after connecting to the project. However, I had to log-off and log back in before it worked. I didn't understand why and how it worked now as opposed to before.
So, I will put a note for next time to directly connect to the project and in case I forget to do so, exit out of dssh and log-back in again to do this.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
A General question though is, if I have to do an exact category search, should I give something like, CATEGORY = "\Jobs\coverage_performance"? Or just CATEGORY = "coverage_performance"?
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
I run this code in a batch job. Gives me three different lists in the director job run log.
Folder is a param. The Folder option worked in versions prior to 8.0 have not tryed to figure out the issue in 8.xxx yet , to busy but I still get a list of all the jobs
FailedOpens = ""
tablename = "DS_JOBS"
OPEN tablename TO F.DS_JOBS Else FailedOpens<-1> = tablename
If FailedOpens Then
Call DSLogFatal("The following tables could not be opened: ":FailedOpens, "Msg")
End
TCL = 'SSELECT DS_JOBS'
If Folder # "" Then TCL := ' WITH F3 = "...':Folder:'...."'
EXECUTE TCL CAPTURING OUTPUT
JobList = ""
AllJobsDone = @FALSE
Loop
Readnext JobName Else AllJobsDone = @TRUE
Until AllJobsDone Do
If JobName[1,2] # "\\" Then
Read Row From F.DS_JOBS, JobName Then
JobList<-1> = FMT(JobName, "L#60"):CHAR(9):Row<5>
End
End
Repeat
Call DSLogInfo("JobList by jobname: ":@AM:JobList, "Msg")
TCL = 'SSELECT DS_JOBS BY F5'
If Folder # "" Then TCL := ' WITH F3 = "...':Folder:'...."'
EXECUTE TCL CAPTURING OUTPUT
JobList = ""
AllJobsDone = @FALSE
Loop
Readnext JobName Else AllJobsDone = @TRUE
Until AllJobsDone Do
If JobName[1,2] # "\\" Then
Read Row From F.DS_JOBS, JobName Then
JobList<-1> = FMT(JobName, "L#60"):CHAR(9):Row<5>
End
End
Repeat
Call DSLogInfo("JobList by number: ":@AM:JobList, "Msg")
TCL = 'SSELECT DS_JOBS'
If Folder # "" Then TCL := ' WITH F3 = "...':Folder:'...."'
EXECUTE TCL CAPTURING OUTPUT
JobList = ""
AllJobsDone = @FALSE
Loop
Readnext JobName Else AllJobsDone = @TRUE
Until AllJobsDone Do
If JobName[1,2] # "\\" Then
Read Row From F.DS_JOBS, JobName Then
JobList<-1> = JobName
End
End
Repeat
Call DSLogInfo("JobList no number: ":@AM:JobList, "Msg")
CATEGORY = '\Jobs\parent category\Child Category' (the full path)
or ...
CATEGORY = '\parent category\Child Category'
or just...
CATEGORY = 'Child Category'
to get the jobs list in the Child Category...
When I did CATEGORY = 'Child Category' , it did not work initially. But as I logged off and logged back on again, it seemed to give me the listing. This question is intended to get a better understanding of the Category names in the repository.
Once again, thank you all for your replies. They are really helpful.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
That looks like KBA code to me... and I've always wondered how much of the things like that people have done "under the covers" would still work in the 8.x world.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Right now I am running all my legacy batchs from 5.0 - 6.0 on 8.5 32 bit and 8.5 64 bit and everything runs with out issue.
I had to modify the runbatch routine to handle multiple instances but that was easy enough.
The batch compiler compiles my 2000 + jobs way faster then the gui does.