Hi, this is Jeff Podlasek with Toad DB2 development. In Toad for DB2 version 5.5, we've introduced features for DB2 LUW that allow you to capture, view, and maintain historical space information for your tables, indexes, and tablespaces.
DBAs will find these new features useful as they often need to track the sizes of their database objects over time to determine their database space requirements. As a DBA, it's important to know what objects are growing and which objects remain static in size.
I've got Toad up and running. And I've got several database explorers open. On the first tab, the Database Explorer, I'm connected to a DB2 LUW version 10 database. I've selected some tables. And right now, I'm going to multiselect these tables, right click, and bring up the Space Management options.
And you can see that we've added three new options in version 5.5-- Capture Space History, Maintain Space History, and View Space History. I've selected View Space History. And what Toad is doing now is going out, fetching data from our space history repository. It's going to open up a space history document and display that information for you.
So this document opens up. As you can see, on the top section, there's information about tablespaces. The left-hand side shows you all the tablespaces for those objects that were selected when this dialogue was invoked. And as you click on these tablespaces, you'll notice several things. There's a chart on the right-hand side that will change to show the table space information.
And related tables for that tablespace get highlighted in the below grid. What I'm going to do now is I'm going to change this chart type to a line chart type. Say I want to show the used space and chart it by a week.
Back on the left-hand side, you could also use the Control and click. And that basically multiselects the tablespaces. So you could actually graph the space that is used for these tablespaces over time.
Again, if I click on a tablespace on top, the below tables in that space are selected. And again, you could change the unit time to week if you want. You could change the chart type. Here, I'm changing it to a Manhattan bar chart.
So you could actually see via bars, 3D stacked bars, space information for the selected tables. So if I select a single table on the left-hand side, you'll see that the chart on the right-hand side is updated to reflect that. And again, by using the Control button and selecting, you could select multiple tables. And whatever is selected on the left-hand side will be graphed on the right-hand side.
And the indexes basically behave the same way as tables. Indexes are displayed on the left-hand side. You select those. The graph on the right-hand side is displayed. So this gives you a really good historical space information for your tables, tablespaces, and indexes.
I'm going to show you some of the other features here as well. So after you select a couple objects, right click, and select Capture Space History, Toad will generate commands that will actually update space in our repository.
As you can see, it's calling one of our Quest Snapshot procedures. So you have to install that in the repository tables on your LUW database. And Toad will actually do that automatically for you. On this Database Explorer tab, I'm connected to another DB2 LUW version 10 database. But I do not have the repository tables installed.
So if I select, for example, to view the space history, Toad's going to come up and say, hey, I don't have the object installed. I'm going to generate a script that will install those objects for me. So you can see this script creates our repository tables.
I'm actually going to use this script map here. It's like a table of contents for the script. So you can see that we've got a Create Table for our tablespaces, for table, for indexes, and we have a Create Procedure.
And again, this procedure is the one we saw being called to actually capture the space information that's stored into our repository. It's a SQL-based procedure. And back on this tab here, if I wanted to maintain or prune the repository rows for several objects, you could select Space Management, Maintain Space History.
And what this will do is this will bring up a dialog that will show me the snapshots that have been captured and allow you to delete those. And what we do is we basically-- when we capture data, we capture data for tables. And we got up to Tablespaces. And so we'll grab all the tablespace information, all the table information, all the indexed information, in one snapshot.
And those are stored at the same snapshot in the tablespace tables and indexes' repository tables. So we believe that you guys will find these features very useful. And thank you for using Toad for DB2.