Compare DDL of 2 databases in DB2

27 July,2013 by Jack Vamvas

Question: How can I write a script to compare the structure of DDL in 2 databases? I understand there are tools on the market , but we are a small company and these tools don’t do exactly what we need and we don’t want to spend extra money on customising

Answer: Comparing the DDL structures of 2 databases can be completed at a budget level by exporting key system catalogs and applying sdiff –s.

You could take the extra step of using a database to store the object information . This would allow a more effective way of storing and comparing DDL structures.

Step 1- Decide which DDL structures you need to compare. For example , it may be : schemas, tables, views, indexes,constraints,and routines. For a full list check DB2 Database objects overviews

Step 2 – Create script to export the key details from the system catalogs.

Step 3 – Create a process to compare the key information from the 2 databases . The process should include :

a) Items ADDED in DB1  and not in DB2

b) Items present in DB2 but not in DB1

c) Items in both DB21 and DB2 but differing in definition

Step 4 – Automate the process.There are plenty of tools at a DB2 and OS level to automate processs. For example, you may want to execute the script every night , if there are lots of changes on a database. Or you may want to execute on an ad-hoc basis to troubleshoot.

Read More

Create a DDL on an existing DB2 table - DBA DB2

DB2 – COPY a table - DBA DB2

DB2 Database objects overviews - DBA DB2

Author: Jack Vamvas(


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Compare DDL of 2 databases in DB2

Comments are moderated, and will not appear until the author has approved them. | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright