Timestamp data loading

arshi · Post by **arshi** » Mon May 04, 2009 4:59 am

Hi,

Facing the problem while loading the data from DB2 to Oracle , The Timestamp column data in the source not loaded correctly to Target .

Using DB2 as source and Oracle as Target.
Datastage server edition : 7.2.1

Source data like : 2009-03-06 13:40:02.000000 Datatype :VARCHAR of length 28
Target loaded as : 06-MAR-20 09.00.00.000000 Datatype :VARCHAR of length 38

Logic used in Transformer derivation for this column as :
Oconv(Iconv((DSLink5.REC_UPDT_DT_TM[1,4]:"-":DSLink5.REC_UPDT_DT_TM[6,2]:"-":DSLink5.REC_UPDT_DT_TM[9,2]),"D-YMD[4,2,2]"),"D-DMY[2,A3,4]")

Here in Target data loaded as 20 09 instead of 2009 and time not showing correctly.Can you provide any solution for this.

Regards,
Arshi

chulett · Post by **chulett** » Mon May 04, 2009 6:40 am

Is the Oracle target a DATE or a true TIMESTAMP? Is your target DML using a TO_DATE() or TO_TIMESTAMP() function?

Kryt0n · Post by **Kryt0n** » Mon May 04, 2009 5:09 pm

Change your output to a flat file and check your dates are converting properly. To me it suggests somewhere along the line you have defined the year to be 2 digits (somewhere within Oracle phase)

What is your full transform rule? As you are adding time, there must be more to it.

PS Iconv(DSLink5.REC_UPDT_DT_TM[1,10]... should suffice rather than breaking it to individual components