DB2 ASCII EXPORT 1

[cristi22]

Hello all -

DB2 V 8.2 on aix.

Need to create a proc to export the data from the table in ASCII delimited format (etc: start from 1 to 9 = ssn, 10 to 40 name & so on)

db2 - a "export to "c:/temp/testfile.txt" select ssn (1,9), name(10,40) from member"

??????

Any help is appreciated!

 

[BrianTyler]

export to c:/temp/testfile.txt of del select ssn,name from member

Personally, I give the file a .csv suffix to identify it as a delimited file.

If the file is to be put back into a DB2 table use an IXF file instead of DEL.

Brian

 

[cristi22]

Thank you Brian for your time!

This file is going to be huge & excel can't handle the number of rows I need. I have to send the text file to the vendor with the specified positions.

It's a project to create a file for the IRS, so I was thinking about (somehow ?? don't know yet) setting up a shell script with a date parameter (because I have to run it more than once) so the user will enter the command & the date for the shell scrip to export the file.

Something like :
!ksh
$ taxes ('2005')
$ vi taxes
$ db2 - a "export ……select …. Where date = '2005'

Any other suggestions / ideas are more than appreciated!!!!
crist.

 

[BrianTyler]

Christi,


Similar to your solution:


I tend to build the SQL in a script, and to let AIX do the substitution.

eg:

LOGFILE=$LOGDIR/temp.log
echo $1
db2 -t -s +v +p -x connect > $LOGFILE

db2 -s -t +p <<FLAG >>$LOGFILE;
export to custextr_$1.csv of del
select ssn, name, .... from custorder where cs_cust_no = $1;
FLAG

The SQL statement can be any number of lines (within reason),
and the system works particularly well when using export to a CSV file to go into Excel etc
When setting up the scripts make sure that there are no trailing spaces after the end of file marker (FLAG in my case)

I am unsure as to the output format required, if it is delimited, the example I have shown is correct (you can change the delimeter from comma to something else if required).
However, if you have to produce fixed length fields with no seperators, you will have to pad them and then concatenate them (remembering that if a field is NULL it must be coalesed before it is concatenated or the whole string will be null)

For example, select
digits(SSN) ||
right((name || ' '),40 ||
etc

This gets very untidy.

Brian