Metadata Import
Moderators: chulett, rschirm, roy
Metadata Import
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
GET access.ds_teg wrote:Unfortunately I don't have select access from datastage server . I have select access from local desktop . So any other suggestions ??
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 .
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 .
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
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
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
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