(转)创新性应用-使用脚本加速DB2存储过程的开发-常红平

在进行DB2存储过程开发时，我们可以利用很多DB2自带的工具，例如开发中心，控制中心等。但有时使用脚本能带给开发人员更大的灵活性和更高的工作效率。
在开始开发一个新的或修改一个已存在的存储过程时，我们通常会进行下面一些准备工作：
1.         查看该存储过程会用到的表的表结构、字段类型、相关索引和示例数据。
2.         查看相关的存储过程或用户自定义函数（UDF）的定义。
3.         查找无效的存储过程并生成绑定语句。
4.         如某个表发生了改变，查看依赖于该表的所有视图、存储过程和用户自定义函数（UDF）
虽然上述信息都可以通过DB2提供的开发工具和管理工具获得,但是通过脚本可以更快速的获得所需信息,并且可以重复执行。
使用脚本完成上述任务的关键是理解和使用DB2的系统表。我们先简单回顾一下有关的DB2的系统表和视图:
1.         syscat.routines：存储所有的存储过程和用户自定义函数（UDF）信息。其中routinename字段为存储过程或用户自定义函数（UDF）的名称，routinetype字段表示该记录表示的是存储过程（P）还是用户自定义函数（F），lib_id字段为存储过程编译后生成的包序列号，origin字段表示该存储过程或用户自定义函数的出处（Q表示是由SQL组成的，E表示用户定义的且是外部的），valid字段表示该存储过程或用户自定义函数是否有效，如果origin字段不是Q的话，该域为空。
2.         syscat.packages：存储所有被绑定的程序包。其中pkgname表示包名称，valid字段表示该包是否合法。
3.         syscat.packagedep：存储关于程序包的依赖关系。其中pkgname字段表示程序包名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
4.         syscat.routinedep：存储关于程序（routine）的依赖关系。其中routinename字段表示程序名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
5.         syscat.viewdep：存储了关于视图的依赖关系。其中viewname字段表示视图名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
回顾并了解了上述系统表和视图，我们就可以很容易的创建脚本以完成前面提到的开发存储过程所要做的准备工作。
1．  查看该表结构、字段类型、相关索引和示例数据
虽然我们可以查询sysibm.systables表获得表结构，但有一种更简便的方法来获取表结构，即使用db2look工具。该工具可以生成创建该表和相关的索引的DDL。如我们要获取指定数据库中指定表的结构和前20条数据作为参考，可编写脚本viewtbl.cmd如下，传入参数分别为数据库名和表名。
@echo ------------------ DDL of table %2 and related index(ex) ------------------
@db2look -d %1 -t %2 -e
@echo ------------------ fisrt 20 rows in table %2 ------------------
@db2 select * from %2 fetch first 20 rows only
2．查看已存在的存储过程和用户自定义函数（UDF）的定义，将结果存入文件并自动打开结果文件。
可以从syscat.routines表中做简单查询实现脚本viewrtn.cmd。
@db2 SELECT text FROM SYSCAT.ROUTINES WHERE ROUTINENAME=upper('%1') > %1.sql
@start %1.sql
3．查看所有无效的存储过程并生成绑定语句
删除存储过程引用的表会引起该存储过程无效。无效存储过程可以采用查询syscat.routines和syscat.packages的方法获得：
SELECT
    RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname ,
    RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AS pkgname
FROM
    SYSCAT.routines r
WHERE
    r.routinetype = 'P'
    AND (
        (r.origin = 'Q' AND r.valid != 'Y')
        OR EXISTS (
            SELECT 1 FROM syscat.packages
            WHERE pkgschema = r.routineschema
            AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)
            AND valid !='Y'
        )
    )
ORDER BY spname
注意要同时查询syscat.routines和syscat.packages表，因为在程序包无效时syscat.routines中的valid值仍可能为Y。
如果要自动生成重新绑定语句，只需将上述SQL改写并存成invalidSP.cmd：
@echo off
db2 "SELECT '@db2 rebind package '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' resolve any' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS (SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y') )" >rebindsp.bat
4．查看某个表所依赖的视图、存储过程和用户自定义函数（UDF）
使用上述系统视图，我们很容易编写出脚本：
@echo off
echo --- dependent SPs ---
db2 "select proc.procschema, proc.procname from syscat.routines r, syscat.procedures proc, syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1') and r.specificname=proc.specificname AND pdep.pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)"
 
echo --- dependent UDF ---
db2 select routineschema, routinename from syscat.routinedep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
 
echo --- dependent view ---
db2 select viewschema, viewname from syscat.viewdep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
行业借鉴经验：提高DB2存储过程性能和健壮性的3个最佳实践
最佳实践1：在创建存储过程语句中提供必要的参数
创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供这些参数可以提高执行效率。下面是一些常用的参数:
l         容许SQL（allowed－SQL）
容许SQL（allowed－SQL）子句的值指定了存储过程是否会使用SQL语句，如果使用，其类型如何。它的可能值如下所示：
  • NO SQL： 表示存储过程不能够执行任何SQL语句。
  • CONTAINS SQL： 表示存储过程可以执行SQL语句，但不会读取SQL数据，也不会修改SQL数据。
  • READS SQL DATA： 表示在存储过程中包含不会修改SQL数据的SQL语句。也就是说该储存过程只从数据库中读取数据。
  • MODIFIES SQL DATA： 表示存储过程可以执行任何SQL语句。即可以对数据库中的数据进行增加、删除和修改。
如果没有明确声明allowed-SQL，其默认值是MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中NO SQL效率最好，MODIFIES SQL DATA最差。如果存储过程只是读取数据，但是因为没有声明allowed-SQL类型，它会被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其allowed-SQL类型。
l         返回结果集个数（DYNAMIC RESULT SETS n）
存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：
  • 在CREATE PROCEDURE 语句的DYNAMIC RESULT SETS子句中声明存储过程将要返回的结果集的数量。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。
  • 使用WITH RETURN子句，在存储过程体中声明游标。
  • 为结果集打开游标。当存储过程返回的时候，保持游标打开。
在创建存储过程时指定返回结果集的个数可以帮助程序员验证存储过程是否返回了所期待数量的结果集，提高了程序的完整性。
最佳实践2：对输入参数进行必要的的检查和预处理
无论使用哪种编程语言，对输入参数的判断都是必须的。正确的参数验证是保证程序良好运行的前提。同样的，在DB2存储过程中对输入参数的验证和处理也是很重要的。正确的验证和预处理操作包括：
  • 如果输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。
  • 根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，NULL与空字符串或0的转换等。
在DB2储存过程开发中，如需要遇到对空(NULL)进行初始化，我们可以使用COALESCE函数。该函数返回第一个非NULL的参数。例如，COALESCE(piName,'')，如果变量piName为NULL，那么函数会返回''，否则就会返回piName本身的值。因此，可以用下面的代码检查piName是否非NULL并且非空字符串：
SET poGenStatus = 0;                                                   
   SET piName   =RTRIM(COALESCE(piName, ''));
    IF (piName ='')  
    THEN                             
        SET poGenStatus = 34100;     
        RETURN poGenStatus;          
    ENDIF;        
同理，使用COALESCE可以对任何类型的输入参数进行初始化或验证。下面是对参数初始化规则的一个总结：
1.       输入参数为字符类型，且允许为NULL的，如希望缺省值为空字符串，可以使用COALESCE(inputParameter, '')把NULL转换成空字符串;
2.       输入类型为整型，且允许为NULL的，如希望缺省值为0，可以使用COALESCE(inputParameter,0)，把NULL转换成0；
3.       输入参数为字符类型，且不允许是NULL或空字符串的，可以使用RTRIM(COALESCE(inputParameter, ''))把NULL转换成空字符串，然后验证函数返回值是否为空字符串；
4.       输入类型为整型，且不允许是NULL的，不需要使用COALESCE函数，直接使用IS NULL进行验证。
最佳实践3：异常（condition）处理
在存储过程执行的过程中，经常因为数据或者其他问题产生异常（condition）。根据业务逻辑，存储过程应该对异常进行相应处理或直接返回给调用者。此处暂且将condition译为异常以方便理解。实际上有些异常（condition）并非是由于错误引起的。
当存储过程中的语句返回的SQLSTATE值超过00000的时候，就表明在存储过程中产生了一个异常（condition），它表示出现了错误、数据没有找到或者出现了警告。为了处理存储过程中出现的异常，我们必须在存储过程体中声明异常处理器（condition handler），它可以决定存储过程怎样响应一个或者多个系统定义的异常或者自定义异常。
异常处理器类型(handler-type)有以下几种：
l         CONTINUE: 在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句。
l         EXIT: 在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。
l         UNDO: 在处理器操作执行之前，DB2会回滚存储过程中执行过的SQL操作。在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。
异常处理器可以处理基于特定SQLSTATE值的自定义异常，或者处理系统预定义异常。系统预定义的3种异常如下所示：
l         NOT FOUND: 标识导致SQLCODE值为＋100或者SQLSATE值为02000的异常。这个异常通常在SELECT没有返回行的时候出现。
l         SQLEXCEPTIOIN: 标识导致SQLCODE值为负的异常。
l         SQLWARNING: 标识导致警告异常或者导致正100以上的SQLCODE值的异常。
如果产生了NOT FOUND 或者SQLWARNING异常，并且没有为这个异常定义异常处理器，系统就会忽略这个异常，并且将控制流转向下一个语句。如果产生了SQLEXCEPTION异常，并且没有为这个异常定义异常处理器，那么存储过程就会失败，系统会将控制流返回调用者。因此如果开发人员想改变这种异常处理流程，必须自定义异常处理器。例如，希望在出现SQLWARNING异常时也终止存储过程，并将名为stmt的变量设为“ABORTED”，可以定义下面语句定义异常处理器：
DECLAREEXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
        SET stmt ='ABORTED';
