DS_JOBOBJECTS
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
DS_JOBOBJECTS
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.
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.
Re: DS_JOBOBJECTS
Hi,vinothkumar wrote:Hi,
the job is getting aborted saying 'Data error'. Do we need to execute this SQL through a different stage or else.
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 ;
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
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
;
Mamu Kim
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
@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.
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.
Code: Select all
ED DICT DS_JOBOBJECTS Kim
Mamu Kim