主机DB2 9存储过程的规划和实施技巧

本文主要介绍主机 DB2 上的存储过程的规划和实施经验，帮助用户了解主机 DB2 上的存储过程的基本特点及其如何进行分类实施。

DB2 版本 9 以后，主机上所有的存储过程都统一由 z/OS 的一个子系统 WLM 来统一管理，WLM 是通过应用环境（Application Environment）来管理存储过程的，当存储过程被调用时，WLM 会自动启动和应用环境相应的启动作业来管理和实现所需要的存储过程功能。

我们可以把数据库存储过程分为 DB2 系统产品数据库存储过程和用户开发的存储过程。关于 DB2 数据库系统产品的存储过程，按照存储过程的功能，开发语言，性能要求等，我们会设置不同的数据库存储过程运行环境。下面的篇幅会面较详细的介绍这方面的情况。对于用户编写的存储过程，存储过程配置基本原则是一样，都需要根据实际应用开发功能和性能的要求进行规划实施，都需要根据具体要求进行具体分析，这里不再做主要描述。

存储过程的客户化规划和实施

在实施主机提供的 DB2 存储过程的时候，就象上文提到的，需要考虑到存储过程调用时的执行要求，这些要求包括存储过程执行的功能是怎么样的，调用时限定条件以及存储过程有无特殊性能要求等。正确配置 DB2 存储过程，以下几个方面是比较重要而应该被重点关注的：

同存储过程相关的 DB2 子系统配置

由于 DB2 9 产品发布后，包括针对存储过程在内一些新的功能增强，DB2 系统都是通过实施 PTF 或 APAR 的方式来实现的，所以需要确认相关的数据库产品的 PTF 或 APAR 是否已经正确实施。具体需要实施的 PTF 或 APAR 可以参考相关的资料。

在 DB2 9 的安装流程里，安装界面 Panel DSNTIPX 是来配置和存储过程相关的参数的，是用来生成安装所需要的 Sample 作业。

清单 1. DB2 9 安装界面 DSNTIPX

DSNTIPX INSTALL DB2 - ROUTINE PARAMETERS

===>

Scrolling backward may change fields marked with asterisks

Enter data below:

* 1 WLM PROC NAME ===> DSN1WLM WLM-established stored procedure JCL PROC

2 NUMBER OF TCBS ===> 8 Number of concurrent TCBs (1-100)

3 MAX ABEND COUNT ===> 0 Allowable ABENDs for a procedure (0-255)

4 TIMEOUT VALUE ===> 180 Seconds to wait before SQL CALL or

function invocation fails (5-1800,NOLIMIT)

5 WLM ENVIRONMENT ===> Default WLM env name

6 MAX OPEN CURSORS ===> 500 Maximum open cursors per thread

7 MAX STORED PROCS ===> 2000 Maximum active stored procs per thread

其中，对于 DSNTIPX 所涉及到的参数，下面做一个简要的描述：

◆WLM PROC NAME

用来指定在安装过程中生成 Sample 中作业的缺省存储过程的名字。

◆NUMBER OF TCBS

用来指定在 WLM 启动的一个地址空间里，可以并发调用多少个存储过程。这个值也受到 USS 下的 MAXPROCUSER（每一个用户在主机 USS 环境下最大可以有多少个 processes）参数的限制。

◆MAX ABEND COUNT

在 DSNZPARM 里对应的参数是 STORMXAB，用来指定存储过程可以有多少次调用失败后，整个调用结束。缺省值为 0，意味着在存储过程第一次执行不正常结束时，存储过程调用结束。

◆TIMEOUT VALUE

对应的 DSNZPARM 参数是 STORTIME。用来指定在存储过程地址空间里，DB2 容许等待存储过程分配 TCB 的时间，单位为秒。如果在这个时间段内没有被分配一个 TCB 来执行，存储过程调用失败。推荐不要设为 NOLIMIT，因为如果设为 NOLIMIT，当出现异常例如存储过程地址空间被关掉，存储过程调用请求会一直等待分配 TCB，直到条件满足或者 thread 被取消。

◆WLM ENVIRONMENT

对应的 DSNZPARM 参数为 WLMENV，用来指定在定义存储过程时，如果没有指定 WLM_ENVIRONMENT 的值时，系统会自动分配的 WLM 应用环境的名字。

◆MAX OPEN CURSORS

对应的 DSNZPARM 参数是 MAX_NUM_CUR，用来指定每一个 DB2 应用 thread 最大可以打开的 cursors 数量

◆MAX STORED PROCS

