From Page Not Found

"Most databases load the compiled procedures into memory the first time they're called, or even before, dramatically reducing overhead when you're doing the same essential process multiple times (such looping operations are very common in database manipulation)."


Which memory area(s) are used by stored procedures? Private memory, package cache or some other heaps? For now, I'll assume that all stored procedures are not fenced.

What this sentence talks about is private memory that is allocated and used due to a dlopen() system call. Usually, you don't worry much memory consumption for the loading of libraries. What really helps performance-wise is the saved I/O and linker resolution. I/O is saved because the library doesn't have to be read from disk all the time but it either remains loaded or it is in the file system cache Linker resolution is something that has to occur upon startup of an executable (or upon loading a library) to make sure all dependencies are resolved (e.g. libc is loaded, too).

Memory-wise, it is much more important to know what the stored procedure or UDF is doing internally and how much memory will be allocated dynamically (or on the stack or in shared memory or ...) by it. However, this part is completely unrelated to the first load of the stored procedure.

Which libraries get loaded when a stored procedure is invoked for the first time? Using 32-bit AIX memory model as an example ( The DB2 UDB memory model ), anything gets loaded into segment 0xF or everything gets loaded into 0x2?


Yes, I'm more interested in understanding how much memory is allocated dynamically and where it's allocated from - for fenced and unfenced stored procedures.


For fenced, I see:
FMP set - Shared Memory allocated per partition. Configurable via DB2_FMP_COMM_HEAPSZ, indirectly through ASLHEAPSZ (FMP area is at least 20 x 2 x ASLHEAPSZ).

Any other memory heaps used when a fenced stored procedure is invoked? What about unfenced stored procedures?


Do most customers create stored procedures as fenced or unfenced in production? I understand unfenced could crash the instance, but it seems like there are less problems (at least memory related problems) and less overhead when they're unfenced.

Shared libs which are loaded by system call dlopen()。
are mapped to 0XD or 0XF segment in 32bit process address space on aix。
0X2 segement is process heap and stack. You can use malloc, alloc etc to alocate memory from it.
for fenced mode, DB2 will start a seperated process called fmp to execute your sp code。Which means sp shared lib is mapped to fmp's 0XD or 0xF segment。
while fmp crashed by some reason , db2 will not bring down the entire instance although db2wdog will know it 。。。。
for unfence mode, DB2 agent will directly use dlopen to load sp shared lib to mapped it at address 0XD or 0XF，IF agent is crashed , because it is a critical process to db2 , the db2wlog will noticed all other process and bring instance down ......
If there is anything wrong in my explaining above ,plz let me know.....

What exactly do you mean here? Memory allocated by DB2 or memory allocated by the stored procedure?

DB2 needs some memory to pass input parameters to the stored procedure and to provide memory for output parameters. Additionally, memory for error codes (e.g. SQLSTATE and SQLCODE) will be needed - but that is pretty small. And then some memory is needed for result sets, e.g. to hold cursors/descriptors etc. opened by the stored procedure and returned to the caller or client.

If you are interested in the memory allocated dynamically be the stored procedure, it actually depends on the programming language in which the SP is written. If it is C/C++, then you don't have any control over the memory if the SP uses language library calls like new/delete or malloc/free. In Java, all memory is used from from the java heap, unless you have something like JNI in the picture, which goes directly to the operating system's memory manager. Only if you have an SP written with LANGUAGE SQL, we are talking about DB2 heaps/memory pools.

I don't know for sure what most customers do. But the overhead for fenced stored procedures is higher because you have IPC (inter process communication) involved. So you may have something like message queues or (more likely) shared memory and semaphores involved to exchange the input/output parameters between the DB2 agent and the db2 FMP. This is bound to be slower compared to direct memory access via a pointer. The overhead incurred there depends on the complexity of the stored procedure or user-defined function. At one point, we had a SP/UDF, which run very quickly. The overhead incurred by fenced execution was something like 10% of the total execution time. With an SP/UDF taking more time due to more complex calculations, more I/O, web access or whatever else, the additional communication overhead may become negligible.

p.s: One argument for using fenced vs. not-fenced SPs/UDFs is also that fenced SPs are executed in a process, which runs with different authorizations/operating system privileges. You can protect the DB2 instance/configuration that way. The stability argument became weaker in the last decade or so. For example, with DB2 LUW V7 (very long time ago), I could sometimes bring down the DB2 instance using a fenced UDF, which crashed in a very specific way at a very specific point during its execution. I was never able to reproduce this since then with DB2 V8 or later. Similarly, I couldn't easily crash the DB2 V8+ instance with a trusted (not-fenced) UDF. Thus, DB2 is doing a very good job from preventing you to crash the database system. So if you know what your SP/UDF is doing (no file access, no security holes, ...) and have applied reasonable testing you may want to go with trusted execution.

fengsun2, You're correct. shared libs don't get maps to 0x2. My mistake.. The reason I mentioned 0xF and not 0xD is because 0x2 and 0xF is what I used to look at when estimating private memory usage for 32-bit agents. But I don't think I need to care where libs used by SP's gets loaded...


Knut, Thank you for the detailed explanation. What I'm actually interested in is:

"Only if you have an SP written with LANGUAGE SQL, we are talking about DB2 heaps/memory pools."

I think it works like this: when an SQL routine is called, it is put in the package cache, then a copy of each executing section is created in the application private memory to preserve its execution context, then also a copy is created in the sql workspace in the application shared memory during the actual execution.