DB2 存储过程中如何使用 Optimization Profile

核心提示： 清单 5.存储过程定义脚本CONNECTTOSAMPLE%CALLSYSPROC.SET_ROUTINE_OPTS('EXPLAINALL')%CREATEPROCEDUREGET_EMP_NUM(INDEPT_NOCHAR(50),OUTEMP_NUMINTEGER)LANGU

清单 5.存储过程定义脚本

　CONNECT　TO　SAMPLE%　
　
　CALL　SYSPROC.SET_ROUTINE_OPTS('EXPLAIN　ALL')%　
　
　CREATE　PROCEDURE　GET_EMP_NUM(　
　IN　DEPT_NO　CHAR(50),　
　OUT　EMP_NUM　INTEGER)　
　
　LANGUAGE　SQL　
　
　BEGIN　
　
　SELECT　COUNT(*)　INTO　EMP_NUM　
　FROM　DEPARTMENT,　EMPLOYEE　
　WHERE　DEPARTMENT.DEPTNO　=　EMPLOYEE.WORKDEPT　
　AND　DEPARTMENT.DEPTNAME　=　DEPT_NO;　
　
　END%　
　
　CONNECT　RESET%　

上述脚本中在存储过程定义之前首先调用了 SYSPROC.SET_ROUTINE_OPTS 存储过程来对存储过程的预编译和绑定选项进行设置。此外，也可以通过修改 DB2_SQLROUTINE_PREPOPTS 注册变量的值来实现同样的功能。如果调用了 SYSPROC.SET_ROUTINE_OPTS 存储过程，则会覆盖默认的 DB2_SQLROUTINE_PREPOPTS 注册变量。在这里我们传给 SYSPROC.SET_ROUTINE_OPTS 的参数为“EXPLAIN ALL”，意思是在创建存储过程的时候，生成其中 SQL 语句的执行计划并将其保存在 Explain 表中。

将上述脚本保存在文件 create_procedure.ddl 中，然后执行以下命名完成存储过程的创建的工作。注意，在实际创建存储过程之前，我们先调用 runstats命令对该存储过程涉及到的两张表进行了统计操作，以便 DB2 能够根据这些统计信息生成更加有效的执行计划。

清单 6.创建存储过程命令脚本

　db2　connect　to　sample　
　db2　'runstats　on　table　db2inst1.department　and　indexes　all'　
　db2　'runstats　on　table　db2inst1.employee　and　indexes　all'　
　db2　connect　to　reset　
　db2　-td%　-vf　create_procedure.ddl