Page 1 of 1
DB2 Connector Warning for reading timestamp
Posted: Thu Mar 03, 2016 7:52 am
by manojbh31
Hi, I am using DB2 Connector to read data from DB2 table. there is column with timestamp (12,10) in table when I am reading this field using DB2 Connector stage I am getting below error.
When reading database column DATETIME(fraction=10) into column DATETIME(fraction=0), truncation, loss of precision or data corruption can occur.
Before posting I did search similar issue, but the solutions provided in other posts is not working. And I cannot use CAST function to change to VARCHAR. I have turned off Fail in size mismatch. I am using timestamp (12,10) in db2 connector stage.
Please help.
Posted: Thu Mar 03, 2016 5:13 pm
by ray.wurlod
Can you verify that the Precision and Scale propertes are set to 22 and 10 respectively?
Posted: Fri Mar 04, 2016 6:53 am
by manojbh31
Yes Ray precision and scale are set to 12 and 10 which are defined as per table definition
Posted: Fri Mar 04, 2016 10:46 am
by asorrell
If you read carefully - he said "22,10" not "12,10".
Posted: Fri Mar 04, 2016 11:11 am
by chulett
Pardon my ignorance of all things DB2 but what exactly would a TIMESTAMP(12,10) be? I could understand TIMESTAMP(10) or maybe even TIMESTAMP(12) but I can't find anything online where DB2 supports anything other than just TIMESTAMP... no mention of precision or 'fraction' needed.
Thanks for the opportunity to learn me something today!
Posted: Fri Mar 04, 2016 3:14 pm
by manojbh31
I tried with 22,10 and 12,10 also but still I am getting error.
Posted: Fri Mar 04, 2016 3:16 pm
by ray.wurlod
DataStage's understanding is governed by Precision and Scale. Precision is the total number of characters, while Scale is the number of fractional seconds.
A timestamp with no fractional digits is 19 characters long. Thus, for ten fractional digits, you really need 30,10 (not 22,10).
Posted: Sat Mar 05, 2016 8:31 am
by qt_ky
According to the DB2 docs:
A timestamp is a six-part or seven-part value (year, month, day, hour, minute, second, and optional fractional second) with an optional time zone specification, that represents a date and time.
The time portion of a timestamp value can includes a specification of fractional seconds. The number of digits in the fractional seconds portion is specified using an attribute in the range from 0 to 12 with a default of 6.
If you want a timestamp with a specified precision, the special register can be referenced as CURRENT TIMESTAMP(integer), where integer can range from 0 to 12. The default precision is 6.
Posted: Sat Mar 05, 2016 8:43 am
by qt_ky
Actual queries:
db2 => SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1
2016-03-05-09.25.17.473106
db2 => SELECT CURRENT TIMESTAMP(12) FROM SYSIBM.SYSDUMMY1
2016-03-05-09.25.24.036628000000
db2 => SELECT CURRENT TIMESTAMP(12,10) FROM SYSIBM.SYSDUMMY1
SQL0104N An unexpected token "," was found following "CURRENT TIMESTAMP(12".
Expected tokens may include: ")". SQLSTATE=42601
-- no such thing within DB2
Another thing I noticed on DB2 10.5 is that no matter how many times I ran the above query with (12), the last 6 fractional seconds were always zeros.