For Getting Snapshot of Database Parameters:

For analyzing the health of the database we need to change setting of certain parameter of the health monitor switches. First need to update buffer pool monitor switch to calculate BPHR.

 

To Know the status of database manager monitor switches

 

db2 get database manager monitor switches

 
It will list out the all the monitor switches. By default the monitor switches will be in off state. For the purpose of getting snapshot we have to put them in ON state.

 

Commands to set the Buffer Pool Monitor Switches ON

db2 update dbm cfg using DFT_MON_BUFPOOL ON

 
After updating these parameters wait for two to three hours and proceed to get the snapshot of the required databases in that particular instances using the following command

 

To get the snapshot of databases

 db2 get snapshot for database on <db name>

 
After getting snapshot switch off the monitor switches using following command

 db2 update dbm cfg using DFT_MON_BUFPOOL Off

 
This activity will require no downtime.

  

To Calculate Hit Ratio:

There are three hit ratio’s to be calculated from the snapshot of the databases. Use values of parameters taken in snapshot of database above.

 

            1. Buffer Pool Hit Ratio (BPHR)

            2. Package Cache Hit Ratio (PCHR)

            3. Catalog Cache Hit Ratio (CCHR)

 

1. Buffer Pool Hit Ratio:

It indicates the % of time that the dbm didn’t need to load a page from disk to service a page request that is page is already in the buffer pool. The > BP ratio lowers the frequency of disk I/O.

 

•  Formula:

 

BPHR (%) = (1 - (("Buffer pool data physical reads" + "Buffer pool index 
physical reads") / ("Buffer pool data logical reads" + "Buffer pool index 
logical reads"))) * 100

• Buffer pool data physical reads: The number of data pages that had to be pulled from a physical disk read 

• Buffer pool index physical   reads: The number of index pages that had to be pulled from a physical disk read                                     

• Buffer pool data logical reads: The number of data pages that had to be pulled from the buffer pool that were memory reads

• Buffer pool index logical reads: The number of index pages that had to be pulled from the buffer pool that were memory reads

 
The good hit ratio should be above 95%. So certain buffer pool parameters needed to be increasing their parameter values.

                                    

a. db2 connect to <db name>

b. db2 get db cfg for <db name>

 
Look for logbufsz parameter and its value keep increasing it in multiples of 4 and calculate hit ratios again and again until we get good hit ratio.         

c. db2 update db cfg for <db name> using logbufsz 64

 Note: logbufsz should be less than dbheap

 
If the BPHR remains low, you may need to redesign your logical layout in the Creating buffer pools and creating table spaces. To increase the buffer pool hit ration, try the following:


To check the size of the buffer pool fire the following command

db2 “Select * from syscat.bufferpools”

To alter the size of the bufferpool fire the following command

db2 Alter buffer pool ibmdefaultbp size 2000

Then check whether the size of the buffer pool using the following command

db2 “Select * from syscat.bufferpools”

Consider allocating multiple buffer pools, possibly one for each frequently accessed large table with its own table space, and one for a group of small tables, and then experiment with different sizes of buffer pools to see which combination provides the best performance.

2.      Package Cache Hit Ratio (PCHR):

The package cache sets the amount of database global memory to be used for caching a package's static and dynamic SQL statements. The package cache is used for caching sections for static and dynamic SQL statements. The Package Cache Hit Ratio (PCHR) should be as close to 100% as possible without taking needed memory away from buffer pools

 

Formula:

PCHR = ( 1-(Package Cache Inserts / Package Cache Lookups))*100

                                                                                           

• Package Cache Inserts: When a new package section is compiled and inserted          into the cache i.e., package cache insert count is incremented.

• Package Cache Lookups:When a request for a package is made and a lookup is performed check if this is already available in the cache so, the value is incremented, whether or not a package   in the cache satisfies the request substituting the values.

         

db2 update db cfg for <db name> using pckcache_sz 4096

 

3.  Catalog cache hit ratio (CCHR):

The catalog cache is used to store table descriptor information that is used when the tables, views, or alias have been referenced in previous statements. This element includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever:

• A table, view, or alias name is processed during the compilation of an SQL   statement.

• Database authorization information is accessed

•  A routine is processed during the compilation of an SQL statement

 
Formula:

CCHR = (1-("Catalog cache inserts" / "Catalog cache lookups"))*100

 

Catalog cache inserts:Number of times that the system tried to insert table descriptor information into the catalog cache.

Catalog cache lookups: Number of times that the catalog cache was referenced to obtain table descriptor Information.

         
The hit ratio above 95%, it can be taken as a good hit ratio. A smaller hit ratio is the indication that the catalogcache_sz should be increased.

 

db2 update db cfg for <db name> using catalogcache_sz 1024

Set the catalog cache, package cache, and log buffer size to an appropriate size to improve performance.

The log buffer holds log records in storage until they are written to hard disk drive. Log records are written to the hard disk drive when one of the following occurs:

    * A log buffer is full
    * A transaction or a group of transactions commits