CODEWITHSUNDEEP
databasememorydb2
Ganesh
Ganesh

Reputation: 261

IBM DB2 Database becomes unresponsive

We have an IBM DB2 database and we run some tests through Fitnesse. After executing a couple of tests, the database starts to hang. So, we downloaded a DB2 profiler, DB2 Monitor, and we found out that the system defined userspace, "USERSPACE1", had a % memory usage of 99.6 %, after which the database starts to hang. This does not happen consistently. We also observed that some times, after reaching the memory usage of 99.6 %, the memory usage went down to around 30 - 40 %.

Upvotes: 0

Views: 326

Answers (1)

AngocA
AngocA

Reputation: 7693

First, USESPACE1 is a tablespace, and this is associated with disk/storage. The default bufferpool for this tablespace is IBMDEFAULTBP (Memory/RAM).

Probably, what you are looking is that the IBMDEFAULTBP is being used intensivelly, and suddently, its workload change, and the memory becomes used for other purposes. I think STMM is activated, and that is why you see memory spaces are balanced/changed and the following workload is related to locks, sorts, and that is the reason you see DB2 'hangs'.

Another scenarios could be if the USERSPACE1 got fully used, and automatic storage is configured for this tablespace, then DB2 will preallocate more space for it. This is the reason you see a 99.6% of usage, and then probably there is a massive delete or a table drop, and that is why you see the usage drops to 40%. At the same time, after that operation, the workload change, and you see the new behaviour.

Well, I explained two possible scenarios, but remember memory/RAM is associated to bufferpools, and storage/disk is associated to tablespaces.

If you want to know what is happening in your database, you should use db2pd or db2top (real time tool). The fact that db2 hangs is because it is doing something else, and you have to discover it. It could be a programming problem.

Upvotes: 1

Related Questions