Hi,

On article http://www.db2mag.com/qanda/2001q1_hayes.shtml it states:
First, before creating your tablespaces, use the db2set command to set the environment variable DB2_STRIPED_CONTAINERS=ON . By doing so, you will reduce physical I/O by as much as 50 percent.

1. Is the benefit the same with SMS or DMS tablespaces?
2. Can this be done this way:
a) offline backup of database
b) drop database
c) db2set
d) restore database
Will this parameter be applied? Will performance increase?

Thanks,
Grofaty

You only need to worry about this if you have version 7. With version 8, stripped containers on is the default. Not sure what happens when you upgrade from version 7 to 8 for new tablespaces (old tablespaces will not change).

1. I believe DMS only.
2. Assuming the original DMS tablespaces are completely gone, then I think it may work (but no guarantees). I would check the db2set value after the restore to make sure it has not changed back. You might also consider a redirected restore. Don't forget to restart DB2 after the db2set.

The purpose of doing this is to ensure that the extent size that is equal to, or a multiple of, the RAID stripe size. Without this change to the environment variable, there is a one-page container tag at beginning of the tablespace, and the extents will not line up with the RAID stripes, so it may be necessary during an I/O request to access more physical disks than would be optimal. When using DMS table space containers this problem is avoided by allocating the tag its own full extent (not just a single page). This avoids the problem but it does require one extra extent of overhead within the container.

Whether you will see any meaningful performance increase depends somewhat on the nature of your application. Decision support systems with table space scans will see a bigger difference in performance. Buffer pool hit ratio is also important in whether you will notice a difference.

Hi,

I use db2 v7.2 fp5 on Win2000. Both: database logs and tablespaces are saved on ESS system. I use SMS tablespaces. Will there be any advantage to set "db2set" parameter.

I have also heard from IBM personal, that RAID strip size and tablespace extend size should be sinhronized. Can someone post any suggestion how to set this up or post web page.

Thanks,
Grofaty

First you would need to ask your System or Disk Administrator what the stripe size of your RAID system is. Typical stripe sizes for RAID are 32K, 64K, or 128K, although ESS may be different. Then make sure that your tablespace extent size is a even multiple of the stripe size. But understand that extent size is usually expressed as number of pages, not K. So an extent size of 16 pages (assuming 4K pages) is 64K. Therefore, an extent size of 16 pages would be suitable for a stripe size of either 32K or 64K.

Hi,

I probably have to create new tablespace. Is it posible to alter existing tablespace and set new extant size?

Thanks,
Grofaty

You cannot alter the extent size of an existing tablespace.

Hi,

Marcus_A, thanks. Does this logic apply to SMS and DMS tablespaces? I use SMS and as I have tested extent size can be defined for SMS as well.

Thanks,
Grofaty

Hi,

What about "prefech size" of container? Is here the same logic as with "extent size"?

Thansk,
Grofaty

Hi,

Strip size is 32 KB. Extent size is 128 KB (32 pages * 4 KB page). So extent size is multiple of stripe size.

Any performance tip how to increase disk activity (and decrease insert time).

Thanks,
Grofaty