[DB2] SQL0805N解决和思考

原创

夜半钟鸣

修改于 2021-06-21 11:28:54

1.4K0

修改于 2021-06-21 11:28:54

文章被收录于专栏：小树洞

一、报错现象

这是一个在使用 DB2数据库过程中比较常见的错误，报错信息如下

 Exception stack trace:
 com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH203 0X5359534C564C3031, DRIVER=3.66.46

二、关键知识点

先说明几个知识点：

[Packages]

DB2 中的包是一组信息，其可以控制任何静态SQL语句的编译，部分控制着任何动态SQL语句的编译以及可以影响在其范围内发出的任何SQL请求的执行。

包信息包括编译期间使用的优化级别、执行期间是否对符合条件的游标使用阻塞以及执行期间使用的并行度等项目。

对于静态SQL语句，包还有一个和每个SQL语句相关联的section

[Section]

因为应用程序可以有许多不同的静态和动态性质的 SQL 语句，所以包也一样。DB2 UDB 将包细分为更小的单元，称为section。 section包含有关 SQL 语句本身（如果存在）以及有关在应用程序中找到 SQL 语句的上下文的信息。

section是 SQL 语句的实际可执行体现。它包含 DB2 UDB 产生指定结果所需的逻辑和数据访问方法。一个section由一系列运算符和任何关联的操作数组成，这些操作数概述了数据访问的执行顺序和最佳操作。

section是 SQL 语句编译的最终结果。 SQL 编译器确定满足 SQL 语句的最有效方法，并生成一个section来实现该计划。

[DB2 CLI Packages]

DB2 调用级接口 (DB2 CLI) 是 DB2 系列数据库服务器的可调用 SQL 接口。可调用 SQL 接口是用于数据库访问的应用程序接口 (API)，它使用函数调用来调用动态 SQL 语句。在创建或迁移数据库时，或者给数据库服务端打补丁时，DB2® CLI 包会自动绑定到数据库。

在 CLI 应用程序中分配的每个语句句柄将占用 CLI 包中的一个section。

默认的：

DB2 CLI包在NULLID集合中创建
为每个隔离级别（4 个隔离级别）和游标保持性 (2种) 创建了三个小包和三个大包。（3*4*2 + 3*4*2=共48包）
每个小包允许每个连接最多 65 个语句句柄，每个大包每个连接最多允许 385 个语句，其中大包和小包各有2个句柄是提供给update/delete语句和execute immediate语句（同一个连接中这两种句柄可以被多次复用），所以每个连接中其他所有语句可以使用的句柄数初始默认为(3 * 63) + (3 * 383) = 1338 个。

CLI包的命名方式：SYSS[H|N]xyy 和 SYSL[H|N]xyy

'S'代表小包，'L'代表大包
'H' 代表 WITH HOLD，'N' 代表 NOT WITH HOLD
'x'是隔离级别：0=NC, 1=UR, 2=CS, 3=RS, 4=RR
'yy' 是包迭代 00 到 FF

默认下面这些包会在数据库中预先创建好：

db2 "select pkgname from syscat.packages where pkgSchema='NULLID' and pkgname like '%SYS%'"

三、解决办法

3.1. Case A

如果提示的缺少的package是上面提到的预先创建好的package其中之一，则说明那个package因为某些未知原因在数据库中丢失了。

可以重新bind一次来创建包

 cd ~db2inst1/sqllib/bnd
 db2 "bind @db2cli.lst blocking all sqlerror continue grant public "

3.2. Case B

如果提示缺少的包的号码大于上面提到的预先创建好的任何package号码，则说明当前存在的包不够用了，程序在申请新的package。

比如应用报805错误说找不到NULLID.SYSLH203这个包，则说明应用已经使用了SYSSH200, SYSSH201, SYSSH202 (3个小包) 和 SYSLH200, 201, 202 (3个大包) 并且正在寻找下一个大包。

首先需要知道，单次应用连接中可使用的CLI Package的句柄数量是有上限的，所以一般有2种情况会导致这种场景：

应用程序代码中存在未正常释放已经不需要使用的语句句柄。
如果程序不存在上述句柄未释放的情况，则可能是发生报错的时间点应用承载了过高的并发压力，而当前单次连接的语句句柄上限满足不了业务需求了

