Export DB2 Data To Fixed Length Text File

jtrapat1 used Ask the Experts™
I need to extract some data from db2 to a fixed length file so that it can be read by some Microsoft Access macros.
I don't think I'll have to join two or more tables - it may be coming all from one table.
(I'll post more info later, if this is the case.)

I need to be able to set the starting and ending positions of these fields so that the macro from access can move the data into its matching fields in the access tables.

Is there a way to do this from db2?

I was thinking of using the export utility to create a text file but I don't know too much about it.
The main thing is that I need to specify the column positions.

Thanks in Advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here is an example:
db2 "export to temp of del modified by coldel;
      select col1,...,coln from staff where dept = 20"
For more, check out

Once having the delimited text, some tools/commands
(for example, the 'tr' command on unix/linux) may be
used to convert it to a fixed length format.

Good luck.


Thanks for the help.
Do you know if there is any way I can strip out the single quotes that get inserted around each data value?
Here's the statement I used:

db2 "EXPORT TO test1.txt OF DEL MODIFIED BY CHARDEL'' SELECT cast(adds_id as char(5)), substr(comment1,1,80),substr(comment2_priv,1,80) FROM nysa.bp12t_approp_adds"

And, here's one record output to the file:
'271  ','PART OF $27.5M REQUEST; FIRST PRIORITY                                          ','

I know it's wrapping here but it's coming out the way I'd like. I would just have to change the starting positions in Access to account for the single quotes.

Thanks again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial