Quick question on querying DS_JOBS table

vivekgadwal · Post by **vivekgadwal** » Mon May 02, 2011 10:04 am

All,

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...

I did:

Code: Select all

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.

pandeesh · Post by **pandeesh** » Mon May 02, 2011 10:16 am

hi what that FMt '60L' do?
i am not aware of that.

Whats the result of the below queries?

1) SELECT * FROM DS_JOBS

2)SELECT * FROM DS_JOBOBJECTS

GJ_Stage · Post by **GJ_Stage** » Mon May 02, 2011 10:18 am

Hi,

Try to do REINDEX.

chulett · Post by **chulett** » Mon May 02, 2011 10:19 am

LOGTO

vivekgadwal · Post by **vivekgadwal** » Mon May 02, 2011 11:14 am

chulett wrote:LOGTO

You are correct Craig. I haven't done LOGTO prior to executing this query...However, even now, I am not getting the result

Code: Select all

>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?

MarkB · Post by **MarkB** » Mon May 02, 2011 1:18 pm

Have you tried:

SELECT NAME FROM DS_JOBS WHERE CATEGORY = '\coverage_performance';

Make sure you are specifying the correctly spelled category - do:

SELECT DISTINCT CATEGORY FROM DS_JOBS;

vivekgadwal · Post by **vivekgadwal** » Mon May 02, 2011 1:40 pm

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.

ray.wurlod · Post by **ray.wurlod** » Mon May 02, 2011 2:09 pm

You might do better with a LIKE query, for example CATEGORY LIKE '%coverage_performance%'.

vivekgadwal · Post by **vivekgadwal** » Mon May 02, 2011 2:15 pm

Thanks Ray.

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"?

ray.wurlod · Post by **ray.wurlod** » Mon May 02, 2011 4:23 pm

Exact means exactly that. In SQL terms, the exact value of a column is its entire contents.

greggknight · Post by **greggknight** » Mon May 02, 2011 4:35 pm

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

Code: Select all

 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")

vivekgadwal · Post by **vivekgadwal** » Mon May 02, 2011 4:53 pm

ray.wurlod wrote:Exact means exactly that. In SQL terms, the exact value of a column is its entire contents.

Pardon me for stating the question in an ambiguous way, Ray. What I meant to ask is that, in the where clause, after the '=' should I be giving:

Code: Select all

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.

chulett · Post by **chulett** » Mon May 02, 2011 5:12 pm

greggknight wrote:I run this code in a batch job.

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.

greggknight · Post by **greggknight** » Mon May 02, 2011 5:35 pm

Ya when he was working for ascential I had a class with him in Texas call building dataware houses with datastage about 9 or 10 years ago

greggknight · Post by **greggknight** » Mon May 02, 2011 6:02 pm

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.