DB2 存储过程开发最佳实践

DB2 9.5 SQL Procedure Developer第1 部分: SQL Procedural Language 入门变量声明SQL 过程允许使用本地变量赋予和获取SQL 值，以支持所有SQL 逻辑。

在SQL 过程中，在代码中使用本地变量之前要先进行声明。

清单1 中的图演示了变量声明的语法：SQL-variable-name定义本地变量的名称。

该名称不能与其他变量或参数名称相同，也不能与列名相同。

图1 显示了受支持的DB2 数据类型：图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 支持以下创建数组数据类型的语法：数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。

LONG VARCHAR、LONG VARGRPAHIC、XML 和用户定义类型不能作为数组元素的数据类型。

请注意，整数―constant‖ 指定数组的最大基数，它是可选的。

数组元素可以通过ARRAY-VARIABLE(subindex) 来引用，其中subindex 必须介于1 到数组的基数之间。

现在可以在SQL 过程中使用这个数据类型：DB2 支持一些操作数组的方法。

DB2常用的命令1．启动数据库db2start2．停止数据库db2stop3．连接数据库db2 connect to database user db2admin using pwd(注：database为数据库名)4．读数据库管理程序配置db2 get dbm cfg5．写数据库管理程序配置db2 update dbm cfg using 参数名参数值6．读数据库的配置db2 connect to o_yd user db2 using pwddb2 get db cfg for o_yd7．写数据库的配置db2 connect to o_yd user db2 using pwddb2 update db cfg for o_yd using 参数名参数值8．关闭所有应用连接db2 force application alldb2 force application ID1,ID2,,,Idn MODE ASYNC(db2 list application for db o_yd show detail)9．备份数据库db2 force application alldb2 backup db o_yd to d: (db2 initialize tape on \\.\tape0) (db2 rewind tape on \\.\tape0) db2 backup db o_yd to \\.\tape010．恢复数据库 db2 restore db o_yd from d: to d: db2 restore db o_yd from \\.\tape0 to d:11．绑定存储过程 db2 connect to o_yd user db2 using pwd db2 bind c:\dfplus.bnd 拷贝存储过程到服务器上的C:\sqllib\function目录中12．整理表 db2 connect to o_yd user db2 using pwd db2 reorg table ydd db2 runstats on table ydd with distribution and indexes all 13．导出表数据db2 export to c:\dftz.txt of del select * from dftzdb2 export to c:\dftz.ixf of ixf select * from dftz14．导入表数据import from c:\123.txt of del insert into ylbx.czyxxdb2 import to c:\dftz.txt of del commitcount 5000 messages c:\dftz.msg insert into dftzdb2 import to c:\dftz.ixf of ixf commitcount 5000 messages c:\dftz.msg insert into dftzdb2 import to c:\dftz.ixf of ixf commitcount 5000 insert into dftzdb2 import to c:\dftz.ixf of ixf commitcount 5000 insert_update into dftzdb2 import to c:\dftz.ixf of ixf commitcount 5000 replace into dftzdb2 import to c:\dftz.ixf of ixf commitcount 5000 create into dftz (仅IXF)db2 import to c:\dftz.ixf of ixf commitcount 5000 replace_create into dftz (仅IXF)15．执行一个批处理文件db2 –tf 批处理文件名（文件中每一条命令用；结束）16．自动生成批处理文件 建文本文件：temp.sql select 'runstats on table DB2.' || tabname || ' with distribution and detailed indexes all;' from syscat.tables where tabschema='DB2' and type='T';db2 –tf temp.sql>runstats.sql17．自动生成建表（视图）语句在服务器上：C:\sqllib\misc目录中db2 connect to o_yd user db2 using pwddb2look –d o_yd –u db2 –e –p –c c:\o_yd.txt18．其他命令grant dbadm on database to user bb19select * from czyxx fetch first 1 rows only20db2look –d ylbx –u db2admin –w –asd –a –e –o a.txt21. 显示当前用户所有表list tables22.列出所有的系统表list tables for system23.查看表结构db2 describe select * from user.tablesDB2常用的SQL语句1、组合语句执行BEGIN ATOMIC表达式1 分号空格/回车表达式2 分号空格/回车END2、应该限制访问权限的表（应该撤销这些表PUBLIC SELECT访问权）SYSCAT.DBAUTHSYSCAT.TABAUTHSYSCAT.PACKAGEAUTHSYSCAT.INDEXAUTHSYSCAT.COLAUTHSYSCAT.PASSTHRUAUTHSYSCAT.SCHEMAAUTH比较有用的目录表SYSCAT.COLUMNS：包含每一行对应于表或视图中定义的列SYSCAT.INDEXCOLUSE：包含每一行包含的所有列SYSCAT.INDEXES：包含每一行对应于表或视图中定义的每个索引SYSCAT.TABLES：所创建每个表，视图，别名对应其中一行SYSCAT.VIEWS：所创建每个视图对应其中一行或几行通过索引保持数据唯一性：CREATE UNIQUE INDEX INDEXNAME ON TABLE (COLUMN)消除重复行：SELECT DISTINCT COLUMN FROM TABLE3、DB2关于时间的一些函数得到当前时间的年份、月份、天、小时等等：YEAR (current timestamp)MONTH (current timestamp)DAY (current timestamp)HOUR (current timestamp)MINUTE (current timestamp)SECOND (current timestamp)MICROSECOND (current timestamp)分别得到当时的日期和时间DATE (current timestamp)TIME (current timestamp)关于时间的一些计算：current date + 1 YEARcurrent date + 3 YEARS + 2 MONTHS + 15 DAYScurrent time + 5 HOURS - 3 MINUTES + 10 SECONDS计算两个日期之间有多少天：days (current date) - days (date(’1999-10-22′))得到去除毫秒的当前时间:CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS将时间转换成字符串：char(current date)char(current time)char(current date + 12 hours)将字符串转换成时间：TIMESTAMP (’2002-10-20-12.00.00.000000′)TIMESTAMP (’2002-10-20 12:00:00′)DATE (’2002-10-20′)DATE (’10/20/2002′)TIME (’12:00:00′)TIME (’12.00.00′)注意：在DB2的命令编辑器中可以输入SQL语句和DB2中的内部命令。

