DB2存储过程开发最佳实践

下面示例代码是返回临时表的存储过程(get_temp_table)：

清单9：返回临时表的存储过程

----------------------------------------------------- -- TEMPORARY TABLE & CURSOR declaration ----------------------------------------------------- DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP ( ID INTEGER, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; P2: BEGIN DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM SESSION.TEMP FOR READ ONLY; INSERT INTO SESSION.TEMP VALUES(1,piName); OPEN R_CRSR; END P2;

存储过程中声明了有两个字段的临时表TEMP,声明了一个游标R_CRSR返回临时表中所有记录，最后在临时表中插入两条记录。

图2：程序第一次执行的结果

可以从图中看出，运行结果是我们期望的。那么如果我们再运行一次，会有什么结果呢?下图是其运行结果：

图3：程序再次执行的结果

第二次执行的时候程序却出错了，这是因为在同一个连接中，临时表并没有被DROP掉，所以在第二次调用存储过程的时候就会出现临时表已经存在的错误。

另外一种情况，就是很多时候例如在websphere中通过JDBC连接数据库时使用了连接池的技术，这带来了一些效率的提升，同时在某些情况下也容易让人误解。客户应用程序中关闭了数据库连接，但是并不一定真正关闭了数据库连接，如果客户应用程序使用了临时表而数据库连接并没有关闭，那么临时表就不会被DROP。当连接池把这个连接分给另一个客户程序的时候，新的客户程序仍然可以使用旧的临时表，这不是我们希望的。如果想避免上述问题，可以在创建临时表时，加上WITH REPLACE;或者根据业务逻辑在合适的地方显示的DROP临时表。

下面是使用WITH REPLACE创建临时表的执行情况。

图4：使用WITH REPLACE创建临时表

可以看出在一个连接里面，多次调用存储过程get_temp_table，也不会出现问题。临时表在某些情况下也是需要避免使用的。大家知道临时表是存放在内存中的，如果一个临时表有上万或者十几万条记录，同时程序的并发数很大，那么在内存中建立的临时表耗费的资源就很庞大，此时数据库的性能会急剧下降，甚至会导致数据库崩溃。因此，大家在使用临时表的时候，需要考虑它对资源的耗费，避免盲目使用临时表。

最佳实践 6：寻找并rebind 非法的存储过程

存储过程会因为其涉及和引用的对象发生了改变而导致其非法(invalid)，例如：修改了表结构，导致引用该表的存储过程非法，或者重新编译一个存储过程，会使调用这个存储过程的父存储过程非法。此时我们需要对非法的存储过程重新编译(rebind)。但是，对非法的存储过程进行rebind的时候，需要确定其引用的对象是合法的，否则非法的存储过程也不能rebind成功。

这里我们介绍一下发现和rebind非法存储过程的方法。我们是通过判断SYSCAT.routines中VALID字段的值来查找非法存储过程的。下面是查找非法存储过程的一段代码：

清单10：查找非法存储过程

SELECT RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname , ' ( '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' )' 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;

获得的结果如下：

清单11：查找非法存储过程的结果

SPNAME ---------------------------------- TEST.DEMO_INFO_8 (TEST. P3550884)

可以使用下面的命令rebind它们

清单12：Rebind 非法存储过程语法

rebind package packagename resolve any@

Packagename就是查询结果中括号里的值。例如，如果rebind上面查出来的存储过程。我们只需要执行下面语句

清单13：Rebind 非法存储过程

rebind package TEST.P3550884 resolve any@

当然，如果此存储过程程序本身有问题，需要先修改存储过程代码后再进行编译。

类似的，通过下面的代码可以获得非法的视图。

清单14：获得非法的视图

SELECT RTRIM(viewschema) || '.' || RTRIM(viewname) AS viewname FROM SYSCAT.views WHERE valid = 'X' ORDER BY viewname;

结束语

本文介绍了我们在 DB2 存储过程开发中经常用到的一些技巧。同时这些技巧也是编写优秀存储过程的基本要求。本文介绍的一些技巧只是揭开了高效使用 DB2 的冰山一角。DB2 为我们提供了丰富和强大的功能。在使用 DB2 的时候，我们应当深入理解其原理，找出更多的最佳实践与大家分享。

参考资料

Red book: IBM DB2 UDB Command Reference Version 8

Red Book：IBM DB2 UDB Application Development Guide

本文由翔宇亭IT乐园（http://www.biye5u.com）提供，有什么意见或建议请留言评论。

上一页  [1] [2] [3]