Hi All,

I am new to db2 field (but have exposure to Oracle Optimization side)but i got a project in which i have to work on the optimization of the COBOL Programs and performance improvement of the DB2 UDB Server.
What are the areas in which one can look for the optimization? can anyone tell me step by step process to follow for getting the performance improvement.
If we declare a column or group of columns unique then do we have to give create Index statement for that or can i give alter table add constraint contsraint name unique(list of columns) ... wat will be the difference between this two?
How to find out the list of indexes which we can remove from the database?
Whats the importance of NLEVEL column in SYSIBM.SYSINDEXES? i came to know that indexes having NLEVEL greater then or equal to 3 are highly inefficient?
Thanks in Advance to everyone and an early response will be highly appreciated.
Vivek

If you create a unique constraint (or a primary key), DB2 will create unique index to ensure the constraint is enforced. The only exception would be if an unique index already existed on the same columns. Some DBA's like to create the index first, and then the constraint later, so that they can name the index themselves (if DB2 creates the index then it comes up with its own index name).

Unless there is a feature added in version 8.2 that I don't know about, you cannot directly determine which indexes are not used. This could indirectly be done by running a SQL Snapshot and explaining all the statements, and checking the explain tables against the list of indexes in syscat.indexes. If you are using DB2 for z/OS and OS/390, you can use the "explain=yes" option on the bind option (instead of the SQL Snapshot) and check the PLAN_TABLE against SYSIBM.SYSINDEXES.

NLEVELS is the number of index levels in the B-Tree structure. It is a direct indicator of the number of index rows (one index row for each table row), and not a direct indicator of performance. Therefore if a table has a large number of rows, then obviously the number of levels in the index may be 3 or greater.

However, if you have a query running against a large table and a tablespace scan is being used (instead of an index), or maybe a scan of the entire index is used (rather than using the B-Tree of the index) in the access path, then performance will obviously be slower for a large table than for a smaller table. For a select statement which has an "=" predicate on a column with a unique index, there will be "virtually" no difference in performance related to the number of levels in an index.

Frequent reorgs of the table and indexes can help ensure optimum performance, regardless of the number of rows on the table or levels in the index.

Thanks a lot for so quick reply.

Can you please let me know what are the other areas in which one can look into for the optimization and improvement purpose?
Is there anyway through which we can know wat are the actual area of bottleneck in DB2 ? like it is frequently giving page faults, deadlocks,buffer busy waits. etc.
Is there anything like row chaining and row migration in DB2?if it is there then how to remove that?


Waiting for your early reply.

with regards
Vivek

There are numerous areas in which DB2 itself can
be optimized - not only the individual tables remedied by
indexes.

There is a great book on optimization available from redbooks,
please search their website. Some of the major areas for optimization
are:

Buffer pools, prefetch size, parallel degree (processors),
parellel I/O (containers), query optimization degree,
system heap sizes & locking strategies.

SY

Ya its true there are so many ways for performance improvement. In my project we have seen improvement in performance after using "reorg" on the tables. Reorganising the tables will query the records very fast. Moreover in case you are using OLTP system then use smaller page size and for DSS use larger page size. Try to use Database Managed Tablespace if you are looking for better performance and place the tablespace in multiple containers spreading accross multiple physical drives which would help in parallel I/O.
You can download the Db2 Knowledge Expert from Quest which would help you in getting more inputs to improve performance.

Can Anyone tell me that in ideal condition how many Bufferpools should be there in the database.In our system we have only 2 bufferpools (BP01, BP32K) and 234 tablespaces and most of the cases we are only using BP01.

What should be the standard for bufferpools keeping in mind that we have around 450 tables,474 indexes (including type 'P','U','D').

There is no standard and it varies by the database/application design. There are also as many different opinions on this as there are DBA's (maybe more).

However, IMO, working with DB2 on all platforms since 1987, I think that there should usually be no more than three 4K bufferpools. One 32K bufferpool is usually needed for large joins or tables with rows larger than 4K.

For OLTP applications, generally 4K page sizes should be used, and for Decision Support systems (with lots of tablespace scans) larger page sizes (and larger page sizes for the corresponding buffer pools) are usually best.

But in general, here is a good starting point for a large OLTP database (naming convention is arbitrary):

IBMDEFAULTBP:
- Syscatspace
- Frequently used Small Tables
- Small and Medium Size Indexes

