Hi, this is Jeff Podlasek with Toad for DB2 Development. Quite often DBAs and developers are charged with rolling out new changes to their database objects. They might need to ensure their test database reflects their production environment. Or they might need to take their stage changes and apply those to their production environments.
Toad for DB2 has a very robust object compare feature that allows you to compare objects across databases, and synchronize your changes across databases. This feature is more than a simple schema comparer, as the object compare feature within Toad works with database-level objects. For example, tablespaces and storage groups, as well as schema- based objects. This video will demonstrate Toad for DB2's object compare future.
I have Toad up and running and I'm connected to a DB2 LUW database. The compare feature also works for DB2 on ZOS. I have a list of tables up in my database browser. And I'm going to select several of those.
Right click, and select Compare Object. And this launches the object compare feature. And the first thing you'll see is it's prompting you to select your target database. In this example, I'm going to use my sample as my target database and then click Next.
What you'll see now is, on this review source objects screen, gives you the ability to see all the objects that you're going to compare. If you want to bring over data from your source to your target, you could check these check boxes underneath the data column.
On the bottom you can see there's a bunch of additional options such as, do you want to include objects that will reference those that you originally selected, or do you want to include the dependents of those of you originally selected. So there's many options on the bottom here, such as migrating privileges, or preserving target data, et cetera. We're going to leave these as the default and select Next.
Now, in this next screen, Toad allows you to define transformation rules. For example, you might have naming standards that are different between your production and test environments. And you could do name mappings here.
So for example, if I wanted to change my schema to a different schema on my target, I could select that. Or if I wanted to use a different tablespace on my target, I could select that. And what you could do is you could come up with a very robust set of your own transformation rules and save those, and then reload those.
So I have done that, and I am going to reload my transformation rules right now by clicking the load this transformation rules. And you can see that in my saved transformation rules, I have my source schema of states, maps to my target schema of POD. Same with my source and target databases.
And the relational operator column right here, there are several ways you could do things. You can have a simple map. That is, we'll take the source object name and map to the target. You could use a wild card such as, if you wanted to change objects to begin with abc*, wild card, to xyz*, you could do that.
Or you could have it use a regular expression. In this case, anywhere a table name contains the text FOOTBALL, that text and that name will be transferred to NFL. And we'll see how this maps out in a little bit.
I'm going to select Next here. And what Toad is doing, it's fetching objects from the source, fetching objects from the target, and does the comparison. And then presents this comparison report.
And this report is-- I actually have it grouped by object type. You can see all the objects are listed on the top here. And if you select an object in the grid, the bottom display will show you the detailed comparisons, or detailed differences, of those objects.
And the result column, the icons actually represent-- this one represents that there's changes. An equal sign means that the tables are equal both on the source and target. And a green arrow pointing to right means it's a source only type of object.
And so if you select an object that's different, again, you can see the differences are highlighted below. And you can notice that we've already provided the mappings here. If you wanted to see the unmapped source, that is the names prior to the name change via the transformation rules, you could click this button.
And it's going to highlight the source as it is on the source without the mapping. So it gives you a visual representation of how your mappings have been applied. And by using this selection here, you can only see the things that matter, the changes that matter for you.
There's a bunch of buttons up on top, one of which is this set here of filter buttons. So if you only wanted to display the object that has changes, you could go only depress this button. If you only wanted to show the objects that were equal, you could depress this button. So for example, I've got a couple of objects that are at the same on my source and my target. It will typically show all of them there.
Toad also comes up with a robust set of advanced compare options. There are many attributes that may differ between your production environment and your test environment. You might want to discard those, or ignore those during our comparison process. So we give you the ability to do that just by checking things.
So if you wanted to ignore the compression-- a lot of people don't compress their small test data-- you could select to do that. Or if you wanted to ignore data capture differences for tables. Or if you wanted to you ignore table organizational differences, you could do that here. It's a very robust set of advanced