I'm having problem with the export function of DB2.
I was able to export the result of a query into a .csv file using the following command:
EXPORT to C:\\Myfolder\\file.csv of DEL MODIFIED BY COLDEL; select * from myTable
The problem is the exported file contains all the data except the constant name of the single column, which is quite annoying because you have all data but you don't know what does it represent.
After googling couple of hours i found the following solutions:
Solution N.1- try to use the command UNION ALL
EXPORT to C:\\Myfolder\\file.csv of DEL MODIFIED BY COLDEL; select * from myTable UNION ALL values('NameCol1', 'NameCol2', 'NameCol3')
this solution works if every column is CHAR type, which is not my case, i have timestamp, integer, float
Solution N.2- Create a new file .csv with the header line and than append the data from the .csv that contains data
This solution works but if the amount of records to move is higher than 1000 rows it takes to much time...
It looks strange that IBM created a useful function but they forgot an important feature to integrate...
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be
db2 "export to %f% of del modified by coldel%coldel% select * from %1.%2"
db2 -xr %f% "select listagg('""'||colname||'""', '%coldel%') within group (order by colno) from syscat.columns where tabschema='%1' and tabname='%2'"
export.bat SYSCAT SCHEMATA
The only "problem" is if you want to have the column names first in the file. Export utility overrides the output file contents, so you really need to append the contents of its output to the file with the column headers which you get by the last query in the script.