DB2 database Export and Import (Windows client)

Step 1: export the DB2 table structure on the server (you can skip step 3 and Step 4 if you use the command line, such as the table has been catalogued) 1. run cmd2. enter db2cmd3. create a node db2 catalog tcpip node mynode remote 192.168.1.90 server 500004. create a remote database instance db2 catalog db dbname as mydb at node mynode5. connect to the database www.2cto.com db2 connect to mydb user username using pssword6. export the table structure db2look-d dbname-e-a-x-I username- w password-o ddlfile. the SQL export process is as follows:
The exported file is D: \ out. SQL Step 2: Execute DDL In the DB2 database to be imported, create a table structure and use a TOAD client to connect to the DB2 database to be imported, and copy D: \ out. all SQL statements in SQL are executed in TOAD. If the user names in the two databases are different, you must manually replace out. step 3: Prepare SQL script syntax for batch export Import: export to [path (example: D: "TABLE1.ixf)] of ixf select [field (example: * or col1, col2, col3)] from TABLE1; I did not find a client that supports batch export, so I wrote a java class myself, run the following SQL code to generate and export data from all tables cyclically. jar): import java. io. bufferedWriter; import java. io. file; import java. io. fileWriter; import java. SQL. *; Www.2cto.com public class ExportDB2 {/*** // *** @ param args */public static void main (String [] args) {String driver = "com. ibm. db2.jcc. DB2Driver "; String url =" jdbc: db2: // ip: 50000/dbname "; String userName =" user1 "; String passWord =" psw1 "; Connection conn = null; statement st = null; ResultSet rs = null; String SQL = null; try {Class. forName (driver ). newInstance (); conn = Drive RManager. getConnection (url, userName, passWord); st = conn. createStatement (); SQL = new String ("SELECT name FROM sysibm. systables where creator = 'user1' order by name "); rs = st.exe cuteQuery (SQL); String table = null; String exportCMD = null; String importCMD = null; // File path file = new File ("d: \ export.txt"); File file2 = new File ("d: \ importCMD.txt"); BufferedWriter out = new Buffe RedWriter (new FileWriter (file, true); BufferedWriter out2 = new BufferedWriter (new FileWriter (file2, true); while (rs. next () {www.2cto.com table = rs. getString (1); exportCMD = "export to D: \ db2-bak \ ixf \" + table + ". ixf of ixf select * from "+ table +"; "; out. write (exportCMD); out. newLine (); importCMD = "import from D: \ db2-bak \ ixf \" + table + ". ixf of ixf into "+ table +"; "; out2.write (imp OrtCMD); out2.newLine ();} out. close (); out = null; file = null; out2.close (); out2 = null; file2 = null; conn. close (); System. out. println ("Finished! ");} Catch (Exception e) {System. out. println ("error:" + e. getMessage (); System. out. println (e. after running toString () ;}}www.2cto.com, these two files are generated on disk D. One is the SQL statement for exporting data from all tables, and the other is the fourth step for importing data from all tables: to export data in tables in batches, connect to the DB2 database on the server using a client such as TOAD, copy all the SQL statements in D: \ export.txt to TOAD, and execute all the ixf files exported, which exist in D: \ bd2-bak \ ixf \ Step 5: Batch import the data in the table with TOAD and other clients to receive the imported DB2 database, copy all the SQL in D: \ importCMD.txt to TOAD, execute the success. From Lei MingQing's column