timestamp over-scale issue in 8.5

[tcheslav]

Hi all.

Recently we moved from IBM IS 8.1 to 8.5.
Jobs were migrated and some issues appeared...
Here is one of them:

In 8.1
For the timestamp data type if You set scale higher than in database, engine throws warnings, but timestamp data is OK. Microseconds are available.

In 8.5
For the timestamp data type if You set scale higher than in database, engine throws warnings, but timestamp data is NOT OK. Microseconds are truncated.

Does anybody saw this issue before?
Maybe there is some variable that You can set to 8.5 would behave like 8.1 regarding overscale with timestamp?

This issue is very annoing because we would have to change zillion of stages to correct this...

Regards
Tcheslav

[SURA]

If you post the warning along with this post would be great!!

There should be an check box to choose the microsecond when you double click the column.

Ensure that option is selected!

DS User

[tcheslav]

OK.

I'll try to add more details:

I have three timestamp fields that I read from database and write to seq file. Here are the field settings:

CZAS1 - sql type : timestamp, extend : microseconds, length: null, scale : null
CZAS2 - sql type : timestamp, extend : microseconds, length: 27, scale : null
CZAS3 - sql type : timestamp, extend : microseconds, length: 27, scale : 7

All three fields have same type and value in database.
Below are peeks from those fields:

Peek_20,1: id:2 czas1:1960-01-01 12:55:01.123456 czas2:1960-01-01 12:55:01.123456 czas3:1960-01-01 12:55:01.000000

As You can see microseconds for CZAS3 are truncated!

Below is the only warning that I got:

ODBC_Connector_6: Schema reconciliation detected a size mismatch for column CZAS3. When reading database column DATETIME(fraction=6) into column DATETIME(fraction=0), truncation, loss of precision or data corruption can occur.

Why fraction is 0 while scale is set to 7?!?

In 8.1 this worked without any problems...

Regards
Bartek

[chulett]

I'm guessing that it is because 7 is an illegal value and that you got away with it in 8.1 but that 'loophole' has now been closed. Where did the 7 come from?

[tcheslav]

Where did the 7 come from?

It came from developers
Now admins have to deal with it

[chulett]

LOL... obviously I was looking for something a little more specific than that but perhaps that is all that is known.

[tcheslav]

Now.

Anybody have any idea how to workaround this?
Or how to quickly change this in zillion of stages inside projects?

Regards
Bartek