CODEWITHSUNDEEP
db2
user1588737
user1588737

Reputation: 320

Migrating a schema from one database to other

As part of some requirement, I need to migrate a schema from some existing database to a new schema in a different database. Some part of it is already done and now I need to compare the 2 schema and make changes in the new schema as per gap finding. I am not using a tool and was trying to understand some details using syscat command but could not get much success. Any pointer on what is the best way to solve this?

Regards, Ramakant

Upvotes: 1

Views: 587

Answers (3)

user1588737
user1588737

Reputation: 320

unfortunately as per company policy, cannot use these tools at this point of time. So am writing some program using JDBC to get the details and do some comparison kind of stuff.

Upvotes: 0

Sualeh Fatehi
Sualeh Fatehi

Reputation: 4784

Use SchemaCrawler for IBM DB2, a free open-source tool that is designed to produce text output that is designed to be diffed. You can get very detailed information about your schema, including view and stored procedure definitions. All of the information that you need will be output in a single file, and can be compared very easily using a standard diff tool.

Sualeh Fatehi, SchemaCrawler

Upvotes: 0

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11052

A tool really is the best way to solve this – IBM Data Studio is free and can compare schemas between databases.

Assuming you are using DB2 for Linux/UNIX/Windows, you can do a rudimentary compare by looking at selected columns in SYSCAT.TABLES and SYSCAT.COLUMNS (for table definitions), and SYSCAT.INDEXES (for indexes). Exporting this data to files and using diff may be the easiest method. However, doing this for more complex structures (tables with range or database partitioning, foreign keys, etc) will become very complex very quickly as this information is spread across a lot of different system catalog tables.

An alternative method would be to extract DDL using the db2look utility. However, you can't specify the order that db2look outputs objects (db2look extracts DDL based on the objects' CREATE_TIME), so you can't extract DDL for an entire schema into a file and expect to use diff to compare. You would need to extract DDL into a separate file for each table.

Upvotes: 1

Related Questions