Hello,

DB2 Version 8 FP12 , Windows 2003.
HADR Enviornment.

One of my tablespaces contain only indexes for one big table (contain over 85 million rows) which is in another table space.
This table space is DMS and 4K pages and contain 3 containers (each over 20GB)

The TS which contain indexes has only 14% free. The HWM is very high. I tried to reduce with IBM help and it was not success. Now I am planning to create a two new TS for the table and indexes with bigger page size.

Question 1. How can I choose the correct page size (8,16 or 32K)?
Question 2. Do I need to create a bufferpool with the same page size?
Question 3. As HADR is in place, i dont want to use the LOAD for the data migration. So Import/Export or CURSOR is suitable for my enviornment?
Question 4 Before I start this above options, i am thinking to reorg the table. due to the HWM and the less space, will reorg cause any issues? what are the reorg parameters can be used to get maximum results?

Please advise me. Thanks, Gunas.

If this index tablespace is only used to store indexes (no tables) and all indexes are for one table only (one "index object" in the tablespace), then I believe that the HWM should be the same or very close to the number of used pages. If the index tablespace contains indexes for more than one table, then the only thing db2dart /lhwm may suggest is to drop and recreate the "index object" for some tables. If the HWM is the same or close to the number of used pages and the tablespace size is close to the limit, then you need to go to 8K in v8.

What about data tablespace? Is it close to the limit? Do you really need all indexes defined on the table? Can you get rid of some of them (may be some are not used or redundant)? If you can, then you may drop all indexes defined on the table and then recreate the ones you really need. This way, you don't have to drop and recreate the table. This will buy you some time until you can migrate to v9 and use large tablespaces. Think about this option...

Thanks for the advise.
The TS id 4 contain only 1 table and the TS id 10 contain 7 indexes for that table. Please see the detail below. I have already droped and recreated all indexes and i could not recover more space as expected.


Tablespace ID = 4
Type = Database managed space
Name = DATASPACE1
Total pages = 16777216
Useable pages = 16777152
Used pages = 13000544
Free pages = 3776608
High water mark (pages) = 15209792
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 2
---------------------------------------------------

Tablespace ID = 10
Name = INDEXSPACE03
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 16112640
Useable pages = 16112592
Used pages = 13629376
Free pages = 2483216
High water mark (pages) = 13629376
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 32
Number of containers = 3

Your main concern should be the tablespace getting close to the maximum limit of 64GB. The total pages of tablespace ID 4 is at the max and tablespace ID 10 is very close to the max (you could still add a bit more space to it). You don't need to worry about the HWM in your case. You may be able to reduce it for ID 4, but this should not be your main concern.

Is this table changing a lot, do you add a lot of data? Is there some old data you can purge? If it doesn't change a lot, then you should be ok for some time and wait until you migrate to v9. If you add new data and there is no way to purge old, then you need to go to the next page size by creating the bufferpools, tablespaces, unloading and reloading the data (load with copy yes should be ok in HADR env).

But if you don't add lots of data, then I'd suggest to wait until you migrate to v9 and convert to large tablespaces.

Thanks.
Yes, this is the main table. we add average 1GB worth of data every week. we dont delete any rows from this table. so i cant purge any.

So only way to resolve this, i think that i have to create a new tablespace with new 8K page. I have never done this before. i will try this first on our test server.

One more question.
I am tring to REORG all indexes (INPLACE) and after 35min the REORG was failed with transaction LOG full message. Our primary log =60 Secondary is =40. Logsize=10MB.
Do i need to increase the number of logs ? any idea that upto how much i can go?

Thanks,

You mentioned inplace, are you doing table reorg or index reorg. Inplace table reorg is recoverable and so it needs to be logged and can cause a significant amount of logging to be performed. Hard to tell how many logs it will need... (take a look at infinite logging - logsecond = -1 in the manual). You don't have enough free space in the tablespace so I think you need to use a temporary tablespace during reorg (can't use for inplace).

Also, why are you doing reorg if you plan to move them to a new set of tablespaces? You can use reorgchk to see if you really need to do a reorg.

1. You need to factor in the maximum size of the tablespace for each page size, as you are aware.

2. Yes, you will need a bufferpool with the appropriate page size.

3. Make sure you use COMMITCOUNT parm on import to do periodic commits (1000 is fine) so that your logs do not fill up.

4. If you are exporting and importing, I don't know why you need to do a reorg. You can specify an "order by" on the export if you want to.

I have dropped one index from INDEXTABLEspace (after talking to application providers). That perticular index size is around 12GB.

But the LIST TS SHOW DETAIL commannd still shows same as before. No change of free pages. Thats why I am trying to REORG all the INDEXES for the table.

Any idea, how can i recoverd the pages?

Thanks,

This is expected. Because pages from any index on the table can be interspersed throughout the index object, when an index is dropped, its pages remain allocated (but empty) to the index object (to be reused by other indexes on the table). It's only when all indexes on the table have been dropped that the pages are released and list tablespaces will show more free pages. I don't think you need to take any action since other indexes defined on this table can reuse the space from the dropped index.

But still you need to get ready to go to 8K (data and index tablespace are close to the limit) or migrate to v9 and convert to large tablespaces

Thank you so much for all the valuable advises. I will start planning to move the table to bigger page size. I am sure that i may need more advise during the migration. Until then THANKS...gunas.

I may be wrong, but not sure you can use infinite logging with HADR and logarchmeth1.

If you use this method with HADR, I believe that a copy of the input to the load command must exist on both primary and standby servers or a shared mount point. I would practice this on a test table before it is tried on table that is needed.

Please check the following link if you decide to use the load command:
DB2 Universal Database

Look at the "Load operations and HADR" section

Also, "index object" = all indexes defined on the table

Marcus was right. Infinite logging is not supported with HADR - all DB2 versions.