What exactly does this mean for stored procedures? Also, how do you specify a routine to be fenced? Thanks!

A fenced procedure runs outside the DB2 address space and is a little less efficient when communicating with DB2 for that reason. But if there is a problem with a unfenced procedure (such as an address pointer problem) it could cause DB2 to crash.

SQL procedures are always unfenced because you cannot specigy memory addresses in SQL procedure language that would cause a problem (unless there is a bug in DB2 SQL procedure language). C/C++ can be fenced or unfenced, and I believe that Java procedures are always fenced.

Fenced vs. not fenced (aka trusted) is also applicable to UDFs - with exactly the same arguments that Marcus gave.

thanks guys... is it possible to get this output in the db2diag.log file for any procedures that are not fenced? We have some procedures that are fenced and some that are unfenced. But the time corresponding to the output in the db2diag.log corresponds to issues we had when many customers were calling the sprocs that were unfenced. For some reason, db2sysc goes to 100% when we have too many users logging in and accessing these unfenced sprocs. Could it be a problem of memory when the fenced and unfenced routines are both being called? Also, i have DB2_FMP_COMM_HEAPSZ=7680. Thanks!!

2008-09-04-20.01.06.111359+000 I293174548G495 LEVEL: Severe
PID : 28776 TID : 4096042688 PROC : db2agent (ENERGY1)
INSTANCE: nrg1 NODE : 000 DB : ENERGY1
APPHDL : 0-296 APPID: D1AC9624.KCE0.0960E4200105
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:60
RETCODE : ZRC=0x8B0F003B=-1961951173=SQLO_NOMEM_UND
"No memory available in 'Undefined Heap'"
DIA8300C A memory heap error has occurred.

2008-09-04-20.01.06.111676+000 I293175044G448 LEVEL: Severe
PID : 28776 TID : 4096042688 PROC : db2agent (ENERGY1)
INSTANCE: nrg1 NODE : 000 DB : ENERGY1
APPHDL : 0-296 APPID: D1AC9624.KCE0.0960E4200105
FUNCTION: DB2 UDB, routine_infrastructure, sqlerAddFmpToPool, probe:20
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0xFFE0AE74 : EEFB FFFF ....

2008-09-04-20.01.06.116712+000 I293175493G642 LEVEL: Error
PID : 28776 TID : 4096042688 PROC : db2agent (ENERGY1)
INSTANCE: nrg1 NODE : 000 DB : ENERGY1
APPHDL : 0-296 APPID: D1AC9624.KCE0.0960E4200105
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFMPIPC, probe:70
MESSAGE : Insufficient memory available for IPC communication with the db2fmp
process. Use the DB2_FMP_COMM_HEAPSZ registry variable to adjust the
amount of memory available for fenced routines.
DATA #1 : Hexdump, 4 bytes
0xFFE0ABC8 : 0000 0000

I understand that SQL sprocs are unfenced always but am confused as to what this phrase means -- 'because you cannot specify memory addresses in SQL procedure language that would cause a problem'

If your procedure is written in C/C++ or Java, you can do your own memory management and access arbitrary memory regions in your process. With LANGUAGE SQL procedures, this is not possible because the language doesn't give you any handles to mess around in the address space.

Regarding the other question: Each stored procedure invocation needs some memory. If too many concurrent invocations occur, DB2 may need more memory than you have configured. As the last message suggests, you should:
(a) read up on the registry variable DB2_FMP_COMM_HEAPSZ, and
(b) adjust the variable's setting according to your needs

p.s: You may also want to run the DB2 Configuration Advisor to get a baseline configuration. I would guess you haven't done that yet.

There is a difference between:

1. DB2 running out of memory to process the running of a stored procedure or function, and
2. A stored procedure or funciton stepping on some memory addresses that they should not be and causing the address space to crash (if running unfenced it would crash DB2).

If your SQL stored procedure or function is encountering problems related to memory heaps, forget about the fenced vs. unfenced scenarios because they are not relevant to your problem, except in the event of a bug in DB2. If you think there is a bug in DB2, please contact IBM support.

In general, anytime you get a ZRC error, you should contact IBM support to resolve it.