Need help with universe metadata!!!

dwscblr · Post by **dwscblr** » Tue Nov 23, 2004 12:31 am

Hi,

I want to read the datastage repository tables. To be able to do so i would need the table definitions of the universe database tables. How can i import the metadata of all the tables in the universe repository of datastage?

him121 · Post by **him121** » Tue Nov 23, 2004 12:47 am

hi..
in univers you can try this query

SELECT @ID, CATEGORY FROM DS_METADATA WHERE CATEGORY LIKE '%#CategoryName#'

u can use universe stage and generate this query...
u can read table defination through this..

himanshu

dwscblr · Post by **dwscblr** » Tue Nov 23, 2004 1:04 am

Hi,

I tried using that query..... it gives me the table definition list. Its does not give me the column names and details for the universe repository tables. How can i get to know the column names and column details for these tables?

ray.wurlod · Post by **ray.wurlod** » Tue Nov 23, 2004 1:29 am

That information is the proprietary intellectual property of Ascential Software and is not in the public domain.

The repository tables are, mainly, published deliberately without column definitions, being intended only to be accessed programmatically.

Where Ascential believes it is suitable that you know, the column definitions are published. You can access them from the command line using the LIST.DICT command.

However, when importing UniVerse (hashed) file definitions, the Repository tables' names are deliberately suppressed from the list of available tables. It is not possible to override this "feature".

With a little UniVerse knowledge you can expose them through parallel table definitions sharing the same file dictionaries. But you will never get the complete definitions of all columns. Not least because there are multiple record formats in most of the repository tables; it is definitely not a first normal form environment!

What are you trying to accomplish? That is, why do you believe you need these table definitions? There may be alternatives, such as extracting data from a MetaStage hub, or from the documentation tool database (which IS first normal form).

dwscblr · Post by **dwscblr** » Tue Nov 23, 2004 2:47 am

I want to replicate the universe repository into another Relational DB which can be anything... Oracle, SQL Server etc. Thus to be able to do that i need to read the universe repository tables. Is there any other way in which i can do that?

kcbland · Post by **kcbland** » Tue Nov 23, 2004 9:07 am

Reverse engineer a .dsx file and normalize the contents

mhester · Post by **mhester** » Tue Nov 23, 2004 9:13 am

And keep in mind that for any given job/routine/transformer/tabledef type for a given release the same fields/columns are used for different things (overloaded) so reverse engineering could be painful and very innacurate.

Also, some fields are bitmaps and unless you understand how to decipher the bitmap this information would be meaningless when viewed in the editor or DSX.

Regards,

peternolan9 · Post by **peternolan9** » Tue Nov 23, 2004 11:17 am

Hi dwscblr,
Ascential have a product for this, it's called MetaStage. It migrates all DS data into a relational database as well as a lot of other stuff as well.

If you are brilliant, like Tom Nell you might even be able to understand the data model of MetaStage and get stuff out of it. Or you can just contact Tom to write the queries you want.....

I would not expect any reputable consultant to assist you to steal Ascential IP in lieu of buying the product they have built to do what you want to do, which is what you are asking for.

dwscblr wrote:I want to replicate the universe repository into another Relational DB which can be anything... Oracle, SQL Server etc. Thus to be able to do that i need to read the universe repository tables. Is there any other way in which i can do that?

ray.wurlod · Post by **ray.wurlod** » Tue Nov 23, 2004 3:50 pm

dwscblr wrote:I want to replicate the universe repository into another Relational DB which can be anything... Oracle, SQL Server etc. Thus to be able to do that i need to read the universe repository tables. Is there any other way in which i can do that?

The Documentation Tool (from Manager) will do this for you.

C:\Program Files\Ascential\DataStage\Documentation Tool contains scripts for setting up the required tables in Oracle, SQL Server, etc., called oracle.sql, mssql.sql, etc.

You also set up an ODBC DSN to point to your database.

When using the Documentation Tool, go to the Options tab and change the DSN from DSReporting (which points to doc_tool.mdb) to your DSN, and away you go!

vmcburney · Post by **vmcburney** » Tue Nov 23, 2004 6:03 pm

You can also twiddle your thumbs for twelve months because by the end of 2005 the Ascential suite will be on a database independent repository with support for SQL Server, Oracle or DB2. I think Ascential have been taking flack for years from Informatica in vendor comparisons because Informatica has an open repository and DataStage doesn't.

kduke · Post by **kduke** » Tue Nov 23, 2004 6:12 pm

How "open" will it be? Does anyone know?

mhester · Post by **mhester** » Tue Nov 23, 2004 7:32 pm

All "they" said at Ascential World was that it will be an "open" architecture, but they gave nothing more than that - no details.

vmcburney · Post by **vmcburney** » Tue Nov 23, 2004 7:48 pm

They said that out of the box you could install it on MySQL, Oracle, SQL Server or DB2 and Sybase were negotiating to get added to that. For other databases you probably have to create your own data structure. MySQL would be free, the others would require a licensed RDBMS. Now unless you know how to hide the metadata on these database types you've got to assume the entire table structure and column structure will be exposed. These table relationships can be very complex, just try reporting on derivations in the DataStage reporting tool.

him121 · Post by **him121** » Tue Nov 23, 2004 8:09 pm

hi..
u can create routine..to read all the colums name, length and scale off the Tebledefinations..

try this routine it may help...

here in discussion i am not clear wht exactly u want ..bcoz after my first reply yesterday....today ..discussion bcomes..very long..
any way..

try this

OPEN "DS_METADATA" TO MetaDataFile ELSE
Call DSLogWarn("Error opening Metadata Library file - DS_METADATA")
END

READ SrcRec FROM MetaDataFile,CategoryName Then
SrcColCnt = DCOUNT(SrcRec<12>,@VM)
ColumnFound = 0

For i=1 to SrcColCnt
MColName = SrcRec<12,i>
MTypeCode = SrcRec<15,i>
MLength = SrcRec<16,i>
MScale = SrcRec<17,i>
MNullable = SrcRec<18,i>
Mkey = SrcRec<19,i>

[/quote]

ray.wurlod · Post by **ray.wurlod** » Tue Nov 23, 2004 8:57 pm

Derivations are simple compared to generating reports containing decoded parameter values!