I've been handed over the task of trying to maintain our DB2 database (7.2).
I have very little experience and I am kind of learning more as I go along.
I created some jcl that copies the table in a dbspace to a temporary disk area, drops the dbspace, recreates the dbspace and reloads the temporary table back to the newly re-acquired dbspace. This releases the empty pages.
I was tracking several dbspaces and I noticed that the number of empty pages in a particular dbspace suddenly went down. I've been trying to find the job in our ops batch schedule that might have done this. All I can find at this point is a reorg of the table. I know a reorg will not release all of the empty pages back, but can a reorg decrease the number of empty pages?
If not, there must be something else that is running that must be releasing the pages.
A reorg will consolidate and order pages, leaving only the empty space that has been defined for that tablespace. It is the job you want. You have basically been imitating a reorg with the procedure you have used.
Being DBA for DB2 is not a simple task, but it is more complex on the mainframe. Tell your management to send you to training.
About the only time you would want to drop the database and recreate all of its subordinate objects would be if you have had a LOT of drop and create of tables/tablespaces/indexes and your DBD(s) has gotten too large. Also, most places you go there are too many objects in a database to enable the process you are speaking of. Also, with using the REORGs, discussed above, you can use CHANGELEVEL SHR and your object is available for use almost the entire time, instead of not existing.
Thanks for information.
I'm a bit confused when you said, 'drop the database'?
I was just speaking of dropping the DBspace to give the empty pages back to the dbspace.
The reorg they run here, up loads the table to tape and reloads it back using an aridbs exec. I was wondering if this process can lower the number of empty pages shown when I run 'Show dbspace xxx'.
It sounds like you are using a third party tool for the reorg. But it doesn't change the basics --
a reorg will do an unload and sort and then reload the table. The end effect is the rows are put in physical order and deleted rows are expunged. On the mainframe, this is done not by the table but by the tablespace. The data pages will be placed in the least amount of space that fits the declaration of the tablespace (pctfree and freepage will determine how much empty space will be left in the tablespace).