The db2updv8 command is a program that will define new user defined functions (UDFs) and stored procedures (SPs) that have been added to DB2 UDB in a Version 8.x FixPak.

The UDFs SYSFUN.GET_DBM_CFG and SYSFUN.APPLICATION_ID are supported in DB2 UDB Version 8.1 FixPak 7. Run db2updv8 to use these new functions. 

e.g. db2 "select application_id() from sysibm.sysdummy1"

Also note that other tools (for example, the Activity Monitor and its stored procedures) may not be fully functional until these UDFs are defined.

This technote provides some FAQs regarding messages in the db2diag.log in regards to db2fmp processes used to run non-SQL routines such as Java and C.

Scenario #1: Threads in db2fmp process, SQL1042

When the DB2 registry variable DB2_MAX_THREADS_PER_FMP is set you may see the messages below which can be safely ignored. This registry variable was introduced as an enhancement to DB2 via IZ08425. 

In some cases the SQL1042 is a legitimate error so its important to note that in this scenario "Max number of thread in fmp reached; no thread created" is the cause of the SQL1042. The SQL1042 can be safely ignored.

Please note that the SQL1042 is transparent to the application, so no errors will be returned to the application. DB2 automatically starts a new db2fmp process to service the existing or subsequent requests.

2011-01-10-11.31.12.480063-300 I34781A381 LEVEL: Warning 
PID : 1200 TID : 1 PROC : db2fmp (Java) 0
INSTANCE: db2inst1 NODE : 000 
EDUID : 1 EDUNAME: db2fmp (Java) 0 
FUNCTION: DB2 UDB, routine_infrastructure, sqlerMasterThreadListener, 
probe:200 
MESSAGE : Max number of thread in fmp reached; no thread created

2011-01-10-12.10.13.923352-300 I74680A573 LEVEL: Warning 
PID : 1234 TID : 100 PROC : db2sysc 
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE 
APPHDL : 0-33648 APPID: 192.168.1.1.50234.11022217100
AUTHID : DB2INST1 
EDUID : 608 EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, routine_infrastructure, sqlerMasterThreadReq, 
probe:89 
MESSAGE : FMP reported it could not create a new thread 
DATA #1 : Hexdump, 4 bytes 
0x000000020210F180 : 0000 0C05 ....

2011-01-10-10.02.25.454418-300 I2221A448          LEVEL: Severe
PID     : 1234                 TID  : 100         PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE
APPHDL  : 0-51434              APPID: 10.111.41.13.56427.110218150224
AUTHID  : DB2INST1
EDUID   : 260                  EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFmpThread, probe:20
RETCODE : ZRC=0xFFFFFBEE=-1042


Scenario #2: Operating system resource issue, SQL1042

The messages are very similar to Scenario #1 so how do we determine if the SQL1042 is legitimate? Work backwards and look for preceding messages originating from the same PID and TID. 

In the case below we see the source of the error is originating from the operating system (OSERR) and the operating system API which returned the error (pthread_create). pthread_create() is an AIX API indicating that the operating system was unable to create a new thread in the db2fmp process. The root cause is likely an operating system limitation or resource issue when trying to create an additional thread due to the EAGAIN return code.


2011-01-01-13.01.03.345767-240 E287334A401 LEVEL: Severe (OS) 
PID : 2609242 TID : 1 PROC : db2fmp (Java) 0
INSTANCE: db2inst1 NODE : 000 
EDUID : 1 EDUNAME: db2fmp (Java) 0 
FUNCTION: DB2 UDB, oper system services, sqloCreateAppThread, probe:100 
CALLED : OS, -, pthread_create 
OSERR : EAGAIN (11) "Resource temporarily unavailable" 

