We have a table of 4 millions records. It is heavily acess by web interface. Each request some times requires around 80,000 records. These type of queries are making exclusive lock on the table for select criteria because of more records. To improve the performace of the database as well as application, we decided to split the data across mutiple tables. Are there any techiniques in db2 to achieve this functionality.

Advanced thanks from Palchuri

I don't know any way of splitting the table, except partitioning, but if its only reading data, why not do an dirty read, where it won't put any locks on the table.

What are you trying to acheive?

If you are getting table locks for a SELECT at the table level, they will be S (share) locks, which do not interfere with each other (they can exist simultaneously among different applications against the same data). Although table locks are thought to have less concurrency than row locks, if the locks are all share locks the table locks are preferable because there is no contention and DB2 spends less time locking each row.

To improve performance, what you want is intra-partition parallelism if you doing tablespace scans against a large volume of data. This is available in all versions of DB2 (DPF is not required). To what extent you should do this depends on what resources your server has in terms of number of CPU's and number of physically separate disks and controllers (or in the case of a RAID implementation, number of physically separate arrays).

There are two ways to implement intra-partition parallelism:

1. For a single tablespace create multiple containers for the DMS tablespace, with each container on a separate physical disk or array. The number of containers should be the same as the degree of parallelism desired (see above for determined this number). The prefetch size should equal the extent size (such as 32 , 64, etc) times the number of container in the tablespace.

2. Create multiple tables and then access them via a UNION ALL View. See the link below for more information. Again, the number and placement of tables should take into account the amount of parallelism desired. In this case, keep each table on separate disks or arrays if possible (multiple containers are not necessary). http://www-106.ibm.com/developerwork...202zuzarte.pdf

For both 1 & 2 above, you will need to set certain DB2 parameters to enable intra-partition parallelism, such as Degree of Parallelism, Number of I/O Servers, etc. I don't remember them all, but a search of this forum should find them.

If your data logically can be divided by dates (months, years, etc) and there is an administrative benefit for creating separate tables for each period, then I would the UNION ALL View. In my experience it works very well.

Our's is on line trading application. Every night our application receives a couple of flat files from the back office(External application). Each flat file contains millions of records. Once we receive the file we will replace the existing table with the incoming flat files.

Our web application access the content of the table. Some of the requests may require around 80,000 records. If we receive mulitiple requests for the same table , it is creating contention on the database side.

Here are the challenges:
1. There is no identifier in the flat file to distribute the data between tables during load process.

2. There are tables with read only as well as read/write

thanks
shri

To come up with an optimal solution, there are multiple things to consider, many of which you have not explained here in sufficient detail. This is a forum to ask and answer specific questions about DB2, but this forum does not work well for designing a database architecture to fit specific application requirements.

If you don't have any DB2 experts on staff, I think your company would be well advised to hire a qualified consultant for a few weeks to evaluate your environment and design a suitable architecture that will meet the requirements of the application.