Does db2 agents only get what it need?

[Woody Ling]

>From the DB2 UDB memory model document, I found the following info:

==============================
The agent private memory set consist of the following memory pools.
Their sizes are specified in the database configuration parameters
enclosed in parenthesis:

Application Heap (applheapsz)
Sort Heap (sortheap)
Statement Heap (stmtheap)
Statistics Heap (stat_heap_sz)
Query Heap (query_heap_sz)
Java Interpreter Heap (java_heap_sz)
===============================

Since I am now using a 32bits DB2 in AIX and the agent private memory
area is limited to max 256MB, I wonder how my existing setting can be
handled by DB2:

Application Heap (applheapsz) 512 ~= 2MB
Sort Heap (sortheap) 10240 ~= 40MB
Statement Heap (stmtheap) 32767 ~= 134MB
Statistics Heap (stat_heap_sz) 20000 ~= 82MB
Query Heap (query_heap_sz) 1024 ~= 4MB
Java Interpreter Heap (java_heap_sz) 2048 ~= 8MB

Total 270 MB per agent that is larger than the limitation of the agent
private memory segment in AIX.

Actually I can have 7 concurrent applications with intra-parallel ON
that 2-4 agents are assigned to one application. How can these agents
fit into the 256MB agent private memory segment in AIX? Does db2
agents only get what it need? These settings are hard limit only?

[Liam Finnie]

On Mar 30, 11:10 am, "Woody Ling" <[e-mail]:

From the DB2 UDB memory model document, I found the following info:

==============================
The agent private memory set consist of the following memory pools.
Their sizes are specified in the database configuration parameters
enclosed in parenthesis:

Application Heap (applheapsz)
Sort Heap (sortheap)
Statement Heap (stmtheap)
Statistics Heap (stat_heap_sz)
Query Heap (query_heap_sz)
Java Interpreter Heap (java_heap_sz)
===============================

Since I am now using a 32bits DB2 in AIX and the agent private memory
area is limited to max 256MB, I wonder how my existing setting can be
handled by DB2:

Application Heap (applheapsz) 512 ~= 2MB
Sort Heap (sortheap) 10240 ~= 40MB
Statement Heap (stmtheap) 32767 ~= 134MB
Statistics Heap (stat_heap_sz) 20000 ~= 82MB
Query Heap (query_heap_sz) 1024 ~= 4MB
Java Interpreter Heap (java_heap_sz) 2048 ~= 8MB

Total 270 MB per agent that is larger than the limitation of the agent
private memory segment in AIX.

Actually I can have 7 concurrent applications with intra-parallel ON
that 2-4 agents are assigned to one application. How can these agents
fit into the 256MB agent private memory segment in AIX? Does db2
agents only get what it need? These settings are hard limit only?

Hi Woody,

These values are only hard limits - we'll only use that much memory
for each heap if and when needed. Also, some of these heaps are
mutually-exclusive - the statistics heap is only used when gathering
table statistics (i.e. the runstats command), and the statement heap
is only used when compiling/optimizing an SQL query before running it
(so, a single agent won't be using the statement heap at the same time
as the sort heap, for example).

Cheers,
Liam.

[Ian]

Woody Ling wrote:

Actually I can have 7 concurrent applications with intra-parallel ON
that 2-4 agents are assigned to one application. How can these agents
fit into the 256MB agent private memory segment in AIX? Does db2
agents only get what it need? These settings are hard limit only?

To add to what Liam wrote, each *agent* has its own private memory.

Parallel agents communicate with each other using the Application
Control Heap (which is allocated from the application group shared
memory segment).

[Woody Ling]

On Mar 31, 11:44 am, Ian <[e-mail]:

Woody Ling wrote:

Actually I can have 7 concurrent applications with intra-parallel ON
that 2-4 agents are assigned to one application. How can these agents
fit into the 256MB agent private memory segment in AIX? Does db2
agents only get what it need? These settings are hard limit only?

To add to what Liam wrote, each *agent* has its own private memory.

Parallel agents communicate with each other using the Application
Control Heap (which is allocated from the application group shared
memory segment).

Yes, Thank you. :)