• 带输出存储过程示例
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = \'%\', @@SUM money OUTPUT AS SELECT \'Title Name\' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。 参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@SUM = variable 形式）。
• 什么是SQL语言？
SQL语言是应用程序和SQL Server数据库之间的主要 编程接口。使用SQL语言编写代码时，可用两种方法 存储和执行代码。
① 第一种是在客户端存储代码，并创建向数据库管理系统发送S QL命令（或SQL语句）并处理返回结果给应用程序； ② 第二种是将这些发送的SQL语句存储在数据库管理系统中，这 些存储在数据库管理系统中的SQL语句就是存储过程。
• 使用 WITH ENCRYPTION 选项
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程， 使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 sysco mments 表中获取关于该过程的信息。 GO USE pubs GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM authors GO EXEC sp_helptext encrypt_this

OLTP类应用系统之DB2数据库优化最佳实践本文所涉及的优化技巧均建立在您的数据库物理架构已经设计完成后而为了保证您的应用有最佳表现所必须做的后续优化工作。

下面这些有关数据库配置调优的技巧将使您在OLTP 环境中取得非常好的性能，同时使您能够避免显而易见的“陷阱”。

在配置参数中，数据库管理器配置参数需要重新启动数据库管理器，而为了使更改生效，大多数数据库配置参数都要求应用程序重新连接到数据库。

这里要优化的配置参数如下所示：一、配置缓冲池大小缓冲池命中率表明数据库管理器不需要从磁盘装入页（即该页已经在缓冲池中）就能处理页请求的时间百分比。

缓冲池的命中率越高，使用磁盘 I/O 的频率就越低。

按如下计算缓冲池命中率：db2pd -d dbname -bufferpools这个计算考虑了缓冲池高速缓存的所有页（索引和数据）。

理想情况下，该比率应当超过95%，并尽可能接近100%。

要提高缓冲池命中率，请尝试下面这些方法：1) 增加缓冲池大小#db2 "alter bufferpool bpname immediate size 40000"2) 考虑分配多个缓冲池，如果可能的话，为每个经常被访问的大表所属的表空间分配一个缓冲池，为一组小表分配一个缓冲池，然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。

#db2 "create bufferpool bpname SIZE 200 PAGESIZE 8K"如果已分配的内存不能帮助提高性能，那么请避免给缓冲池分配过多的内存。

应当根据取自测试环境的快照信息来决定缓冲池的大小。

