pajama

When a DB2 Java application is running a dynamic SQL statement, it uses a dynamic section from DB2 CLI packages at DB2 server side to prepare/execute the statement. Please note that these DB2 CLI packages are exactly the same packages used by a CLI application.
By default, there are 3 large CLI packages (containing 385 sections) and 3 small CLI packages (containing 65 sections) bound at a DB2 database server. There are two sections from each package (both large and small) which are reserved for positioned update/delete statements and execute immediate statements. Therefore the total number of available sections for all other statements by default is (3 * 63) + (3 * 383) = 1338, which means by default a DB2 Java application can only run 1338 dynamic statements at one time.

When this limit is hit, the application will receive an error of "Out of Package Error Occurred" with SQLCODE -805, which means the DB2 server was running out of dynamic sections available from the CLI packages.

For example, running below Java application would hit the limit and report the error as below:


PreparedStatement pStmt = null;
for (int i=1; i<=1339; i++) {
pStmt = db.con.prepareStatement("insert into myt values (1, 'name1')");
pStmt.execute();
}


***** Out of Package Error Occurred (2014-05-14 22:20:32.431) *****

Exception stack trace:
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106
com.ibm.db2.jcc.am.bd.a(bd.java:682)
com.ibm.db2.jcc.am.bd.a(bd.java:60)
com.ibm.db2.jcc.am.bd.a(bd.java:127)
com.ibm.db2.jcc.am.io.c(io.java:2706)
com.ibm.db2.jcc.t4.ab.p(ab.java:872)
com.ibm.db2.jcc.t4.ab.h(ab.java:144)
com.ibm.db2.jcc.t4.ab.b(ab.java:41)
com.ibm.db2.jcc.t4.p.a(p.java:32)
com.ibm.db2.jcc.t4.qb.i(qb.java:135)
com.ibm.db2.jcc.am.io.gb(io.java:2112)
com.ibm.db2.jcc.am.jo.rc(jo.java:3526)
com.ibm.db2.jcc.am.jo.b(jo.java:3976)
com.ibm.db2.jcc.am.jo.hc(jo.java:2732)
com.ibm.db2.jcc.am.jo.execute(jo.java:2715)
DbConn2.main(DbConn2.java:74)

Concurrently open statements:
1. SQL string: INSERT INTO MYT VALUES (1, 'NAME1')
Number of statements: 1339
********************

DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH204 0X5359534C564C3031, DRIVER=3.64.106


Then what to do if you encounter above errors?

1) firstly you need to check if you can modify the application to avoid running so many dynamic SQL statements at the same time. 

2) If you can't avoid it, you will need to increase the number of available sections by increasing the number of DB2 CLI packages. 

Please note that only the number of DB2 CLI large packages can be increased, not for small packages, and the maximum number of large packages you can increase to is 30, which also basically means the maximum available sections would be (3 * 63) + (30 * 383) = 11679.

3) To increase the number of large CLI packages, you can run DB2 bind command with CLIPKG option via the instance owner as following example:

cd ~/sqllib/bnd
db2 connect to SAMPLE

--CLPKG 10, means it will increase the number of large CLI packages to 10.
db2 "bind @db2cli.lst CLIPKG 10 grant public blocking all"

--You can then check the number of CLI large packages per isolation and cursor holdability, where you will see the number would be 10 now.

db2 "list packages for all"

db2 terminate

In both techniques outlined below, the explain tables need to be created. For more information on doing this task, see Explain Tables.

Technique 1: Obtain the explain plan when creating the stored procedure:

1) Turn on explain either:

• Dynamically within the scope of the current session by issuing
  db2 "call SET_ROUTINE_OPTS('EXPLAIN ALL')" 
  
• Or, globally at the instance level if the procedure is not being called within the current session
  db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
  db2 terminate 
  db2stop 
  db2start 
  

• If it was turned on for the current session: 
  db2 "CALL SYSPROC.SET_ROUTINE_OPTS('')"
  
• If it was turned on globally
  db2set DB2_SQLROUTINE_PREPOPTS=
  db2 terminate 
  db2stop 
  db2start

db2 "SELECT executable_id FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL,NULL, -1)) AS T where stmt_text like '%INSERT INTO SYSIBM.SYSDUMMY1%'"

3) With the executable_id returned in step 2 (For example: x'0100000000000000581E00000000000002000000010020140109134503816499'), make the following call. Also replace EXPLAIN_SCHEMA with your actual explain schema.

db2 "call EXPLAIN_FROM_SECTION(x'0100000000000000581E00000000000002000000010020140109134503816499', 'M', NULL, 0, 'EXPLAIN_SCHEMA', ?, ?, ?, ?, ?)"

This will populate the explain tables under the schema you specified

