DS_JOBOBJECTS

[vinothkumar]

Hi,
I was trying to get familiar with DS_JOBS ans DS_JOBOBJECTS table and I searched some posts in dsxchange. I found the below SQL in one post (viewtopic.php?t=99349)

SELECT
DS_JOBS.NAME AS JOB_NAME,
DS_JOBS.CATEGORY,
DS_JOBOBJECTS.NAME AS OBJECT_NAME,
DS_JOBOBJECTS.OLETYPE,
EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),'PIPPO',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L'
FROM
DS_JOBS,
DS_JOBOBJECTS
WHERE
DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO
and FOUND = 'FOUND'
GROUP BY
JOB_NAME,
DS_JOBS.CATEGORY,
OBJECT_NAME,
DS_JOBOBJECTS.OLETYPE,
FOUND
;
I am trying to execute the SQL through Universe Stage in DataStage job. But the job is getting aborted saying 'Data error'. Do we need to execute this SQL through a different stage or else.

[swarnkar]

Hi,
the job is getting aborted saying 'Data error'. Do we need to execute this SQL through a different stage or else.

Hi,

I think you can run SQL through the Universe stage, but you can also try the same through Administrator client just you have to format the SQL to make it a single line command,
Refer the following command, It is your SQL statement just I have make it a single line command.

Code: Select all

SELECT DS_JOBS.NAME AS JOB_NAME, DS_JOBS.CATEGORY, DS_JOBOBJECTS.NAME AS OBJECT_NAME, DS_JOBOBJECTS.OLETYPE, EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),'PIPPO',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L' FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO and FOUND = 'FOUND' GROUP BY JOB_NAME, DS_JOBS.CATEGORY, OBJECT_NAME, DS_JOBOBJECTS.OLETYPE, FOUND ;

[vinothkumar]

Thanks for your response. I am executing this SQL by making it as a single line only. But still no progress.

[vinothkumar]

To add more... If I execute the same SQL without having "EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),'PIPPO',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L'" , then it is working .

[kduke]

That should work. The syntax is correct. Here is a similar one I use in EtlStats.

Code: Select all

SELECT 
   DS_JOBS.NAME AS JOB_NAME, 
   DS_JOBS.CATEGORY, 
   DS_JOBOBJECTS.NAME AS OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   EVAL DS_JOBOBJECTS."if index(upcase(@RECORD),':1',1) > 0 then 'FOUND' else ''" AS FOUND FMT '5L'
FROM 
   DS_JOBS, 
   DS_JOBOBJECTS 
WHERE 
   DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO 
   and FOUND = 'FOUND'
GROUP BY
   JOB_NAME, 
   DS_JOBS.CATEGORY, 
   OBJECT_NAME, 
   DS_JOBOBJECTS.OLETYPE, 
   FOUND
;

[vinothkumar]

Hi kduke,
Can you please explain the usage of @RECORD in SQL. Because this variable is blocking to make it run through Universe Stage in job. Also can you please confirm that I am in right direction. i.e executing through Universe Stage.

[vinothkumar]

The SQL is working. I used the same SQL given by kduke. Thanks.

[kduke]

@RECORD includes all fields. The EVAL will create a dictionary item on the fly or at run time. Otherwise Universe expects the dictionary item to be created b editting the DICT side of the hashed file. DS_JOBOBJECTS are normal hashed files containing DataStage design objects. You can edit the dictionary of DS_JOBOBJECTS and create fields to report on.

Code: Select all

ED DICT DS_JOBOBJECTS Kim

Will create a field named Kim which I can use in SELECT statements. You can also INSERT into the DICT side of a hashed file. Do a search there are examples of these. Ray does both INSERT and EVAL. EVAL is a little tricky in that it messes up on some GROUP BY statements. Most EVAL statements would be considered I-Descriptors. I-Descriptors are special dictionary items with more complex formulas in them. Most BASIC functions are available in I-Descriptors like FIELD(), TRIM() and so on. This is a very powerful part of extracting information from these tables.