主機DB2 9存儲過程的規劃和實施技巧講解

主機DB2 9存儲過程的規劃和實施技巧講解

本文主要介紹主機 DB2 上的存儲過程的規劃和實施經驗，幫助用戶了解主機 DB2 上的存儲過程的基本特點及其如何進行分類實施。

隨著主機 DB2 技術的發展，DB2 產品的功能越來越豐富，以及 DB2 產品增強了對其分布式訪問功能，所以正確配置 DB2 產品提供的存儲過程，就越來越重要。對現在的主機數據庫用戶來說，DB2 9 for z/OS 下的存儲過程已經從一個可選項逐漸演變成了在數據庫應用開發考慮的重要因素。在主機數據庫存儲過程的應用方面，除了用戶可以根據需要開發存儲過程外。主機數據庫產品也提供了功能豐富的各類存儲過程供用戶使用。本篇就是為了幫助主機系統和開發人員更好的規劃和實施基於主機數據庫（DB2 for z/OS）的存儲過程。

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 remote server

db2 catalog dcs database as

db2 catalog database as

at node authentication DCS

以上信息可以通過在 MVS console 輸入以下命令–DISPLAY DDF 命令來確定 DB2 子系統的 DB2 location，IP 地址和 TCP 端口號等連接信息。在正確配置好客戶端後，我們可以通過以下命令來連接 DB2 子系統：

清單11. DB2分布式連接命令

db2 connect to user using