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
Timestamp corruption using Oracle Enterprise stages
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 8
- Joined: Wed Aug 30, 2006 3:50 am
- Location: the Netherlands
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Premium Member
- Posts: 8
- Joined: Wed Aug 30, 2006 3:50 am
- Location: the Netherlands
- Contact:
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?
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?
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
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
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Premium Member
- Posts: 8
- Joined: Wed Aug 30, 2006 3:50 am
- Location: the Netherlands
- Contact:
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!
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!