How to Query Stage Names from Universe

[Lucky]

Hi Gurus,

By querying on Universe, we can get all the job names and Link names from the Universe.

Same way, How to query all the Stage names from Universe.

Eg: I have given stage names in a Job as src_finance , tfm_finance,tgt_finance. How to query these stage names from DS Universe

Kind Regards
LUK

[kduke]

The job name is stored in DS_JOBS.NAME. The link name is stored in DS_JOBOBJCTS.NAME. You need to search for a query which joins these two tables together.

[ray.wurlod]

Why not use the API?

Code: Select all

DSGetJobInfo(hJob, DSJ.STAGELIST)
DSGetStageInfo(hJob, DSJ.LINKLIST)

[Lucky]

Thanks for u r reply Kim,

The code (dsx) which u have given (EtlStats) to query the Universe and to generate job reports is really awesome.

From EtlStas from LoadEtlJobLink Job, it is giving Job Name , Link Name and Category. Will it possible to get all the names given to Stages in the job using query on Universe.
Kindly let me know how to query Stage Names.

Kind Regards
LUK

[Lucky]

Thanks for the inputs Ray,


But want to get all the Names of the Stages in each job from my Project.

Just like NAME, CATEGORY,LINK_NAME FROM DS_JOBS and DS_JOBOBJECTS for getting job name, link name and category.

Kind Regards
LUK

[kduke]

EtlStats has a script named DsJobLinksAll.sql. Here is the SQL:

Code: Select all

SELECT 
   DS_JOBOBJECTS.OLETYPE as LinkType, 
   DS_JOBS.NAME as JobName, 
   DS_JOBOBJECTS.NAME as LinkName, 
   DS_JOBS.CATEGORY as Category
FROM 
   DS_JOBOBJECTS,  
   DS_JOBS 
WHERE  
   OBJIDNO = JOBNO 
   and DS_JOBOBJECTS.OLETYPE like '%put' 
Group by 
   LinkType, 
   JobName, 
   LinkName, 
   Category
;

Change the where clause. Remove LinkType and you are there.

[Lucky]

Dear Kim,

I have modified the query like this,

SELECT
DS_JOBOBJECTS.OLETYPE as StageName,
DS_JOBS.NAME as JobName,
DS_JOBOBJECTS.NAME as LinkName,
DS_JOBS.CATEGORY as Category
FROM
DS_JOBOBJECTS,
DS_JOBS
WHERE
OBJIDNO = JOBNO
Group by
StageName,
JobName,
LinkName,
Category
;

And i m getting Job name, all the stage names and Link names in Stage_name column.

Kindly correct the query to get JobName, StageName, LinkName and Categroy from the Universe for all the Jobs in Project.

Pls let me know if i need to change any Conditions in Job Constraint (At Trn stage for job LoadEtlJobLink) to get the exact output.

Thanks in Advance

Kind Regards
LUK

[kduke]

StageName is really StageType or LinkType. I doubt if things printed like you think it did. The problem is it printed down the page because the columns are too wide for a page. Eliminate some columns.

[ray.wurlod]

Or make your page wider.

[kduke]

Code: Select all

SELECT 
   DS_JOBOBJECTS.OLETYPE as StageType, 
   DS_JOBS.NAME as JobName, 
   DS_JOBOBJECTS.NAME as StageName 
FROM 
   DS_JOBOBJECTS,  
   DS_JOBS 
WHERE  
   OBJIDNO = JOBNO 
   and DS_JOBOBJECTS.OLETYPE like '%Stage%' 
Group by 
   StageType, 
   JobName, 
   StageName 
;

[Lucky]

Hi Kim,

Thanks a lot.

Kind Regards
LUK