二、配置日志缓冲区大小（LOGBUFSZ）LOGBUFSZ 是一个数据库配置参数。

它是用于日志缓冲区的参数。

它允许您指定数据库共享内存的大小以用作在将日志记录写到磁盘之前这些记录的缓冲区。

当下列事件之一发生时会将日志记录写到磁盘：1) 事务提交；2) 日志缓冲区已满；3) 其它某个内部数据库管理器事件发生时。

DB2 最佳实践: 性能调优和问题诊断最佳实践，第1 部分性能调优从配置和监控开始2009 年 3 月12 日本系列介绍了DB2 系统性能的最优方法，分两部分。

第一部分首先介绍为了达到良好性能，我们如何从软硬件配置方面来保障，紧接着讨论了在多种在操作和故障诊断的情况下，有助于我们了解系统性能的监控方法。

第 2 部分我们介绍在出现性能问题时如何逐步地、有条不紊地去处理它们。

内容提要大多数DB2 系统都经过了性能的演变。

首先，不论出于硬件还是软件的观点，该系统首先要能被配置。

在多数情况下，这将成为系统在实施后如何运行的基础。

其次，系统一经发布，勤勉的数据库管理员（DBA）将监控系统的性能，来监测任何可能的开发问题。

如果发现任何问题，我们就进入下一个阶段- 故障诊断阶段。

每个阶段都是基于上一阶段，如果没有适当的准备，我们极有可能需要处理比较困难的问题。

本文介绍了DB2 系统性能的最优方法。

我首先涉及到一些有助于我们确保良好软硬件性能的重要原则。

然后我们讨论多种在操作和故障诊断的情况下，有助于我们了解系统性能的监控方法。

最后，尽管我们做了最好的准备，性能问题仍然可以降临到我们身上，我们讨论如何逐步地处理它们，并有条不紊的进行。

回页首简介任何形式的性能问题都将严重影响并降低一个系统对你组织的价值、削弱业务能力、服务中断、以及增加管理开销。

所有这些都会提升总的拥有成本。

缺乏对系统配置的基本原则，监视和性能故障诊断可能导致不同程度的性能低下，并且降低对于组织的价值。

因此，在前期花些时间去考虑基本的配置指导方针和建立健全的系统监控这样的做法，将使你对处理许多可能出现的典型性能问题，有充分的准备。

并使数据服务器得以高性能运行，以提高投资回报率。

回页首第一步：从配置上实现性能良好像InfoSphere 平衡的仓库（BW）这类的DB2 部署类型，或者那些在SAP 系统之内的系统，配置都是高度确定的。

在BW 案例中，像CPU 个数、内存对 CPU 的比率、硬盘的个数和配置这样的硬件因素，以及在预先指定版本的基础上，详尽的测试，以确定最优配置。

db2 调用存储过程【原创版】目录1.DB2 简介2.存储过程简介3.DB2 调用存储过程的方法4.调用存储过程的实例5.存储过程的优点正文【1.DB2 简介】DB2 是一种关系型数据库管理系统，由 IBM 公司开发。

它广泛应用于企业级数据管理，提供高效、安全、可靠的数据存储和处理能力。

DB2 支持多种平台，如 Windows、Linux、Unix 等，可以满足不同用户的需求。

【2.存储过程简介】存储过程是一种预编译的 SQL 语句，它封装了一些逻辑操作，可以减少网络传输量，提高数据库性能。

存储过程可以用于实现复杂的业务逻辑，降低应用程序的开发难度。

【3.DB2 调用存储过程的方法】在 DB2 中，可以通过以下几种方法调用存储过程：（1）使用 CALL 语句：CALL<存储过程名>([参数 1, 参数 2,...])。

（2）使用 EXECUTE 语句：EXECUTE<存储过程名>([参数 1, 参数2,...])。

（3）使用动态 SQL：使用 PREPARE 和 EXECUTE 语句动态创建和执行存储过程。

【4.调用存储过程的实例】假设有一个名为“get_employee_list”的存储过程，它用于查询员工表中的所有记录。

可以使用以下代码调用该存储过程：```sqlCALL get_employee_list();```【5.存储过程的优点】存储过程具有以下优点：（1）封装性：存储过程可以将业务逻辑封装起来，降低应用程序的复杂度。

（2）性能：存储过程可以减少网络传输量，提高数据库性能。