对应的 DSNZPARM 参数是 MAX_ST_PROC。用来设定 DB2 应用 thread 可以调用的最大存储过程数量。在每一次 COMMIT 后，重新开始记数。

主机 WLM 应用环境的规划和实施

DB2 9 之后，所有的存储过程都运行在 WLM 应用环境管理之下，由于主机数据库下存储过程数量较多，我们在定制的时候，需要把具有类似属性的存储过程定义在一个应用环境下。目的是最大限度的减少 WLM 应用环境的定义数量。当然如果你只是客户化少量的存储过程或者是对某些存储过程有一些特定的要求，例如性能或存储等，也可以定义粒度更小的 WLM 应用环境。另外在一些存储过程里所涉及的数据集里，有一些是需要具有 APF 属性的，需要特别注意。以下为一个 WLM 应用环境的定义例子，例子中应用环境所对应的启动作业为 DB11WLM1。

清单 2. 主机 WLM 应用环境定义界面

Appl Environment Name . . DB11WLM1

Description . . . . . . . DB2 DB11WLM1

Subsystem type . . . . . DB2

Procedure name . . . . . DB11WLM1

Start parameters . . . . DB2SSN=&IWMSSNM,NUMTCB=1,APPLENV=DB11WLM1

创建 DB2 存储过程

DB2 产品提供的存储过程是通过客户化 DSNTIJSG 作业来定义的。在提交作业完成创建存储过程之前，需要对存储过程进行必要的规划。根据存储过程的功能不同，建议分为以下 4 类：

表 1. 存储过程的分类

另外，需要注意以下几点：

1.存储过程的分类可能会依据 DB2 的 PTF 版本不同而有改变；

2.存储过程的 NUMTCB 的设置除了根据存储过程的功能要求外，还依赖于系统资源情况；

3.建议 WLM 应用环境和启动过程名称一致，便于管理；

4.存储过程分类的粒度根据实际情况可能会有不同，基本原则为在满足功能需求的情况下，尽可能的简化配置，便于管理。

以下我们重点介绍为四个不同的 WLM 应用环境准备的启动作业过程，通过启动作业可以为 WLM 环境准备不同的存储过程执行环境。

为在线 Utility 执行而准备的存储过程

此类存储过程功能是需要调用数据库的在线 UTILITY，例如数据库所提供的 DSNUTILS 和 DSNUTILU，在配置此类存储过程的时候，NUMTCB 要求定义为 1，也就是不容许并发调用。主要是由于在调用时，会生成一些中间文件，如果容许同时调用存储过程的话，这些文件会被互相覆盖，导致不可预料的错误发生。

以下为此类存储过程所调用的启动作业。在作业中的 SYSIN 是分配一些临时空间存储 Utility 的输入语句。SYSPRINT 是分配临时空间来存储 utility 的输出信息。RNPRIN01 是为 DFSORT 信息所分配的空间，UTPRINT 也是为 DFSORT 信息所分配的空间，DSSPRINT 是当执行 concurrent copies 时，为输出信息分配空间。

清单 3. 在线 Utility 存储过程对应的 STC 作业

//DB11WLM1 PROC APPLENV=DB11WLM1,DB2SSN=DSN,RGN=0K,NUMTCB=1

//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,

// PARM=''&DB2SSN,&NUMTCB,&APPLENV''

//STEPLIB DD DISP=SHR,DSN=prefix.SCEERUN

// DD DISP=SHR,DSN=prefix.SDSNEXIT

// DD DISP=SHR,DSN=prefix.SDSNLOAD

//UTPRINT DD SYSOUT=*

//RNPRIN01 DD SYSOUT=*

//DSSPRINT DD SYSOUT=*

//SYSIN DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)

//SYSPRINT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)

为通用情况准备的存储过程

另外，我们为大多数的存储过程准备了一套运行环境，所准备的启动作业如下所示，其中 NUMTCB 可以根据系统资源情况来定，一般推荐可以设置为 40 或 60。

清单 4. 通用情况下存储过程对应的 STC 作业

//DB11WLM2 PROC APPLENV=DB11WLM2,DB2SSN=DSN,RGN=0K,NUMTCB=40

//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,

// PARM=''&DB2SSN,&NUMTCB,&APPLENV''

//STEPLIB DD DISP=SHR,DSN=prefix.SCEERUN

//* DD DISP=SHR,DSN=prefix.SDFHEXCI For CICS

// DD DISP=SHR,DSN=prefix.SDSNEXIT

// DD DISP=SHR,DSN=prefix.SDSNLOAD

//* DD DISP=SHR,DSN=prefix.SCSQLOAD For MQ

//* DD DISP=SHR,DSN=prefix.SCSQAUTH For MQ

