Creating Table DDL Dynamically from DataStage

horserider · Post by **horserider** » Tue Sep 04, 2007 1:36 pm

I have a sequential file as a source that has over 150 columns. I have to read the file and dump the data to a teradata table.

(1) Either I first create the table using CREATE TABLE command outside
Datastage manually, then import the metadata and then use it
as a target.

OR

(2) Is there any other way, I can create the table Definition dynamically
using Datastage? I dont want to type CREATE TABLE (all column name
and type) manually.

It would be nice if I can read the source and dump it to a TEMP table,
them make the TEMP table a persistent table and just get the DDL.

Any thoughts?

ray.wurlod · Post by **ray.wurlod** » Tue Sep 04, 2007 4:30 pm

Search the forum for a routine that dumps a table definition. One of the available formats is a CREATE TABLE statement.

rajaobj · Post by **rajaobj** » Mon Sep 10, 2007 2:33 am

Hi,
Is anybody got the solution for this issue.

Thanks,
Raja

ray.wurlod · Post by **ray.wurlod** » Mon Sep 10, 2007 6:25 am

rajaobj · Post by **rajaobj** » Mon Sep 10, 2007 7:10 am

I tried searching for the post but i could not able to locate it,can you please provide me a link for the post.

Thanks,
Raja

shamshad · Post by **shamshad** » Mon Sep 10, 2007 7:28 am

The way I do is,

First dump all the Columns I need in a text file. So, now I have all the metadata I need for the table. Then, from the same transformer that feeds the text file, I take 1 more link and connect to a teradata Table Stage (Enterprise, API) [if one is using teradata]

On the Table properties, we have a option to append/replace/create table etc. Select the option that will first create the table and then load the data.

When the ETL is first run, it will first create the table and then insert the data. Once the ETL is complete, go to Database Query Tool (such as queryman), select that newly created table, get the table definition and make appropriate changes needed.

Then create the table outside datastage. Remember to change the load option in the ETL. Now, it should NOT be dropped and created every time [if that is what you want], simple select append, replace or appropriate load option.

Hope that helps.