Overview of developer features in Toad for DB2 Part VII

Another thing working with data is the ability to get multiple result sets and compare those. So for example, let's say I have a football table here. Here I'm connected to two different databases. One's called Sample. One's called Pod1013A. But here, I'm just going to generate a SQL statement to the SQL editor. So this is going to open up an editor with one SQL statement in there. And I'm just going to run this query, just to make sure it runs on this database. And it does, and successfully returns one result set. And I'm going to close the result set here. And I'm going to select that I want to see the group execution. And what group execution allows you to do-- once it is enabled, you could run a SQL statement against multiple databases, if you so wish. So I could check all the databases in my list here. And as you can see down here, I have two targets selected. So when I go run this now, it's going to actually execute the SQL against two databases. So for instance, you might have a staging database and a development database. And you might want to see if your test data in those areas are the same. You could use this group execute to do that. So it's going to prompt me saying hey, do you acknowledge that you're running this against multiple targets? Yes, you are. And you could see that I actually have two result sets now. And they show up in this Tab format. But what I typically would do, if I wanted to make sure, or just eyeball if my data was equal in those two different environments, I could easily right click, and say Compare To, and then select that result set that I want to compare to. And a dialogue pops up that gives me the ability to select the Results Columns that I could use as key columns. So I'm going to select the PLAYER_ID and click Compare. And what happens now is Toad comes up and displays this Data Diff viewer. So it shows the differences of data between those two result sets. And these buttons on top can be used to filter the displaying rows. So for example, if I only wanted to show the rows that were unequal, I would click on this. It's going to show me the unequal rows. If I only wanted to show the rows that were in the first results set and not the second, in both results sets but are different, and in the second result set. So I typically like using this view right here that shows me all the differences. And then I can actually use this button to scroll to the next differences. So for example, this row is a source only row. It's in the first result set. It's not in the second one. So it shows up in this manner. The next difference here are rows that show up in the target, but not the source. And it's going to wrap around. Now I'm going to click and show the results that are actually in both result sets but have some differences. And you can notice, as you click on the grid on top, the bottom grid gets updated to actually show the details. So you could see that this row on the first result set-- this person-- his position was a defensive back. It's been updated to be a linebacker. And again, his years pro values change from 1 to 2 years. This person has a different team that he's associated with. So very easy to quickly identify data in two tables. And they could be in two different databases, maybe one production, one stage, one test, one development, et cetera, to give you an idea if your data is the same and those platforms are not. And going a little step further in that same scenario, if you wanted to, for example-- you could run any type of script along with group execute. So if you had a script that, for example, you wanted to create a table in a couple different environments rather quickly, you could type in a simple create table statement right here while the group execute is enabled. And you can see that the table is actually successfully created in both databases, or if it was DB2 z/OS, in both subsystems. So that's another important way that you could leverage group execute, executing scripts across multiple databases, multiple subsystems. All right, I'm going to close some of these SQL editors down and then demonstrate another feature of Toad that deals with data. Now again, maybe you want to sync your data across databases, or across tables, et cetera. So let's say, for example, you've got a table. And we're just going to actually open the Data Compare facility with Toad for DB2. And this allows you, again, to see the data differences, but not only to see them, but actually to synchronize them. So you could run this on the same database, if you map your schemas, or across databases. I'm going to run this on the same database across schemas. I'm going to show you what I mean. So this wizard allows you to specify column keys, again, that we're going to compare on. And since both of these tables have primary keys identified on the PLAYER_ID column, you really don't have to do anything else. If you wanted to limit some data, if you didn't want to retrieve all the data in the source or target table, you could specify a WHERE clause in either of those to limit the amount of data being fetched. But in this case, I'm going to just click OK and take the default. Here we have some options where you could ignore things such as case, and text, or white space and text columns, and ignore blob columns. But we're going