BP4K1
- Infrequently used Small Tables
- Medium size tables
- Large Indexes

BP4K2
- Large and Very Large Tables
- 4K System Temporary Tablespaces

BP32K
- 32K System Temporary Tablespaces

In the above scenario, the IBMDEFAULTBP should be large enough to hold at least 75% of all the tablespaces pages assigned to it, BP4K1 should be large enough to hold at least 35% of the tablespace pages assigned to it, and BP4K2 should be large enough to hold at least 10% of all the tablespace pages assigned to it.

BP32K should be relatively small, unless you have user tablespaces larger than 4K.

For a production database, you should allocate as much memory to the bufferpools as possible. Usually you are limited to around 1GB total bufferpool size for 32 bit instances (depending on your operating system). If you using V7, you will need to significantly increase your dbheap if you have large bufferpools.

Make sure you have real (not virtual) memory available for allocating DB2 bufferpools. Leave at least 25% of the total system memory for the OS and other DB2 memory requirements.

The more memory you have for bufferpools (relative to the total size of all the data), the fewer bufferpools you need. If all your data (including sycatspace) is less than the total size of the memory available for bufferpools, then you only need one bufferpool (except for a small 32K bufferpool).

Can Anyone send me the sample log file which shows different parameters which one has look into for the performance related issues.
kindly tell me about all possible logs or snapshots which should be monitored and wat parameters are the cause of the performance degradation and how to overcome that.

I am not a dba and only thing which we know that some jobs are taking about 4 to 5 hours of CPU time and we have to reduce that time and minimize that CPU Usage.

Any early reply will be highly appreciated.

Thanks a lot Marcus for you replies i hope you will get back to me soon with some sample files.

With Regards
Vivek

As in Oracle we have three different types of Buffers in database buffer cache(keep,default and recycle) wat are the corresponding bufferpools in DB2?
We have some tables in our database which are accessed quite frequently from GUI screens and from batch jobs.
Will it be good idea to create a buffer pool (corresponding to KEEP in oracle) and allocated to these tables so that it is always there in the database buffer cache and whenever the data pertaining to these tables are required, no I/O will be required to get these data into buffer?
Can anyone please help me to explain in detail and the possible bottleneck which i will be facing in doing so.

With Regards
Vivek

Before Oracle 9i , we have one block size.Oracle 9i has introduced
different block size, so we can create tablespaces of multiple block size
caching these blocks to their cache buffer.

DB2 has somewhat same concepts
We can create multiple buffers pools of different page size and offcourse
their own size.

You create tablespace and assigned bufferpools to that tablespace
with the same page size.

regards

Mujeeb

Can anyone look at our database snapshot here and attached file and tell me wat can be done to improve the performance of our database .currently we are working on optimization of and performance related issues.


waiting for an early reply...

with regards
Vivek
> *-BUFFER POOL B-GROUP BUFFER POOL ================================================== ============================= > BUFFER MANAGER INFORMATION BMGR + Current Number Open Datasets = 1683 + High Water Mark Open Datasets = 2154 + Maximum Number Open Datasets Allowed = 10000 + Open Dataset Count In Active Pools = 27837 + + * + Pool VP HP Pages Pages Getp Read Prefetch Write + ID Size Size Alloc In Use Rate I/O Rate Req Rate I/O Rate + ------ ------ ------- ------ ------ -------- -------- -------- -------- + BP0 9000 0 9000 1365 3.61 .04 .00 .11 + BP1 4000 0 0 0 .00 .00 .00 .00 + BP2 5000 0 5000 35 .02 .02 .00 .02 + BP3 500 0 0 0 .00 .00 .00 .00 + BP32K 12 0 0 0 .00 .00 .00 .00

Marcus can you please have a look at the attached file and give me your valuable suggestions to improve the performance of our database .

I will be highly thankful to you .

With Regards
Vivek Srivastava

Hello Tank,

can you please look into the attached file and give me your valuable suggestion about the improvement of the database performance for our system.

Atleast tell me the area which can be optimized in our database as i am not able to get the clear picture with the file and parameters.


Thanks in Advance

Vivek

Look here:

http://www.db2mag.com/story/showArti...cleID=12803202

and

http://www.db2mag.com/story/showArti...cleID=15300098

cheers...