Creating Teradata table in the Datastage job

[anandkumar442]

Hi All,

My requirement is to design a job which would create a table (teradata) by itself and should get loaded from the input file.

File ------- transformer --------- TD table.

The TD table will not exists in the DB, we need to create in the job itself.

Could any one help in this, as I am new to TD stages.

Thanks,
Anand

[sagar.skhtt@gmail.com]

Hi Anand,
Please use TeradataPX stage in General tab Create table aection put Generate DDL and Drop table action put Generate DDL . this will work each run Drop the table and Create it.

Regards,
Sagar.

[anandkumar442]

Hi Sagar,

Thanks for your reply.

We only have two stages in Teradata. they are
Teradata enterprize
Teradata multiload.

I am not sure about the Teradata Px stage.

Please suggest.

Thanks,
Anand

[sagar.skhtt@gmail.com]

Hi Anand,
in Mload Script tab -> Before use this syntax
DROP TABLE database.TableName;
CREATE TABLE database.TableName (
ID DECIMAL(12,0) NOT NULL,
NAME CHAR(5) NOT NULL,
)
PRIMARY INDEX (Keycolumns);


Regards,
Sagar.

[anandkumar442]

Hi Sagar,

Job is finishing successfully.
But the table is not getting created.

Any idea? not warning is there in the job as well

Thanks,
Anand.

[chulett]

You two need to learn to use the Reply to topic button rather than always quoting everything. I just had to go and clean up the mess this thread was, each reply getting bigger and bigger... sheesh.

[chulett]

My requirement is to design a job which would create a table (teradata) by itself and should get loaded from the input file.

Clarify your requirement, please. Do you need to create this table once (the first run only) or is it ok to drop and recreate it each run? The advice you are getting is for the latter solution, just wanted to double-check.

The former is a tougher nut to crack.

And I don't really see how the job can be "finishing successfully" if the table is not getting created.

[anandkumar442]

Hi Chulett,

My apologies for the above replies.

Yes, the requirement is to drop and recreate the table for each and every run.We actually were able to create a table and load it from the file, using the 'Teradata Enterprise stage'.

Now, we would like to get some advice, to drop and recreate the teradata table for each and every run of the job.

Note: We do not want to use Teradata Multiload stage.

Thanks,
Anand.

[RAJARP]

Hi,
I do not know, if i am missing something.

But why can't we simply use a write mode 'Replace' option in Teradata enterprise stage.'Replace' would drop a table and create new one.


Regards,
Raja R P

[anandkumar442]

Thanks a lot All.

I am able to create the table and truncate it for each and every run by using the Replace option.

Thank You.

Thanks,
Anand.

[chulett]

Just to be anal, from what Raja posted that 'Replace' option drops and recreates the table in question, it is not being truncated. One would do one or the other, not a combination of the two.