学习
实践
活动
工具
TVP
写文章
专栏首页小树洞[DB2] SQL0805N解决和思考
原创

[DB2] SQL0805N解决和思考

一、报错现象

这是一个在使用 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的获取和加载到私有内存,即代理间是独立的非共享的,所以不存在最开始提到的那个思考误区。

六、参考文章

https://www.ibm.com/support/pages/75-ways-demystify-db2-9-tech-tip-db2-cli-packages-demystified

https://www.ibm.com/support/pages/sql0805n-package-nullidsyslh21e-was-not-found

https://www.ibm.com/support/pages/how-many-concurrently-running-statements-allowed-db2-java-application-and-how-increase-it

原创声明,本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

登录 后参与评论
0 条评论

相关文章

  • 详述一则DB2 Error Code 1639和SQL State 08001案例诊断和解决方案

    导读:在 db2inst1/sqllib/security 目录下,检查 db2ckpw 和 db2chpw 文件的权限。

    数据和云
  • 对shipyard打开后显示没有容器和镜像的思考和解决-设置wubantu静态ip-修改shipyard容器的ip和端口

    Shipyard是一个基于Web的Docker管理工具,支持多主机,可以把多个Docker主机上的容器统一管理,可以查看镜像,甚至构建镜像,并提供RESTful...

    jiankang666
  • Oracle大数据量更新引发的死锁问题解决方法及Oracle分区和存储过程的思考

    前几天上午在对数据库的一张表进行操作的时候,由于这张表是按照时间的一张统计表,正好到那天没有测试数据了,于是我想将表中所有的时间,统一更新到后一个月,于是对80...

    星哥玩云
  • 浅析MongoDB中的意向锁

    成熟的数据库设计中,需要一个模块对资源的并发控制进行管理。意向锁就是实现资源并发控制管理的经典方式。在讨论它的概念与设计前,我们先举几个MongoDB的经典场景...

    MongoDB中文社区
  • 浅析MongoDB中的意向锁

    成熟的数据库设计中,需要一个模块对资源的并发控制进行管理。意向锁就是实现资源并发控制管理的经典方式。在讨论它的概念与设计前,我们先举几个MongoDB的经典场景...

    MongoDB中文社区
  • MySQL多源复制之复制过滤

    在上一篇文章《深入了解MySQL多源复制》中,介绍了MySQL多源复制的相关内容,本文将继续讲解MySQL多源复制,主要内容是过滤复制以及在已有复制过滤配置中新...

    SEian.G
  • 科技向善,中国银行软件中心在你冷的地方做暖阳

    地震了、洪水了、台风了……如果碰巧你生活在那里,又碰巧和身边的许多人一样,你手边还有一些多余的物资,你会怎么办?或者说可以怎么办?

    IT创事记
  • 运维必备--如何彻底解决数据库的锁超时及死锁问题

    之前有介绍过,我主要是做数据仓库运维的,业余也会动手写 python 程序,django 应用,vue 的 app,有兴趣可以加我好友一起学习。最近比较让我头疼...

    somenzz
  • 解决ER\Studio无法生成mysql列注释问题

    最近改用ER\Studio建模,发现ER\Studio居然不支持生成mysql列注释,看网上都说勾选即可,然后生成mysql时并没有那个勾选项,试了下生成Ora...

    用户1409099
  • 工程物料管理信息化建设(五)——系统集成实战总结

    工作内容:将原OA系统中合同支付流程所需要用到的合同基本信息和供应商基本信息改为从采购管理软件里读取

    天堂向左
  • 你是否也被Centos下默认iptables规则坑过

    今天让同事去Beta环境实践模拟线上环境多机房异地备份,我们有一个统一登录的数据库,很多产品的登录都基于这个库做的统一登录,所以是比较重要的一个数据库,所以让他...

    后场技术
  • 多维数据库概述之一---多维数据库的选择

    1. 多维数据库简介 多维数据库(Multi Dimesional Database,MDD)可以简单地理解为:将数据存放在一个n维数组中,而不是像关系数据库那...

    用户1148526
  • 3306π武汉站嘉宾专访-斗鱼网络资深DBA赵飞祥

    网名 yumushui ,拥有多年一线传统行业和互联网数据库架构设计与运维经验。Oracle 11g OCM,对MySQL、Oracle、PostgreSQL、...

    田帅萌
  • 剖析Spring多数据源

    在实际开发中,经常会遇到应用要访问多个库的情况,需要配置多个数据源。本文会介绍spring多数据源的典型场景,如何优雅的实现多数据源,并结合spring、myb...

    田守枝
  • db2 分区数据库详解

    本文主要介绍什么是 DB2 数据库分区,为什么采用数据库分区,并以 Balanced Warehouse E7100 为例介绍数据库分区管理的基本方法及应用实践...

    全栈程序员站长
  • DB29.7 fp11补丁安装遇到的问题(二)Java.lang.NumberFormatException:For input String "B"

    DB2安装完成后,启动项目发现报java.lang.NumberFormatException:For input String "B"的异常,开始以为配置文件...

    西门呀在吹雪
  • 数据火器库 - 八卦系列之借老枪谈可靠性

    上次聊了瑞士军刀SQLite, 从年纪上SQLite出生于大数据和手机时代之前,对比后来的大数据引擎和云原生数据库,SQLite可谓个头不大,辈分不小了。不过数...

    数据库小组
  • 从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

    编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

    数据和云

扫码关注腾讯云开发者

领取腾讯云代金券