Metadata Import

ds_teg · Post by **ds_teg** » Tue Oct 26, 2010 4:26 pm

Hi All ,

I am trying to import the table definition into datastage . But I dont have access to the oracle table which I am importing into datastage . I have DDL script for that table .Is there any way I can import the table definition . I have to do this for 20 tables which are having many columns .I think it will take some time to manually create the table definition .Could you please suggest any approach ?

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Oct 27, 2010 12:15 am

Can you get SELECT access to the Oracle system tables in which metadata are stored? That's all you need to import metadata - you don't need to be able to access the table itself.

ds_teg · Post by **ds_teg** » Wed Oct 27, 2010 7:01 am

Unfortunately I don't have select access from datastage server . I have select access from local desktop . So any other suggestions ??

ds_teg · Post by **ds_teg** » Wed Oct 27, 2010 7:05 am

Is there any way to create a schema file from the DDL script ??

chulett · Post by **chulett** » Wed Oct 27, 2010 7:27 am

How are you planning on running any jobs against that database if there is no "select access from the DataStage server"? Surely you must have at least one id defined on the server for connectivity there - can you not use that for the metadata import?

eostic · Post by **eostic** » Wed Oct 27, 2010 7:56 am

No "direct" way that I know of to import a SQL create stmt "as is". I've seen a whole lot of solutions over the years, such as creating a .dsx (look at one for a table, it's a very simple format), or using the manual shared table creation method that I describe below at my blog. Still, other creative ways would be to import the SQL DDL into a tool like ERwin or other modeling tool, and then use a bridge or broker via Import/Export manager.

Usually though, the absolute simplest is to just do what Craig suggests...find a database ...any database.... and just create the tables and import. It doesn't matter if it's a test database, dev, etc.... all you care about is to have some tables created for metadata purposes.

Ernie

ray.wurlod · Post by **ray.wurlod** » Wed Oct 27, 2010 9:06 am

ds_teg wrote:Unfortunately I don't have select access from datastage server . I have select access from local desktop . So any other suggestions ??

GET access.

ds_teg · Post by **ds_teg** » Thu Oct 28, 2010 10:33 am

Thanks a lot Ernie for giving more inputs .

Ray , I do know that i need to have access to the table . Here I am trying to understand if there are any other ways .

May be i should have explained in more detail ....

I am getting a bunch of files , which I need to read,format , transform and load into different tables . Those files are nothing but extract from oracle tables by source system . I have files but need metadata . I think it's difficult to create table definition if number of columns are more . Source system obviously didn't agree for giving access to the table as they are providing files .

eostic · Post by **eostic** » Thu Oct 28, 2010 12:11 pm

What tooling are they using to dump the files? There are lots of tools out there that will put the column headers into the first line.....perhaps they are using a method that supports this..... ultimately that's all you need and will be the simplest for everyone if the files are pure ascii delimited text.

Ernie

ds_teg · Post by **ds_teg** » Tue Nov 02, 2010 1:19 pm

They are using abinitio to dump the files .But there are no headers for the files ..

Mike · Post by **Mike** » Tue Nov 02, 2010 2:06 pm

Ask your ab initio guy for the dml that was used to produce the file. Converting that to a schema file should be fairly straight forward.

Mike

ds_teg · Post by **ds_teg** » Tue Nov 02, 2010 4:04 pm

Ok now I got DML file in abinitio .Now how to convert that DML file in abinitio to datastage schema file ??

Mike · Post by **Mike** » Tue Nov 02, 2010 6:45 pm

If you have just a few, use a text editor with global search and replace of DML syntax with equivalent orchestrate schema syntax.

If you have many, I'd go for a DML to schema conversion utility written in your tool of choice ... a DataStage job, shell script or perl script would handle that nicely. I've considered creating one of these, but never had a project need to do so.

I wouldn't be too surprised if IBM already has some kind of utility along those lines. You could inquire with your official support provider.

Mike

ds_teg · Post by **ds_teg** » Wed Nov 03, 2010 1:09 pm

Hi Mike ,

Thanks for your response..Just wondering if any one has a utility to convert a DML file into datastage schema file ..