（3）安全性：存储过程可以实现数据访问控制，保护数据的安全。

10.1 日常运维工具概述Runstats是run statistics的缩写，意思是收集统计信息，目的是为DB2优化器提供最佳路径选择；Reorg是重组的意思，目的是减少表和索引在物理存储上的碎片，提供性能；Reorgchk是重组前的检查Rebind是对一些包、存储过程或静态程序进行重新绑定。

几个工具的执行流程：首先通过Runstats收集表和索引的统计信息，然后执行Reorg重组，如果有必要则执行，然后再次收集统计信息。

最后，对于静态语句、存储过程等，执行Rebind绑定。

10.2 Runstats在系统运行一个查询的时候，优化器需要决定用某种方式来访问数据。

只有当DB2对表中的数据有一个大概的了解，才能知道每一步操作大约需要处理多少数据，返回多少行。

当优化器了解了这些信息后，就会根据一系列的运算，判定出各种访问途径所需要消耗的资源，然后从中选择一个消耗资源最少的方法。

最普通的Runstats就是统计表和索引中有多少行数据，有多少不同的数值。

Runstats命令使用DISTRIBUTION参数手机数据分布。

数据分布分为两种，一种叫做频率采样（Frequency）,一种叫做百分比采样（Quantile）。

当收集数据分布时，两种采样方式都会被收集。

其中频率采样是手页脚内容1机表中拥有相同数量最多的几行，比如10000行数据中9000行为10，然后500行为9，然后100行为8，剩下的部分平均分布。

如果我们制定Frequency为3的话，那么系统就会记录下来有9000行10,500行9，然后100行8，剩下的部分在估算时则假定平均分布。

而百分比采样则是将整个10000行数据分成相等大小的若干段，然后记录每一段的段首和段尾的数值，当需要查询一个数据段时（比如C1>10 AND C1<15）,就可以根据每一个数据段的启始数值加上段落的大小，估算出符合查询条件的记录数量。

理论上，数据分布收集的越细致越好。

java调用db2存储过程例子java调用db2存储过程例子1. JAVA 调用db2存储过程最简单的例子：存储过程创建代码：<o:p></o:p>sql 代码1.SET SCHEMA IES ;2.3.Create procedure ies.test()4.NGUAGE SQL6.7.Update t_ryxx set xm =’xy’ where ryxxid=’xm’java 代码1.conn = DbMaster.getConn();2.System.out.println("begin………");3.proc = conn.prepareCall("{call test()}");4.proc.execute();<o:p></o:p>2. Java调用db2带输入参数存储过程的例子：<o:p></o:p>Db2创建存储过程的代码：<o:p></o:p>sql 代码5.Drop procedure ies.test();6.SET SCHEMA IES ;7.Create procedure ies.test(in i_ryxxid varchar(50))NGUAGE SQL9.Update t_ryxx set xm =’xy’ where ryxxid=i_ryxxid10.java 代码11.conn = DbMaster.getConn();12.System.out.println("begin");13.proc = conn.prepareCall("{call test(?)}");14.proc.setString(1,"RY0003");15.proc.execute();16.System.out.println("end:");3.有输入输出参数的代码：创建存储过程的代码：sql 代码17.SET SCHEMA IES ;18.CREATE PROCEDURE IES.test (IN in_ryxxid varchar(50),out out_xm varchar(50))NGUAGE SQL20.21.select xm into out_xm from ies.t_ryxx where ryxxid=in_ryxxid;java 代码22.proc = conn.prepareCall("{ call test(?,?)}");23.proc.setString(1, "011900380103");24.proc.registerOutParameter(2, Types.VARCHAR);25.proc.execute();26.String xm = proc.getString(2);27.System.out.println("end:"+xm);4.带有游标的存储过程（不知道这里的游标什么时候close的。

DB2 HADR最佳实践摘要DB2 High Availability Disaster Recovery (HADR) 是一个简单易用的数据复制特性，该特性为局部和全面站点故障提供一个高可用性（HA）解决方案。

但是，由于用户的需求千差万别，因此不存在绝对理想的HADR 配置。

您的HADR 环境设置、调优和维护决策通常是权衡利弊的结果。

比如，您可能需要在数据库可用性要求和防止数据丢失之间进行权衡。

好消息是，进行权衡并不一定意味着需要以牺牲某项需求为代价。

