一，编写存储过程。

[db2inst1@db2lab ~]$ cattest.sql

create procedure sales_status

(in quota integer)

dynamic result sets 2

languagesql

begin

declare SQLSTATE char(5);

declarers cursor with return for

select * from t1;

openrs;

end

@

二，建立存储过程

[db2inst1@db2lab ~]$ db2 -td@ -f 1.sql

DB20000I The SQL command completed successfully.

三，执行存储过程

[db2inst1@db2lab ~]$ db2 "call sales_status(1)"

Result set 1

--------------

ID

-------

1.

1 record(s) selected.

Return Status = 0

四，利用表函数MON_GET_PKG_CACHE_STMT抓取static的信息，获取PACKAGE_NAME及SQL语句

[db2inst1@db2lab ~]$ db2 "select

PACKAGE_NAME,SECTION_NUMBER,EXECUTABLE_ID

from TABLE(MON_GET_PKG_CACHE_STMT ( 'S', NULL, NULL, -1)) as T"

PACKAGE_NAME SECTION_NUMBER EXECUTABLE_ID -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------

P0462483 1

x'0100000000000000BE01000000000000010xxxxxxxxxxxx0415004624839232' 1 record(s) selected.

五，利用EXECUTABLE_ID，获取SQL语句

[db2inst1@db2lab ~]$ db2 "SELECT STMT_TEXT FROM

TABLE(MON_GET_PKG_CACHE_STMT

> (null,

x'0100000000000000BE01000000000000010xxxxxxxxxxxx0415004624839232', null, -2))"

?

?

?

? STMT_TEXT ------------------------------------------------------- DECLARE RS cursor with return for select * from T1 where ID = :HV00008 :HI00008 1 record(s) selected. 六，查看package_name信息，valid列信息需要重点关注，信息中心解释如下： N = Needs rebinding V = Validate at run time X = Package is inoperative because some function instance on which it depends has been dropped; explicit rebind is needed Y = Valid

[db2inst1@db2lab ~]$ db2 list packages for all |grep -i P0462483

Bound Total Isolation

Package Schema Version by sections Valid Format level Blocking

----------- --------- ----------- --------- ------------- ------- -------- --------- --------

P0462483 DB2INST1 DB2INST1 1

Y 0 CS U

七，获取该package_name的执行计划信息

[db2inst1@db2lab ~]$ db2expln -d pos -g -c db2inst1 -p P0462483 -s 0 -t

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008 Licensed Material - Program Property of IBM

IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** PACKAGE ***************************************

Package Name = "DB2INST1"."P0462483" Version =

Prep Date = 2014/04/15

Prep Time = 00:46:24

Bind Timestamp = 2014-04-15-00.46.24.839232

Isolation Level = Cursor Stability

Blocking = Block Unambiguous Cursors

Query Optimization Class = 5

Partition Parallel = No

Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "DB2INST1"

-------------------- SECTION ---------------------------------------

Section = 1

Statement:

DECLARE RS cursor

with return

for

select *

from T1

where ID =:HV00008 :HI00008

Section Code Page = 1208

Estimated Cost = 7.569436

Estimated Cardinality = 1.000000

Access Table Name = DB2INST1.T1 ID = 2,4

| #Columns = 1

| Skip Inserted Rows

| Evaluate Block/Data Predicates Before Locking Committed Row

| May participate in Scan Sharing structures

| Scan may start anywhere and wrap, for completion

| Fast scan, for purposes of scan sharing management

| Scan can be throttled in scan sharing management

| Relation Scan

| | Prefetch: Eligible

| Lock Intents

| | Table: Intent Share

| | Row : Next Key Share

| Sargable Predicate(s)

| | #Predicates = 1

| | Return Data to Application

| | | #Columns = 1

Return Data Completion

End of section

Optimizer Plan:

Rows

Operator

(ID)

Cost

1

RETURN

( 1)

7.56944

|

1

TBSCAN

( 2)

7.56944

|

1

Table:

DB2INST1

T1

总结：DB2的执行计划变化较多，不经常runstats和rebind的时候，有可能本地执行计划很好，但在实际生产环境上执行计划较差，这需要DBA能抓取实时SQL执行计划，

静态sql通过上述方式抓取，动态sql需要借助db2expln的cache选项抓取，来分析sql的消耗情况

Dynamic Statement Options:

-cache <anchID>,<stmtUID>,<envID>,<varID>

= Retrieve the statement identified by the given IDs from the dynamic SQL cache. (The IDs can be

obtained by running db2pd with the -dynamic

option.

第二篇：DB2存储过程--基础详解

DB2存储过程-基础详解

2010-12-20 来源：网络

简介

DB2 SQL Procedural Language（SQL PL）是 SQL Persistent Stored Module 语言标准的一个子集。该标准结合了 SQL 访问数据的方便性和编程语言的流控制。通过 SQL PL 当前的语句集合和语言特性，可以用 SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。

SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。

变量声明

SQL 过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。 清单 1 中的图演示了变量声明的语法：

清单 1. 变量声明的语法

.-,-----------------.

V |

|--DECLARE----SQL-variable-name-+------------------------------->

.-DEFAULT NULL------.

>--+-data-type--+-------------------+-+-------------------------|

| '-DEFAULT--constant-' |

SQL-variable-name 定义本地变量的名称。该名称不能与其他变量或参数名称相同，也不能与列名相同。

图 1 显示了受支持的 DB2 数据类型：

DEFAULT 值 – 如果没有指定，在声明时将赋值为 NULL。

下面是变量声明的一些例子：

?

?

?

? DECLARE v_salary DEC(9,2) DEFAULT 0.0; DECLARE v_status char(3) DEFAULT ‘YES’; DECLARE v_descrition VARCHAR(80); DECLARE v1, v2 INT DEFAULT 0;

请注意，从 DB2 version 9.5 开始才支持在一个 DECLARE 语句中声明多个相同数据类型的变量。

数组数据类型

SQL 过程从 9.5 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。

DB2 支持以下创建数组数据类型的语法：

清单 2. 创建数组数据类型的语法

Sql代码

1.

2.

3.

4.

5. >>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[----------> .-2147483647-------. >--+------------------+--]------------------------------------->< '-integer-constant-'

数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。

下面是数组类型的例子：

Sql代码

1.

2.

3. CREATE TYPE numbers as INTEGER ARRAY[100]; CREATE TYPE names as VARCHAR(30) ARRAY[]; CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];

请注意，整数 “constant” 指定数组的最大基数，它是可选的。数组元素可以通过 ARRAY-VARIABLE(subindex) 来引用，其中 subindex 必须介于 1 到数组的基数之间。

现在可以在 SQL 过程中使用这个数据类型：

清单 3. 在过程中使用数组数据类型

Sql代码

1.

2.

3.

4.

5.

6.

7.

8. CREATE PROCEDURE PROC_VARRAY_test (out mynames names) BEGIN DECLARE v_pnumb numbers; SET v_pnumb = ARRAY[1,2,3,5,7,11]; SET mynames(1) =’MARINA’; … END

DB2 支持一些操作数组的方法。例如，函数 CARDINALITY(myarray) 返回一个数组中元素的个数。

赋值

SQL PL 提供了 SET 语句来为变量和数组元素赋值。

下面是一个 SET 语句的简化的语法：

SET variable_name = value/expression/NULL;

这个变量名可以是一个本地变量、全局变量或数组元素的名称。

下面是一些例子：

如果关闭该游标，则结果集将不能返回给调用者应用程序。

清单 10 演示了一个游标的声明，该游标从一个过程中返回一个结果集：

清单 10. 返回一个结果集的游标的声明

CREATE PROCEDURE emp_from_dept()

DYNAMIC RESULT SETS 1

P1: BEGIN

DECLARE c_emp_dept CURSOR WITH RETURN

FOR SELECT empno, lastname, job, salary, comm.

FROM employee

WHERE workdept = ‘E21’;

OPEN c_emp_dept;

END P1

游标处理

为了在一个过程中处理一个游标的结果，需要做以下事情：

1.

2.

3.

4. 在存储过程块的开头部分 DECLARE 游标。 打开该游标。 将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。 关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。

条件语句

SQL PL 中支持两种类型的条件语句 — IF 语句和 CASE 语句。

IF 语句

通过 IF 语句可以根据一个条件的状态来实现逻辑的分支。IF 语句支持使用可选的 ELSEIF 子句和默认的 ELSE 子句。END IF 子句是必需的，它用于表明 IF 语句的结束。

清单 11 展示了一个示例 IF 语句。

清单 11. IF 语句示例

IF years_of_serv > 30 THEN

SET gl_sal_increase = 15000;

ELSEIF years_of_serv > 20 THEN

SET gl_sal_increase = 12000;

ELSE

SET gl_sal_increase = 10000;

END IF;

CASE 语句

SQL PL 支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支：

?

? simple CASE 语句用于根据一个字面值进入某个逻辑。 searched CASE 语句用于根据一个表达式的值进入某个逻辑。

清单 12 显示了使用 searched CASE 语句的一个存储过程的例子。

清单 12. 使用 searched CASE 语句的存储过程

CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6)) BEGIN

DECLARE years_of_serv INT DEFAULT 0;

DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0;

SELECT YEAR(CURRENT DATE) - YEAR(hiredate) INTO years_of_serv

FROM empl1

WHERE empno = empid;

CASE

WHEN years_of_serv > 30 THEN

SET v_incr_rate = 0.08;

WHEN years_of_serv > 20 THEN

SET v_incr_rate = 0.07;

WHEN years_of_serv > 10 THEN

SET v_incr_rate = 0.05;

ELSE

SET v_incr_rate = 0.04;

END CASE;

UPDATE empl1

SET salary = salary+salary*v_incr_rate WHERE empno = empid;

END

迭代语句

SQL PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环： ? LOOP 循环 -- 简单的循环

o

o

o

o L1: LOOP SQL statements; LEAVE L1; END LOOP L1;

? WHILE 循环 -- 进入前检查条件

o

o

o

o

? WHILE condition DO SQL statements END WHILE; REPEAT 循环 -- 退出前检查条件

o

o

o

o

? REPEAT SQL statements; UNTIL condition END REPEAT; FOR 循环 -- 结果集上的隐式循环

o

o

o

o

o FOR loop_name AS SELECT … FROM DO SQL statements; END FOR;

请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。

为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname varchar(15)、hiredate date 和 birthdate date。

请注意，使用一个简单的 SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。

清单 13. 简单的循环例子

CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER) Ll: BEGIN

DECLARE v_at_end , v_counter INTEGER DEFAULT 0;

DECLARE v_lastname VARCHAR(15);

DECLARE v_birthd, v_hired DATE;

DECLARE c1 CURSOR

FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;

OPEN c1;

FETCH_LOOP: LOOP

FETCH c1 INTO v_lastname, v_hired, v_birthd;

IF v_at_end <> 0 THEN -- loop until last row of the cursor LEAVE FETCH_LOOP;

END IF;

|--DECLARE--+-CONTINUE-+--HANDLER--FOR--------------------------> +-EXIT-----+

'-UNDO-----'

>--+-specific-condition-value

-+--| SQL-procedure-statement |----|

'-general-condition-value

--'

WHERE specific-condition-value

.-,----------------------------------------.

V .-VALUE-. | |----+-SQLSTATE--+-------+--string-constant

-+-+-----------------|

'-condition-name

-----------------------'

下面是演示它如何工作的一些例子。在下面的过程中，如果 UPDATE 语句失败，则控制被转移到 EXIT 处理程序。结果，该过程被终止，但是它的输出参数包含 SQLCODE 和 SQLSTATE 的值。

清单 18. 返回 SQLCODE 和 SQLSTATE 的处理程序的例子

CREATE PROCEDURE simple_error

(IN new_job CHAR(8), IN p_empno CHAR(6),

OUT p_state_out CHAR(5),OUT p_code_out INT)

SPECIFIC simple_error1

BEGIN

DECLARE SQLCODE INT DEFAULT 0;

DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SELECT SQLSTATE, SQLCODE

INTO p_sqlstate_out, p_sqlcode_out

FROM SYSIBM.SYSDUMMY1;

UPDATE EMPLOYEE

SET job = new_job

WHERE empno = p_empno;

END

请注意，SQLCODE 和 SQLSTATE 应该被显式地声明为本地变量。

清单 19 演示了一个场景，在此场景中，当一个给定的错误发生时，不是退出过程，而是继续某个动作。为了理解这个例子，请注意表 TAB1 被定义为（col1 int, col2 char(5)），在默认情况下，如果发生值溢出，DB2 不是截短一个值，而是产生 SQLSTATE ‘22001’。

清单 19. 具有 CONTINUE 处理程序的过程

CREATE PROCEDURE

proc1 (IN num int,

IN new_status

varchar(10))

P1: BEGIN

DECLARE

SQLCODE INTEGER

default 0;

DECLARE

SQLSTATE CHAR(5)

default ‘ ‘;

DECLARE

v_trunc INTEGER

default 0;

DECLARE

overflow CONDITION

FOR SQLSTATE

'22001';

DECLARE

CONTINUE HANDLER

FOR overflow

BEGIN

INSERT

INTO tab1 VALUES

(num, substr

(new_sataus,1,5));

SET

v_trunc = 2;

END;

INSERT INTO tab1

VALUES(num,

new_status);

RETURN v_trunc;

END P1

如果以 ‘Too many’ 作为输入参数 new_status 的值调用这个过程，那么在 INSERT 语句执行期间会产生 SQLSTATE ‘22001’，控制被转移到 CONDITION HANDLER。结果，v_trunc 指示符将被设置为 2，新行将被插入到 TAB1 表中，插入时对 COL2 列使用了截短后的值，该过程最终成功完成。

强制发出异常 -- SIGNAL SQLSTATE

SQL PL 支持发出一个错误或警告条件。这导致一个具有指定 SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。清单 20 显示了 SIGNAL 语句的语法。

清单 20. SIGNAL 语句的语法

>>-SIGNAL------------------------------------------------------->

.-VALUE-. >--+-SQLSTATE--+-------+--+-sqlstate-string-constant

-+-+-------->

| '-variable-name

------------' |

'-condition-name

------------------------------------'

>--+------------------------+---------------------------------->< '|--+-SET MESSAGE_TEXT-- = --diagnostic-string-expression

-+------|

可以以包含 5 个字符的字符串常量的形式发出一个用户定义的 SQLSTATE。它必须以数字 7、8 或 9 或者字母 I 到 Z 开始。还可以发出一个特定的条件名称，但是必须在包含 SIGNAL 语句的复合语句中声明它，如下面的清单所示。

DECLARE condition overflow for SQLSTATE ‘22001’;

….

SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;

清单 21 演示了一个 SIGNAL 语句的使用。

清单 21. SIGNAL 语句的使用

CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10)) P1: BEGIN

DECLARE SQLCODE INTEGER default 0;

DECLARE SQLSTATE CHAR(5) default '';

IF length (new_status) > 5 THEN

SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG';

END IF; INSERT INTO TAB1 VALUES (num, new_status);

END P1

在本教程中，您学习了用于编写过程、用户定义函数和触发器的 SQL Procedural Language。您学习了 SQL Procedure Language 的所有基本要素，包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。您还学习了如何使用错误处理和结果集。这使您能够构建可集成到数据库应用程序中的定制的、复杂的业务逻辑。