Hello All,
I have a table in db2 having structure
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
DATIME SYSIBM TIMESTAMP 10 0 No
TIMEDIT SYSIBM TIME 3 0 No
LANG SYSIBM SMALLINT 2 0 No
ALERT SYSIBM SMALLINT 2 0 No

I want to export the data of this table to text file and then to import it to Postgresql database. The problem is when I export data from this table with export command as.
Export into table.txt of del modified by timestamp=\"YYYY.MM.DD HH.MM.SS\" select * from table.
everything is fine expect time format into exported file. I am getting time data in format 09.44.22(instead of 09:44:22 or 094422) which is not accepted by postgresql. Can anybody help to work around it so that I can insert table data from text file into postgresql from db2.

From the Command Reference manual:

The export utility normally writes
date data in YYYYMMDD format
char(date) data in ″YYYY-MM-DD″ format
time data in ″HH.MM.SS″ format
time stamp data in ″YYYY-MM-DD-HH. MM.SS.uuuuuu″ format

Data contained in any datetime columns specified in the SELECT statement
for the export operation will also be in these formats.

So it is doing as expected with the TIME data. I can't find any MODIFY option for time. However, one option is to change you Select * to a Select column list and use the CHAR function to force JIS format.

Export into table.txt of del modified by timestamp=\"YYYY.MM.DD HH.MM.SS\" SELECT DATIME, CHAR(TIMEDIT, JIS), LANG, ALERT FROM table

This will get you a time value in the format 09:44:22

Hi Stealth,
Thank you very much. It worked fine. Forcing JIS generated the desired Time format. Thanks you very much once again...


Regards,
Paramjit Joshi