Page 1 of 1
Teradata along with datastage
Posted: Mon Apr 14, 2008 3:50 am
by 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
Posted: Mon Apr 14, 2008 3:59 am
by 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.
Posted: Mon Apr 14, 2008 4:05 am
by 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.
Re: Teradata along with datastage
Posted: Mon Apr 14, 2008 7:24 pm
by John Smith
swathik wrote: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
![Wink ;)](./images/smilies/icon_wink.gif)
it's entirely up to the developer to tell DS what to do.
Posted: Wed Apr 30, 2008 11:03 pm
by 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
hamzaqk wrote: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.