How to use the multi database query facility in Toad for DB2

Hi, this is Jeff Podlasek with Toad DB2 development. And Toad for DB2 version 5.5, we've introduced a new multi-database query facility. This feature allows you to easily create and execute queries across multiple DB2 databases or subsystems and have the results shown in a single result set. Advanced cell formatting and highlighting are also included in this feature. So this can be very useful when you want to gather and evaluate the same information for several databases or subsystems, for example, monitoring the health of your database objects. I have Toad up and running. And I'm not connected to any databases yet. You can run this feature whether you're connected or not. And now on the Tools menu, you'll see a Query Multiple Databases. And I'm selecting that right now. When this dialogue comes up, you could do one of several things. You could create your own custom query. You could select from one of the samples that we ship. Or you could open a previously saved query. Right now, I'm going to click on one of the samples and modify it a bit or clone it, if you will. I'm going to click Next. This dialogue opens up and allows me to select the servers I want to run this query against. And there's a filtering option here. So I know that my servers begin with 10Q. And right now, I'm going to select two of those. So I'm going to select my AIX and my Windows Server. And what Toad is doing in the background is loading up your schemas. I know that the SQL in this example is fully qualified. But if it was not fully qualified, you could actually bring down and select the schemas that you might want to use to qualify any qualified tables in your query. But right now, we're going to leave it as Using Fully Qualified SQL. And hit Next. Now in this screen, since I'm cloning it, my existing query is shown here. If you're creating a new one, this would be empty and you'd be able to add a query or add a query from a file. Right now I'm just going to go click on Edit, and this will bring up the Query Editor screen. The top left section shows the query that I've entered there. The bottom preview section, if I click the Generate button, will actually run the query against one of the database connections just to give me a preview of the report that I'm creating. The top right cell formatting section allows me to specify highlighting and cell criteria. So you can see I've got a couple of criteria already specified. And I'm just going to show you how to add a new one. I'm going to say click on Add. Then click the table just for a very simple rule. I'm going to say if my table space use pages is greater than one, click OK. Then you could actually format the color-- the foreground color and the background color. And you see as I'm doing this, the preview pane below is being updated. And you could select an icon. Or you could enter a message. So for example, high pages, so now when I cursor over this, you'll see my little message here. So it's very easy to criteria. You could also add custom criteria, if you will. This allows you to roll your own. You could say where type equal DMS, for example. Just say, OK. Then you get to pick the column that is associated with. And you could say, I want it to be red. So very easily add your own custom formatting, criteria. I'm going to get rid of this last one. And we're going to click OK. And then you're going to click Finish. So now what Toad is doing is going out and actually executing the SQL against the databases that I've selected. And it brings up this report. So this grid shows you the server and scheme information that you've selected when you're running your report. You can see that I've run against several different servers. And what I like to do is actually show the group panel and then group by server schemas, gives me a little bit of a better way of understanding and seeing the items by server. So it's a very handy, powerful tool. If you want to, you can save this file. I'm just going to save it as any file name. And then back in Toad, you can always just open up that file and it will open up that report. I'm going to bring up the feature again then go over a few more things here. This time I'm going to select or clone a LEW table reorg check query that we have saved. And again, I'm going to select a couple of databases I've got out there. I've got a Windows database out there. And I've got an AIX database out there. And since this report is actually specified as non-qualified SQL, I could select the SQL that I want this to run against. So I'm going to select various SQL qualifiers from this first database and then a couple from the second database as well. And just to give you an idea of how this works, I'm going to click Next and going to click Edit Preview. And again, the query is going to be shown top left. And you'll see that we have a bind variable called Schema that can be used. And what Toad will do is any of the schema that you've selected in those previous panes will be used and substituted in as that value. And I'm going to generate, so you'll actually see the results of calling the reorg check. Store procedure with the first schema. It just basically does a preview, selecting one of those. And we've precanned this example with the F1, F2, F3 function criteria to color