Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2015

    Talking Unanswered: Export Function in DB2

    Hello Everyone,

    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...

    Thanks for your help.

  2. #2
    Join Date
    Apr 2006
    Provided Answers: 11
    export is for data part and not ddl
    if ddl is needed ..of ixf is also supported : keeping the ddl - not readable
    if colnames are needed : select * from table > tab.out
    all info as always : in infocenter
    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

  3. #3
    Join Date
    Jul 2013
    Moscow, Russia
    Provided Answers: 55

    Try the following export.bat file
    set f=%1.%2.txt
    set coldel=;
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts