Solved

IBM DB2 5.2 Compact Utility

Posted on 2004-09-19
14
1,381 Views
Last Modified: 2008-03-10
We have an old IBM DB2 5.2 database running on a server that is running out of space, a few weeks at best. All options have been explored and the best option for now, is to see if there is a utility, with DB2 or third party, that can combact the databae. A utility that will go through and delete the deleted records/ reclaim unused space, something along the lines of Compact and Repair in MS Access. I have compacted and repaired Access databased from 5 gig down to 700 meg.


Is there a similiar utility/command/tool for DB2 5.2?
0
Comment
Question by:awilson11
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 12103230
no, there is not, that being said you can easily accomplish what you want by running delete statements, then re organize the tables to re claim the free space. Make sure you do a runstats for all tables concerned.

If you want to archive the data, export the records you want to archive first, then do the deletes. Or, put the archived data in another database by creating nicknames to a database on another server (I think 5.2 supports nicknames) then delete the archived records, reorg and runstats.

Of course, you can always backup the database, install a larger hard drive, drop the db from the orginal drive and restore the database to the larger drive.
0
 

Author Comment

by:awilson11
ID: 12103544
I wish delete statements were that easy. It isn't our database it is a leased application with 100+tables each with 100+ fields that is non-relational. The same key field was added to each table to relate the tables and each group of tables is sequenced with each table in the sequence adding another key field. I have worked with this database it it is EXTREMELY badly designed and replacing it would cost $1.5 million. Archiving would be as complicated or more.

We have considered replacing the hard drives, but the server is so old that replacing the drives would cost more than a new server.


But it looks like you have answered my question and if nobody comes up with a tool you will get the points. Thanks.
0
 
LVL 4

Expert Comment

by:bondtrader
ID: 12104627
If you have some large tables that have had significant delete activity over their lifetime, a reorg of those table will reclaim the space.  If you don't have enough space left even to do a reorg, you can export the data out, drop the table, recreate it and the indexes (perhaps decreasing the PCTFREE to leave less free space and thus further shrink the size), and reload the table.  Of course this later method can get a bit hairy if you have Referential Integrity between tables but is still feasible.  

By the way, what kind of tablespaces are you using?  DMS or SMS?  If you are using DMS, maybe they are over allocated; in which case they can be re-created with smaller allocations or you can just switch over to SMS which automatically grows as the data increases.

I've also seen instances where the database logs were taking up more space than the database itself.  perhaps your log directory needs pruning?

Also, you didn't say what platform you are running on...

Hope this helps...
0
 

Author Comment

by:awilson11
ID: 12106347
We are running Windows NT 4 SP6a.  This database has been a hands off instalation, we have nothing to do with it except maintaining the server and we have no DB2 experience, only SQL Server.

Could you be a little more explicit about "reorg" and "DMS" and "SMS" and about pruning logs.

Thanks.
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 12108836
well, you are saying your database is worth 1.5 million but replacing the drives is too expensive? That's hilarious!
Sounds like the client has reached the end of the line and now expects miracles, you are wasting your time.
0
 
LVL 3

Accepted Solution

by:
granbajo earned 350 total points
ID: 12110269
 I  find this a bit funny as well, I guess the easiest answer would be to add more disk.  I will try to translate some of the comments above into something a bit more meaningful for the non-DB2 user.

Logs.

  You can have either circular or archival logging.  If you have circular logs, then only a small number of files are kept and are used over and over again.  If you are using archival logging, then perhaps you have all the logs that the database has ever used, and deleting a few of them can release a lot of space.

  To check the type of logging from the DB2 command prompt

db2 GET DATABASE CONFIGURATION FOR dbname

  Here are some lines to look for in the output of the command...

...
 Log file size (4KB)                         (LOGFILSIZ) = 250
 Number of primary log files                (LOGPRIMARY) = 3
 Number of secondary log files               (LOGSECOND) = 2
 Changed path to log files                  (NEWLOGPATH) =
 Path to log files                                       = C:\DB2\NODE0000\SQL00001\SQLOGDIR\
 First active log file                                   =
