Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    5

    Unanswered: Question About Exporting Data via Command Line

    Hello All -

    I have a query which i'm running via the DB2 command line to export data to a text file. The vendor needs a basic tab-delimited text file with column names/headers from the table. They do NOT want any sort of text-qualifier in the file. As i'm very new to DB2, i'm not sure how to do that.

    Here's the EXPORT command i'm using now in my query file:

    Export to 'C:\DB2Client\FileName.txt' of del modified by codepage=1208

    I've looked at the NOCHARDEL option but not sure how to get any of this to work - can someone help?

    Thanks!!

  2. #2
    Join Date
    Mar 2012
    Location
    Canberra, Australia
    Posts
    38
    MODIFIED BY COLDEL0x09 NOCHARDEL

    gives you no quotes and tab delimited

  3. #3
    Join Date
    May 2012
    Posts
    5
    Hey thanks, that worked.

    Just one more question - the column header names are not returning, we need those. How do I ensure that the column names are included in the export file?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Polymorphist View Post
    Hey thanks, that worked.

    Just one more question - the column header names are not returning, we need those. How do I ensure that the column names are included in the export file?
    Can't do that directly in the export command.

    What you can do is something like this:

    1. export the data to export.temp file
    2. Echo the column headings to FileName.txt file using shell script and > (create new).
    3. copy (cat) the export.temp data to FileName.txt file using shell script and >> (append).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another option might be UNION ALL column name strings.


    Here is an example.
    Note 1: Tested on Command Editor on DB2 9.7.5 on Windows/XP.
    So, adjust new lines and blanks, if the command was issued from CLP.
    Note 2: Cast all column values to char or varchar.
    Code:
    ------------------------------ Commands Entered ------------------------------
    EXPORT TO export_with_header.txt OF DEL MODIFIED BY COLDEL0x09 NOCHARDEL
    SELECT empno
         , firstnme
         , lastname
         , CHAR(birthdate)
         , VARCHAR(edlevel)
     FROM  employee
    UNION ALL
    VALUES
       (  'empno'
        , 'firstnme'
        , 'lastname'
        , 'birthdate'
        , 'edlevel'
       );
    ------------------------------------------------------------------------------
    SQL3104N  The Export utility is beginning to export data to file 
    "export_with_header.txt".
    
    SQL3105N  The Export utility has finished exporting "33" rows.
    
    
    Number of rows exported: 33

    conents of export_with_header.txt file:
    Code:
    empno	firstnme	lastname	birthdate	edlevel
    000010	CHRISTINE	HAAS	1963-08-24	18
    000020	MICHAEL	THOMPSON	1978-02-02	18
    000030	SALLY	KWAN	1971-05-11	20
    000050	JOHN	GEYER	1955-09-15	16
    000060	IRVING	STERN	1975-07-07	16
    000070	EVA	PULASKI	2003-05-26	16
    000090	EILEEN	HENDERSON	1971-05-15	16
    000100	THEODORE	SPENSER	1980-12-18	14
    000110	VINCENZO	LUCCHESSI	1959-11-05	19
    000120	SEAN	O'CONNELL	1972-10-18	14
    000130	DELORES	QUINTANA	1955-09-15	16
    000140	HEATHER	NICHOLLS	1976-01-19	18
    000150	BRUCE	ADAMSON	1977-05-17	16
    000160	ELIZABETH	PIANKA	1980-04-12	17
    000170	MASATOSHI	YOSHIMURA	1981-01-05	16
    000180	MARILYN	SCOUTTEN	1979-02-21	17
    000190	JAMES	WALKER	1982-06-25	16
    000200	DAVID	BROWN	1971-05-29	16
    000210	WILLIAM	JONES	2003-02-23	17
    000220	JENNIFER	LUTZ	1978-03-19	18
    000230	JAMES	JEFFERSON	1980-05-30	14
    000240	SALVATORE	MARINO	2002-03-31	17
    000250	DANIEL	SMITH	1969-11-12	15
    000260	SYBIL	JOHNSON	1976-10-05	16
    000270	MARIA	PEREZ	2003-05-26	15
    000280	ETHEL	SCHNEIDER	1976-03-28	17
    000290	JOHN	PARKER	1985-07-09	12
    000300	PHILIP	SMITH	1976-10-27	14
    000310	MAUDE	SETRIGHT	1961-04-21	12
    000320	RAMLAL	MEHTA	1962-08-11	16
    000330	WING	LEE	1971-07-18	14
    000340	JASON	GOUNOT	1956-05-17	16

Posting Permissions

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