I am very interested in the outcome of this post. I have looked in the documentation and I cannot find the reference but I believe I read somewhere that the Teradata Enterprise Stage will create a work table when an append to a Teradata table is selected for the job. I believe this is because a FastLoad must load to an empty table which is impossible with an append operation. To get around this DataStage FastLoads to a generated empty work table and then does insert into (select * from work table) on the database side. This seems to match what you are seeing. It appears that the Append option will create an empty teradata table with ALL fields but NO defaults. It generates this empty table from the teradata metadata NOT your DataStage job metadata. "Teradata Metadata" say that 5 times fast.
Can you confirm or shoot down this theory as total whooie?
teradata enterprise with unknown destination columns
Moderators: chulett, rschirm, roy
-
Krazykoolrohit
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Do not get deep into the work tables created by teradata. they will be created as per your input file schema. so if you are defaulting some values in your datastage job then the work tables will be created according to that automatically.
What i would suggest is instead of teradata stage as output send all the records to a flat file and see is the default values are getting loaded. if they are getting loaded fine then get hold of your teradata admin.
What i would suggest is instead of teradata stage as output send all the records to a flat file and see is the default values are getting loaded. if they are getting loaded fine then get hold of your teradata admin.
Thanks all for your interest.
In response for Trobinson, datastage use teradata metadata to create temporary table with (select * from work table), this make the load being faster using fastload (empty table, no rollback constraints,no lock....).
Unfortunatly we can't specify the list of columns to load (keeping default values).
The case opened talk about the "select list" propertie which name destination columns but doesn't modify the list of source columns...
For Krazykoolrohit:
When using temporary table, datastage replace unknowns columns whith null.
inserting null in teradata doesn't use the default values mechanism which is used only when columln is not specified
If support can't find any solution, we may use resticted column list using insert throught teradata view (may impact performances)
In response for Trobinson, datastage use teradata metadata to create temporary table with (select * from work table), this make the load being faster using fastload (empty table, no rollback constraints,no lock....).
Unfortunatly we can't specify the list of columns to load (keeping default values).
The case opened talk about the "select list" propertie which name destination columns but doesn't modify the list of source columns...
For Krazykoolrohit:
When using temporary table, datastage replace unknowns columns whith null.
inserting null in teradata doesn't use the default values mechanism which is used only when columln is not specified
If support can't find any solution, we may use resticted column list using insert throught teradata view (may impact performances)
Well I prefere considerate using views than rewrite the job with fastload, even if i sometimes think that using fastload instead of datastage would be nicer....DSguru2B wrote:If IBM is not able to find a solution then you can load your records to a flat file and generate a sql script. Invoke the teradata utility and execute the sql scripts.
But in this case all the application steps and metrics use datastage and some people wouldn't appreciate the exclusion of datastage product (thinking of IBM and some chiefs...
)