安装DB2默认值?
在WINDOWS或OS/2中默认实例的是DB2
在LINUX或UNIX环境下默认实例的是DB2INST1
在WINDOWS或OS/2中默认帐户的是DB2ADMIN
在LINUX或UNIX环境下默认帐户的是DB2AS
如何定义序列
CREATE SEQUENCE ORDERSEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24
例如:
create sequence pk_only_empid;
select NEXTVAL FOR pk_only_empid from (values 1) as tmp;
如何关闭表的日志
ALTER TABLE TABLE_NAME ACTIVE NOT LOGGED INIALLY
如何获取SQL执行计划
SQL 解释工具
SQL 解释工具提供查询优化器为 SQL 语句所选择的访问计划的有关详细信息。该信息存储在 EXPLAIN 表中,可以在稍后使用诸如 Visual Explain、db2expln、dynexpln 和 db2exfmt 的工具进行格式化,从而以友好的可视方式进行表示。
EXPLAIN 表可以在您第一次使用 Visual Explain 时自动进行创建。即使没有创建它们,您也可以手工进行创建,如下:
% cd <db2 install path>\sqllib\misc
% db2 connect to bank
% db2 -tvf EXPLAIN.DDL
本文中,我们使用 db2exfmt 工具。例如,使用 db2exfmt 解释动态 SQL 语句,在 DB2 命令窗口中按照下列步骤进行:
% db2 connect to <database_name>
% db2 set current explain mode explain
% db2 -tvf <Input file with an SQL statement ended with a semicolon>
% db2 set current explain mode no
% db2exfmt -d <dbname> -g TIC -w -1 -n % -s % -# 0 -o <output file>
如何创建事例?
DB2ICRT <client> INSTNAME <...PARAMETERS>
如果是客户端,加上client关键字
例如:
1) 用root登录,命令为smitty创建用户db2inst1,用于数据库管理。
2) 用root登录,使用db2icrt创建实例DB2 实例,使用以下命令。
cd /usr/opt/db2*/instance/
./db2icrt -s client db2inst1
3) 用db2inst1用户登录,创建数据库的本地节点目录,建立远程数据库映射别名,使用以下命令。
cd /usr/opt/db2*/bin
db2 catalog tcpip node wmsint remote 182.247.70.94 server 60000
db2 catalog database wmsint as wmsint at node wmsint
db2set db2codepage=1386
db2 terminate
4) 为了使root用户可以使用db2命令,要把home/db2inst1下的.profile文件中的下面一段文字加到根目录下的.profile中。
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.
export PATH
# The following three lines have been added by UDB DB2.
if [ -f /home/db2inst1/sqllib/db2profile ]; then
. /home/db2inst1/sqllib/db2profile
fi
5)退出root用户,重新登入
SQL写法技巧
多个字段时如何不通过使用select子句使用in/not in
select * from tabschema.tabname where (colA, colB, colC) [not] in (values (valueA1, valueB1, valueC1), (valueA2, valueB2, valueC2), ...(valueAn, valueBn, valueCn))
Update tablenameA t1
Set (colA,colB,colC)=(selelct b.colA,b.colB,b.colC from tablenameB t2 where t1.key = t2.key)
使用部分命令
列出所有实例 DB2ILIST
获取当前实例 GET INSTANCE
更新事例的配置 DB2IUPDT
删除事例 DB2IDROP INSTANCE_NAME
具体步骤如下:
停止事例上所有应用程序
在所有打开的命令行上执行DB2 TERMINATE
运行DB2STOP
备份DB2INSTPROF注册变量指出的事例目录
退出事例所有登陆者
使用DB2IDROP
也可以删除ID
列出本地系统上有许可信息的所有产品 DB2LICM -L
增加一产品许可DB2LICM -A FILENAME
删除一个产品的许可 DB2LICM -R PRODUCT PASSWORD
更新已购买的许可数量DB2LICM -U
强制只使用已经购买的数量DB2LICM -E HARD
更新系统上所使用的许可政策类型 DB2LICM -P REGISTERED CONCURRENT
更新系统上的处理器的数量 DB2LICM -N
查询许可文件记录的版本信息 DB2LICM -V
查询DB2LICM的帮助信息 DB2LICM -H
二、存储过程
如何声明一个存储过程
CREATE PROCEDURE 存储过程名(IN 输入变量名 输入变量类型,OUT 输出变量名 输出变量类型)
紧跟其后的是存储过程属性列表
常用的有:LANGUAGE SQL、MODIFIES SQL DATA、RESULT SETS 1(返回结果集个数)
l 存储过程体以begin开始
l 存储过程体以end结束
存储过程约束规则
存储过程中调用存储过程
CALL 存储过程名(参数1,参数2,参数n)
例:
call spco_init_custom(bankcode,errno,errmsg);
GET DIAGNOSTICS retval=RETURN_STATUS;
if(retval<>0) then
set errno=errno;
set errmsg=errmsg;
return errno;
end if;
变量的定义
变量使用前必须先定义,方法为
DECLARE 变量名 变量类型 (default 默认值)
例:
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE inum INTEGER DEFAULT 0;
DECLARE curtime char(8);
DECLARE bcode char(6);
DECLARE sqlstate char(5);
if 表达式
if 条件1 then
逻辑体;
elseif 条件2 then
逻辑体;
else
逻辑体;
end if;
例:
IF rating = 1 THEN
UPDATE employee
SET salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
ELSEIF rating = 2 THEN
UPDATE employee
SET salary = salary * 1.05, bonus = 500
WHERE empno = employee_number;
ELSE
UPDATE employee
SET salary = salary * 1.03, bonus = 0
WHERE empno = employee_number;
END IF;
case表达式
case 变量名 when
变量值1 then
. . .
when
变量值2 then
- - -
else
. . .
end case;
或
case when
变量名=变量值1 then
. . .
when
变量名=变量值2 then
- - -
else
. . .
end case;
例一:
CASE v_workdept
WHEN 'A00'
THEN UPDATE department
SET deptname = 'DATA ACCESS 1';
WHEN 'B01'
THEN UPDATE department
SET deptname = 'DATA ACCESS 2';
ELSE UPDATE department
SET deptname = 'DATA ACCESS 3';
END CASE;
例二:
CASE
WHEN v_workdept = 'A00'
THEN UPDATE department
SET deptname = 'DATA ACCESS 1';
WHEN v_workdept = 'B01'
THEN UPDATE department
SET deptname = 'DATA ACCESS 2';
ELSE UPDATE department
SET deptname = 'DATA ACCESS 3';
END CASE;
for 表达式
for 循环名 as
游标名或select 表达式
do
sql表达式;
end for;
例:
1)
DECLARE fullname CHAR(40);
FOR vl AS
SELECT firstnme, midinit, lastname FROM employee
DO
SET fullname = lastname || ',' || firstnme ||' ' || midinit;
INSERT INTO tnames VALUE (fullname);
END FOR
2)
for loopcs1 as cousor1 cursor as
select market_code as market_code
from tb_market_code
for update
do
end for;
goto表达式
goto 标示名;
标示名:
逻辑体;
例:
GOTO FAIL;
...
SUCCESS: RETURN 0
FAIL: RETURN -200
while表达式
while 条件表达式 do
逻辑体;
end while;
LOOP表达式
LOOP... END LOOP;
例:
OPEN c1;
ins_loop:
LOOP
FETCH c1 INTO v_dept, v_deptname, v_admdept;
IF at_end = 1 THEN
LEAVE ins_loop; --中断循环
ELSEIF v_dept = 'D11' THEN
ITERATE ins_loop; --下一个循环
END IF;
INSERT INTO department (deptno, deptname, admrdept)
VALUES ('NEW', v_deptname, v_admdept);
END LOOP;
CLOSE c1;
关于游标
定义游标:
DECLARE 游标名 CURSOR FOR
Select 语句;
打开游标:
OPEN 游标名;
取值:
FETCH 游标名 INTO 变量列表
例:
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
WHERE DEPT = deptNumber
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM staff
WHERE DEPT = deptNumber;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
注:游标的申明如果放在中间段,要用”begin。 。 。end;”.段分割标志分割开;
动态sql
1) declare stmt varchar(1024);
set stmt='create table zhouhaiming( f1 smallint, f2 varchar(9), f3 char(5) )';
prepare s1 from stmt;
execute s1;
set stmt='insert into zhouhaiming values (1,'www','aaa')';
prepare s1 from stmt;
execute s1;
2) DECLARE CURSOR C1 FOR STMT1;
PREPARE STMT1 FROM
'ALLOCATE C2 CURSOR FOR RESULT SET ?';
临时表的建立
DECLARE GLOBAL TEMPORARY TABLE TABLE_NAME
AS (FULLSELECT) DEFINITION ONLY
EXCLUDING IDENTITY COLUMN ATTRIBUTES
ON COMMIT DELETE ROWS
NOT LOGGED IN 临时表空间名 with replace;
第一行規定臨時表的名稱.
第二行規定臨時表的列的定義.
第三行規定不是從源結果表定義中復制的恒等列.
第四行規定如果沒有打開WITH GOLD光標,將會刪除表的所有行.
第五行規定不對表的改變進行記錄.
With replace选项会隐式的自动删除该临时表。
例如:
DECLARE GLOBAL TEMPORARY TABLE DEC_BSEMPMS
AS (SELECT * FROM BSEMPMS) DEFINITION ONLY
EXCLUDING IDENTITY COLUMN ATTRIBUTES
ON COMMIT DELETE ROWS
NOT LOGGED;
DB2中的几个全局变量
n ROW_COUNT—影响行数
UPDATE CORPDATA.PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = deptnbr;
GET DIAGNOSTICS rcount = ROW_COUNT;
n RETURN_STATUS--返回状态
CALL TRYIT;--调用存储过程
GET DIAGNOSTICS RETVAL = RETURN_STATUS;
IF RETVAL <> 0 THEN
...
LEAVE A1;
ELSE
...
END IF;
n SQLSTATE—SQL返回错误代码
注:使用前必先定义
declare sqlstate char(5);
declare state char(5);
insert into tbname values(…)
set state=sqlstate;
if(state<> '00000') then
return -1;
end if;
关于ATOMIC和NOT ATOMIC
P1:BEGIN ATOMIC –P1段的事务会自动回滚
P1:BEGIN NOT ATOMIC –P1段的事务不会自动回滚
DB2中的条件句柄
句柄类型:
n CONTINUE
n EXIT
n UNDO
条件类型:
n SQLSTATE string
n SQLEXCEPTION
n SQLWARNING
n NOT FOUND
例:
1) DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
2) DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SET rating = -1;
3) DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR
SELECT deptno, deptname, admrdept
FROM department
ORDER BY deptno;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
如何抽取/提交存储过程
db2 "get routine into 文件名 from procedure 存储过程名"
抽取存储过程;
提交存储过程
db2 "put routine from 文件名"
安装已编译好的存储过程。
如何在命令符下提交存储过程
在存储过程的最后加上@符号,然后在命令符下打入:db2 -td@ -vf procfile.sql 就可以生成过程。
非存储过程的SQL文件,在命令符下打入:db2 –tvf sqlfile.sql
从存储过程返回结果集(游标)的用法
1、建一sp返回结果集
CREATE PROCEDURE DB2INST1.Proc1 (
LANGUAGE SQL
result sets 2 --(返回两个结果集)
P1: BEGIN
declare c1 cursor with return to caller for
select market_code
from tb_market_code;
--指定该结果集用于返回给调用者
declare c2 cursor with return to caller for
select market_code
from tb_market_code;
open c1;
open c2;
END P1
2、建一SP调该sp且使用它的结果集
CREATE PROCEDURE DB2INST1.Proc2 (
out out_market_code char(1))
LANGUAGE SQL
P1: BEGIN
declare loc1,loc2 result_set_locator varying;
--建立一个结果集数组
call proc1;
--调用该SP返回结果集。
associate result set locator(loc1,loc2) with procedure proc1;
--将返回结果集和结果集数组关联
allocate cursor1 cursor for result set loc1;
allocate cursor2 cursor for result set loc2;
--将结果集数组分配给cursor
fetch cursor1 into out_market_code;
--直接从结果集中赋值
close cursor1;
END P1