Export data from db2 with column names

Question

I want to export data from db2 tables to csv format.I also need that first row should be all the column names. I have little success by using the following comand ``` EXPORT TO "TEST.csv" OF DEL MODIFIED BY NOCHARDEL coldel: , SELECT col1,'COL1',x'0A',col2,'COL2',x'0A' FROM TEST_TABLE; ``` But with this i get data like ``` Row1 Value:COL1: Row1 Value:COL2: Row2 Value:COL1: Row2 Value:COL2: ``` etc. I also tried the following query ``` EXPORT TO "TEST.csv" OF DEL MODIFIED BY NOCHARDEL SELECT 'COL1',col1,'COL2',col2 FROM ADMIN_EXPORT; ``` But this lists column name with each row data when opened with excel. Is there a way i can get data in the format below ``` COL1 COL2 value value value value ``` when opened in excel. Thanks

Accepted Answer

After days of searching I solved this problem that way:

```
 EXPORT TO ...
 SELECT 1 as id, 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1
 UNION ALL
 (SELECT 2 as id, COL1, COL2, COL3 FROM myTable)
 ORDER BY id
```

You can't select a constant string in db2 from nothing, so you have to select from sysibm.sysdummy1. To have the manually added columns in first row you have to add a pseudo-id and sort the UNION result by that id. Otherwise the header can be at the bottom of the resulting file.

Export data from db2 with column names

Solution

Problem