DB2 FOR LUW / DB2FORLUW / A10 - DB2

When INGEST reads TIMESTAMP values from the input source, a 
format can be specified to the TIMESTAMP field on the INGEST 
field list. 
If the TIMESTAMP field format is not specified, then INGEST 
wrongly truncates a TIMESTAMP value to precision 6 (SQL3131W), 
if the TIMESTAMP value has a precision greater than 6. 
 
Example: 
 
Input data (data.del): 
1111-10-2-23.00.00.000000123456 
2222-10-2-23.00.00.000123456789 
3333-10-2-23.00.00.001234567 
 
Test: 
CREATE TABLE T1( C1 TIMESTAMP(12) ) 
 
INGEST FROM FILE data.del FORMAT DELIMITED ($field1 TIMESTAMP) 
RESTART OFF INSERT INTO T1(C1) VALUES($field1) 
 
SQL2922I  The following warning or error occurred while 
formatting data from 
line "1" of input file "data.del". 
SQL3131W  The field containing "1111-10-2-23.00.00.000000123456" 
in row "1" and 
column "1" was truncated into a TIMESTAMP field because the data 
is longer 
than the database column. 
 
SQL2922I  The following warning or error occurred while 
formatting data from 
line "2" of input file "data.del". 
SQL3131W  The field containing "2222-10-2-23.00.00.000123456789" 
in row "2" and 
column "1" was truncated into a TIMESTAMP field because the data 
is longer 
than the database column. 
 
SQL2922I  The following warning or error occurred while 
formatting data from 
line "3" of input file "data.del". 
SQL3131W  The field containing "3333-10-2-23.00.00.001234567" in 
row "3" and 
column "1" was truncated into a TIMESTAMP field because the data 
is longer 
than the database column. 
 
Output: 
SELECT C1 FROM T1 ORDER BY C1 
 
C1 
---------------------------------------- 
1111-10-2-23.00.00.000000000000 
2222-10-2-23.00.00.000123000000 
3333-10-2-23.00.00.001234000000

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 LUW, INGEST command.                                     * 
* Serial, DPF, SD or in any mode.                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v 10.5 FP4.                                       * 
****************************************************************

The INGEST command must set by default the precision the maximum 
(12). This way, the values ingested into the table will adhere 
to the precision defined on the target table column. 
 
CREATE TABLE T1( C1 TIMESTAMP(12) ) 
 
INGEST FROM FILE data.del FORMAT DELIMITED ($field1 TIMESTAMP) 
RESTART OFF INSERT INTO T1(C1) VALUES($field1) 
 
Output: 
SELECT C1 FROM T1 ORDER BY C1 
 
C1 
---------------------------------------- 
1111-10-2-23.00.00.000000123456 
2222-10-2-23.00.00.000123456789 
3333-10-2-23.00.00.001234567000

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

When INGEST reads TIMESTAMP values from the input source, a 
format can be specified to the TIMESTAMP field on the INGEST 
field list. 
If the TIMESTAMP field format is not specified, then INGEST sets 
default precision to the maximum (i.e. 12). This way, the values 
ingested into the table will adhere 
to the precision defined on the target table column, rather than 
wrongly truncates a TIMESTAMP value to precision 6 (SQL3131W), 
when the TIMESTAMP value has a precision greater than 6.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

21.02.2014
09.09.2014
09.09.2014