importing data with date format

[E.Bartosiewicz]

I have several files with data I would like to import into DB2, but I
have timestamps set in a format, which DB2 can't catch - DD-MM-YYYY
HH:MM:SS

DB2 wants to get the year first. Is there a way I can say that my data
is going to come in this format or do I need to convert it outside the
database?

--
Ewa

[Serge Rielau]

E.************@gmail.com wrote:

I have several files with data I would like to import into DB2, but I
have timestamps set in a format, which DB2 can't catch - DD-MM-YYYY
HH:MM:SS

DB2 wants to get the year first. Is there a way I can say that my data
is going to come in this format or do I need to convert it outside the
database?

Which version of DB2?
You can load the data into a table with a generated column and let the
generated column convert the string into a timestamp.
Then you alter the generated column to a normal column and drop the
staging column.
Reorg.. done.

In DB2 9.5 for LUW the TIMESTAMP_FORMAT (aka TO_DATE) function can do
this conversion easily. Prior to DB2 9.5 you have to reshuffle the
timestamp string with substr and concat and then pass it into the
TIMESTAMP function

Of course you can always to a regular LOAD into a string column to a
staging table and then LOAD FROM CURSOR to the final table. Doing the
transformation in the cursor.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[E.Bartosiewicz]

I found what I was looking for in the DB2 Control Center where in the

import function I can define the date format which I have in my file,
but I have another problem now. During the import I get an error, that
says that the data in my first column and first row can't be converted
into an INTEGER. My data looks like this:

1,01-01-2007 00:00:00,S,0,30-10-2007 13:02:51:357,30-10-2007
13:02:51:357,inf39dl,inf39dl
2,02-01-2007 00:00:00,R,0,30-10-2007 13:02:51:387,30-10-2007
13:02:51:387,inf39dl,inf39dl
3,03-01-2007 00:00:00,R,0,30-10-2007 13:02:51:387,30-10-2007
13:02:51:387,inf39dl,inf39dl

Any ideas? Should the integer values be formatted in some special way?

Hmmm... I wonder... Just for kicks add a space after that first comma.
Maybe in your local a 1,01 is a decimal 1.01 ???
I'm guessing....

That was a long shot ;) Unfortunately that's not the case... I'm
getting another error that says something about not importing all of
the data after the 8 column (inf39dl) in the first row (and then the
same 2 errors for the rest od the rows in the file), so he gets all
the columns right and it's definitely a problem with the "1"... I just
can't get it...

--
Ewa

[Serge Rielau]

E.************@gmail.com wrote:

That was a long shot ;) Unfortunately that's not the case... I'm
getting another error that says something about not importing all of
the data after the 8 column (inf39dl) in the first row (and then the
same 2 errors for the rest od the rows in the file), so he gets all
the columns right and it's definitely a problem with the "1"... I just
can't get it...

Hmmm... while we're doing long shots:
1,01-01-2007 00:00:00,S,0,30-10-2007 13:02:51:357,30-10-2007
13:02:51:357,inf39dl,inf39dl
~~~~~~~~~~~~
This cannot be converted to integer. Wrapping of the message or a
problem in your file?

I think to help further we'd need to see the IMPORT statement ("show
SQL" in control center?) and the table definition.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[E.Bartosiewicz]

That was a long shot ;) Unfortunately that's not the case... I'm

getting another error that says something about not importing all of
the data after the 8 column (inf39dl) in the first row (and then the
same 2 errors for the rest od the rows in the file), so he gets all
the columns right and it's definitely a problem with the "1"... I just
can't get it...

Hmmm... while we're doing long shots:
1,01-01-2007 00:00:00,S,0,30-10-2007 13:02:51:357,30-10-2007
13:02:51:357,inf39dl,inf39dl
~~~~~~~~~~~~
This cannot be converted to integer. Wrapping of the message or a
problem in your file?

Sorry, wrapping...

I think to help further we'd need to see the IMPORT statement ("show
SQL" in control center?) and the table definition.

CONNECT TO DEVDB USER "devdb";
IMPORT FROM "C:\DB\dane.txt" OF DEL MODIFIED BY TIMESTAMPFORMAT="DD-MM-
YYYY HH:MM:SSTT" COLDEL, METHOD P (1, 2, 3, 4, 5, 6, 7, 8) MESSAGES "C:
\DB\komunikaty.txt" INSERT INTO DEVDB.KALENDARZ ("id_kalendarza",
"dzien_w_kalendarzu", "status_dnia", "id_typu_kalendarza",
"kiedy_utworzono", "kiedy_modyfikowano", "kto_modyfikowa³",
"kto_utworzy³");
CONNECT RESET;

"id_kalendarza" INTEGER 4 Nie
"dzien_w_kalendarzu" TIMESTAMP 10 Nie
"status_dnia" CHARACTER 1 Nie
"id_typu_kalendarza" INTEGER 4 Nie
"kiedy_utworzono" TIMESTAMP 10 Nie
"kiedy_modyfikowano" TIMESTAMP 10 Nie
"kto_modyfikowa³" VARCHAR 16 Nie
"kto_utworzy³" VARCHAR 16 Nie

I hope this will give a better picture (sorry for the polish names, I
didn`t have the inspiration to do the translating ;))

Thanks a lot for your help!

--
Ewa

[E.Bartosiewicz]

I hope this will give a better picture (sorry for the polish names, I

didn`t have the inspiration to do the translating ;))

Found it!

It was an encoding problem... just figures...

Thanks a lot for all your help :)

--
Ewa