Section 15.5. Agent-Level Private Memory


15.5. Agent-Level Private Memory

Each DB2 agent process also needs to acquire memory to perform its work. As you can see in Figure 15.5, they use memory to optimize, build, and execute access plans on behalf of the application, to perform sorts, to record cursor information such as location and state, to gather statistics, and so on.

Figure 15.5. Agent-level private memory


When a DB2 agent is created, it allocates the minimum amount of memory that is required to do some basic work. As it works, it may allocate larger areas of these memory areas depending on the statements that are being processed, the number of cursors the application uses, and other functions. Once the DB2 agent allocates any of these memory heaps, it does not release the memory back to the operating system automatically, since the areas are normally reused over and over again. DB2 has chosen to do this for performance reasons so that you do not waste resources allocating and deallocating small pieces of memory all of the time.

15.5.1. Application Heap

The application heap stores a copy of the currently executing sections of the access plan for the application associated with the DB2 agent or subagent. The application heap is allocated when an agent or subagent is initialized for an application. The amount of memory allocated will be only what is needed to process the request that has been given to the DB2 agent or subagent. When a DB2 agent or subagent requires additional application heap to be able to process larger SQL statements, DB2 will allocate additional memory, up to the maximum specified by the application heap size.

NOTE

If the database is partitioned, the executing sections of the SQL statements for the agents and subagents will be stored in the application control heap (APP_CTL_HEAP_SZ), not in the application heap.


15.5.2. The Sort Heap and Sort Heap Threshold

The sort heap (SORTHEAP) size specifies the maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts. If the DB2 optimizer chooses to perform a private sort, the sort heap size affects agent private memory. If the DB2 optimizer chooses to perform a shared sort, the sort heap size affects the database-level shared memory.

Each sort operation has a separate sort heap that is allocated as needed by DB2 where the underlying data is sorted. Normally DB2 will allocate a full sort heap; however, if directed by the optimizer, a smaller amount of memory than specified by the sort heap size may be allocated using the information provided by the optimizer and the database statistics.

For private sorts, the sort heap threshold parameter (SHEAPTHRES) is an instance-wide soft limit on the total amount of memory that can be used at any given time. When the total usage of private sort memory for a DB2 instance reaches this limit, the memory allocated for new private sort requests will be reduced by a factor of one half. Each time this happens, you will receive the following message in the db2diag.log:

[View full width]

Not enough memory available for a (private) sort heap of size <size of sortheap>. Trying smaller size...

Chapter 17, Diagnosing Problems, discusses the db2diag.log file.

15.5.3. Query Heap

A query heap stores each SQL statement in the private memory for the DB2 agent executing the statement. The information stored in the query heap for an SQL statement includes the following:

  • The input SQL descriptor area (SQLDA)

  • The output SQLDA

  • The statement text

  • The SQLCA

  • The package name

  • The package creator

  • The section number

  • A consistency token

  • The cursor control block for any blocking cursors

When an application connects to DB2, the initial size of the query heap allocated is a minimum of two pages, or the size of the application support layer heap (ASLHEAPSZ). If the currently allocated query heap is not large enough to handle the request, the query heap will be reallocated with a larger size that will handle the request, as long as it does not exceed the query heap size.

15.5.4. Client I/O Block Size

The maximum requester I/O block size is the maximum amount of data that can be sent back and forth between a DB2 client and server. Each agent process allocates the communication block in agent private memory for a DB2 instance; however, DB2 only uses what it needs up to this maximum size. If the application request or the output from DB2 is larger than the block size, the data will be split into multiple pieces and sent in multiple communication packets. The default maximum requester I/O block size is 32KB; the maximum size is 64KB.

15.5.5. Agent Stack

Each agent process uses the agent stack to process SQL statements. When an SQL statement is running, its sections are copied to the agent stack to be processed. Large, complex queries need a larger agent stack as they typically have much larger access plans with a large number of sections.

15.5.6. Java Interpreter Heap

The Java Interpreter Heap parameter determines the maximum size of the heap used by the Java interpreter started to service Java DB2 stored procedures and UDFs. This heap is used as a work area for the Java interpreter as it processes actions for the DB2 agent.

15.5.7. Statement Heap

The DB2 optimizer uses the statement heap as a work space as it examines and builds the access plan for an SQL statement. The statement heap is allocated when optimizing an SQL statement, and released once the access plan for the statement has been built. For applications using dynamic SQL, this work area is allocated and deallocated each time the statement is run in the application. Applications using static SQL statements only use this work area when the program is bound to the database.

15.5.8. Statistics Heap

The statistics heap is a memory area used by the runstats command to maintain and calculate the statistical information about tables and indexes. As runstats scans the table, it counts items, builds histograms for data distribution, and so on; this information is maintained in the statistics heap. When the runstats command completes, the information is then written to the catalog tables, and the statistics heap is freed.



Understanding DB2(R. Learning Visually with Examples)
Understanding DB2: Learning Visually with Examples (2nd Edition)
ISBN: 0131580183
EAN: 2147483647
Year: 2004
Pages: 313

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net