如果预定义异常集仍不能满足需求，可以为特定的SQLSTATE值声明自定义异常，然后再为这个异常定制异常声明处理器。为特定的SQLSTATE值声明自定义异常的语法如下：
DECLARE condition-name CONDITION FOR SQLSATE ‘mysqlstate’
定义了异常和异常处理器后，在存储过程执行的任何都使用SIGNAL condition-name语句触发这种自定义类型的异常。
异常处理器可以由单独的存储过程语句定义，也可以使用复合语句定义。注意在执行复合语句的时候，SQLSATE和SQLCODE的值会被改变，如果需要保留异常前的SQLSATE和SQLCODE值，就需要在复合语句中的第一个语句把SQLSATE和SQLCODE赋予本地变量或参数。下面是一个例子：
    DECLARECONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOTFOUND
    BEGINNOT ATOMIC                 
      -- Capture SQLCODE & SQLSTATE to local variables 
      SELECT SQLCODE, SQLSTATE      
      INTO    hSqlcode, hSqlstate    
      FROM    SYSIBM.SYSDUMMY1;      
      -- your handler statements below
      ……
    END; 
应用难点技巧：使用Case让你的SQL语句有条件的执行
我们在编写SQL语句时，常常遇到希望SQL能够按条件执行的情况。这里的条件不是指Where子句中的条件，而是指让DB2根据条件执行SQL的语句块。大多数情况下我们可以使用case来实现。
例如，我们希望从员工表中查出员工的工资情况，如果小于20000，则标志为low，20000到50000间为middle，否则为high。一般大家会想到先取出工资数据然后在Java代码中做判断，但我们也可以在SQL中完成上述操作。如下例：
select empno, sex, salary,
case
 when salary < 20000 then 'low'
 when salary >=20000 and salary <50000 then 'middle'
 else 'high'
end as salaryclass
from employee
类似的，如果想在SQL语句中把性别翻译成文字，也可以用case实现，注意这两条SQL语句使用了不同的case表达式写法：
select empno,
case sex
 when 'M' then 'male'
 when 'F' then 'female'
 else 'invalid'
end,
salary
from employee
Case不但能够在Select子句中使用，在From子句和Where子句中同样可以使用。下面是在Where子句中使用的一个例子：
select empno, sex, age
from employee
where
case sex
 when 'M' then 55
 when 'F' then 50
end > age
在From子句中使用的例子极为少见，下面是一个极端的例子。在合同表ctrct_list、客户表customer和订单表quote中都有合同号字段，但订单表中的合同号可能为空。要求查询出订单表订单id和合同号，如订单表中合同号为空的话，查出客户表中相应的合同号。
select distinct
    q.quote_id,
    case rtrim(coalesce(q.ctrct_num, ''))
        when '' then rtrim(coalesce(c.ctrct_num, ''))
        else rtrim(coalesce(q.ctrct_num, ''))
    end ctrct_num
from
    (quote q
    left outer join customer c
        on q.sold_to_cust_num = c.cust_num)
    inner join ctrct_list cl
        on cl.cust_num = q.sold_to_cust_num
        and cl.ctrct_num = (
                case rtrim(coalesce(q.ctrct_num, ''))
                when '' then c.ctrct_num
                else q.ctrct_num
                end
            )
有时让SQL语句有条件的执行也可以不使用case。下面是一个例子：
select *
from EMPLOYEE
WHERE
((job='MANAGER') AND vMgrFlag=1)
or
((job='DESIGNER' or job='ANALYST') AND vTechFlag =1)
or
((job='CLERK' or job='OPERATOR') AND vOfficeFlag=1)
此SQL可以要求根据标志位的不同选择出不同类型的雇员。各个标志位在执行SQL前应提前设置好。这种方法可以在某些情况下将动态SQL改写为静态SQL，因此在编写存储过程时非常实用。但要指出的是，DB2的查询优化器不可能将这种SQL也优化得非常高效，因此在数据量比较大时可能会带来性能问题。开发人员需要在编写完成后使用实际数据测试，必要的话进行性能优化。的b2 " BY sql,es
posted @ 2007-08-01 07:28  彷徨......  阅读(689)  评论(0)  编辑  收藏  举报