Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    9

    Unanswered: Import TIME(3) column to DB2 table

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

    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:

    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.
    Last edited by markmorgan; 04-19-11 at 16:23. Reason: DB2 version

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2011
    Posts
    9
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •