存储过程优化方法

SQL存储过程出错处理

当存储过程出错时，你希望在日志里看到

SQLCODE = 119

还是

SQL0119N An expression starting with "CALL_AREA_CD" specified in a SELECT

clause, HA VING clause, or ORDER BY clause is not specified in the GROUP BY

clause or it is in a SELECT clause, HA VING clause, or ORDER BY clause with a

column function and no GROUP BY clause is specified.

呢？

其实DB2提供的GET DIAGNOSTICS语句可以获得文字消息：

DECLARE vMsgText VARCHAR (256);

-- 在存储过程的Exit Handler里面获取SQL错误的文本消息

GET DIAGNOSTICS EXCEPTION 1 vMsgText = MESSAGE_TEXT;

-- 参考DB2文档《SQL Reference Volume 2》

有了MESSAGE_TEXT，存储过程的调试和查错就方便多了。

声明临时表需要注意

指定合理的分区键

建议声明临时表时显式地指定分区键。

很多时候，DB2缺省选择的分区键是非常糟糕的。

例子：PRO_TM_USR_CONSUME_MO

CVS:/DSS代码/21软件代码/05基线代码/ETL/存储过程/

版本V01.400.000，CVS版本1.8

代码第75行

测试结果：对该SESSION临时表指定与目标表TM_USR_CONSUME_MO一致的分区键

后（只作此改动，其它代码不修改），原来跑6个小时的存储过程只需要不到2

个小时。

ETL.PRO_TM_CALLV AL_USR_MO_JF

尽量使关联表之间的分区键一致，并且关联条件包含所有的分区列。

目的是减少昂贵的分区间数据通信。

尽量使源表、目标表的分区键一致。

目的是减少昂贵的分区间数据通信。

定义必要的约束

创建约束的好处？

顾名思义，约束的作用就是对数据进行约束，DB2不允许把违反约束规则的数据插入到数据库。约束可以帮助我们尽早发现SQL中的逻辑错误。大部分程序缺陷是通过运行时的错误发现的，如果不定义任何约束，等于放弃了很多检测错误的机会。例：唯一约束可以避免重复运行同一个INSERT语句带来的恶果。

唯一约束、主键约束都是通过索引实施的，实际上它们无异于一个唯一索引。最常用的约束有

1.非空（NOT NULL）

2.唯一（UNIQUE）

ALTER TABLE ADD UNIQUE (columns list);

3.主键（PRIMARY KEY）

创建必要的索引

当需要从大量数据中选出少量数据时，我们需要索引。

提高SQL性能

RUNSTATS

执行RUNSTATS命令时要加上“WITH DISTRIBUTION”选项，统计各字段的取值分布情况，这也对Access Plan的选择有影响。

CREATE INDEX KF2.IDX_CNSM_GZ ON KF2.TW_USR_CNSM_RNK_GZ (CURRMO_AMT_FEE);

SELECT * FROM KF2.TW_USR_CNSM_RNK_GZ WHERE CURRMO_AMT_FEE = 100;

SELECT * FROM KF2.TW_USR_CNSM_RNK_GZ WHERE CURRMO_AMT_FEE <> 100;

建议RUNSTATS选项：

RUNSTATS ON WITH DISTRIBUTION AND INDEXES ALL

选择不存在于另一表的数据

看看以下三种写法

写法1：SELECT ... FROM A

WHERE A.key NOT IN (SELECT key FROM B);

写法2：SELECT ... FROM A

LEFT JOIN B ON A.key = B.key

WHERE B.key is null;

写法3：SELECT ... FROM A

WHERE NOT EXISTS

(SELECT 'x' FROM B WHERE A.key = B.key);

写法1采用NOT IN的写法。很不幸DB2对于NOT IN通常采用TBSCAN（表扫描），这是效率很差的写法。最佳写法是第三种写法，如果B.key上有索引，它可以不用fetch B表的数据就可以完成查询。第二种写法采用对外表B的is null判断进行过滤，效率稍差。

注：事实上，在DB2优化器的作用下，第二种写法与第三种写法的存取方案相关无几，只是第二种写法比第三种写法多了一步filter操作。

建议使用第3种写法，己使用第2种写法的代码也不必修改，因为其效率与第3种写法差不多。

例：

SELECT A.*

from EDS.TW_BCUST_200409 A LEFT OUTER JOIN KF2.TW_BCUST B ON

A.TM_INTRVL_CD =

B.TM_INTRVL_CD and A.CUST_ID =B.CUST_ID and

R_ID =

R_ID and A.BCUST_EFF_MO =B.BCUST_EFF_MO

WHERE B.TM_INTRVL_CD is null

SELECT *

from EDS.TW_BCUST_200409 A

where NOT EXISTS

(select 'x'

from KF2.TW_BCUST B

WHERE A.TM_INTRVL_CD =B.TM_INTRVL_CD and A.CUST_ID =B.CUST_ID

and R_ID =R_ID and A.BCUST_EFF_MO =

B.BCUST_EFF_MO)

这两种写法对应的存取方案：

RETURN RETURN

( 1) ( 1)

| |

BTQ BTQ

( 2) ( 2)

| |

FILTER HSJOIN

( 3) ( 3)

| / \

HSJOIN TBSCAN TBSCAN

( 4) ( 4) ( 5)

/ \ | |

TBSCAN TBSCAN Table: Table:

( 5) ( 6) EDS KF2

| | TW_BCUST_200409 TW_BCUST

Table: Table:

EDS KF2