DB2 Connector Warning for reading timestamp

manojbh31 · Post by **manojbh31** » Thu Mar 03, 2016 7:52 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 03, 2016 5:13 pm

Can you verify that the Precision and Scale propertes are set to 22 and 10 respectively?

manojbh31 · Post by **manojbh31** » Fri Mar 04, 2016 6:53 am

Yes Ray precision and scale are set to 12 and 10 which are defined as per table definition

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Fri Mar 04, 2016 10:46 am

If you read carefully - he said "22,10" not "12,10".

chulett · Post by **chulett** » Fri Mar 04, 2016 11:11 am

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!

manojbh31 · Post by **manojbh31** » Fri Mar 04, 2016 3:14 pm

I tried with 22,10 and 12,10 also but still I am getting error.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 04, 2016 3:16 pm

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).

qt_ky · Post by **qt_ky** » Sat Mar 05, 2016 8:31 am

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.

qt_ky · Post by **qt_ky** » Sat Mar 05, 2016 8:43 am

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.