通过索引保持数据唯一性:CREATE UNIQUE INDEX INDEXNAME ON TABLE (COLUMN)
消除重复行:SELECT DISTINCT COLUMN FROM TABLE
3、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 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current 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中的内部命令。要想显示当前时间的话,不能直接输入current time,这个只能在SQL语言中引用,要想显示有下面方式:
1) VALUES (current time)
2) SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1
这个与SQL SERVER2000中不一样,在SQL SERVER2000中可以输入Getdate()得到时间,既可以显示,也可以在语句SQL中用。
4、所有返回前N条数据的表达式
在SQL SERVER2000中使用TOP N 格式
比如: SELECT TOP 10 CARDNO FROM CARD
在DB2中使用fetch first N rows only 格式
比如:SELECT CARDNO FROM SEALCARD fetch first 10 rows only
5、函数使用
查看系统函数: SELECT * FROM SYSibm.sysfunctions;
比如:ABS(-89)可以作为值输入到SQL中,但是要想在命令编辑器中显示函数的结果的话可以用下列方式:
1)SELECT ABS(-89) FROM SYSIBM.SYSDUMMY1;
2)VALUES ABS(-89);
@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
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
@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
@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';
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
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)