本文就设置和维护您的HADR 环境提供了一些推荐方法，以帮助您在HADR 提供的保护与性能和成本之间找到平衡点。

本文重点关注以下领域：∙为快速故障转移设置系统∙调优参数以改进网络性能∙调优参数以最小化HADR 相关日志记录对性能的影响∙在HADR 环境中选择适当的表重组方法和负载操作HADR 简介HADR 从一个源数据库（称为主数据库）向一个目标数据库（称为备用数据库）复制数据更改。

由于这是一个无共享架构，每个数据库都使用自己的存储器。

HADR 在主数据库失败时提供快速故障转移。

您还可以在实施更新和实施维护等场景中便捷地转换主数据库和备用数据库的角色，从而将停机时间减至最小。

HADR 用途广泛，并被完全集成到DB2 数据库，不需要任何特殊硬件和软件，使用标准TCP 接口连接主数据库和备用数据库，其设置只需几个数据库配置参数。

HADR 的一个核心原则是：数据库的性能和可用性不能被某些挑战所影响，比如工作负载的突然波动（这影响备用数据库上的日志重放活动量）和服务器或网络失败（这将导致故障转移）。

为实现最优性能而调优您的HADR 解决方案应该遵循一些基本原则，以避免一段时间后出现的潜在问题。

另外，您需要了解几个涉及您的数据库维护行为的HADR 设置项目。

这个最佳实践文档将解决这些问题。

本文着眼于为您设计HADR 基础设施和配置数据库提供指南和推荐方法，从而改进HADR 相关性能，特别是日志记录和故障转移速度。

DB2实验报告一、实验目的本次 DB2 实验的主要目的是深入了解和掌握 DB2 数据库的基本操作和管理功能，包括数据库的创建、表的设计与操作、数据的插入、查询、更新和删除，以及索引和存储过程的使用等。

通过实际操作和实践，提高对数据库原理和技术的理解和应用能力，为今后在数据库相关领域的学习和工作打下坚实的基础。

