Compare DDL of 2 databases in DB2

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