DB2 8.2 for SAP on AIX 5.3

Hey all,

I was just trying to figure out why there is a descrepency between the result of a LIST TABLESPACES SHOW DETAIL (which shows a particular tablespace has:
Total pages = 1275000
Useable pages = 1274976
Used pages = 935072
Free pages = 339904

Versus
Which gives:
TABLESPACE CARD NPAGES FPAGES
---------- -------------------- ----------- -----------
MS1#PROTD 1592543 37169 37294

I've run stats on all tables.

What am I missing here?

Um... Indexes and LOBs?

Nope, sorry...I should have said... all the indexes are in another tablespace. I don't believe there are any LOB's and in fact most of the tables in this tablespace are empty. The largest just got emptied out (7.5M rows deleted).

db2dart /lhwm yields:

Step #95: Object ID = 1034

=> EXPORT all data from table, drop the table, disconnect, reconnect,
recreate the table, and LOAD the data back into the table.

Table: SAPR3.DBTABLOG

DAT object size: 2
INX object size: 0
LF object size: 55040
LOB object size: 0
LOBA object size: 0
BMP object size: 0

Total size of object parts: 55042
Minimum number of extents that will move by this operation: 55042

Current highwater mark: 58440
Desired highwater mark: 0
Number of used extents in tablespace: 58441
Number of free extents below original HWM: 20364
Number of free extents below desired HWM: 0
Number of free extents below current HWM: 0

Now I see that it's suggesting that I drop and recreate the table, but I'm reluctant to do that until I understand why it's necessary....
Any ideas?

There are a lot of information that we don't know:

• is this the only table in the tablespace? (other tables could occupy space)
• what kind of tablespace is that? (DMS tablespaces have things like a free space map)
• have you run RUNSTATS recently? (if not, then the values in SYSCAT.TABLES may just be outdated)
• was the table reorganized? (that would free up empty pages again)

No, there are several hundred tables in the tablespace, most are empty or close to empty. The largest has just had all it's rows deleted (7.5Mil) but the space doesn't seem to be freeing up.

It's a DMS but the difference in space is far too large to be a mere overhead.

Runstats has been run very recently.

That's the problem, reorgs are not freeing up the space as I would have thought.

This problem has occurred on several systems now. I have managed to free up the space with the following procedure:

1. Rows deleted
2. Runstats on all tables in tablespace
3. Reorg all tables in tablespace
(this does not free up space)
4. Disconnect all application (I really don't want to do this on the production systems)
5. db2dart <DBID> /lhwm (giving the tablespace ID and 0 as the params)
This suggests a reorg with no access using the longlobdata option
6. Grep the output to get the tablenames in order
7. Create/run reorg statements based on the grep output (for some reason it only seems to work if the reorgs are done in the order advised by db2dart???)
--- the space is STILL not freed up----
8. db2stop (for some reason a FORCE is always required, I can't do this on Prod)/db2start
9. Runstats on all tables in tablespace

List tablespaces now shows the HWM and used pages has dropped and the tablespace can be resized (lowered by 90%)

So trhe problem/question is:

-How much of the above is Voodoo admin (ie stuff I'm doing because I think it's necessary)
-Why do I need to restart the instance to free the space, is this normal and can I avoid it?

Cheers,

Have you reorg'd that table to reclaim this free space? (This is one of the task that reorg does.)

On all tables in the tablespace?

In which table have you done the reorg? On all tables or just one?

2. completely unnecessary step only adding overhead; you can collect statistics on the fly during reorg
3. does free up space; how do you do the reorg? (options)
7. repeats work from step 3. You could determine the tables with LONG/LOB columns before step 3 and use the LONGLOBDATA option in 3. already
8. why do you recycle db2? And if there are still some connections, you should figure out which ones those are
9. is again unnecessary if you collect statistics during the reorg phase

Yes.
A 'reorg table <tablename> allow read access' run on all tables in the tablespace did not free up the space. It seems that only after a reorg with longlobdata option is run and then the instance restarted that the space is freed up and the HWM reduced.


2. I'm on DB2 LUW 8.2, and I can't see any way collect stats on the fly during REORG. I was under the impression that best practice was runstats, reorg, runstats....no?

3. I have tried a classic reorg and an inplace reorg...neither seemed to do the trick.

7. Is there any harm in just specifying LONGLOBDATA for all tables in the tablespace? If not I might just do this.

8. It seems to me (and I have by no means rigorously tested this, since once the space is freed up I can't really recreate the exact situation again) that the space and HWM are only changed AFTER a recycle, which is the main problem here since I can't easily do a recycle when I move on to my production systems.

Cheers

Did you also reorg all indexes on those tables? If not, then indexes may occupy space.

You are right. I mixed that up with REORGCHK.

As I said before, REORG frees unused space. I suggest that you pick one table and work through that first. Trying to handle all tables at once just complicates things.

It may slow down the reorg process.

Knut,

All indexes are in a seperate tablespace. The space is being freed up and the HWM lowered, but only after an instance restart. Obviously this isn't desirable for 24/7 production systems. If I want an outage I have to explain why DB2 isn't operating as expected.....

The HWM is lowered by RUNSTATS and/or REORG. But there are a few situations where it can't be lowered. Here is a nice summary in HWM and when it can/cannot be lowered by DB2:
http://www-1.ibm.com/support/docview...id=swg21006526
http://www****ban.de/DB2_luw/HWM_redu...reduction.html

Another question I have is why you want to lower the HWM? You delete a lot of data from the table. Wouldn't you expect the table to fill again at some later point in time? If so, then it doesn't make much sense to lower HWM and deallocate the already allocated extends. Allocating them again results in unnecessary overhead. So you should have another, compelling reason...

Knut

These tables will not fill again to the same degree and will fill vey slowly, also other tablespaces are growing a lot faster and are in need of space. So I have to redistribute the space.

I understand that runstats/reorg will sometimes not reduce the HWM but this has happenned on 5 seperate DB's so far, so it looks like it always happens to these systems...perhaps it's an issue with SAP, I don't know.
My main concern is that I always need to restart the instance, and while I can do this on m test/dev systems...it's a real problem for my 24/7 systems (20 databases on 5 servers!!)