对于代码层的原因，需要排查代码来解决问题根本原因。比较常见的出现问题的语句为prepareStatement, DECLARE CURSORS, 或者嵌入式SQL(静态SQL)等，每一个独立的这种语句都会占用一个句柄，使用完毕后需要调用Statement.close()方法释放句柄。

对于第二种情况，则需要手动增加CLI 包的数量

 cd ~db2inst1/sqllib/bnd
 db2 "bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG X"

这里的"X"是指可以创建的大包的数量，可以指定的范围为3-30。

四、实验

4.1. 错误复现

这里有一个Java Demo，用来复现SQL0805N错误。

其中通过调用prepareStatement语句但不正常释放来模拟句柄数耗尽。

 import java.sql.*;
 import java.io.*;
 import java.math.*;
 import java.util.*;
 
 public class db2805_1
 {
     public static void main(String[] args)
     {
         try{
             Class.forName("com.ibm.db2.jcc.DB2Driver").getDeclaredConstructor().newInstance();
             Connection conn= DriverManager.getConnection("jdbc:db2://10.211.55.10:60000/sample","db2inst1","db2inst1");
             if (conn==null)
             {
                 System.out.println("Can not connect to database");
             }
 
             PreparedStatement pstmt = null;
             ResultSet rs=null;
 
             String sql = "select count(*) from emp";
 
             int i=1;
             while(i<10000){
                 pstmt = conn.prepareStatement(sql);   /*每次调用占一个section*/
                 pstmt.execute();
                 System.out.println("i="+i);
                 i++;
             }
 
 
         } catch(Exception e){
             e.printStackTrace();
         }
     }
 }

数据库端的包的情况如下（默认）：

 -- 通过SQL检查
 $ db2 connect to sample
 
    Database Connection Information
 
  Database server        = DB2/LINUXX8664 10.1.4
  SQL authorization ID   = DB2INST1
  Local database alias   = SAMPLE
 $ db2 "select substr(PKGNAME,1,20),TOTAL_SECT,CREATE_TIME,ISOLATION,BOUNDBYTYPE,PKG_CREATE_TIME,LASTUSED from syscat.packages where PKGNAME like 'SYSSH20%' or PKGNAME like 'SYSLH20%'"
 
 1                    TOTAL_SECT CREATE_TIME                ISOLATION BOUNDBYTYPE PKG_CREATE_TIME            LASTUSED
 -------------------- ---------- -------------------------- --------- ----------- -------------------------- ----------
 SYSLH202                    385 2021-06-18-18.18.44.883818 CS        S           2021-06-18-18.18.44.883818 01/01/0001
 SYSLH201                    385 2021-06-18-18.18.44.874181 CS        S           2021-06-18-18.18.44.874181 01/01/0001
 SYSLH200                    385 2021-06-18-18.18.44.865003 CS        S           2021-06-18-18.18.44.865003 01/01/0001
 SYSSH202                     65 2021-06-18-18.18.44.818740 CS        S           2021-06-18-18.18.44.818740 01/01/0001
 SYSSH201                     65 2021-06-18-18.18.44.816721 CS        S           2021-06-18-18.18.44.816721 01/01/0001
 SYSSH200                     65 2021-06-18-18.18.44.814545 CS        S           2021-06-18-18.18.44.814545 01/01/0001
 
   6 record(s) selected.
   
 -- 也可以这样检查
 $ db2 connect to sample
 
    Database Connection Information
 
  Database server        = DB2/LINUXX8664 10.1.4
  SQL authorization ID   = DB2INST1
  Local database alias   = SAMPLE
 
 [db2inst1@db01] [~]
 $ db2 "list packages for all"|grep -i NULLID|egrep -i 'SYSSH2|SYSLH2'
 SYSLH200    NULLID                SYSIBM              385 Y       3        CS        B
 SYSLH201    NULLID                SYSIBM              385 Y       3        CS        B
 SYSLH202    NULLID                SYSIBM              385 Y       3        CS        B
 SYSSH200    NULLID                SYSIBM               65 Y       3        CS        B
 SYSSH201    NULLID                SYSIBM               65 Y       3        CS        B
 SYSSH202    NULLID                SYSIBM               65 Y       3        CS        B

