Modified field in logs : wrong dates

fanfanlat · Post by **fanfanlat** » Tue Jun 26, 2012 7:43 am

Hello !

I'm asking a question to Datastage gurus because I'm totally lost here.

I've got a simple parallel job with an Oracle connector as a source, then a simple transformer and an ODBC stage as the target.

The target is actually an SQL Server Database.

I get wrong results in the date fields in the target compared to the source and I don't understand why.

I've tried quite a lot of things :
Trying to use a TO_CHAR function in source and then specify a varchar column in the transformer and then use a StringtoTimestamp in the transformer with the appropriate format.
Trying to disable the RCP because apparently, there is a possible mismatch in the datatypes used for dates.

And the thing is that from one execution to another, the behavior may be different. When I run the job for the same period, the results are different. Sometimes, the dates in target are wrong and just after (without touching anything but relaunching the job!), they are correct !

In the Job Logs, the only thing I got is the following :

Modified field: START_DATE, attribute: DATE_PATTERN. Design-time value: N/A. External value: N/A
Modified field: END_DATE, attribute: DATE_PATTERN. Design-time value: N/A. External value: N/A
Modified field: TRANSACTION_DATE_TIME, attribute: DATE_PATTERN. Design-time value: N/A. External value: N/A
Modified field: ORIG_TRANSACTION_DATE_TIME, attribute: DATE_PATTERN. Design-time value: N/A. External value: N/A

and all those fields are the ones that are dates and may have wrong data !
Apparently, datastage is a bit lost with the data types and is modifying something but I don't know what ! These messages are information only and not warnings...

I really don't undertsand.

If you have any clue on this, please help !

Thanks in advance,

Kryt0n · Post by **Kryt0n** » Tue Jun 26, 2012 4:21 pm

What is the source field coming in as: date or string? Have you attempted anywhere to define the format string of the date?

Try output your records to a flat file and see if any consistency comes to your dates (two tests: straight to flat file and through transformer). Will give a better idea where it is going wrong.

sreewin7 · Post by **sreewin7** » Tue Jun 26, 2012 5:04 pm

Are you using , user defined query or Auto generated, check the date format of the all date columns,if you are using user defined, try to check the date format example:- dd/mon/yyyy
All the best.

fanfanlat · Post by **fanfanlat** » Wed Jun 27, 2012 1:23 am

The source field is an Oracle DATE field.

I've tried to convert it to a varchar field, using the following syntax :
TO_CHAR(TRANSACTION_DATE,'YYYY-MM-DD HH24:MI:SS').

I've populated the data into a flat file and I got the good values (the same as in the source) but it's still wrong in the SQL Server table.

There must be something in the target that is not correctly defined but I can't find what...

For example, on one record, I've got this value in the source :

2012-06-26 21:11:45.000

In the flat file, I got the same value : 2012-06-26 21:11:45.000

And in the SQL Server table, I got this one : 2012-06-26 10:38:05.000

And in the same jobs, I've got two other fields that are also DATES in the source but without the time defined.

For this same record, the two values are 2012-08-01 and 2012-08-07

and in the target, I get 2012-07-04 and 2012-07-05 !

And yes, it is a user defined query and I've changed all the fields that were dates in Varchar to be sure that the format was good... but I have still the same behavior !

fanfanlat · Post by **fanfanlat** » Wed Jun 27, 2012 2:30 am

Is it possible that the INSERT statement and the UPDATE statement make these differences ?

Apparently, when the insert is made, the date is wrong and when the record is updated, it's correct !

The target is on ODBC. Can the ODBC driver cause this problem ?

The SQL is generated and not user-defined...

If someone already faced this problem, please share your knowledge...

fanfanlat · Post by **fanfanlat** » Wed Jun 27, 2012 6:00 am

The good news is that I succeeded in reproducing the problem !

When the ODBC stage is doing ONLY inserts, everything is working properly !

But if there is one update only, then, all the inserts are wrong (for the dates)!

I think there is a bug somewhere...

As a workaround, I think I will split the update/insert into two links, one for update and the other for insert but i'm a bit afraid of the deadlocks it may cause (in SQL Server)...

Any other suggestion ?

fanfanlat · Post by **fanfanlat** » Wed Jun 27, 2012 6:02 am

Changing the array size and record count to 1 is also working...

Apparently, there is something wrong with the values stored in memory or something... and when the DB is making commits for each line, then, there is no problem anymore...

A bit strange...

Kryt0n · Post by **Kryt0n** » Wed Jun 27, 2012 4:18 pm

It would suggest there is actually something wrong with your update statement in that it is updating records it shouldn't. Are you sure the update keys make the record unique?