2011-01-01-13.01.03.426469-240 I287736A455 LEVEL: Severe 
PID : 2609242 TID : 1 PROC : db2fmp (Java) 0
INSTANCE: db2inst1 NODE : 000 
EDUID : 1 EDUNAME: db2fmp (Java) 0 
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging 
func, probe:0 
MESSAGE : Failed to create app thread: 
DATA #1 : Hexdump, 4 bytes 
0x0FFFFFFFFFFFEF60 : 0000 000B ... 

2011-01-01-13.01.03.426684-240 I288192A343 LEVEL: Severe 
PID : 2609242 TID : 1 PROC : db2fmp (Java) 0
INSTANCE: db2inst1 NODE : 000 
EDUID : 1 EDUNAME: db2fmp (Java) 0 
FUNCTION: DB2 UDB, routine_infrastructure, sqlerCreateWorkerThread, 
probe:20 
RETCODE : ZRC=0xFFFFFBEE=-1042 

Scenario #3: SQL1042N returned on AIX platforms using Power7 processors

There is an incompatibility between the IBM JDK bundled with DB2 v9.7 and Power7. For details refer to the link db2setup or db2fmp (Java) abnormally terminated on Power7

Scenario #4: SQL1042N returned due to permission issue 

Ensure the DB2 instance home directory has the permission below, otherwise the db2fmp may not be able to access files located in ~/sqllib/function

/home $ ls -la 
drwxr-xr-x 14 db2inst1 db2admg 4096 Aug 04 10:51 db2inst1/

Also check the stored procedure binaries in ~/sqllib/function to ensure they are world readable because the fenced userid is usually not the instance owner.

/home/db2inst1/sqllib/function:
-rw-r--r-- 1 db2fenc fencgrp 875 Jul 29 14:24 DUMMY_JAVASP.class 


Scenario #5: Executing long running query 

A remote DB2 client has abnormally terminated when executing a long running query which includes a call to a routine. These messages are informational in nature and can be ignored. However the application should contain some exception handling logic to handle any abnormal terminations. Under the covers DB2 will clean up any non-SQL routines being executed on behalf of the connection. one of the side effects is that the db2diag.log may contain a 0xFFFFFB38 ( SQL1224) 

In the example below the message originating from the db2fmp process about "The database manager is not able to accept new requests..." is misleading. The message is originating from the JDBC (db2jcc.jar) used internally by the DB2 server to execute a Java routine and can be ignored. There is nothing wrong with the DB2 database server.

2011-01-12-12.08.33.158935-300 I6813000A541       LEVEL: Error
PID     : 9644                 TID  : 172         PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE
APPHDL  : 0-20965              APPID: 192.168.1.1.33819.110220052256
AUTHID  : DB2INST1
EDUID   : 172                  EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, common communication, sqlcctcptest, probe:11
MESSAGE : Detected client termination
DATA #1 : Hexdump, 2 bytes
0xFFFFFFFF4B7F3376 : 0036                                       .6 

2011-01-12-12.08.33.170419-300 I6813542A523       LEVEL: Error
PID     : 9644                 TID  : 172         PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE
APPHDL  : 0-20965              APPID: 192.168.1.1.33819.110220052256
AUTHID  : DB2INST1
EDUID   : 172                  EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, common communication, sqlcctest, probe:50
MESSAGE : sqlcctest RC
DATA #1 : Hexdump, 2 bytes
0xFFFFFFFF4B7F346E : 0036  

2011-01-01-12.08.33.170664-300 I6814066A503       LEVEL: Error
PID     : 9644                 TID  : 172         PROC : db2sysc 0
INSTANCE: db2inst11             NODE : 000         DB   : SAMPLE
APPHDL  : 0-20965              APPID: 192.168.1.1.33819.110220052256
AUTHID  : DB2INST1
EDUID   : 172                  EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeAgent::AgentBreathingPoint, probe:10
CALLED  : DB2 UDB, common communication, sqlcctest
RETCODE : ZRC=0x00000036=54 

2011-01-01-12.08.33.173237-300 I6814570A507       LEVEL: Error
PID     : 9644                 TID  : 172         PROC : db2sysc 0
INSTANCE: db2inst11             NODE : 000         DB   : SAMPLE
APPHDL  : 0-20965              APPID: 192.168.1.1.33819.110220052256
AUTHID  : DB2INST1
EDUID   : 172                  EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerInvokeFencedRoutine, probe:40
DATA #1 : Hex integer, 4 bytes
0x804B006D
DATA #2 : Hex integer, 4 bytes
0xFFFFFB38  /* SQL1224 */ 

2011-01-01-12.08.33.173599-300 E6815078A3474      LEVEL: Severe
PID     : 9644                 TID  : 172         PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE
APPHDL  : 0-20965              APPID: 192.168.1.1.33819.110220052256
AUTHID  : DB2INST1
EDUID   : 172                  EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, routine_infrastructure, sqlerReturnFmpToPool, probe:900
DATA #1 : String, 50 bytes
Marking fmp as unstable, fmp is forced or aborted:
DATA #2 : Boolean, 1 bytes
true
DATA #3 : String, 20 bytes
Fmp entry use count:
DATA #4 : unsigned integer, 4 bytes
1
DATA #5 : String, 8 bytes
Fmp TID:
DATA #6 : Hexdump, 4 bytes
0x000000020079F650 : 0000 0037  /* 0x37 = 55 */
DATA #7 : String, 8 bytes
Fmp row:
DATA #8 : sqlerFmpRow, PD_SQLER_TYPE_FMP_ROW, 496 bytes
 fmpPid: 15781 

When executing Java routines one additional side effect is that the message below will appear in the db2diag.log. This message is misleading because there is nothing wrong with the database manager on the DB2 server. The message originates from the JDBC driver which is local to the DB2 server used to execute Java routines. This message can be safely ignored.

2011-01-01-12.08.33.213727-300 I6840110A1387      LEVEL: Warning
PID     : 15781                TID  : 55          PROC : db2fmp (Java) 0
INSTANCE: db2inst1             NODE : 000
EDUID   : 55                   EDUNAME: db2fmp (Java) 0
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
DATA #1 : String, 962 bytes
com.ibm.db2.jcc.am.DisconnectNonTransientException: [jcc][2055][11259][3.61.86] The database manager is not able to accept new requests, has terminated all requests in progress, 
or has terminated this particular request due to unexpected error conditions detected at the target system. ERRORCODE=-4499, SQLSTATE=58009
    at com.ibm.db2.jcc.am.ed.a(ed.java:321)
    at com.ibm.db2.jcc.t4.ab.T(ab.java:1264) 
...


Scenario #6: SQL1131 or SQL4302

This indicates the non-SQL routine was abnormally terminated. This is usually not a serious error as long as the application is coded to handle these exceptions and the messages do not appear frequently e.g. once a month.

2011-02-02-15.01.23.476662-240 I299008A471 LEVEL: Warning 
PID : 8245982 TID : 1280 PROC : db2fmp (Java) 0
INSTANCE: db2inst1 NODE : 000 
EDUID : 12080 EDUNAME: db2fmp (Java) 0 
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging 
func, probe:0 
MESSAGE : Thread of db2fmp terminated with nonzero rc 
DATA #1 : Hexdump, 4 bytes 
0x000000011E7685EC : FFFF FB95 /* SQL1131 */ ...

2011-02-02-15.01.23.883044-240 I299480A453 LEVEL: Severe 
PID : 8245982 TID : 1280 PROC : db2sysc 0 
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE 
APPHDL : 0-40776 APPID: 192.168.1.10.47637.100712165705 
AUTHID : DB2INST1 
EDUID : 17812 EDUNAME: db2agent (CLNPIS) 0 
FUNCTION: DB2 UDB, routine_infrastructure, sqlerDisassociateWithFmp, 
probe:30 
RETCODE : ZRC=0xFFFFFB95=-1131 


Scenario #7: Marking fmp as unstable

This message is returned when DB2 decides to shut down a db2fmp process and can be part of normal DB2 operations. DB2 automatically picks a new db2fmp or creates a new db2fmp process to execute any subsequent calls to stored procedures or UDFS.

To determine whether this message is more than informational please check for additional db2diag.log entries around the same timestamp which return a ZRC return code, 0xFFFFxxxx return code or an error from the operating system (OSERR). Please use the db2diag tool to decode these return codes. 

This message may also be returned when:

• DBM CFG parameter KEEPFENCED=NO since DB2 would terminate any db2fmp process after a routine has completed execution.
• FORCE APPLICATIONS was issued or if "db2stop force" was called.

We've added a problem diagnose purpose code in sqlowqueInternal function on DB2 UDB Version 9.

When a command such as "db2 list tablespaces show detail | head -81" or "db2 select * from employee | head -10" is run, DB2® Version 9 logs the following message in the db2diag.log file:

------------------------------------------------------------------------
2007-03-06-23.17.14.080280-300 E10282A659         LEVEL: Error (OS)
PID     : 9883690              TID  : 1           PROC : db2bp
INSTANCE: hidehy               NODE : 000
APPID   : *LOCAL.hidehy.070307041048
FUNCTION: DB2 UDB, oper system services, sqlowqueInternal, probe:40
MESSAGE : ZRC=0x870F003E=-2029060034=SQLO_QUE_BAD_HANDLE "Bad Queue Handle"
          DIA8555C An invalid message queue handle was encountered.
CALLED  : OS, -, write
OSERR   : EINVAL (22) "Invalid argument"
DATA #1 : system V message queue identifier., PD_TYPE_SYSV_QUEUE_ID, 4 bytes
0x1DD001B8
DATA #2 : Pointer, 8 bytes
0x0000000110035760
DATA #3 : unsigned integer, 8 bytes
78
------------------------------------------------------------------------


When a command such as db2 "select * from tab1" | more is run (tab1 has a lot of records such as 700000) then run Control + C on HP, will show the message too.
(The same scenario above does not reproduce in Solaris)

This error may occur in any scripting language that does not use direct connection to the DB2 instance but instead spawns a subshell to handle the DB2 connection (e.g. shell, C using "system", Perl using "open", "system" or "``", etc.).

The logging of this message is expected. There is no option to remove the logging of this message. The message can be ignored.

If you pipe the DB2 command output to the less or tail commands, the message will not be written.

If you pipe the output to awk first then pipe to head or others, the message will not be written.

Examples:

$db2 select * from employee | awk '{print $0}' | head -10

$db2 list tablespaces show detail | awk '{print $0}' | head -81 

If you use Perl and a sleep(), the message will not be written.

Example:
---
open(DB2QUERY,"db2 -tf $querysql |") || die "Failed to get data from DB2, $!";
while (<DB2QUERY>)
{
. . .
}
while (!eof(DBQUERY)){};

sleep(1);
close(DB2QUERY);
---

https://www-304.ibm.com/support/docview.wss?uid=swg21259051

You see a message similar to the following in db2diag.log:

2008-08-11-13.59.07.053103-300 I584734A1271 LEVEL: Error
PID : 581690 TID : 16022 PROC : db2sysc
INSTANCE: my_inst NODE : 000 DB : MYDB
EDUID : 16022 EDUNAME: db2agent (MYDB) 0
FUNCTION: DB2 UDB, Query Gateway, sqlqgRouter_conn_lost_cleanup, probe:25
DATA #1 : <preformatted>
Message: Fenced server is not null but not connected,
Cleanup failed for server: MYSERVER
CALLSTCK:
[0] 0x090000000F632524 sqlqgRouter_conn_lost_cleanup__FP12FencedServer + 0x104
[1] 0x090000000F62DE88 sqlqgRouter__FP17sqlqg_FMP_RequestPP15sqlqg_FMP_ReplyP10sqlri_ufob + 0x348
[2] 0x090000000F633070 sqlqg_fedstp_hook + 0x1B0
[3] 0x090000000AF8988C sqlqgDyload__FP10sqlri_ufob + 0x23C
[4] 0x090000000AF68F34 sqlriFedInvokerTrusted__FP10sqlri_ufobP21sqlriRoutineErrorIntf + 0x1A8
[5] 0x090000000AF68554 sqlriFedInvokeInvoker__FP10sqlri_ufobP14sqlqg_Fmp_Info + 0xE4
[6] 0x0900000009CBA7B0 sqlqg_Call_FMP_Thread__FP17sqlqg_FMP_RequestPP15sqlqg_FMP_Reply + 0x458
[7] 0x090000000B248EE8 sqlqgFedStart__FP14UnfencedServerc + 0x80C
[8] 0x0900000009CC30B0 sqlqgPassthruPrepare__FP15sqlri_rpassthru + 0x2BC
[9] 0x090000000991B5E8 sqlrj_passthru_prepare__FP8sqlrr_cbP11sqlrrstringP14sqlrr_cmpl_envPi + 0x48

This message can usually be ignored. It can be a side effect of any of the following conditions:

(1) An earlier error occurred that caused Federation Server to lose the connection to a data source. In this case, there will be an earlier error entry in db2diag.log or an earlier error message resulting from an SQL statement that tried to access the data source. A common error message in this case is SQL30081N (SQLCODE -30081).

(2) You have the data source server status health indicator or the nickname status health indicator turned on and Federation Server cannot access the data source. You can determine whether the data source status health indicator or the nickname status health indicator is on by issuing the following commands from the DB2 command line processor (CLP):

GET ALERT CFG FOR DATABASES USING db.fed_servers_op_status;
GET ALERT CFG FOR DATABASES USING db.fed_nicknames_op_status;

(3) Federation Server Version 9.5 or later: You have database configuration parameterauto_runstats turned on for nicknames and Federation Server cannot access the data source, or there is no user mapping from the instance owner to the data source. You can determine whether auto_runstats is on by issuing the following command from the DB2 command line processor and looking at the setting of auto_runstats:

GET DB CFG FOR dbname;

By default, the database configuration parameter auto_runstats is on for nicknames unless there is an auto_runstats maintenance policy that excludes nicknames (for example, specifies TYPE<>'N').

See the list of causes shown above.

This message can usually be ignored. 

If the cause is an earlier error that caused Federation Server to lose the connection to a data source (cause 1 listed above), then resolve the problem that caused Federation Server to lose the connection.

If the cause is a data source server status health indicator or a nickname status health indicator (cause 2 listed above), then resolve the problem that prevents Federation Server from accessing the data source, or turn off the health indicators. To turn off the health indicators, issue the following commands from the DB2 command line processor:

UPDATE ALERT CFG FOR DATABASES USING db.fed_nicknames_op_status SET THRESHOLDSCHECKED NO;
UPDATE ALERT CFG FOR DATABASES USING db.fed_servers_op_status SET THRESHOLDSCHECKED NO;

Federation Server Version 9.5 or later: If the cause is auto_runstats being on (cause 3 listed above), then do one of the following, listed in order of preference:

• Resolve the problem that prevents Federation Server from accessing the data source. In particular, make sure there is a user mapping from the instance ID to the data source.
• Create an auto_runstats maintenance policy that excludes nicknames belonging to servers that Federation Server cannot access.
• Turn off auto_runstats for the database. To turn off auto_runstats, issue the following command from the DB2 command line processor:

UPDATE DB CFG FOR dbname USING auto_runstats OFF;

Note that this turns off auto_runstats for all tables and nicknames, including local tables. DB2 will no longer maintain table or nickname statistics automatically, which can result in performance degradation.