...
 Log retain for recovery enabled             (LOGRETAIN) = OFF

  In this case LOGRETAIN is set to OFF, which means that I am using circular logging.  I only have 3  files (LOGPRIMARY) of 1 MB each (LOGFILSIZ*4).  If I needed more for a very long transaction, then DB2 would create up to 2 more (LOGSECOND) and then delete them, if I needed more than that, then the transaction would fail..  This is a very small test database, these numbers are usually much higher.  

  If it is set to ON, then you would have a value under 'First Active Log'.  It means that all log files that are smaller in number than the one there can be removed from the system.  Do this with care, because the log files are also used as part of the recovery strategy to rollforward after a restore from an on-line backup.  More details on this can be found on the documentation.  But this can be a good place to start.

Tablespaces

  There are two types of tablespaces.  SMS (System Managed) where DB2 would use whatever space is on the file system, in the case of Windows the whole of the partition, i.e. C:\.  I presume this is what you have, otherwise you would have ran into problems a while back

  The second type is DMS (Data Managed). in which you allocate the space used for the tablespace beforehand, and has to be manually extended if you run out of space.  This is very useful if you are doing data stripping accross many disks, but that is for another day.  Thjis is what bondtrader refered to when he mentions that they might have been overallocated.

  After connecting to the database from the command line you can issue the following command...

db2 LIST TABLESPACES

  A simple output might be like this

Tablespace ID                        = 2
Name                                 = USERSPACE1
Type                                 = System managed space
Contents                             = Any data
State                                = 0x0000
  Detailed explanation:
    Normal

  You can also use the LIST TABLESPACES SHOW DETAIL command to get more details about each tablespace.

Hope this helps.
Carlos
0
 

Author Comment

by:awilson11
ID: 12112281
ghp7000

No I am not saying the database is worth $1.5 million, I am saying to buy the upgraded version of the applicaton would cost $1.5 million and we don't want to buy the upgraded version of this application as we are looking for another application to replace it, that or write one. And when we do it will be SQL Server based running on new servers we just installed.  To move the existing application would require the comany to send somebody down and migrate it for us for $10,000 or more because it would have to be modified to run on a newer version of DB2 (according to the company - it is a ridiculously complex DOS based application)


granbajo

 "I  find this a bit funny as well, I guess the easiest answer would be to add more disk.  "

Carlos - I will go try your suggestions out right now.


We have asked our supplier to try and find some drives that are compatible with our array controller and so forth and it will cost over $3000, if they can find them. The server is only a 350MHz with 384Meg of RAM, it isn't worth putting $3000 into it.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:awilson11
ID: 12112654
Carlos,

I ran the commands and we are using circular logging. Here is what they were:

Number of primary log files                (LOGPRIMARY) = 3
 Number of secondary log files           (LOGSECOND) = 57

I also checked the physical log files each of the three database have 3 log files and they are each bout 2 meg each.

Let me better explain our situation so you know why the other options are not being considered.

First, the application is an expensive, $1 million +, insurance rating pacakge. The software itself isn't that expensive it is the constant updates to the insurance laws and rules and so-forth that we are buying. The application we have uses DB2 like linked flat-files, it is completely non-relational. The client software is DOS based and when the insurance rater wants to pull up a record the DOS app connects to the DB2 database and downloads the records to a local Access database where it is worked on and then when the rater is through loads it back into the DB2 database. The whole process is non-transactional, there are no database backups, restores, or maintenance, it is completely hand free.

The company has a nice new modern Windows based application that uses a fully relational database in the database of our choice, Oracle, SQL Server,DB2. However this application is $1.5 million. We don't want to buy it becasue we are in the early stages of a complete platform migration from a mainframe environment running COBOL and RPG and a proprietary database to Windows Server 2003, SQL Server 2000 and VB.Net (the only app we currenlty have running on Windows - other than stuff like Office -  is this DB2 insurance rating package). We are looking at replacing all of our systems including going with a different company that provides the insurance rating software.

For now all we (the IT dept) want is to buy ourselves enough time to last until the VP's have decided on what new systems we will be buying, 12 - 18 months, however we only have space for a few more weeks at best.

The company that makes the rating package has suggested that we run REORGCHK and REORG but they say they are not DB2 people and don't know if this will help or not. We don't know what this will accomplish, if anything, and what the implications are to do this.


I am currently studying for MCDBA for SQL Server 2000 and know you can compact SQL Server databases and remove some empty space from the tables, but I know nothing about DB2 and have no documentation about the internal workings of the rating pacakge and database configuration.
0
 
LVL 3

Expert Comment

