Create Table in DataStage from Source Definition

[Tornquist]

I have a series of tables in a source database, and I would like to create a copy of all of those tables in my staging database. I have job parameters that I am able to use, but I do not have direct access to the source database. With this information I was able to import table definitions.

Each of the tables have large numbers of columns and I do not want to just open the table definition view and retype everything, that's an easy way to make a mistake. I was wondering if there is a way for me to take a source table and have it auto-generate a copy of that table in a destination database. It doesn't really matter if the content of the table is copied, I just want the column information moved. I've looked into exporting the table definition, but the xml that is generated is not much better than just looking at the table definitions within datastage.

Thanks for any advice you may have!

--Nathan

[eostic]

I do this all the time because I'm always "in" DataStage and often too lazy to fire up one of my SQL tools....use one of the target stage types that has a "Create" option...it will generate CREATE DDL from the input link column list and then load it...

Ernie

[Tornquist]

Is there any way to do this with a Netezza Database? I don't see create in the Netezza options.

[eostic]

I am not a Netezza expert, so I can't comment on the viability of the possible stage types, but it looks like the Netezza Enterprise Stage has the option for CREATE, as does, of course, ODBC. The Netezza Connector doesn't appear to, at least at first glance, but there may be ways that I am not aware of to get it to initiate a CREATE.

Those other two stages can be found by opening the repository tree, then Stage Types....Parallel....Database.....

Ernie

[Tornquist]

Ah, excellent. I was looking in the wrong spot. Within the stage I set the SQL action to Insert, then lower down in the list I changed the append option to create instead. These changes are being made within my output Netezza stage.

I've set it up so I have the input connection to the MySQL database I'm using, with a transformer in the middle to add the ETL rows that we use. All of this feeds into a stage that will create the table. There will be loads of garbage values in there because of the way I'm doing the ETL rows, but I'll have a perfect table once the job runs, I just need to delete the data and then build it correctly.

Thanks for your help!

[ray.wurlod]

Once the table has been created you can use the Truncate table action.