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 ??
Extract Job Name and Short Description for all Jobs
Moderators: chulett, rschirm, roy
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.
Not sure, if we need get it by other means.
Hope Kim or Ray or someone else will answer this.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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
Try
Code: Select all
SELECT EVAL "@RECORD<4>", EVAL "@RECORD<7>" FROM DS_JOBOBJECTS WHERE OLETYPE='CJobDefn'
Success consists of getting up just one more time than you fall.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
will create the synonym. When it's no longer required:
Code: Select all
COPYI FROM VOC DS_JOBS,MY_JOBS
Code: Select all
DELETE FROM VOC WHERE @ID = 'MY_JOBS';
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
The DS_JOBS.NAME = 'BuildEtlOleTypeHash' needs to be removed but is good for testing.
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'
;
Mamu Kim