Db2 timestampformat for load statement

I've got a file with timestamps like 20130807183000000. I'm trying to load using timestampformat="YYYYMMDDHHMMSSUUU" and I'm getting the error

SQL0181N The string representation of a datetime value is out of range.

I'm pretty sure the issue is that db2 is interpreting the minutes as months. I've been unable to find any documentation on how to explicitly format it as a minute for the load statement. I tried using 'MI' since that works for the timestamp_format function, but no luck. Does anyone know of any hidden documentation or have any suggestions that don't involve editing the file? Thanks.

Replay

That should work. I created a file like:

[[email protected] src]$ cat apa.csv
20130807183000000

and a table like:

[[email protected] src]$ db2 "create table tt (ts timestamp not null)"

and loaded like:

[[email protected] src]$ db2 "load from apa.csv of del modified by timestampformat=\"YYYYMMDDHHMMSSUUU\" insert into tt"
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file
"/home/lelle/src/apa.csv".

SQL3500W  The utility is beginning the "LOAD" phase at time "07/29/2016
20:35:42.233607".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

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

SQL3519W  Begin Load Consistency Point. Input record count = "1".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "07/29/2016
20:35:42.482947".

Number of rows read         = 1
Number of rows skipped      = 0
Number of rows loaded       = 1
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1

Could it be the reason that you did not quote the " so that the shell got rid of those?

If I do:

[[email protected] src]$ db2 "load from apa.csv of del modified by timestampformat="YYYYMMDDHHMMSSUUU" insert into tt"

I get the error:

SQL3192N  In the filetmod a user specified format "TIMESTAMPFORMAT" beginning with the string "YYYYMMDDHHMMSSUUU" is not valid.

If that is not the problem, can you provide a small sample of rows that fail during load?

Category: db2 Time: 2016-07-29 Views: 5

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.152 (s). 12 q(s)