0

I have a file import_file.csv with the below mentioned timestamp format.

Oct/21/2013 09:45:01 AM EDT
Dec/04/2013 04:41:01 PM EST

I created a table in IBM DB2 v9.7

create table table_import_timestamp ( my_timestamp timestamp )

And then tried importing the file.

db2 import from import_file.csv of del messages msg.out insert into table_import_timestamp

I did try with a few modified timestamp format available in db2 import utility but didn't find one that is suitable to import the timestamp with the format mentioned in the file.

Is there a way for me to import this format in to a table without modifying the file and by specifying the timestamp format in my import statement itself?

Abhi
  • 314
  • 1
  • 7
  • 23
  • You could use the `timestampformat` modifier, if it weren't for the time zone specifications in your file. DB2 timestamps do not carry the time zone information, so your data cannot be imported as they are. – mustaccio Dec 01 '15 at 21:36
  • Thanks for the update. So if I cannot import the data from the file as they are, will I at least be able to ignore the timezone in the file within my import statement. I do not want to modify the file that I receive from elsewhere. Just need to load the data what is required. – Abhi Dec 02 '15 at 01:27

1 Answers1

1

If you're willing to lose time zone information, you have a couple of options.

1.Use the INGEST command, which allows you to manipulate input data before inserting:

ingest from file YOURFILE format delimited ($ts char) restart off
  insert into YOURTABLE (TS_COLUMN) 
  values (
   timestamp_format(left(cast($ts as varchar(30)),23),'Mon/DD/YYYY HH:MI:SS AM')
  )

You can even add an extra column to your table to store the time zone offset value, e.g.

ingest from file YOURFILE format delimited ($ts char) restart off
  insert into YOURTABLE (TS_COLUMN,TZ_COLUMN) 
  values (
   timestamp_format(left(cast($ts as varchar(30)),23),'Mon/DD/YYYY HH:MI:SS AM'),
   case when right(cast($ts as varchar(30)),3) = 'EDT' then -4 else -5 end
  )

You will need to install version 10.1 of the DB2 client for that, as INGEST was not available in the 9.7 client. It will still work with the 9.7 server.

2.Relax your "don't want to modify the file" requirement and use something like sed or perl to reformat the data on the fly, e.g.

mkfifo /tmp/mypipe
db2 "import from /tmp/mypipe of del 
modified by timestampformat=\"MMM/DD/YYYY HH:MM:SS TT\" 
insert into YOURTABLE (TS_COLUMN)" &
perl -npe 's/ E(D|S)T(,|$)//' YOURFILE > /tmp/mypipe
rm -f /tmp/mypipe

By using the OS pipe you don't modify the original file and don't use extra disk space either.

mustaccio
  • 18,234
  • 16
  • 48
  • 57