DB2 import/export data

Exporting data to an SQL file for migration can be achieved by generating INSERT statements. However, DB2 doesn’t have a direct EXPORT TO SQL option. Instead, you can combine a query with scripting to generate SQL INSERT statements.

Alternatively, DB2’s CSV, DEL, or IXF formats can be used for easy exporting and importing between servers. Below, I explain both approaches.

1. Export Table Data as SQL

To export the table SFRZ.t_sfrz_para as an SQL script:

Using db2 and Query:

Run the following query to generate INSERT statements:

db2 "EXPORT TO t_sfrz_para.sql OF DEL SELECT 'INSERT INTO SFRZ.t_sfrz_para VALUES (' || col1 || ', ' || col2 || ', ''' || col3 || ''');' FROM SFRZ.t_sfrz_para"

Steps:

• Replace col1, col2, col3 with your table’s actual column names.
• Use appropriate string delimiters and escape quotes (''') for strings.

The resulting file (t_sfrz_para.sql) will contain one INSERT statement per row.

2. Importing Data from CSV, DEL, or IXF

A. CSV/DEL Import:

1. Export the data to CSV or DEL:

   db2 "EXPORT TO t_sfrz_para.csv OF DEL SELECT * FROM SFRZ.t_sfrz_para"
   

2. Import the data into another DB2 server:

   • Ensure the target table (SFRZ.t_sfrz_para) exists in the destination database.
   • Use the IMPORT or LOAD command:

     db2 "IMPORT FROM t_sfrz_para.csv OF DEL INSERT INTO SFRZ.t_sfrz_para"
     

   • For faster loading (especially for large datasets):

     db2 "LOAD FROM t_sfrz_para.csv OF DEL INSERT INTO SFRZ.t_sfrz_para"
     

B. IXF Import:

1. Export the data to IXF:

   db2 "EXPORT TO t_sfrz_para.ixf OF IXF SELECT * FROM SFRZ.t_sfrz_para"
   

2. Import the IXF file:

   • The IXF format includes table structure and data, so you don’t need to pre-create the table:

     db2 "IMPORT FROM t_sfrz_para.ixf OF IXF CREATE INTO SFRZ.t_sfrz_para"
     

   • For existing tables:

     db2 "IMPORT FROM t_sfrz_para.ixf OF IXF INSERT INTO SFRZ.t_sfrz_para"
     

3. Pros and Cons

Recommendation

• If you prioritize readability and flexibility, use the SQL approach.
• If you’re dealing with large datasets or automation, prefer CSV or IXF.