DATABASE_MEMORY calculation

DATABASE_MEMORY can raise to a maximum equivalent to the following calculation::

buffer pools + database heap (DBHEAP) + utility heap (UTIL_HEAP_SZ) + lock
list heap (LOCKLIST) + 2 * package cache (PCKCACHE_SZ) + catalog cache
(CATALOGCACHE_SZ) + shared sort heap threshold (SHEAPTHRES_SHR) if
INTRA_PARALLEL is ON + approximate 20% overhead

V9.5 New feature---auto_del_rec_obj

V9.5 has a new db level parameter auto_del_rec_obj .When this parameter is set to ON, the database manager automatically deletes all the backup images,load copy images and logfiles which are associated with the expiration status in recovery history file.

Recovery history file is automatically pruned based upon upon num_db_backups and rec_his_retentn parameters.So when this pruning happens with the auto_del_rec_obj turned ON, all the backup images and logs associated will also be removed.

num_db_backups and recovery history file

num_db_backups parameter (db level) controls the number of database backups to be retained for a database.This parameter specifies the number of backups that should be retained in the recovery history file.Once this number is reached , older backups are marked as expired in history file.The next database backup would prune these expired entries from the recovery history file.It should be noted that the actual backup images are not removed and it is only the entries in the recovery history file that are removed.

Entries in recovery file can have different types of status:Active,Inactive,Expired

Example for Active entry:

Start Time: 20071218191004 End Time: 20071218191016 Status: A

Active-->Using a active backup and current logs the database can be restored to current state

Inactive-->Restoring from inactive will take the db to a previous state than the current state

Expired-->It means that the backup is no longer needed as more recent backups are available

AGENT RELATED CFG PARAMETERS

---DBM LEVEL---

MAXCONNECTIONS--->Maximum number of connections that can come to the instance at any point of time(includes both local and remote connections)

MAXAGENTS--->This parameter indicates the total agents including subagents that can be allocated on an instance.The recommended value is atleast the sum of all the values of MAXAPPLS for all the databases that exist concurrently.

MAX_COORDAGENTS--->This parameter indicates the maximum number of coordinating agents that can exist.(Number of COORDAGENTS+Number of subagents )is controlled by MAX_AGENTS

MAXCAGENTS---->The maximum number of concurrent coordinating agents that can exist on any instance.This parameter can be used in cases of high concurrent transaction workloads.

----DB LEVEL----

MAXAPPLS----->This parameter is DB level limit for the maximum number of applications that can connect to a database at any given time.This parameter is also dependent on MAX_AGENTS and MAX_COORDAGENTS .

Experiments on connection concentrator

Experiments were done basing a DVD store application to verify the impact of connection concentrator.MAX_CONNECTIONS was set to 10,000 and MAX_AGENTS was set to 500.

Connections were scaled up each time in units of 2ooo.Throughput on the data server increased gradually as the number of connections increased.One other observation is related to memory consumed.With 2000 connections,the memory utilised was around 300 MB and it reached 1.5GB with 10,000 connections which is way less than the memory that might have been allocated with one-to-one association(Connection concentrator disabled).

Reference::http://devzone.zend.com/article/915-Performance-case-study-using-Zend-Core-for-IBM-with-IBM-DB2-9-to-service-10000-active-database-connections

Connection concentrator and performance

Connection concentrator is enabled when you set max_connections greater than max_coordagents. By default this feature is not enabled and this feature helps in applications where there are many idle connection at any given point of time.For example, any webapplication that has lot of users connected to database but are not performing any table level transactions might be benifited by enabling connection concentrator.

Connection concentrator architecture is illustrated on the left.Db2dispatcher process is the key component in the architecture and it plays the role of multiplexing the agents.Any agent associated with an idleconnection might be used to service any connection in the queue.This feature helps in handling huge set of connections with limited agents thereby saving memory allocated.

Reference::http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0511lam/