Mapping an External Routine to its db2fmp Process.

Technote (FAQ)


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”.

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

The Syntax for the db2pd command
A brief description of the fmp history command

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
DB2 Programming Interfaces - Non SQL Stored Procedure

Software version:

9.5, 9.7, 10.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Software edition:

Advanced Enterprise Server, Enterprise Server, Express, Express-C, Personal, Workgroup Server

Reference #:

1606838

Modified date:

2012-11-08

Translate my page

Machine Translation

Content navigation