Teradata along with datastage

[swathik]

Hi Guys,

How to create a table with some columns having default values in create table statement. Now how to create a job to load data using Teradata Enterprise in this table without having default columns in source data.
-Aim is to find out how DataStage behaves when we have created a Teradata table with default values and when we do not have those columns in source file.Please give me a suggetion in doing this.


Thanks in advance.

swathi.k

[hamzaqk]

it will ignore the values when you use the enterprise stage. this is because the work table which is created is based on the concept

Code: Select all

 create SET TABLE orch_workxxx as select * from source with data 

and not .

Code: Select all

 a create multiset table work_orchxx as source with data 

this will drop any default values. Also note as the temp table is being created SET it will drop duplcated when you try to load it to TD. i think this is a bug in datastage. you can search the forum here. we have discussed this many times in detail.

p.s. also have a look into run time column propogation and an option in the enterprise stage. something like silently drop columns not in target.

[bkumar103]

Could you please elaborate more on you requirement... Do you want to create table from some other existing table or you want to create table in Data stage.

[John Smith]

Hi Guys,

How to create a table with some columns having default values in create table statement. Now how to create a job to load data using Teradata Enterprise in this table without having default columns in source data.
-Aim is to find out how DataStage behaves when we have created a Teradata table with default values and when we do not have those columns in source file.Please give me a suggetion in doing this.


Thanks in advance.

swathi.k

er..How DataStage behaves is dependent on how you make it behave
it's entirely up to the developer to tell DS what to do.

[sas]

hamzaqk,
Just wanted to clarify on the temporary table creation mechanism.

Will it not be

create SET TABLE orch_workxxx as select * from source with NO data

Instead of

create SET TABLE orch_workxxx as select * from source with data


Thanks

it will ignore the values when you use the enterprise stage. this is because the work table which is created is based on the concept

Code: Select all

 create SET TABLE orch_workxxx as select * from source with data 

and not .

Code: Select all

 a create multiset table work_orchxx as source with data 

this will drop any default values. Also note as the temp table is being created SET it will drop duplcated when you try to load it to TD. i think this is a bug in datastage. you can search the forum here. we have discussed this many times in detail.

p.s. also have a look into run time column propogation and an option in the enterprise stage. something like silently drop columns not in target.