4) Run this command to extract the explain plan:
db2exfmt -d <db name> -g TIC -w -1 -n % -s % -# 0 -o exfmt.out

To generate and run reorg and runstats scripts:

1. Log in as the DB2 administrator, and connect to the database.
   
   
2. To create a script for reorg table, run the following command on one line:
   db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/;/g' > /tmp/reorg_tbl.ddl
   
   
3. To create a script for reorg index, run the following command on one line:
   db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG INDEXES ALL FOR TABLE /g' | sed 's/$/;/g' > /tmp/reorg_idx.ddl
   
   
4. To create a script for runstats, run the following command on one line:
   db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS on TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL;/g' > /tmp/runstats_tbl.ddl
   
   
5. Run reorg against tables and indexes, then runstats by entering the following lines:
   db2 -tvf /tmp/reorg_tbl.ddl
   db2 -tvf /tmp/reorg_idx.ddl
   db2 -tvf /tmp/runstats_tbl.ddl
   
   
6. Ignore the following message if you receive it: 
   SQL2212N The specified table is a view. The Reorganize Table utility cannot be run against a view.
   
   
7. To capture the message printed on screen, you can pipe the standard output into a file, such as with the following command:
   db2 -tvf /tmp/runstats_tbl.ddl > filename.log 2>&1
   
   NOTE: The commands in step 2, 3 and 4 will not run on windows system.

1. Log in as the DB2 administrator, and connect to the database.
   
2. To create a script for reorg table, run the following command on one line:
   
   db2 -x "select 'REORG TABLE ' ||rtrim (tabschema)|| '.' || rtrim (tabname) || ' ;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'" > reorg.out
   
3. Run reorg against tables entering the following line:
   db2 -tvf reorg.out

NOTE: The where clause in step 2 can be altered to include other tables in the reorg.out script. 
It is recommended to runstats on any table that has been reorged. 
Also, these commands may not work on all versions and levels of Unix and Linux.

Often, during maintenance events, a database is deactivated and the database-instance is stopped but the application is not stopped and will continue to attempt to establish a connection to the database while the database is under maintenance, this is not a problem in itself.
Later after maintenance completes and the database-instance is restarted and the database is activated, the application's attempt to establish a database connection will be successful.

However, there is a timing window where, after maintenance completes and the database-instance is started but before the database is activated, an application's database connection attempt can come in and automatically start database activation before a DBA has explicitly activated it. Since database activation can sometimes take several minutes, the application might timeout before the activation completes, and rollback the database activation. If the number of application threads attempting to connect to the database is very large, they may repeatedly start database activation ahead of the DBA's request to explicitly activate the database, and repeatedly timeout and rollback the activation. 
This can cause a repeated loop of automatic database activation and timeout/rollback, while the DBA's explicit request to activate the database appears to hang.

In order to prevent this kind of phenomenon, we can temporarily pause application database connection attempts from reaching the database server and automatically starting database activation, so that the DBA's request to explicitly activate the database can complete.

There are two methods that can be used:

Method #1: use the db2trc -suspend option to pause incoming database connections from reaching the database server, and then un-pause them after the DBA has completed database activation. (note that this method does not actually perform a trace, so it does not have any performance impact).

1) db2trc on -debug DB2.SQLCC.sqlcctcpconnmgr_child.115 -suspend 
2) db2start 
3) db2 activate database <dbname>
4) db2trc off 


Method #2: use the 'db2start admin mode restricted access' command to quiesce the database in restricted mode during db2start, and then unquiesce after the database activation is complete.

1) db2start admin mode restricted access 
2) db2 activate database <dbname>
3) db2 unquiesce instance <instance name>

To determine if a table has been taken out of check pending (by the SET INTEGRITY ... IMMEDIATE UNCHECKED command), you can use the following SELECT query to see what options were used to perform the SET INTEGRITY command: 

select tabschema as Schema, tabname as Name, status as State, substr(const_checked,1,1) as Foreign_Keys, substr(const_checked,2,1) as Check_Constraints, substr(const_checked,5,1) as Summary_Tables from syscat.tables where tabname = '<tablename>' and tabschema = '<schema>'


...where <tablename> is the table and <schema> is the table schema.

For example, if <schema> = db2inst1 and <tablename> = employee, then an example of the result of the query is as follows:


SCHEMA NAME STATE FOREIGN_KEYS CHECK_CONSTRAINTS SUMMARY_TABLES
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------ ----------------- --------------
DB2INST1 EMPLOYEE N U Y Y 

1 record(s) selected.

The FOREIGN_KEYS column shows the status of the check constraint for table DB2INST1.EMPLOYEE and is shown to have a value of 'U' -- indicating it was checked by the user using the SET INTEGRITY ... IMMEDIATE UNCHECKED command.