Question & Answer
Question
How do I find out which db2fmp process ran a specific external routine?
Answer
Starting with DB2 version 9.5 Fix Pack 5, 9.7 Fix Pack 1 and 10.1 GA, the db2pd tool has a new routine execution history option which, when used with the db2pd -fmp option, can identify which routine (stored procedure or UDF) ran in a particular db2fmp process. To use these two options to find a specific routine, first run the db2pd -fmp command. This command will return a snapshot of the current status of the db2fmps. Below is a sample of the db2pd -fmp output.
Database Partition 0 -- Active -- Up 7 days 00:41:07 -- Date 07/18/2012 16:11:37
FMP:
Pool Size: 2
Max Pool Size: 200 ( Automatic )
Keep FMP: YES
Initialized: YES
Trusted Path: /home/db2inst1/sqllib/function/unfenced
Fenced User: db2fenc1
Shared Memory: 0x0780000000570420
IPC Pool: 0x0780000000570480
FMP Process:
Address FmpPid Bit Flags ActiveThrd PooledThrd ForcedThrd Active IPCList
0x0780000000B1D860 7405928 64 0x00000003 1 0 0
Yes 0x0780000000BCEAA0
Active Threads:
Address FmpPid EduPid ThreadId
0x0780000000B1DB40 7405928 2572 1801
Pooled Threads:
Address FmpPid ThreadId
No pooled threads.
Forced Threads:
Address FmpPid ThreadId
No forced threads.
FMP Process:
Address FmpPid Bit Flags ActiveThrd PooledThrd ForcedThrd Active IPCList
0x0780000000B1E200 33489022 64 0x00001000 0 0 0
No 0x0780000000B1EC80
Active Threads:
Address FmpPid EduPid ThreadId
No active threads.
Pooled Threads:
Address FmpPid ThreadId
No pooled threads.
Forced Threads:
Address FmpPid ThreadId
No forced threads.
The above output indicates that there are currently 2 db2fmps and that one of these (7405928 )is a threaded db2fmp with one active thread (1801). The name and schema of the routine can be found by first generating a SQL query using the genquery option of the db2pd command. Below is the syntax for the db2pd genquery option.
db2pd -fmpe n=128 genquery
The above command will return a SQL query that when run returns information on the last 128 routines executed. The output can be run as a db2 command script by redirecting it to file using the OS redirect ( the ">" symbol) and then modifying the file that results as follows.
1. Remove the first line of the output which should begin with the words “Database Partition”
2. Add a “@” symbol to the end of the last line in the file which should end with the words “R.TID, R.RTNTIME”.
Modified Query
Database Member 0 -- Active -- Up 0 days 00:11:00 -- Date 2014-12-05-11.48.32.224658
WITH RTNHIST ( PID, TID, RTNID, RTNTIME) AS
( VALUES ( 20447370 , 772 , 65932 , TIMESTAMP('2014-12-05-11.44.41.088546')),
( 20447370 , 772 , 65822 , TIMESTAMP('2014-12-05-11.42.43.807698')),
( 20447370 , 772 , 65822 , TIMESTAMP('2014-12-05-11.42.43.335626')),
( 20447370 , 772 , 65822 , TIMESTAMP('2014-12-05-11.42.43.191981')),
( 20447370 , 772 , 65822 , TIMESTAMP('2014-12-05-11.42.43.089549')),
( 20447370 , 772 , 65822 , TIMESTAMP('2014-12-05-11.42.41.841561')),
( 20447370 , 1029 , 65822 , TIMESTAMP('2014-12-05-11.47.41.175732'))
)
SELECT R.PID, R.TID, R.RTNTIME, ROUTINESCHEMA, ROUTINENAME, SPECIFICNAME, ROUTINEID
FROM syscat.routines, RTNHIST as R
where ROUTINEID = R.RTNID
ORDER BY R.PID, R.TID, R.RTNTIME @
Once the output file has been modified the script can be run by connecting to the database and issuing the command.
db2 -td@ -vf <script.file.name>
The query output will contain the columns PID, TID, RTNTIME, ROUTINESCHEMA, ROUTINENAME, SPECIFICNAME, ROUTINEID
To identify which routine ran in which db2fmp you only need the PID, TID, ROUTINESCHEMA and ROUTINENAME output. Below is a sample of the output with just these columns
PID TID ROUTINESCHEMA ROUTINENAME
------------ ----------- -------------- -----------------
7405928 1801 DB2INST1 OUT_LANGUAG
7405928 1801 DB2INST1 OUT_LANGUAG
7405928 1801 DB2INST1 OUT_LANGUAG
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
33489022 0 SYSPROC ADMIN_CMD
Thus you now know that the db2fmp 7405928's thread 1801 ran the routine DB2INST1.OUT_LANGUAGE and that the db2fmp 33489022 ran the routine SYSPROC.ADMIN_CMD. The column RTNTIME which is also returned with the query will identify the time and date the routine ran so you can identify the last routine that was run in a pooled but inactive db2fmp.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21606838