SEO

Hi all,

I found this ideas (below) from an old post, my question is, if I
choose alternative number 3, with two selects and one for the header,
is there a way to automate that code? I mean, without having to
hardcode the name of each field?

Regards

DW Marker wrote:
> All -

> I am trying to figure out how to get a comma delimited file from the
> command line which includes the column headings as the first line. I
> know how to create a del file using export, but it doesn't put the
> headings. I also know I could redirect the output from a select, but a
> select isn't comma delimited... Any ideas?

Lots of options:

(1) write to the file and phrase the query in the export in such a way
that
the first row produces the required headings
(2) use EXPORT and subsequently modify the file to prepend the header
(3) use a simple SELECT statement, redirect the output to a file, and
along
the way ensure that the first row contains the header. You can do that
by
redirecting two SELECT statements to the same file.
(4) Use a separate file for the header. That avoids the whole issue
from
the beginning.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Assuming that your second query is in "select * from ...." format

you can use catalog tables. (i.e. sysibm.columns) in your first select

SELECT NAME FROM SYSIBM.COLUMNS WHERE TNAME = '....'

regards,

I've been challenged by this in the past since I'm require to deliver
data sets to other systems on a recurring basis. Since DB2 can output a
nicely formatted and largely compatible CSV format through its EXPORT
utility, I cobbled together a UNIX Korn shell script to do the dirty
work for me. It's not elegant, but works for me.

It's currently implemented for AIX and relies on the output from the
DESCRIBE utility (v 7.2) to get the column headers as they would be
returned in a cursor. (It may have to be tweaked if the format has
changed for later versions of DB2 or other types of OS.) It assumes you
have an SQL statement terminated with a semi-colon in a file which
would be passed to the script as the first argument. I named the script
"csv" and would execute it like this:

$> csv calctotal.sql

which create an output file called:

calctotal.sql.csv



Here's the script:
------------------------------------------------------------------------------------------------
#!/bin/ksh

db=yourdatabase
dbuser=youruserid
dbpasswd=yourpassword

FILE=$1
tmpfile=/tmp/csv.$$
outfile=$FILE.csv

## connect to database
db2 connect to $db user $dbuser using $dbpasswd

## create statement to materialize headers
print "describe" > $tmpfile
cat $FILE >> $tmpfile

## get column headers from db2 output and
## assemble into comma separated list
db2 -tf $tmpfile|grep -E "^ 3|^ 4"|cut -c32-62|while read colname
do
colhdr="$colhdr,$colname"
done

## strip of leading comma
colhdr=$(echo $colhdr|cut -f2- -d,)

## create file used to export
print "export to /tmp/data.$$ of del" > $tmpfile
cat $FILE >> $tmpfile

## execute export SQL
db2 -tf $tmpfile

## assemble headers + data
print $colhdr > $outfile
cat /tmp/data.$$ >> $outfile

## clean up
rm /tmp/*.$$

Thanks very much for your help, I'll try this!

Bye!

esmith2112 ha escrito:

> I've been challenged by this in the past since I'm require to deliver
> data sets to other systems on a recurring basis. Since DB2 can output a
> nicely formatted and largely compatible CSV format through its EXPORT
> utility, I cobbled together a UNIX Korn shell script to do the dirty
> work for me. It's not elegant, but works for me.
>
> It's currently implemented for AIX and relies on the output from the
> DESCRIBE utility (v 7.2) to get the column headers as they would be
> returned in a cursor. (It may have to be tweaked if the format has
> changed for later versions of DB2 or other types of OS.) It assumes you
> have an SQL statement terminated with a semi-colon in a file which
> would be passed to the script as the first argument. I named the script
> "csv" and would execute it like this:
>
> $> csv calctotal.sql
>
> which create an output file called:
>
> calctotal.sql.csv
>
>
>
> Here's the script:
> ------------------------------------------------------------------------------------------------
> #!/bin/ksh
>
> db=yourdatabase
> dbuser=youruserid
> dbpasswd=yourpassword
>
> FILE=$1
> tmpfile=/tmp/csv.$$
> outfile=$FILE.csv
>
> ## connect to database
> db2 connect to $db user $dbuser using $dbpasswd
>
> ## create statement to materialize headers
> print "describe" > $tmpfile
> cat $FILE >> $tmpfile
>
> ## get column headers from db2 output and
> ## assemble into comma separated list
> db2 -tf $tmpfile|grep -E "^ 3|^ 4"|cut -c32-62|while read colname
> do
> colhdr="$colhdr,$colname"
> done
>
> ## strip of leading comma
> colhdr=$(echo $colhdr|cut -f2- -d,)
>
> ## create file used to export
> print "export to /tmp/data.$$ of del" > $tmpfile
> cat $FILE >> $tmpfile
>
> ## execute export SQL
> db2 -tf $tmpfile
>
> ## assemble headers + data
> print $colhdr > $outfile
> cat /tmp/data.$$ >> $outfile
>
> ## clean up
> rm /tmp/*.$$