Import TIME(3) column to DB2 table

markmorgan · #1 (**permalink**) 04-19-11, 15:01

I am importing an SQL standard TIME(3) column into DB2 form a CSV file using the IMPORT utility.

The source database that produced the export has the times in the format TIME(3) i.e. HH:MM:SS.UUU. There are no options on the CSV export to manipulate this.

DB2 only supports TIME with no decimal places (effectively TIME(0)).

If I try the following to specify the source time format on the import it fails with "SQL3192N In the filetmod a user specified format "TIMEFORMAT" beginning with the string ""HH:MM:SS.UUU"" is not valid."

Quote:

IMPORT FROM "filename.csv" OF DEL
MODIFIED BY dateformat="YYYY-MM-DD"
timeformat="HH:MM:SS.UUU"
timestampformat="YYYY-MM-DD HH:MM:SS.UUU"
coldel|
nochardel
keepblanks
codepage=1252
REPLACE INTO tablename (col1, col2, ...);

I was hoping that the import utility could drop the decimal places on the import and just give me a warning.

So I tried:

Quote:

IMPORT FROM "filename.csv" OF DEL
MODIFIED BY dateformat="YYYY-MM-DD"
timeformat="HH:MM:SS"
timestampformat="YYYY-MM-DD HH:MM:SS.UUU"
coldel|
nochardel
keepblanks
codepage=1252
REPLACE INTO tablename (col1, col2, ...);

Which fails with "SQL3191N The field in row "1", column "12" which begins with "09:00:00.000" does not match the user specified DATEFORMAT, TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected."

Does anyone know of a way to get the data imported?

DB2 9.7.2 on Windows

Thanks,
Mark.

n_i · #2 (**permalink**) 04-20-11, 07:38

If you cannot modify the input file (a perl one-liner would do the trick in most cases), you could load data into a staging table, treating the field in question as a string, and subsequently load them into the target table, manipulating the time string in the process and converting it into a TIME value.

stolze · #3 (**permalink**) 04-20-11, 13:36

TIME(3) is not a standard SQL data type. The SQL standard from 2003 says in subclause 4.6.2 that no fractions of seconds are available. So everything you find in this respect are product-specific extensions.

The short version for DB2 is that there is no TIME(3) data type available and you have to use either a TIMESTAMP data type, which does support fractions, or you have to modify your input data to get rid of those fractions.

markmorgan · #4 (**permalink**) 04-27-11, 12:21

Thanks guys.

I've gone with creating a Perl script to transform the data as I was having problems with carriage returns within string data too. The delprioritychar option should have sorted that out but it conflicts with the nochardel option I was using as the string data is not enclosed in quotes.

Mark.

tonkuma · #5 (**permalink**) 04-27-11, 14:38

I could import by timestampformat="HH:MM:SS.UUU".

But, I got error SQL3017N by coldel|.

Here is the test result.

Create test table:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE test_import_time3
( key INTEGER NOT NULL
, tm  TIME
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Contents of imported file(test_import_time3.del):

Code:

1!13:57:09.246
2!02:03:04.005

Successful:

Code:

------------------------------ Commands Entered ------------------------------
IMPORT FROM d:\DM_Tech\dBforums\test_import_time3.del OF DEL
 MODIFIED BY timestampformat="HH:MM:SS.UUU"
 coldel! nochardel
 REPLACE INTO test_import_time3;
------------------------------------------------------------------------------
SQL3109N  The utility is beginning to load data from file 
"d:\DM_Tech\dBforums\test_import_time3.del".

SQL3418W The NOCHARDEL file type modifier should not be specified if ...
...

SQL3130W  The field containing "13:57:09.246" in row "1" and column "2" was 
truncated into a TIME field because the data is longer than the database 
column.

...
...

Number of rows read         = 2
Number of rows skipped      = 0
Number of rows inserted     = 2
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 2


------------------------------ Commands Entered ------------------------------
SELECT * FROM test_import_time3
;
------------------------------------------------------------------------------

KEY         TM      
----------- --------
          1 13:57:09
          2 02:03:04

  2 record(s) selected.

Failed:

Code:

------------------------------ Commands Entered ------------------------------
IMPORT FROM d:\DM_Tech\dBforums\test_import_time3.del OF DEL
 MODIFIED BY timestampformat="HH:MM:SS.UUU" coldel| nochardel
 REPLACE INTO test_import_time3;
------------------------------------------------------------------------------
SQL3109N  The utility is beginning to load data from file 
"d:\DM_Tech\dBforums\test_import_time3.del".

SQL3418W  The NOCHARDEL file type modifier should not be specified if the data 
was exported using DB2. It is provided to support vendor data files that do 
not have character delimiters.

SQL3017N  A delimiter is not valid or is used more than once.

SQL3110N  The utility has completed processing.  "0" rows were read from the 
input file.

Internet Services

Web Development

Digital Marketing

Consumer Tech