Hi,
In my job I am using the target as the Teradata Eneterprise stage.
Source as a dataset.
In source one of the column is varchar 5 and having the values 12345
In the target aslo i have the column with the same Varchar 5.
After running the Job Teradata column is populating with ????? values
Can any one tell me why the data is populating with '????? '
I tried to trace back the data till data reach to the teradata stage. It's coming fine till reaches the teradata stage.
But in teradata it is populating with Null values.
Can pls help in this....
Terdata Issue
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 82
- Joined: Fri Jun 03, 2005 5:23 am
- Location: Bangalore
- Contact:
Terdata Issue
Rajasekhar
-
- Premium Member
- Posts: 82
- Joined: Fri Jun 03, 2005 5:23 am
- Location: Bangalore
- Contact:
phanee_k wrote:Hi,
Are you finding null values for all the records that are getting into the table.
Did you check for warnings in the director log.
Thanks,
I am not getting any warnings in the log file. the status is showing finished
Data Volume:
------------
Input rows: 5
Input bytes: 105
Input rows rejected: 0
Duplicate rows: 0
Field conversion errors: 0
Duplicate index errors: 0
Rows written: 5
But after I tried to extract the same data through Teradata Enterprise Stage.
Whichever the columns are poplated with ????? values at the database is showing with NULL values at the stage level.
Regds
Rajasekhar
-
- Premium Member
- Posts: 82
- Joined: Fri Jun 03, 2005 5:23 am
- Location: Bangalore
- Contact:
Hi I am not aware of this Tpump utility. Is it available in datastage inbulit utilities? If not what is the functionality and how to get it?phanee_k wrote:Did you try loading the data into Teradata table using Tpump utility.
Teradata Enterprise Stage is having some restrictions for using it as Target. Please try with Tpump and let us know.
Thanks
Regds
Rajasekhar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
TPump is a Teradata utility.
Scan through the stage types available to talk to Teradata and you will find a stage there that can talk via TPump. Maybe even two stages.
Scan through the stage types available to talk to Teradata and you will find a stage there that can talk via TPump. Maybe even two stages.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Teradata(TD) EE when used as Source calls FASTEXPORT (TD Utility) and when used as Target calls FASTLOAD (TD Utility). Your source being Dataset and your investigation has found that data is coming fine till TD EE stage you are certian issue is with TD EE. Better understanding of TD EE stage might be helpful in solving your issue.
TD EE (Target) invokes FastLoad for bulk loading into TD Tables. TD EE Stage will create a work table when an append to a Teradata table is selected for the job. 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. Append option will create an empty TD table with ALL fields but NO defaults. It generates this empty table from the Teradata metadata NOT your DataStage job metadata. Also unknown columns are replaced with null.
Ex:
Incoming columns are col1, col2, col3
Target table columns are col1, col2, col3, col4, col5 with col4 default value as 0 and col5 as current_timestamp.
Step 1:
Creation of orch_work table with:
CREATE TABLE ORCH_WORK_xxxxx AS ( SELECT * FROM TargetTable ) WITH NO DATA PRIMARY INDEX (col1)
Note: Orch_work table doesn't keep default values. ie. Orch_work table has col4 and col5 also, but with no default values.
Step 2:
Incoming records are loaded in orch_work with values col1, col2, col3,null,null.
INSERT INTO ORCH_WORK_xxxxx (:col1, :col2, :col3, null, null)
Step 3:
Append using Insert command into Target table:
INSERT INTO TargetTable SELECT * FROM ORCH_WORK_xxxxx
Caution: Step 3 will fail if Col4 and Col5 (or any of them) are set as not null in the Target table. To avoid this you need to pass col4 and col5 with default values inside the job itself.
Now you know DataStage calls a TD utiliy to load the data into your target table and you might have to figure out where data is getting converted to the way you see.
TD EE (Target) invokes FastLoad for bulk loading into TD Tables. TD EE Stage will create a work table when an append to a Teradata table is selected for the job. 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. Append option will create an empty TD table with ALL fields but NO defaults. It generates this empty table from the Teradata metadata NOT your DataStage job metadata. Also unknown columns are replaced with null.
Ex:
Incoming columns are col1, col2, col3
Target table columns are col1, col2, col3, col4, col5 with col4 default value as 0 and col5 as current_timestamp.
Step 1:
Creation of orch_work table with:
CREATE TABLE ORCH_WORK_xxxxx AS ( SELECT * FROM TargetTable ) WITH NO DATA PRIMARY INDEX (col1)
Note: Orch_work table doesn't keep default values. ie. Orch_work table has col4 and col5 also, but with no default values.
Step 2:
Incoming records are loaded in orch_work with values col1, col2, col3,null,null.
INSERT INTO ORCH_WORK_xxxxx (:col1, :col2, :col3, null, null)
Step 3:
Append using Insert command into Target table:
INSERT INTO TargetTable SELECT * FROM ORCH_WORK_xxxxx
Caution: Step 3 will fail if Col4 and Col5 (or any of them) are set as not null in the Target table. To avoid this you need to pass col4 and col5 with default values inside the job itself.
Now you know DataStage calls a TD utiliy to load the data into your target table and you might have to figure out where data is getting converted to the way you see.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>