Technote (FAQ)
Question
How does the db2fmp process relate to an application? How do I determine which application is calling a routine executing inside this db2fmp process?
What is the criteria to reuse db2fmp process?
Answer
From a DB2 perspective we can determine the application and routine calling the routine along with some diagnostics to help the developer that wrote the routine troubleshoot any performance problems.
What is the criteria to reuse db2fmp process ?
Threadsafe routines: One db2fmp can run many routines servicing multiple application handles (connections).
Non-threadsafe routines: One db2fmp can run ONE routine servicing ONE application handle.
By default DB2 pools inactive 32-bit (db2fmp32) and 64-bit (db2fmp) processes up to the value specified by FENCED_POOL. Checking the UNIX/Linux "ps" output to look for "db2fmp (idle)" processes indicates there is no db2fmp associated with a DB2 co-ordinator agent, this has no bearing regarding whether it is active or inactive.
FENCED_POOL determines the number of pooled threads inside one db2fmp process if it is running threadsafe routines. For non-threaded routines this parameter controls the total number of db2fmp processes. In our case db2fmp32 always runs as non-threaded. For example If we have FENCED_POOL=10, we can have 10 inactive non-threaded db2fmp processes plus 5 inactive threaded db2fmp processes for a total of 15 inactive db2fmp processes.
If Connection A called routine SPTEST, disconnects and calls the SPTEST is the same db2fmp process reused? No. Once Connection A terminates the db2fmp is returned to the pool. When it reconnects DB2 will start a new db2fmp process.
How do we know if the db2fmp process is active/inactive ?
The active/inactive state can be monitored via db2pd -fmp which will provide an entry for each db2fmp process along with all threads inside. In some cases you may see multiple active threads because each thread is executing a different routine (stored procedure/user defined function) on behalf of multiple applications.
The output below indicates there is no db2fmp associated with a DB2 agent. This has no bearing regarding the active/inactive state of the db2fmp.
db2fmp (idle)
The output below indicates the db2fmp process is a single threaded routine which has been associated with DB2 agent thread #1234
db2fmp32 (1234)
db2fmp64 (1234)
How do I determine which application is calling a routine executing inside this db2fmp process?
To determine if a routine is threadsafe check the CREATE PROCEDURE DDL statement.
In this example you can see that PID 6422648 is working on behalf of DB2's coordinator agent 77811. An application snapshot will provide a coordinator PID. Once you have the application handle the application snapshot should be able to provide more information such as dynamic SQL statement, or package in the case of a stored procedure. There are alternatives to using get snapshot such as using the db2pd -applications -alldbs.
[
db2 "get snapshot for all applications"
Application Snapshot
Application handle = 1283
Application status = Connect Completed
Status change time = Not Collected
Application code page = 1208
...
Coordinator agent process or thread ID = 77811
db2pd -fmp
FMP:
Pool Size: 99
Max Pool Size: 100
Keep FMP: YES
Initialized: YES
Trusted Path: /home/db2inst1/sqllib/function/unfenced
Fenced User: db2fenc1
Shared Memory: 0x0780000000790420
IPC Pool: 0x0780000000790480
FMP Process:
Address FmpPid Bit Flags ActiveThrd PooledThrd ForcedThrd Active IPCList
0x0780000001D3EE60 6422648 64 0x00000000 0 0 0 Yes 0x0780000001DD4E20
Active Threads:
Address FmpPid EduPid ThreadId
0x0780000001D3EE78 6422648 77811 0
Pooled Threads: (these are inactive threads)
Address FmpPid ThreadId
No pooled threads.
Forced Threads:
Address FmpPid ThreadId
No forced threads.
Once a routine is finished executing it can be used by a completely different application. The command below will help you identify which routines were executed in the past. This feature was introduced in v9.5 FP5 and v9.7 FP1.
We can see that the stored procedure last executed was SYSPROC.SYSINSTALLOBJECTS. Normally you would see a C=C or J=Java but P probably means its a built in DB2 routine.
$ db2pd -fmpexechistory
Database Partition 0 -- Active -- Up 2 days 13:28:24 -- Date 10/04/2011 11:49:05
FMP Process:
FmpPid Bit Flags ActiveThrd PooledThrd ForcedThrd Active
20709658 64 0x00000002 0 1 0 Yes
Active Threads:
No active threads.
Pooled Threads:
ThreadId : 772
Routine ID Timestamp
65818 2011-10-03-16.35.49.678540
Forced Threads:
No forced threads.
$ db2 "select routineschema, routinename, routinetype from sysibm.sysroutines where routine_id=65818"
ROUTINESCHEMA ROUTINENAME ROUTINETYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -----------
SYSPROC SYSINSTALLOBJECTS P
1 record(s) selected
Monitoring Memory/CPU
The principle goal of running third party applications inside a separate process which we decide to call db2fmp is to isolate untrusted code from the DB2 engine to reduce the possibility of bringing down the DB2 instance. Essentially all DB2 does is fork() a new process and setup a communications buffer to the process via a shared memory segment and load the libraries needed to run the routine.
The memory and CPU is managed by the JVM if running a Java routine and C if running a C routine. Since troubleshooting a db2fmp process is similar to troubleshooting a third party application, we have to use the tools provided at the operating system level.
Please see link below "Collecting data: DB2 Stored Procedure Problems" Non-SQL section for diagnostics to collect to help the application developer that wrote the non-SQL routine troubleshoot the root cause of the problem. DB2 Support may also request this information to help guide your application developer as well.