by:granbajo
ID: 12113024
OK, so the logs are not an issue, you are using circular logging, so you won't get anything from there.

  REORG is usually used to improve performance.  Basically all the rows are stored on disk in pages, usually 4K pages, usually many rows per page.  When you fetch the data you fetch a complete page, ideally you will want to fetch as few pages as possible.  A REORG would let you make sure that the rows are stored in order, based on a particular index.

  Sometimes, if over time you delete and delete rows, you could potentially have free space on some pages.  But I don't expect it to be that much.  This is really why you don't have a compact utility like in Access.  If this was the case, then a REORG would get rid of that.

  REORGCHK just check the tables to see whether they need to be reorfganised or not.   So from the command window run

db2 REORGCHK ON TABLE ALL

  Basically if you have a start ('*') in any of the last three columns of the report, then it is recommended that you do ta REORG.  Everytime you do a REORG, you have to do a RUNSTATS to update the statistics, otherwise DB2 thinks that your table is still all over the place and the access plans might not be the ideal ones slowing the access to your database.

  A good tip for running RUNSTATS ion all the tables at once (it may take a long time though) is to run REORGCHK with the update statistics flag after the reorg.  There are full details on the REORGCHK output on the Administration Guide book on-line

db2 REORGCHK UPDATE STATISTICS ON TABLE ALL

  Finally, if adding disk will cost you something like $3,000.  You can get a pretty decent desktop for half that money (which is all you need by the sounds of it) with gigs of space, get an image of the machine with something like ghost and put it on the new PC!

Carlos
0
 

Author Comment

by:awilson11
ID: 12114205
Tht is what I thought about REORG, but the IT supervisor still thinks that running REORG is going to get rid of the deleted files in the table, I tried to tell him all it is going to do is reorganize, similiar to defraging a hard disk.

My recommendation is that we get a new server and migrate the whole thing over to it, for around $2100, but that is up in the air. In the meantime the IT supervisor wants me to pursue some way to compact it and that was why I posted this question, though I believed it to be a waste of time.

I will give you the points for all the help and work you have put in. Thanks for your help.
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 12156994
if you are short on disk space, reorg probably wont work because db2 will run out of disk space as it builds a temporary table to hold the reorganized data, so I doubt that will help you. You can try reorganizing smaller tables first to see if there is enough disk space to complete the reorg command. Reorg, if successful, will reclaim empty empty space (if any) since the data pages are compacted, however, in your case, I would be careful about running runstats after a reorg, especially if you are satisfied with db2 query performance as it is. I would first compare the basic stats in the catalogs to the tables themselves, for example, is there a close correspondance between the number of rows listed in the system catalogs compared to the actual number of rows in the table?
If you have secondary logs defined as 57, it means that when the first 3 log files are used up in a given transaction, db2 will create secondary log files up to a maximum of 57, so that means a potential 57*2MB=115 megs of disk space devoted to the secondary logs. When the database is idle or the secondary log files are not required, you wont see them in the log file directory, you only see them if db2 needs to create them. Once the transaction that required the logs is finished, the secondary logs are deleted, so this has some impact on performance, since if you are constantly waiting for logs to be created and then deleted, overall database performance will be impacted due to the I/O wait, log write, transaction commit/rollback, log delete. You can find out how much log space has been used by running the db2 get snapshot for database manager command to see if 57 is really  needed.
Another way to accomplish the reorg is to backup the database, install db2 5.2 on another machine with plenty of diskspace, restore said backup into new database, reorg all the tables, compare the database data file sizes and if there is a real big difference, backup restored database and then restore the restored backup into the original database.
0
 
LVL 4

Expert Comment

by:bondtrader
ID: 12160359
...or just export the data to a Network share drive, drop and recreate the table, reload the data, and do a runstats - pretty much the same thing as a reorg...
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 12165685
well, over 100+ tables, I think that would take quite some time, dont you?
much easier to generate a script for the reorg using the system catalog tables, like
db2 select 'reorg table', concat(tabschema,'.') concat(tabname) from syscat.tables where tabschema=etc etc

your way it is export 100+ tables, load 100+tables, reorg etc etc, its a scripting nightmare!
0
 
LVL 4

Expert Comment

by:bondtrader
ID: 12180121
Yes, but chances are only a handful of tables are the main tables and the ones that would benefit from any type of reorg.  A sibngle reorgchk command would reveal the size and reorg needs for all of the tables.  Of course, a knowledge of the tables that have deletes would help too.

A single db2move command could export all the tables, then you could drop the tables, and run a single db2move command to reload the tables, then do a reorgchk update statistics and you're done.  of course, if there referential integrity, things get a bit hairier!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now