Compare DB2 z/OS and LUW schemas? Yes, you can!

Comparing schemas across database platforms, like DB2 LUW and DB2 z/OS, is sometimes necessary and often puzzling. No matter your motivation for comparing them, this can be a daunting task. Maybe you’re preparing to migrate an application to use data on LUW instead of z/OS. Perhaps you’re simply trying to make sure that two copies of a database schema on two different platforms are being kept in sync as expected. You can look to Quest’s Toad family of products for help with this. You might already own the components you need. This article introduces DB2 professionals to the cross-platform schema comparison capabilities of Toad Data Modeler.

Toad products can help you accomplish this type of comparison

Toad for DB2 will directly compare schemas of two databases of the same version and same platform; for example, a schema on one DB2 LUW Version 11.1 database with another DB2 LUW Version 11.1 database.  To accomplish the cross-platform variety of schema comparison, such as an LUW schema with a z/OS schema, one method is to use Toad Data Modeler.

Toad Data Modeler (TDM) is a powerful tool that is packaged with some Editions of Toad for DB2 (and other Toads), so you might already own it. It might surprise you to know that it can do more than build logical and physical models during your design phase of a project. For example:

Reverse engineering in TDM allows the creation of a physical model from an existing database, and supports many DBMS types.  

Model conversion allows you to use that physical model to quickly create a physical model for a different platform – say, from DB2 z/OS Version 11 to DB2 LUW Version 10.

How to compare schemas between DB2 for z/OS and DB2 for LUW

One practical use of reverse engineering and model conversion is schema comparison, as I described above. If you need to compare a DB2 z/OS schema with a DB2 LUW schema and see a detailed report of the differences, here are the easy steps:

In Toad Data Modeler,

1. Set up connections for the source and target databases
2. Create a model from a DB2 for z/OS subsystem
3. Convert that z/OS model to DB2 for LUW  
4. Create a model from the target DB2 for LUW schema
5. Compare the models, generating a report to see the differences

Let’s look at each of those steps in some detail, so you can try this yourself.  

Set up connectionsfor the source DB2 subsystem and the target DB2 database

Before you can generate your models, convert one of them, and then compare them, you’ll need to connect to the environments in order to access the schema information. In Toad Data Modeler, that’s accomplished in the Connections dialog. First, you “Add a connection” and the Connections wizard steps you through, prompting for what it needs.  You will have choices of connectivity types, including creating a “native” DB2 client connection or an ODBC connection.

One of the first things you will need to specify is the database type and version.  Getting the version right is critical, because the model you then generate via reverse engineering will depend on the version in TDM matching the actual version of the database schema.  Errors will likely occur if you do not indicate the correct database version.

Figure 1:  Above, my resulting two connections – one for DB2 on z/OS (which I’ve called “mainframeV10”) and one for DB2 on LUW, “DB201”.

Figure 2:  Choose the correct database version for each connection – this is critical for correct model generation from the schemas you will want to compare.

You’ll be prompted to save the connections, and you will need them for the next steps. And be sure to test the connections here to make sure they connect successfully, and adjust if necessary. When successful, a message appears and the “Last Connection Date” column will be updated.

Figure 3:  The Test Connection button on the TDM Connections setup screen toolbar

Create a modelfor the z/OS schema

Reverse engineer a schema from DB2 z/OS into a physical model – this will be the source for the schema compare.  You’ll connect to the subsystem using the connection we saved in the previous step.

Figure 4:  Reverse engineer the DB2 z/OS subsystem schema to create a physical model 

As you step through the wizard screens, options will appear where you can choose the types of schema objects you want to add to the model, as shown below.

Figure 5:  For now, I am sticking with the default of generating my model with all object types included.

Figure 6:  I may not want to include tablespaces or other things that I know are going to be different between the source and target schemas.

Figure 7:  I choose my schema name, limiting the objects of the subsystem that will be compared

Figure 8:  To select the tables for the generated model, click the “Select All” button.

Click the “Execute” button to produce the model.

Figure 9:  The completed physical DB2 z/OS schema model. Note the “Message Explorer” at the bottom.

I can edit my model at this point – simply right click and choose “Edit”.

Figure 10:  I’ve unselected certain objects from my model – leaving just tables, in this case.  

Finally, make sure you save your model from the File menu, “Save Model as…”

Convert the DB2 z/OS schema model to DB2 LUW

We need to convert the physical z/OS schema model to DB2LUW Version 10.5 since that’s what we’ll be comparing with.

Figure 11:  From the TDM Model menu, choose “Convert Model”, then Run

Figure 12:  Choosing to convert the model to LUW v10.5, same as my comparison target database.

I save my new model with a name that reflects what it is…I included the word “converted” in the name:

Figure 13:  Saved new model converted into a LUW model

Create your target modelfrom a DB2 LUW schema

From the TDM ‘File’ menu, choose Reverse Engineering to choose your second database connection (which will later be used as the target of your schema compare). Click “Create New Model from Database”.

Figure 14:  initiating the reengineering of my DB2 LUW schema into a physical model

Figure 15:  I can choose the schema name, then need to select the objects I want to include.

Figure 16:  My reengineered model for the DB2 LUW schema

Compare the two schema models, generating a “differences” report

The method I’m going to use to compare these two schemas (now models in TDM) is to open the first model (z/OS) and then run the Compare function (with report option), during which I’ll be able to choose my second model for the comparison (LUW).

The following several screen captures show the progression to complete the comparison.

Figure 17:  Opening the first model for the compare.

Figure 18:  Run a compare and generate a report; I will choose my target of the compare next…

Figure 19:  Browse for the target model or use an already available model in TDM

Figure 20:  The comparison reporting starts here – I’m choosing to show differences only

Figure 21:  Expand objects on the report to see specific differences between the two schemas

Figure 22:  A summary is displayed last

Figure 23:  Generate the report to a file using the button on the toolbar as shown.  I’ve chosen a PDF format

You will be prompted to select a location for your new report file (the PDF file, in my example).

Conclusion

In summary, to compare schemas of different database platforms we connected to the source and target databases, then reengineered both of them into physical models.  The comparison required that the two models had the same type and version of database, so we converted the source (left side of the compare)) DB2 z/OS schema model into an LUW version 10.5 model to match the comparison target (right side of the compare).  Toad Data Modeler is a product that you might already have with your Toad for DB2 license.  If you’re not sure, please check with your Quest Toad account manager.

To find out more about Toad Data Modeler, visit the product page here.

To find out more about Toad for DB2 LUW, and Toad for DB2 z/OS, explore the Toad for DB2 page on Toad World here, and find out all that’s new in the latest release.  You’ll also find Blog and Forum buttons on the Toad World toolbar, where you can navigate to product- or solution-specific information or conversations. These are great places to get questions answered and learn more about Quest’s extensive database management solutions.

Questions?

Start a discussion about this blog in the Toad World® forum, Toad for IBM DB2 . 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!