二、实验环境操作系统：Windows 10DB2 版本：DB2 ExpressC 111开发工具：DB2 Command Line Processor （CLP)、IBM Data Studio三、实验内容及步骤（一）数据库的创建1、打开 DB2 CLP，输入以下命令创建数据库：CREATE DATABASE sample;2、连接到新创建的数据库：CONNECT TO sample;（二）表的设计与创建1、设计了以下两张表：员工表（EMPLOYEE）｜列名｜数据类型｜约束｜｜｜｜｜｜ EMPID ｜ INT ｜ PRIMARY KEY ｜｜ NAME ｜ VARCHAR(50) ｜ NOT NULL ｜｜ AGE ｜ INT ｜ CHECK （AGE ＞＝ 18) ｜｜ DEPARTMENT ｜ VARCHAR(50) ｜工资表（SALARY）｜列名｜数据类型｜约束｜｜｜｜｜｜ EMPID ｜ INT ｜ FOREIGN KEY REFERENCES EMPLOYEE(EMPID) ｜｜ SALARY ｜ DECIMAL(10, 2) ｜ NOT NULL ｜2、使用以下命令创建表：CREATE TABLE EMPLOYEE （EMPID INT PRIMARY KEY,NAME VARCHAR(50) NOT NULL,AGE INT CHECK （AGE ＞＝ 18)，DEPARTMENT VARCHAR(50)）；CREATE TABLE SALARY （EMPID INT,SALARY DECIMAL(10, 2) NOT NULL,FOREIGN KEY （EMPID) REFERENCES EMPLOYEE(EMPID)）；（三）数据的插入1、向员工表中插入以下数据：INSERT INTO EMPLOYEE （EMPID, NAME, AGE, DEPARTMENT) VALUES （1, ＇张三'， 25, ＇研发部'），（2, ＇李四'， 30, ＇销售部'），（3, ＇王五'， 28, ＇财务部'）；2、向工资表中插入相应的数据：INSERT INTO SALARY （EMPID, SALARY)VALUES （1, 800000)，（2, 1000000)，（3, 600000)；（四）数据的查询1、查询所有员工的信息：SELECT FROM EMPLOYEE;2、查询年龄大于 25 岁的员工信息：SELECT FROM EMPLOYEE WHERE AGE ＞ 25;3、查询每个部门的平均工资：SELECT DEPARTMENT, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEEJOIN SALARY ON EMPLOYEEEMPID ＝ SALARYEMPID GROUP BY DEPARTMENT;（五）数据的更新1、将员工“张三”的年龄更新为 26 岁：UPDATE EMPLOYEESET AGE ＝ 26WHERE NAME ＝＇张三'；（六）数据的删除1、删除员工表中“王五”的记录：DELETE FROM EMPLOYEEWHERE NAME ＝＇王五'；（七）索引的创建1、为员工表的“NAME”列创建索引：CREATE INDEX idx_employee_name ON EMPLOYEE （NAME)；（八）存储过程的创建与使用1、创建一个存储过程，用于计算某个部门的总工资：CREATE PROCEDURE calculate_total_salary(IN department_name VARCHAR(50)， OUT total_salary DECIMAL(10, 2)）LANGUAGE SQLBEGINSELECT SUM(SALARY) INTO total_salaryFROM EMPLOYEEJOIN SALARY ON EMPLOYEEEMPID ＝ SALARYEMPIDWHERE DEPARTMENT ＝ department_name;END;2、调用存储过程计算“研发部”的总工资：CALL calculate_total_salary(＇研发部'，＠total_salary)；SELECT ＠total_salary;四、实验结果及分析（一）实验结果1、数据库创建成功，能够正常连接和操作。

本文以 DB2 开发人员的角度介绍了在 DB2 存储过程开发中需要注意的事项和技巧。

新手如果能够按照本文介绍的最佳实践来开发存储过程，可以避免一些常见的错误，从而编写出高效的程序。

本文从初始化参数、游标、异常处理、临时表的使用以及如何寻找并 rebind 非法存储过程等常见问题进行了着重讨论,并且给出了示例代码。

DB2 提供的强大功能可以让开发人员创建出非常高效稳定的存储过程。

但对于初学者来说，开发出这样的程序并不容易。

本文主要讨论开发高效稳定的 DB2 存储过程的一些常用技巧和方法。

读者定位为具有一定开发经验的 DB2 开发经验的开发人员。

读者可以从本文学习到如何编写稳定、高效的存储过程。

并可以直接使用文章中提供的 DB2 代码，从而节省他们的开发和调试时间，提高效率。

本文以 DB2 开发人员的角度介绍了在 DB2 存储过程开发中需要注意的事项和技巧。

新手如果能够按照本文介绍的最佳实践来开发存储过程，可以避免一些常见的错误，从而编写出高效的程序。

本文从初始化参数、游标、异常处理、临时表的使用以及如何寻找并 rebind 非法存储过程等常见问题进行了着重讨论,并且给出了示例代码。

在存储过程中，开发人员能够声明和设置 SQL 变量、实现流程控制、处理异常、能够对数据进行插入、更新或者删除。

同时，客户应用（这里指调用存储过程的应用程序，它可以是 JDBC 的调用，也可以是 ODBC 和 CLI 等）和存储过程之间可以传递参数，并且从存储过程中返回结果集。

其中，使用 SQL 编写的 DB2 存储过程是在开发中常见的一种存储过程。

本文主要讨论此类存储过程。

最佳实践 1：在创建存储过程语句中提供必要的参数创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。

下面是一些常用的参数容许 SQL （allowed－SQL）容许 SQL （allowed－SQL）子句的值指定了存储过程是否会使用 SQL 语句，如果使用，其类型如何。

DB2数据库SQL存储过程高性能的SQL过程是数据库开发人员所追求的,我将不断把学到的,或在实际开发中用到的一些提高SQL过程性能的技巧整理出来,温故而知新.1，在只使用一条语句即可做到时避免使用多条语句让我们从一个简单的编码技巧开始。

如下所示的单个 INSERT 行序列：INSERT INTO tab_comp VALUES (item1, price1, qty1);INSERT INTO tab_comp VALUES (item2, price2, qty2);INSERT INTO tab_comp VALUES (item3, price3, qty3);可以改写成：INSERT INTO tab_comp VALUES (item1, price1, qty1),(item2, price2, qty2),(item3, price3, qty3);执行这个多行 INSERT 语句所需时间大约是执行原来三条语句的三分之一。

孤立地看，这一改进看起来似乎是微乎其微的，但是，如果这一代码段是重复执行的（例如该代码段位于循环体或触发器体中），那么改进是非常显著的。

类似地，如下所示的 SET 语句序列：SET A = expr1;SET B = expr2;SET C = expr3;可以写成一条 VALUES 语句：VALUES expr1, expr2, expr3 INTO A, B, C;如果任何两条语句之间都没有相关性，那么这一转换保留了原始序列的语义。

为了说明这一点，请考虑：SET A = monthly_avg * 12;SET B = (A / 2) * correction_factor;将上面两条语句转换成：VALUES (monthly_avg * 12, (A / 2) * correction_factor) INTO A, B;不会保留原始的语义，因为是以“并行”方式对 INTO 关键字之前的表达式进行求值的。

创建：db2-td@-vf createSQLproc.db2--end@ (此处的@可替换成其他符号)调用：db2call过程名(参数)1 基础--声明变量：DECLARE<variable-name><data-type><DEFAULT constant>--赋值：SET x=10;SET y=(SELECT SUM(c1)from T1);VALUES10INTO x;SELECT SUM(c1)INTO y from T1;--会话全局变量:CREATE VARIABLE var_name DATATYPE[DEAFULT value];2 、数组2.1定义CREATE TYPE mynames as VARCHAR(30)ARRAY[];--定义数组2.2声明DECLARE nameArr mynames;--声明数组2.3赋值SET TESTARR=ARRAY[1,2,3,4,5,6,7,8,9,10];SET TESTARR=ARRAY[VALU ES(1),(2)];--方法1,使用SET语句SELECT SUM(NUM)INTO TESTARR[1]FROM(VALUES(1),(2))AS TEMP(NU M);--方法2,使用VALUES INTO语句VALUES1INTO TESTARR[1];--方法3,使用SELECT INTO语句SET TESTARR[1]=1;--方法4,使用ARRAY构造函数2.4操作数组的函数ARRAY_DELETE：删除数组元素TRIM_ARRAY：从右开始删除指定数目个元素ARRAY_FIRST：返回数组中第一个元素ARRAY_LAST：返回数组中最后一个元素ARRAY_NEXT：返回数组下一个元素ARRAY_PRIOR：返回数组前一个元素ARRAY_VARIABLE：返回参数指定的元素ARRAY_EXISTS：判断数组是否有元素CARDINALITY：返回数组中元素的个数MAX_CARDINALITY：返回数组中元素的个数UNNEST：将数组转换为表3 复合语句语法：label:BEGIN[ATOMIC|NOT ATOMIC]--ATOMIC关键字封装的复合语句被当作一个处理单元--变量声明、过程逻辑等END label4流程控制--条件判断IFIF<condition>THEN<SQL procedure statement>;ELSEIF<condition>THEN<SQL procedure statement>;ELSE<SQL procedure statement>;END IF;IF FRIEND='张三'THENSET MSG='你好，张三';ELSEIF FRIEND='李四'THENSET MSG='你好，李四';ELSESET MSG='对不起，我不认识你';END IF;--循环WhileWHILE<condition>DO<sql statements>;END WHILE;WHILE I<=10DOSET NUM=NUM+I;SET I=I+1;END WHILE;--循环forFOR<loop_name>AS<sql statements>DO<sql statements>;END FOR;FOR TEST AS SELECT I FROM(VALUES(1),(2),(3))AS TEMP(I)DOSET NUM=NUM+I;END FOR;--循环LOOPLABEL:LOOP<sql statements>;LEAVE LABEL;END LOOP LABEL;TEST_LOOP:LOOPSET NUM=NUM+I;SET I=I+1;IF I>10THENLEAVE TEST_LOOP;END IF;END LOOP TEST_LOOP;--循环RepeatREPEAT<sql statements>;UNTIL<condition>END REPEAT;REPEATSET NUM=NUM+I;SET I=I+1;UNTIL I>10END REPEAT;--其他关键字ITERATE label--。

使用 ibm data studio 开发调试 db2 存储过程IBM Data Studio 是一款免费的基于 Eclipse 的用于数据库开发的工具。

IBM Data Studio 包含了开发数据库存储过程的所有功能，同时提供了对 DB2 v9 的 XML 功能的支持。

本文将通过一个开发实例介绍 IBM Data Studio 是如何帮助我们进行存储过程开发的。

项目实例介绍在开始使用 IBM Data Studio 之前，让我们先来了解一下本文的项目实例。

该项目实例是一个简化版的软件开发管理系统。

系统主要管理 User Story 和Work Item 的信息。

User Story 就是以用户的角度编写的业务需求，是软件需要实现的功能。

我们需要记录 User Story 的具体内容和其状态。

这里的状态是指该User Story 是在草拟状态还是完成状态。

Work Item 用于记录软件开发的过程。

Work Item 可以是根据某个 User Story 编写的详细设计，也可以是一个编码任务，或者是一个 bug 报告。

我们需要记录其状态(未分配，处理中和完成等)，结对编程人员的 Email 等信息。

本系统应该实现如下功能 ( 未列出所有功能 ):创建 User Story 。

修改 User Story 。

查询所有草拟状态的 User Story 。

创建 Work Item 。

修改 Work Item 。

查询属于某个 User Story 的所有 Work Item 。

为此我们设计了相应的数据库表:USER_STORY 和 WORK_ITEM 。

它们的详细定义如下表所示: 图 1. User story 和 Work item 的关系表 1. User Story 的定义 USER_STORY 列名称类型说明id INTEGER 表主键。

author VARCHAR 编写人的 Email 地址。

-17-
模块 - 其他语句
删除整个模块
− DROP MODULE myMod;
保留规格说明内容，删除实现
− ALTER MODULE myMod DROP BODY;
删除模块中的存储过程(SP)
− ALTER MODULE myMod DROP PROCEDURE myProc;
将模块的执行权限赋给joe
ITERATE FETCH_LOOP1;
END IF; INSERT INTO department(deptno, deptname, admdept) VALUES(‘NEW’, v_deptname, v_admdept); END LOOP FETCH_LOOP1;
-27-
GOTO语句
GOTO语句用于直接跳转到指定标签处。例如： IF v_DEPT = ‘D11’ GOTO bye; ……
DECLARE my_var INTEGER DEFAULT 6;
条件声明
DECLARE not_found CONDITION FOR SQLSTATE ‘02000’;
游标声明
DECLARE c1 CURSOR FOR select * from staff;
异常处理器声明
DECLARE EXIT HANDLER FOR SQLEXCEPTION …;
（DECLARE关键字，cl游标名称， CURSOR是必须有的，；指通过c1的游标来操作staff里所有的数据）最常用的最普 通的。
2.DECLARE c1 CURSOR WITH HOLD FOR select * form staff; 3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * form staff; 4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select * form staff;

优化DB2数据库的十个最佳实践(上)结构化查询语言(SQL)对于关系型DBMS是把双刃剑，利弊参半。

因为从关系型数据库检索任何数据都需要SQL，本文所要探讨的话题就是：不论是终端用户还是开发人员或是数据库管理员（DBA），他们将如何访问一个关系型数据库。

当使用高效的SQL 时,系统会变得易于升级、灵活、而且便于管理。

当使用低效的SQL 时, 响应时间和程序运行时间都会延长，并且还会产生应用系统的中断。

鉴于通常的数据库系统一般要花费90% 的处理时间用于从数据库检索数据，由此很明显的可以看出尽可能的保证SQL的高效是多么的重要。

考察通常的SQL 语句问题譬如"SELECT * FROM"仅是冰山一角,我们将在本文中探讨其他容易确定的普遍的问题。

需要记住的是, 检索得到同一数据的SQL 语句有很多种殊途同归的写法，所以不存在好的查询语句或是坏的查询语句，而只有满足适当需求的查询语句。

各关系型数据库都有自己的方式来优化和执行查询语句。

因此，各DBMS都拥有自己的最佳性能的查询技巧。

本文将使用Quest 软件中Quest Central for DB2 的例子和概述来集中讨论DB2 for OS/390 和z/0S。

要是在十七年前，这张技巧单会更长，并且会包含对最小化的SELECT 场景的矫正方法。

每一个新版本的DB2 都会增加成千上万行的新代码，用以扩展智能优化，和查询重写及执行。

例如，多年来一种被称为数据管理器的组件, 通常被提供作为"第一阶段处理"以增加它的过滤容量一百倍。

另一组件是关系型数据服务器，通常被提供作为"第二阶段处理"来进行其主函数的查询重写和优化。

另一关键组件就是基于当前的SQL，并使用存取路径以决定检索数据的DB2 优化器。

DB2 优化器改善了每一个DB2 的版本, 考虑到另外的DB2目录中的统计, 可以提供新的和改善过的存取路径。