DSXchange

Posted: **Thu Sep 21, 2006 10:38 pm**

Can someone please suggest how to extract all Job Names and its corresponding short description into a text file using a DataStage Job. Probably by querying the repository.

What Stage to use and whats the SQL ??.
What privilige is required to do such a task ??
What connection parameters need to be used in the source extraction stage ??

Posted: **Thu Sep 21, 2006 11:27 pm**

You can parse the exported dsx file.
But you can alternativly get these information from DS_JOBS.
But when I tried to get Description from DS_JOBS, I get the following error.

Code: Select all

DataStage/SQL: syntax error.  Unexpected symbol.  Token was "DESC"

Not sure, if we need get it by other means.
Hope Kim or Ray or someone else will answer this.

Posted: **Fri Sep 22, 2006 12:25 am**

The descriptions are stored within the DS_JOBOBJECTS, not with the DS_JOBS, that's why you could not find it in DS_JOBS.

Try

Code: Select all

SELECT EVAL "@RECORD<4>", EVAL "@RECORD<7>" FROM DS_JOBOBJECTS WHERE OLETYPE='CJobDefn'

Posted: **Fri Sep 22, 2006 4:19 am**

You can use a UV stage to extract the required records from the DS_JOBS table (the short descriptions ARE stored there, as well as in DS_JOBOBJECTS). The advantage of using DS_JOBS is that the relevant column names all exist in the metadata. Importing them into DataStage, however, requires a "trick", since this table is one of those explicitly blocked to the import process. Create a synonym in the project, for example MY_JOBS, and import the table definition from that.

Code: Select all

COPYI FROM VOC DS_JOBS,MY_JOBS

will create the synonym. When it's no longer required:

Code: Select all

DELETE FROM VOC WHERE @ID = 'MY_JOBS';

Posted: **Fri Sep 22, 2006 8:27 am**

Ray the description field in DS_JOBS does not match the one in DS_JOBOBJECTS. I would create ShortDesc field before importing the metadata as Ray described. Here is the SQL for your UV stage.

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'ShortDesc', 
   'D', 
   '4', 
   'ShortDesc', 
   '64L', 
   'S' 
)
;

Code: Select all

select
   DS_JOBS.NAME JobName FMT '40L',
   ShortDesc
from 
   DS_JOBOBJECTS, 
   DS_JOBS
where 
   DS_JOBOBJECTS.OLETYPE = 'CJobDefn' 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
   and DS_JOBS.NAME = 'BuildEtlOleTypeHash'
;

The DS_JOBS.NAME = 'BuildEtlOleTypeHash' needs to be removed but is good for testing.

DSXchange

Extract Job Name and Short Description for all Jobs

Extract Job Name and Short Description for all Jobs