HADR enhancements---db2haicu in V9.5

db2haicu stands for db2 high availability instance configuration utility.Db2 V9.5 has integrated HA feature which needs to be configured and maintained using db2haicu.
From version 9.5 there is no necessity to install heartbeat scripts seperately.Heartbeat scripts come with HADR and they need to be set up using db2haicu.

When db2haicu is run for the first time,it will search the database system for database configuration that is related to cluster configuration.Then it creates a cluster domain for instance if it does not exist.

db2haicu maintainance mode::

db2haicu will be run in maintainance mode once the cluster domain is created.Cluster elements such as databases or cluster nodes can be added or removed in maintainance mode.Also the failover mechanism can be defined using db2haicu in maintainance mode.

MDC---PART 1

Multi dimensional clustered tables can be used to improve performance in many cases.With clustered index you can only cluster data in one dimension.With MDC you can cluster data in multiple dimensions.The other difference between MDC and clustered index is that MDC does not require reorgs as the data is automatically clustered.Below is a create statement for a MDC table for MAKE base table.
CREATE TABLE MAKEMDC(
ID INT,
MAKE CHAR(30),
MODEL CHAR(30)
)
ORGANIZE BY (MAKE,MODEL)

CELL: Every unique combination of dimensions form a cell.A cell can have one or more blocks.
BLOCK:It is a block of pages that contain a unique combination.Its size(blocking factor) is equal to the extent size defined.
A cell for unique combination of FORD(make) FOCUS(model) will be like this

DESIGN OF THE MDC TABLES:
Number of cells can be estimated by executing a select with distinct clause on the dimension columns to arrive at the unique combinations of the dimension values.
For example, for the above mentioned MDC table,the number of cells can be known by executing the following query:
SELECT DISTINCT MAKE,MODEL from MAKEMDC;
A large value of C/R might indicate the wastage of space where C indicates the number of cells and R indicates the total number of rows in the table.So,dimension columns should be wisely chosen.Choosing a firstname column as dimension column with employeeid might generate lot of cells and thereby wastage of space.A column like state with 10 possible values might be a good candidate as it would have only 10 possible values

Space occupied per cell(SC) = R/C * average row size
If SC is less than (extentsize*pagesize) then there is a wastage of space.So,the design should be done in a way such that SC matches up the (extentsize*pagesize) value.
Following are the options that can be used to design MDCs optimally:
1)Decrease the extent size if the blocks are not filled up.Page size can be increased if there are too many blocks per cell
2)Granularity can be varied to decrease the cells ..For example a new dimensional column can be added to the totalbalance table defined as totalbalance/100 to decrease the cells.
3)New dimensional keys can be added or existing ones can be removed depending on the situation.
4)A larger extent size might help in reducing the I/O but it might impact the usage of the space.
5)It is always better to do a comparison with the base table before the MDC table is actually used.For example MAKEMDC mentioned above should be compared with the MAKE table(base table) interms of space usage and performance before the decisions are actually taken.

Reference::Advanced DBA Certification Guide and Reference for DB2 UDB by Snow and Thomas

GET_DBSIZE_INFO procedure and database size

GET_DBSIZE_INFO procedure can be used to calculate database size.It takes 4 parameters in total.3 of them are output parameters and one is input parameter:

snapshot-timestamp:This indicates the time when the database size was actually calculated.This is an output parameter.

dbsize:This is the size of the database in bytes.This is an output parameter.

dbcapacity:This indicates the capacity of the database in bytes.This is an output parameter.

refresh-window:This is an input parameter.It indicates the number of minutes after which the database calculation values are refreshed.


Examples::
1)CALL GET_DBSIZE_INFO(?, ?, ?, -1) -1 which defaults to 30 minutes indicates that the cached values will be refreshed if 30 minute window is reached.

2)CALL GET_DBSIZE_INFO(?, ?, ?, 0) This indicates the cached values are refreshed immediately

3)CALL GET_DBSIZE_INFO(?, ?, ?, 100) indicates that the refresh happens every 100 minutes.

Automatic client reroute registry parameters

DB2_CONNRETRIES_INTERVAL and DB2_MAX_CLIENT_CONNRETRIES are the registry level(db2set) variables that can be configured for Automatic client reroute.

Example::
DB2_CONNRETRIES_INTERVAL=4
DB2_MAX_CLIENT_CONNRETRIES=2
DB2_CONNRETRIES_INTERVAL variable specifies the sleep time between consecutive connection retries, in seconds, for the automatic client reroute feature.This indicates that 2 clientretiries happen with a gap time of 4 seconds before the client rerooute actually happens