Hi,
on web page http://www.db2mag.com/qanda/2001q1_h...tionPoll.jhtml
there is recomendation of setting DB2_STRIPED_CONTAINERS=ON if using RAID disk devices before creating tablespace.

Does it make any sense to set this db2 environment settings after tablespace is created? I didn't set this variable
at tablespace creation time. Is it too late?

Thanks,
Grofaty

this is a setting for pre v8
and look in the info center online V8 for this setting and read :
....Users are thus strongly advised against setting this registry variable... and

There will be no impact to existing containers that were created before the registry variable was set.

guyprzytula, thanks for reply.

One more question related to RAID disks. Intel server has 4 physical disks incorporated into one RAID5 aray (RAID is hardware configured). So Windows operating system see only one logical disk (with only one partition).

How should I set IOSERVERS parameter? Should I set "IOSERVERS = 4" or should I set "IOSERVERS=1"?

My system:
- Intel server
- Windows 2003
- DB2 workgroup edition v8.2.2 (v8 fixpack 9).

Thanks,
Grofaty

always define more (eg 4)
there is an overhead for defining more, but this can be ignored to the gain you could have from use of parallel io with more ioservers

guyprzytula, thanks a lot.

One more question...

In Admin Guide I have found PREFETCHSIZE setting related to RAID5 settings.
I have executed the following command: "db2 list tablespaces show detail" and output is:
If I understand correcty the prefetchsize of tablespace should be defined as:
Prefetch size = (number of containers) * (number of disks per container) * extent size
In my case "prefetch size" = 1 * 4 * 32 = 128. Did I understand this correctly?

One more question about db2 environment. In the Admin Guide there is also written:
If I understand correctly the DB2_PARALLEL_IO db2 environment variable should be set.

Should I set this two settings to increase performance? Is there any site effect, that I should be aware of?

yes : set already db2_parallel_io setting.
and later change the prefetch size
it is never recommended to change different setting at the same time, in case the result is worse, you don't know which setting implied this behavior.

Thanks a lot.

I always change parameters with commands and I save the changed commands in text file. So if something goes wrong I can change it back.

All-

I want to discuss my problem here.

I have got tablespaces with only one container . ANd surprisingly the
Prefetch size is 3 time the extent size. And more than one instance was set up with this kind of configuration .

And on Top of that my unix Admin told that these containers were created across multiple physical disks.

Is this a correct setting ? Can i improve the performance of the system by chaning these values ?


Btw- DB2_PARALLEL_IO is strictily for RAID . Is n't it correct?

Extent size (pages) = 16
Prefetch size (pages) = 48
Number of containers = 1

Hi,
DB2_PARALLEL_IO is only for RAID. With disk settings you tell DB2, that you have more physical disk inside operating system logical disk.

But I don't understand the statement: "And on Top of that my unix Admin told that these containers were created across multiple physical disks."

If you have only one container in your tablespace, than only one physical disk is used. If not using RAID disks then the optimal configuration is one container by physical disk. So if you are using only one container by tablespace, you probably don't have optimal configuration applied.

Hope this helps,
Grofaty

DB2_PARALLEL_IO is not just for RAID. A DBA can create one container per separate singular physical disk (let's say 8 disks) and then set db2 parallel on. DB2 will now evenly spread the data accross all 8 containers. I have heard that this was the original intent of this parallel feature in the first place because. It is similar to a software RAID 0. You can get the benefit of parallel I/O without having to buy RAID. I don't recommend using single unprotected disks though... Use at least RAID1.

As for "And on Top of that my unix Admin told that these containers were created across multiple physical disks." A single container can be spread accross many physical disks by two methods.

1. Build a container on a file system that is built from a hardware RAID

or

2. Build a container on an AIX file system built on a volume group that is comprised of many logical volumes 0- each lv belonging to a separate pv (disk). THis is AIX striping of physical disks. Again, similar to RAID.

In addition, an AIX admin can stripe many RAID disks into volume groups. This is sometimes called "striping a stripe". If you use this latter method + build many containers, this is sometimes referred to as "Scattering your data accross the Universe". This can be really good or bad depending on the data access type. Mostly this method is used in conjunction with SANs. I have had SAN "experts" tell me to do this scatter method and other say it is horrible. Only testing bares this out, one way or the other.

Steve Harris
Certified DB2 DBA

Thank you Steve, your comments was usefull, I have a question

recently we have moved to SAN system, what is your recomendation for changing in db, dbm cfg and registry variables for better performance ? we are using db2v8.2.3 and aix 5.2, do you know any reference related to this ?

Thank you in advance for your help

M_RAS,

You should start out with the "Best Practices for Tuning DB2 UDB v8.1 and its Databases" white paper from IBM. There may be a newer one for 8.2 now. I outlines most of what you will want to know. This paper will probably answer your questions much better than most anthing else.

Remember that each system, being unique, requires its own special settings.
Even systems with the exact same program and database (such as SAP) will need differing setting due to the data size differences, number of users, etc.
There are just too many variables involved that preclude one from saying, "these boilerplate settings are the best."

Plus, if you do the research yourself by trial and error, you get move futher along to becoming an expert

General Observations and Advice:

First, I assume you are using DMS tablespaces exception for the catalog, userspace, and temp(s).

Second, DB2 best practices can differ depending on the SAN manufacturer you are using. Some say to build only one large container for data and one for indexes per tablespace. Others say to make one container for each non-partity physical disk in the RAID. Yet again, others say to make one container per RAID. EMC, IBM Shark, IBM FAStT, Net Appliance, etc. each have a different best practices. The famous "it depends" answer applies here.

I suggest that you also look for white papers from the SAN manufacturer. Finding anything for DB2 is difficult, but EMC and IBM do have papers on DB2 UDB.

Third, start out with moderate settings and test. Change only one setting at a time and test. If you change 2 or more at once, you may not get the result you expect and conclude all the changes were bad, or get a good result and conclude all changes were good - but the result would have been even better if one of the setting had't been changed. Yes it is tedious, but a DBA's job is supposed to be Exception - some settings need to be changed together by design and it is appropriate and maniditory to do so in these cases.

I set the I/O Servers and Prefetchers fairly low, based upon the number of CPUs and non-partity disks. I then ran load tests and looked at the snapshots to see if I needed more cleaners and prefetchers.

Fourth, OLTP vs OLAP, or a lot of static reads vs a lot of dynamic writes? It makes a big difference which type of data access your programis doing. The databases I work with are 3rd party canned (off the shelf) and cannot be changed a lot. They are a mixture of all data access types, so I have to tune to the worst of all worlds. If I tune for super fast reads, the write speed suffers and vice-versa. Finding a balance is an art and takes time and testing.


-- Steve

Thank you again Steve for your help and recomendation. it seems I am at the begginig of a long way, no problem it is the life.