Accessing Universe tables from one project to another

[imbos123]

Hi,

I have two Datstage projects
RETAIL and FINANCE
I need to access DS_JOB and DS_OBJECTS of RETAIL project from FINANCE project.

Basicallly, I have written Server jobs which is using UNIVERSE stage and trying to find out objects used in each Project. To get the objects from each project I have to copy this job in every project and get these details. I was thinking whether we have a better way to do it. So that the DSN name localuv can be replaced dynamically to access DS_JOB tables.

[xcb]

Be warned that fooling around in DS_JOBS and DS_JOBOBJECTS can cause major instabilities in your project. There are other ways to compare this data - The provided reporting assitant can get to this info and has pretty much everything that DS_JOBS and DS_JOBOBJECTS contains.

Having said that...

I don't know if what you are asking can be done easily, however you can create a Q-pointer to a table in another project which might help you out. It acts like a synonym to that table.

From a telnet/administrator prompt type HELP SET.FILE to see how the command works. You would have to set the pointer up before being able to use it in your job.

[chulett]

Perhaps you could explain why you think you need to "access DS_JOB and DS_OBJECTS of RETAIL project from FINANCE project"?

[ray.wurlod]

If you use DataStage/SQL, you can qualify the table name with the schema name. For example, in Project1 you can (assuming you have Project2 and the table EXAMPLE1 exists there)

Code: Select all

SELECT * FROM Project2.EXAMPLE1;

Therefore, to detect differences, you might left outer join DS_JOBS from Project1 with DS_JOBS in Project2, and left outer join DS_JOBS from Project2 with DS_JOBS in Project1. DataStage/SQL does not support right outer join, as it's provable that any right outer join can be crafted as the reverse left outer join.

I would strenuously advise against using DataStage/SQL to copy job designs from one project to another. Use export/import; it's 1000% safer.

[imbos123]

H Ray,

I was trying to access the <ProjectName>.DS_JOBS and was getting below error
Table does not exist.

Is there anyway, I can call this from Universe stage.
The reason I am doing this to list all the ojest from all the projects we are working on and load it into the database.

[DSguru2B]

Do it via a script. Really small and easy. Something like this

Code: Select all

#!/usr/bin/ksh
export file=/..../..../...../ProjectNames.txt
export jobNames=/..../..../.../AllJobs.txt

#Source the dsenv file
cd $DSHOME 
. ./dsenv 

#Remove AllJobs.txt from previous run
rm -f $jobNames
cat $file | while read projname
do
   dsjob -ljobs $projname >> $jobNames
done

source this in the before job subroutine via 'ExecSH' and read the file 'AllJobs.txt' from a sequential file stage.