//* DD DISP=SHR,DSN=prefix.SCSQANLE For MQ

//*SYSIN DD SYSOUT=* For Debugger and XML

//*SYSPRINT DD SYSOUT=*

//*WSERROR DD PATH=''/tmp/wsc.err'', For WEB_SERVICES

//* PATHOPTS=(ORDWR,OCREAT,OAPPEND),

//* PATHMODE=(SIRUSR,SIWUSR,SIRGRP,SIWGRP,SIROTH,SIWOTH)

REXX 语言编写的存储过程

对于由 REXX 语言写的存储过程来说，启动作业同其他类型的存储过程是不同的。见下面的例子。需要特别提出的是，REXX 类型的存储过程的 NUMTCB 应该设为 1。

清单5. REXX 存储过程对应的STC作业

//DB11WLMR PROC APPLENV=DB11WLM_REXX,DB2SSN=DSN,RGN=0K,NUMTCB=1

//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,

// DYNAMNBR=5, <== Allow for Dyn Allocs

// PARM=''&DB2SSN,1,&APPLENV'' <== Use 1, not NUMTCB

//*

//NUMTCB@1 SET NUMTCB= <== Null NUMTCB symbol

//*

//* Include SDSNEXIT to use Secondary Authids (DSN3@ATH DSN3@SGN exits)

//STEPLIB DD DISP=SHR,DSN=prefix.RUNLIB.LOAD

// DD DISP=SHR,DSN=CBC!!.SCCNCMP <== C Compiler

// DD DISP=SHR,DSN=prefix.SCEERUN <== LE runtime

// DD DISP=SHR,DSN=prefix.SDSNEXIT

// DD DISP=SHR,DSN=prefix.SDSNLOAD

//SYSEXEC DD DISP=SHR,DSN=DSN!!0.SDSNCLST <== Location of DSNTPSMP

//SYSTSPRT DD SYSOUT=*

//CEEDUMP DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SYSABEND DD DUMMY

//DSNTRACE DD SYSOUT=*

//*

//**** Data sets required by the SQL Procedures Processor

//SQLDBRM DD DISP=SHR, <== DBRM Library

// DSN=DSN!!0.DBRMLIB.DATA

//SQLCSRC DD DISP=SHR, <== Generated C Source

// DSN=DSN!!0.SRCLIB.DATA

//SQLLMOD DD DISP=SHR, <== Application Loadlib

// DSN=DSN!!0.RUNLIB.LOAD

//SQLLIBC DD DISP=SHR, <== C header files

// DSN=CEE!!.SCEEH.H

// DD DISP=SHR,

// DSN=CEE!!.SCEEH.SYS.H

// DD DISP=SHR, <== Debug header file

// DSN=DSN!!0.SDSNC.H

//SQLLIBL DD DISP=SHR, <== Linkedit includes

// DSN=CEE!!.SCEELKED

// DD DISP=SHR,

// DSN=DSN!!0.SDSNLOAD

//SYSMSGS DD DISP=SHR, <== Prelinker msg file

// DSN=CEE!!.SCEEMSGP(EDCPMSGE)

//*

//**** DSNTPSMP Configuration File - CFGTPSMP (optional)

//* A site provided sequential dataset or member, used to

//* define customized operation of DSNTPSMP in this APPLENV.

//*CFGTPSMP DD DISP=SHR,DSN=

//*

//**** Workfiles required by the SQL Procedures Processor

//SQLSRC DD UNIT=SYSALLDA,SPACE=(23440,(20,20)),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)

//SQLPRINT DD UNIT=SYSALLDA,SPACE=(23476,(20,20)),

// DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)

//SQLTERM DD UNIT=SYSALLDA,SPACE=(23476,(20,20)),

// DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)

//SQLOUT DD UNIT=SYSALLDA,SPACE=(23476,(20,20)),

// DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)

//SQLCPRT DD UNIT=SYSALLDA,SPACE=(23476,(20,20)),

// DCB=(RECFM=VB,LRECL=137,BLKSIZE=23476)

//SQLUT1 DD UNIT=SYSALLDA,SPACE=(23440,(20,20)),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)

//SQLUT2 DD UNIT=SYSALLDA,SPACE=(23440,(20,20)),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)

//SQLCIN DD UNIT=SYSALLDA,SPACE=(32000,(20,20))

//SQLLIN DD UNIT=SYSALLDA,SPACE=(3200,(30,30)),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)

//SQLDUMMY DD DUMMY

//SYSMOD DD UNIT=SYSALLDA,SPACE=(23440,(20,20)),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=23440)

JAVA 语言应用准备的存储过程

