Terdata Issue

irajasekharhexa · Post by **irajasekharhexa** » Mon Jul 23, 2007 8:45 am

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....

phanee_k · Post by **phanee_k** » Mon Jul 23, 2007 10:49 am

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,

irajasekharhexa · Post by **irajasekharhexa** » Mon Jul 23, 2007 11:42 am

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

phanee_k · Post by **phanee_k** » Mon Jul 23, 2007 11:57 am

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

irajasekharhexa · Post by **irajasekharhexa** » Tue Jul 24, 2007 2:50 am

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

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?

Regds

ray.wurlod · Post by **ray.wurlod** » Tue Jul 24, 2007 4:17 am

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.

throbinson · Post by **throbinson** » Tue Jul 24, 2007 5:57 am

Teradata Multiload EE Stage. It can do a multi-load OR TPUMP (trickle updates).

JoshGeorge · Post by **JoshGeorge** » Wed Jul 25, 2007 9:23 pm

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.