There is always a need to sync a DB2 database from one environment with another. Instead of using unload or load utilities that may require authorization, the following SQL (SELECT) statement can be executed in the source environment. The result can then be stored in a file/dataset that can be run in the destination environment to replicate the records. This SQL can be used in any DB2 version; I tested it on DB2 v7. Execute it one environment (e.g., Production) and its output can be executed on the other environment (e.g., Test). I think it can be used in any other RDBMS too, except that the single quote might need to be replaced with double quotes depending on the database.
Here is the table structure:
EMP EMPNO INTEGER EMPNAME CHAR(15) MGRNO INTEGER SALARY DECIMAL(7,2) JDATE DATE
Here are the records:
EMPNO EMPNAME MGRNO SALARY JDATE 25 ADAMS 15 70000 2001-02-02 30 BILL 15 60000 2000-06-01
Here is the SQL:
SELECT 'INSERT INTO EMP VALUES ( '|| EMPNO ||','|| ''''||EMPNAME ||'''' ||','|| MGRNO ||','|| SALARY ||','|| ''''||JDATE ||'''' ||');' FROM EMP
Here is the output:
INSERT INTO EMP VALUES (25,'ADAMS',15,70000,'2001-02-02'); INSERT INTO EMP VALUES (30,'BILL',15,60000,'2000-06-01');
The idea is to write a single quote(') to your output, so you have to use four quotes ('''').
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.
This was first published in November 2002