Can High Water Mark be cleared?

Question

Lately I experience below errors when restoring db2 database

• SQL2043N Unable to start a child process or thread
• SQL1084C Shared memory segments cannot be allocated

db2stop and then db2start always helps.

As a part of my job (testing) I restore databases often during the day. Current usage can be decreased via deactivating databases when is it really close to Memory Limit. But lately only deactivation didn't help and I was still getting these errors. Then I found out that HWM usage might be the trouble part as it is only increasing and after I do db2stop & db2start both Current usage & HWM usage are reset.

Is there any other way how to 'clear/reset' HWM on db2?

• DB2 9.7.2 on SuSE

Answer 1

From what I have seen reaching a High Water Mark means approaching some established limit. There are multiple "high water marks" in DB2 depending on which area you are talking about. Some relate to memory used for sorting/joining. Others relate to maximum agents allowed or differing heap sizes, or even package caching.

I'd recommend taking a look at this article on developerworks. Though it was written for an older version of DB2 (8.1), it still holds valuable nuggets for what parameters to tune when to get what you are looking for. I recommend taking a peek at it. If you search for high water mark on the page you find all the different instances I was referring to and which parameters tune them.

Also, take a look to see if you are running any hash joins and how many. This article is also helpful for understanding which parameters can be tuned that affect hash joins and certain usages of memory.