QUERY UNIVERSE DATABASE

ramank1981 · Post by **ramank1981** » Wed Apr 19, 2006 12:58 am

I need to generate report by querying metadata in UNIVERSE database....is there any way to query universe database?

ArndW · Post by **ArndW** » Wed Apr 19, 2006 1:32 am

You can query UniVerse Databases with either the native query language INFORM or you can use standard SQL queries within the TCL environment. Note that DataStage is no longer UniVerse but they are clones and very similar. The IBM website offers all of the UniVerse documentation free of charge in PDF format for you to download.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 19, 2006 1:39 am

If you have genuine UniVerse (not DataStage) you can set up ODBC access to it.

I have also used an External Source stage to get data out of UniVerse into a parallel job. Construct a query that does not column wrap (perhaps using FMT to override the default field widths and setting the device width sufficiently wide). Wrap this in a uv, uvsh or dssh command as the command executed by the stage; its stdout will become the input link of the stage.

Example

Code: Select all

uv "LIST DS_JOBS ID.SUP NAME FMT '40L' JOBNO WITH NAME LIKE '1A0X'"

ramank1981 · Post by **ramank1981** » Thu Apr 20, 2006 12:55 am

I want to query Univers db of datastage, it is not external db, so is there any stage defined for quering Universe db?
Secondly can i treat this Universe Db as external and try to connect it using ODBC?

ArndW · Post by **ArndW** » Thu Apr 20, 2006 12:57 am

You use the Hashed file stage to access the Db directly, and you can access via ODBC with the dsn "localuv"

ray.wurlod · Post by **ray.wurlod** » Thu Apr 20, 2006 1:10 am

ramank1981 wrote:I need to generate report by querying metadata in UNIVERSE database....is there any way to query universe database?

Why do you need to do it that way?
Perfectly good tools are provided "out of the box".

ray.wurlod · Post by **ray.wurlod** » Thu Apr 20, 2006 1:12 am

ArndW wrote:You use the Hashed File stage to access the Db directly

In a PARALLEL job?!!

Can the Enterprise ODBC stage "see" the localuv DSN and, if so, can it make any sense of "DBMSTYPE = UNIVERSE" in uvodbc.config ?

ArndW · Post by **ArndW** » Thu Apr 20, 2006 1:17 am

Ray - thanks for pointing that out, I saw hashed files and assumed that we were in the Server world and answered the question as posted (knowing that the answer wasn't going to help, since knowing which stages to use to query a database doesn't give you the tables required or the layout of columns in those tables...).

I will try the ODBC connectivity to Hashed files today in a PX job - I haven't attempted that before and you've piqued my curiosity.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 20, 2006 5:05 am

I don't have access to DataStage this week - hence the question rather than the research.

dwblore · Post by **dwblore** » Fri Apr 21, 2006 5:39 am

Which are the tools available for quering on Universe Databse and for making reports out of Metadata?

Is there any mechanism to access Univers database(in both server or Parallel jobs).

kduke · Post by **kduke** » Fri Apr 21, 2006 8:41 am

Yes, use a UV stage in a server job.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 21, 2006 3:02 pm

You could build a server shared container containing a UV stage. Read the end of Chapter 2 of Parallel Job Developer's Guide for the restrictions on what you can do using this approach.

You could use an External Source (or External Target) stage, as noted above.

ramank1981 · Post by **ramank1981** » Mon Apr 24, 2006 11:03 pm

Still i m not able to figure out which database nd which table i should query to get metadata information about all the jobs in project,using Universe Stage in a server job.

kumar_s · Post by **kumar_s** » Tue Apr 25, 2006 12:34 am

Already mentioned.
To get the informations about the jobs query DS_JOBS.
To get the metadate of this file use LIST DICT DS_JOBS.

koolnitz · Post by **koolnitz** » Tue Apr 25, 2006 1:03 am

ArndW says:

The IBM website offers all of the UniVerse documentation free of charge in PDF format for you to download.

Can you please give us the URL from where we can download the mentioned PDF?

Thanks in advance!

DSXchange

QUERY UNIVERSE DATABASE

QUERY UNIVERSE DATABASE

Re: QUERY UNIVERSE DATABASE