By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,278 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,278 IT Pros & Developers. It's quick & easy.

Export Command - How to generate a row with column headers

P: n/a
I have been using the export command for quite some times to export
comma delimited file. I have always been looking at how to generate
the first row with the column header but never found a clean way to do
it. I have searched this newsgroup and the only suggestion found was
to cast all column to strings and do a union, or to add the first row
afterwards and I saw one suggestion where it was to create a unix
script.

Does anybody knows how to do this ? Or can you let me know if it is
not possible for comma delimited list to produce an output where the
first row contains the column header?

I found one way of doing it but it is sort of a HACK. You use WSF
format instead of DEL for the export and you can open that binary
output with Excel. (It's a WKS 1.2.3 file... Lotus note) For Example,
you run:
Db2export to "C:\you_file.xls" of WSF select * from your_table

Then, after you open the above file with Excel, you can save it as a
CSV format. The new file in the CSV format now contains the column
header as the first row.

Oct 12 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

Here is a sample DOS BAT file (Although, this is not so elegant, it
will work):
DB2 "EXPORT TO Dept_Data.txt OF DEL SELECT * FROM DEPARTMENT"
DB2 "EXPORT TO Dept_Header.txt OF DEL Modified by NoCharDEL SELECT
SUBSTR(REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME c,
colname) ORDER BY colno) AS VARCHAR(100)),'<C>',', '),'</C>',''),3)
FROM syscat.columns WHERE tabschema='DB2ADMIN' AND
tabname='DEPARTMENT'"
copy Dept_Header.txt+Dept_Data.txt Dept_with_Header.txt

Oct 13 '06 #2

P: n/a
That works great thanks! ...but I had to remove the nochardel.

For some odd reason it doesn't work and give this error:

SQL3016N An unexpected keyword "nochardel" was found in the filetmod
parameter for the filetype.

I type "db2 ? export" and it shows these options that are available for
the DB version I have ( Database server = DB2/6000 8.2.2)

NODOUBLEDEL, LOBSINFILE, CHARDELx, COLDELx, DLDELx
DECPLUSBLANK, DECPTx, DATESISO, 1, 2, 3, 4, CODEPAGE=x
Is it possible with the options above to produce the same behavior as
nochardel? For example, somehow pass a space to CHARDELx?

Oct 13 '06 #3

P: n/a
I think RS/6000 is already get minor.
I also don't know DB2/6000.
But, if you know RS/6000 script, it would be easy to remove first
character and last character of a file.
So, my idea is as fllowing:
DB2 "EXPORT TO Dept_Data.txt OF DEL SELECT * FROM DEPARTMENT"
DB2 "EXPORT TO Dept_Header.txt OF DEL SELECT
SUBSTR(REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME c,
colname) ORDER BY colno) AS VARCHAR(100)),'<C>',', '),'</C>',''),3)
FROM syscat.columns WHERE tabschema='DB2ADMIN' AND
tabname='DEPARTMENT'"
/* insert OS/script to remove first and last character of Header.txt */
copy Dept_Header.txt+Dept_Data.txt Dept_with_Header.txt

Oct 16 '06 #4

P: n/a
Thanks Tonkuma!

Oct 19 '06 #5

P: n/a
Thanks Tonkuma!

Oct 19 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.