Timestamp corruption using Oracle Enterprise stages

epaardekooper · Post by **epaardekooper** » Thu Dec 23, 2010 10:34 am

Hello,

When retrieving a timestamp column from an Oracle source and load it into an Oracle source on another server, some (not all!) of the timestamp fields are getting corrupted.
A normal date like '30-9-2010' will be corrupted, but the dateformat is ok.

In the source and target databases this column is of the datatype DATE.
In datastage the columns are defined as timestamp of 19 positions.

When using an Upsert statement we get this warnings/failure:

StageTarget,0: rtpexecutil.signalHandler(int)() at 0xd2a39034
StageTarget,0: .() at 0xf05c
StageTarget,0: ttcclr(??, ??, ??, ??, ??, ??, ??, ??) at 0xd476bad8
StageTarget,0: ttcedtm2dtm(??, ??, ??, ??, ??, ??, ??, ??) at 0xd47707b4
[....more of these messages]
StageTarget,0: Operator terminated abnormally: received signal SIGSEGV
main_program: Step execution finished with status = FAILED.

Can anyone help us were to look for a solution?

We do have a workaround, but because all our jobs use the Upsert write method, we do not want to change this.

Workaround 1: When changing to a Write method to Load & Replace there will be no timestamp corruption.

Workaround 2: Also when I add a transformer with and include a StringToTimestamp, the timestamp will process the right date.

Server OS version AIX 5.3.11.0
Datastage EE version: 7.5.2
Datastage NLS: UTF-8 (project default) & ISO-8859-1
Oracle Connection: Oracle Enterprise stage
Job type: parallel job (4nodes)
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

chulett · Post by **chulett** » Thu Dec 23, 2010 11:09 am

That's... odd. Have you determined the pattern here, which dates are ok and which get corrupted? What are the NLS_DATE_FORMAT settings for your instances?

ray.wurlod · Post by **ray.wurlod** » Thu Dec 23, 2010 3:36 pm

DataStage is pedantic about date formats. For example if 30-09-2010 is OK then 30-9-2010 is not. Make sure that you return an absolutely consistent date format, with two digits each for day and month. YYYY-MM-DD is preferred as this is the default format in DataStage.

epaardekooper · Post by **epaardekooper** » Mon Jan 03, 2011 8:58 am

Thanks for your help.

There is no pattern in this, the corrupted dates looks random. Running the script several times will cause the same result, but this result changes in time. We do not know what activity is done on the source systems that changes the result.

Indeed there are some differences in NLS_DATE_FORMATS:

SOURCE SYSTEM ==>
NLS_DATE_FORMAT=DD-MON-RR
NLS_LANG=American_America.UTF8

SQL> select sysdate from dual;

SYSDATE
---------------
03-JAN-11

TARGET SYSTEM ==>
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
NLS_LANG=AMERICAN_AMERICA.US7ASCII

SQL> select sysdate from dual;

SYSDATE
-------------------
2011-01-03 14:40:11

The Import/Export functionality of Oracle can cope with these differences, but datastage apparently not.
What do you suggest in this case? Is this a bug in DataStage?

BI-RMA · Post by **BI-RMA** » Mon Jan 03, 2011 9:31 am

Hi,

what is relevant for DataStage is not the NLS_DATE_FORMAT on any of the server machines, but the one specified on the Oracle-Client you are using to connect to both Servers, which should be installed on the DataStage-Server in the Engine-Tier.

kind regards

Roland

epaardekooper · Post by **epaardekooper** » Fri Jan 28, 2011 6:10 am

Now we have done a lot of technical changes and promoted the new code in a new DataStage project, the issue does not occur in the new environment anymore. In the old environment the issue still occur, so the cause should be found in some project settings.

Although we never found a reason or solid solution, we are not putting more effort in understanding this odd situation.

Many thanks for your help!