pajama

Session in "Commit Active" means it's performing the commit of a Unit of Work, but not being able to finish the same yet for some reasons.

Out of many possible reasons following could be some common ones :

- The database is using HADR and at the standby side it's hitting a momentary HADR log receive buffer full situation.

- Network issues affecting HADR communications between primary and standby

- The database has very large history file

- Disk bottleneck at the storage side where the database transaction logs are kept

To identify HADR receive buffer full situation just run "db2pd -hadr" at the standby side while the "Commit Active" state is present at the primary side of the HADR and see what the output of StandByRcvBufUsed field shows. If it's near 100% then it can cause the "Commit Active" situation in the primary side. Consider increasing DB2_HADR_BUF_SIZE registry setting to address the issue.

If a network issue is suspected between two sides of HADR pair then that should be taken to the network administrator to check on possible network bottleneck or any other kinds of network issues.

To check whether history file have become too big. Check for the file db2rhist.asc file under database directory. If it's big (example, more than 100MB or so) then try to prune it down.

Bottleneck in disk containing the active log directory or, mirror log path can cause the "Commit Active" situation most. Usually, iostat output collected while the "Commit Active" is in place can show if the disk bottleneck is in place. As for example, under AIX operating system collecting "iostat -TDla 1 30" might show the column "%tm" reaching near 100% for the disk containing log paths. If the %tm is 100% and all the data flow in that disk shows zero, that will indicate a momentary stalled disk situation.