执行程序报错：

可知，在申请第1339个section时失败，这里和上面说明的单次连接1338个句柄上限一致。

4.2. 错误修复

这里给Demo代码添加正常释放语句句柄的逻辑，如下

 ...
 while(i<10000){
    pstmt = conn.prepareStatement(sql);
    pstmt.execute();
    pstmt.close();                  /*释放句柄*/
    System.out.println("i="+i);
    i++;
 }
 ...

再次执行成功

4.3. APP占用section数的监控

在开启了实例级别的监控开关后，可以通过采集应用的snapshot来获取其对于section的占用情况，例如：

 Section number = 35
 Application creator = NULLID
 Package name = SYSLH206

则应用已经占用的句柄数可以由上面的信息计算出来。因为APP现在已经用到SYSLH206包的35个section，则其已经使用过了SYSSH200, SYSSH201, SYSSH202 (3个小包) 和 SYSLH200, 201, 202, 203, 204, 205(6个大包) 的所有section，具体数目为：3*64 + 6*384 + 35 = 2531

正常释放句柄的APP

这里我们来观察下正常的APP在获取CLI包section时的情况，demo程序为

 while(i<10000){
    pstmt = conn.prepareStatement(sql);
    pstmt.execute();
    pstmt.close();                  /*释放句柄*/
    System.out.println("i="+i);
    i++;
 }

可知：句柄每次不需要使用后都正常释放的APP，由于这里只存在prepareStatement的调用和释放，所以使用的section是固定的，为第一个小包SYSSH200的1个section。

未正常释放句柄的APP

这里为了方便观察，给demo程序后面加了一层模拟休眠的SQL，从而模拟程序处于未提交状态，另外prepareStatement语句每次循环使用完后并未释放句柄

 String sql = "SELECT * FROM sysibm.systables where fid=?";
 String sql1 = "call dbms_alert.sleep(1000000)";  /*休眠*/
 
 int i=1;
 while(i<1338){
   pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1,5);
   pstmt.execute();
   System.out.println("i="+i);
   i++;
 }
 pstmt = conn.prepareStatement(sql1);
 pstmt.execute();

执行程序，可以看到成功执行了1338个语句(含一个sleep语句调用)

再采集DB2 snapshot观察下section的占用

可以计算下：3*63 + 3*383 = 1338

可知：未正常释放句柄时，单次连接中句柄的占用是逐渐递增的，直到达到上限为止

4.4. 句柄未释放是否影响其他并发连接

以上一小节agentid=562的应用为对比，再执行另一段未正常释放句柄的程序，来观察section的未释放是否不会影响其他并发的连接

显而易见，是无影响的。

五、思考总结

5.1. 最开始的思考误区

一开始以为DB2 CLI包是一组由多个应用连接共享的资源，每个连接对于section的申请按照先到先得的原则，占用句柄不释放的异常应用程序最终会消耗光总的section从而产生805报错。

此种思考结论，不能解释应用人员提出来的：出现报错后再次重试可以继续执行而未出现报错，以及别的一些应用访问数据库正常的现象。

5.2. DB2内存结构

这里主要说明下DB2代理私有内存。

每个DB2 代理进程都有自己的私有内存工作区域，以执行任务。代理进程将代表应用程序使用内存来优化、构建和执行访问计划、执行排序、记录游标信息，收集统计信息等。

5.3. SQL语句工作流程

可知，SQL的执行是依赖于最终的编译结果section的获取。

对于CLI 包的调用，也应该是遵循这个过程，通过JDBC调用DB2 CLI接口时，程序中包含的PrepareStatement、Execute Immediate等语句都需要申请section，最终从CLI Package中获取section并加载到自身代理的私有内存中。但是，在同一个应用连接中，CLI Package所包含的section个数是有上限的，如果存在已占用的语句句柄在执行完并未正常释放时，最终将导致达到上限而报错。

并且，不同的应用连接在数据库连接层的连接代理负责自己那一部分的包和section的获取和加载到私有内存，即代理间是独立的非共享的，所以不存在最开始提到的那个思考误区。