AIX 5
DB2 8.2

Spent half the day looking through different conversion articles and have not made any progress.


I have a CSV from an oracle export that looks like this
1204141165Ç1Ç3Ç0Ç50ÇIBMPS G_StorageEventÇ27-FEB-08 00:00:00ÇUM_SERVICESÇ169.30.6.45Çsl dn60vegrtp02ÇÇ

I need to import into DB2 where the last_modified_time column is in DB2 timestamp format.

So '27-FEB-08' needs to be converted into

'2008-02-27-00.00.00.000000'


I have tried using the IMPORT modified by parms

db2 "IMPORT FROM /home/test.txt OF DEL MODIFIED BY timestampformat=\"DD-MM-YY\" COLDELÇ DELPRIORITYCHAR /home/test.msg INSERT_UPDATE INTO db2inst1.table"



No luck. Any help with this command or a better way would be appreciated.


Charlie

I think you will be better off specifying an ISO-compliant date format on export.

PKPChuck,
I don't know if there is any way of converting timestamp in Oracle into ISO compilant format. Try looking CHAR function or something like that.

But if you can't get data into correct format you can do that with SQL inside DB2. So import data to DB2 table and then execute select statement to get correct data.

Sample:
create table admin.tab (col1 char(20))
insert into admin.tab values ('27-FEB-08')

select timestamp('20' concat substr(col1,8,2) concat '-' concat case substr(col1,4,3) when 'JAN' THEN '01' WHEN 'FEB' THEN '02' END concat '-' concat substr(col1,1,2) concat '-00.00.00.000000') from admin.tab

The output is: 2008-02-27-00.00.00.000000
Note: continue case syntax for all months.

Hope this helps,
Grofaty