Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-430

Proper csv export from IBM DB2

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      IBM DB2

      Option 1)

      Using the IBM command line processor:

      • Connect to the database you want to export
      • Use the EXPORT TO-command combined with a SELECT query to export a table
        (Documentation: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.8.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html)
      • We recommend to apply following parameters to ensure compatible formatting for your exported csv file:
        o MODIFIED BY datesiso
        -> exports dates in ISO format (YYYY-MM-DD)
        o MODIFIED BY timestampformat=”YYYY-MM-DD HH:MM:SS”
        -> exports timestamps in selected format
        o COALESCE(column,’’)
        -> checks if content of data field equals NULL and replaces it with “” (empty string) if necessary.
        This addition is needed for every selected column that allows NULL values.

      Example:

      Notes:

      • IBM command line processor automatically masks double quotes ("Example" -> ""Example"")

      Option 2)

      Using the IBM Data Studio:

      • Connect to the database you want to export
      • Select the table you want to export
      • Open context menu by right clicking on the table
      • Select the option Unload -> With Export Utility to open the integrated export tool
      • Select JDBC as Run method
      • Pick your path and file name for the csv file
      • To specify additional options, select the tab called Options (top left)
      • To ensure a compatible format for your csv file, you should check the available control boxes as follows
      • Click Run to start exporting the table
      • You will find the csv file in your selected path

      Notes:

      • IBM Data Studio automatically converts NULL to "" (empty string)
      • IBM Data Studio automatically masks double quotes ("Example" -> ""Example"")
      Show
      IBM DB2 Option 1) Using the IBM command line processor : Connect to the database you want to export Use the EXPORT TO -command combined with a SELECT query to export a table (Documentation: https://www.ibm.com/support/knowledgecenter/SSEPGG_9.8.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html ) We recommend to apply following parameters to ensure compatible formatting for your exported csv file: o MODIFIED BY datesiso -> exports dates in ISO format (YYYY-MM-DD) o MODIFIED BY timestampformat=”YYYY-MM-DD HH:MM:SS” -> exports timestamps in selected format o COALESCE(column,’’) -> checks if content of data field equals NULL and replaces it with “” (empty string) if necessary. This addition is needed for every selected column that allows NULL values. Example: Notes: IBM command line processor automatically masks double quotes ("Example" -> ""Example"") Option 2) Using the IBM Data Studio : Connect to the database you want to export Select the table you want to export Open context menu by right clicking on the table Select the option Unload -> With Export Utility to open the integrated export tool Select JDBC as Run method Pick your path and file name for the csv file To specify additional options, select the tab called Options (top left) To ensure a compatible format for your csv file, you should check the available control boxes as follows Click Run to start exporting the table You will find the csv file in your selected path Notes: IBM Data Studio automatically converts NULL to "" (empty string) IBM Data Studio automatically masks double quotes ("Example" -> ""Example"")
    • Category 1:
      ETL / Data Integration
    • Category 2:
      3rd Party Tools - ETL

      Attachments

        Issue Links

        1. DB2_1.png
          DB2_1.png
          11 kB
        2. DB2_2.png
          DB2_2.png
          11 kB
        3. DB2_3.png
          DB2_3.png
          103 kB
        4. DB2_4.png
          DB2_4.png
          120 kB
        5. DB2_5.png
          DB2_5.png
          125 kB
        6. DB2_6.png
          DB2_6.png
          108 kB
        7. DB2_7.png
          DB2_7.png
          107 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: