DB2 database proxy and connection FAQ Analysis and Optimization

This article mainly describes the analysis and optimization of common problems related to DB2 database proxy and connection. If you encounter common problems related to DB2 database proxy and connection during actual operations, but you don't know how to solve it correctly, so the following articles must be good teachers and friends for you.

Connection Overrun

When the MAX_CONNECTIONS or MAXAGENTS value set in DB2 V8 and V9.1 is relatively small, an error occurs if there are too many external connections. The error is shown in Listing 1.

Listing 1. db2diag. log diagnostic log

 
 

  
  
2008-01-15-14.30.13.090289-360 I12983210A1195 LEVEL: Info   
  
  
PID : 762076 TID : 772 PROC : db2acd   
  
  
INSTANCE: db2inst1 NODE : 000   
  
  
APPID : *LOCAL.db2inst1.080115203015   
  
  
EDUID : 772 EDUNAME: db2acd   
  
  
FUNCTION: DB2 UDB, DRDA Communication Manager, sqljcReceive, probe:30   
  
  
MESSAGE : ZRC=0x8136001C=-2127167460=SQLZ_RC_NO_CONNECTION, SQLT_SQLJC   
  
  
"No connection"   
  
  
DATA #1 : String, 11 bytes   
  
  
CCI Error:   
  
  
DATA #2 : unsigned integer, 8 bytes   
  
  
...  
 
 

In this case, you can run the following command to view the current number of connections:

List 2. view the current number of connections

 
 

  
  
$ db2 list applications   
  
  
Auth Id Application Appl. Application Id   
  
  
DB # of   
  
  
Name Handle   
  
  
Name Agents   
  
  
DB2INST1 db2taskd 583 *LOCAL.db2inst1.080112150958   
  
  
SVT_DB 1   
  
  
DB2INST1 db2stmm 582 *LOCAL.db2inst1.080112150957   
  
  
SVT_DB 1   
  
  
DB2INST1 Java 592 *LOCAL.db2inst1.080115201505   
  
  
SVT_DB 1   
  
  
DB2INST1 java 572 *LOCAL.db2inst1.080115201445   
  
  
SVT_DB 1   
  
  
DB2INST1 java 585 *LOCAL.db2inst1.080115201458   
  
  
SVT_DB 1   
  
  
DB2INST1 java 565 *LOCAL.db2inst1.080115201437   
  
  
SVT_DB 1   
  
  
DB2INST1 java 584 *LOCAL.db2inst1.080115201457   
  
  
SVT_DB 1   
  
  
DB2INST1 java 590 *LOCAL.db2inst1.080115201503   
  
  
SVT_DB 1   
  
  
DB2INST1 db2bp 591 *LOCAL.db2inst1.080115201502   
  
  
...   
 
 

You can view the comparison between the number of connections and the value of MAX_CONNECTIONS to make adjustments. In the v9.1 or v9.5 environment, special applications inside two servers, db2stmm and db2taskd, should not be counted as external connections. Db2stmm is the DB2 database proxy used to manage the automatic memory adjustment feature. db2taskd is the proxy used to allocate database background tasks.

Java in the example indicates that external connections come from JAVA applications. Db2bp represents a connection from the CLP (DB2 command window. We can see that all these connections are connected to the database SVT_DB.