对与 JAVA 语言编写的存储过程，由于主机上的 JAVA 是运行在 USS 下，所以需要指定指定 JAVA 运行环境，见启动过程中的 JAVAENV 所指定的数据集。

清单6. JAVA 存储过程对应的STC作业

//DB11WLMJ PROC APPLENV=DB11WLM_JAVA,DB2SSN=DSN,RGN=0K,NUMTCB=5

//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,

// PARM=''&DB2SSN,&NUMTCB,&APPLENV''

//STEPLIB DD DISP=SHR,DSN=prefix.SCEERUN

// DD DISP=SHR,DSN=prefix.SDSNEXIT

// DD DISP=SHR,DSN=prefix.SDSNLOAD

//JAVAENV DD DISP=SHR,DSN=prefix.DB11WLMJ.JAVAENV

//JSPDEBUG DD SYSOUT=*

//JAVAOUT DD PATH=''/V1R7/USR/db2a10/JAVAOUT.TXT'',

// PATHOPTS=(ORDWR,OCREAT,OAPPEND),

// PATHMODE=(SIRUSR,SIWUSR,SIRGRP,SIWGRP,SIROTH,SIWOTH)

//JAVAERR DD PATH=''/V1R7/USR/db2a10/JAVAERR.TXT'',

// PATHOPTS=(ORDWR,OCREAT,OAPPEND),

// PATHMODE=(SIRUSR,SIWUSR,SIRGRP,SIWGRP,SIROTH,SIWOTH)

其中 JAVAENV 指定 JAVA 运行环境定义，JAVAENV 文件的内容，举一例子做参考：

清单7. JAVAENV 配置文件信息

ENVAR("_CEE_ENVFILE=/u/db2tdbg/nolimit.txt",

"JAVA_HOME=/u/java/J1.4"),

MSGFILE(JSPDEBUG,,,,ENQ),

XPLINK(ON)

其中 nolimit.txt 的配置信息如下：

清单8. JAVAENV 配置文件中 nolimit.内容信息

LIBPATH=/griv2r2/lib:/usr/lpp/db2810/lib

CLASSPATH=/egfv1r1/egfsproc.jar:/usr/lpp/db2810/classes/db2j2classes.zip

:/u/java/J1.4/lib/charsets.jar

DB2_HOME=/usr/lpp/db2810/

WORK_DIR=/u/db2tdbg

TMSUFFIX=/griv2r2/griserver.jar:/griv2r2/griobjects.jar

:/griv2r2/xercesImpl.jar:/griv2r2/xmlapis.jar

:/griv2r2/grinls.jar:/griv2r2/griuf.jar:/griv2r2/bicompare.jar

PATH=/u/java/J1.4/bin

DB2SQLJPROPERTIES=/u/db2tdbg/db2sqljjdbc.properties

STEPLIB=SYS1.DSN810.PE01.SDSNEXIT:DSN810.SDSNLOAD:DSN810.SDSNLOD2

JAVA_HOME=/u/java/J1.4

其中 /u/db2tdbg/db2sqljjdbc.properties 的配置信息为：

清单9.JAVAENV 配置文件 db2sqljjdbc.properties 配置信息

DB2SQLJSSID=PE11

DB2SQLJPLANNAME=DSNJDBC

DB2SQLJ_TRACE_FILENAME=/tmp/javatrc.db2tdbg

DB2CURSORHOLD=YES

DB2SQLJMULTICONTEXT=YES

DB2SQLJATTACHTYPE=RRSAF

DB2SQLJDBRMLIB=DSNPE01.V8.DBRMLIB.DATA

DB2CURSORHOLD=YES

db2.connpool.max.size=100

db2.jdbc.profile.pathname=/usr/lpp/db2810/classes/DSNJDBC_JDBCProfile.ser

最后，用户可以通过配置客户端来连接 DB2 子系统使用存储过程提供的功能，在连接前，需要保证在服务器端 DDF（Distributed Data Facility）已经配置并正常启动。

在DB2命令窗口里输入以下命令：

清单10. DB2 分布式连接配置命令

db2 catalog tcpip node <node>remote <ip address>server <port>

db2 catalog dcs database <dbname>as <location>

db2 catalog database <dbname>as <DB alias>

at node <node>authentication DCS

以上信息可以通过在 MVS console 输入以下命令–DISPLAY DDF 命令来确定 DB2 子系统的 DB2 location，IP 地址和 TCP 端口号等连接信息。在正确配置好客户端后，我们可以通过以下命令来连接 DB2 子系统：

清单11. DB2分布式连接命令

db2 connect to <DB alias>user <user>using