模板
教程
环境
性能
功能
管理
SYBASE数据库迁移到AS400db2的FAQ（四）
31MILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">．Db2的循环控制语句loop用法?
答：loop例子：
OPEN c1;
SET at_end = 0;
SET numrec = 0;
fetch_loop: 1
LOOP
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
LEAVE fetch_loop; 2
END IF;
END LOOP fetch_loop; 3
CLOSE c1;
32．Db2的循环控制语句while用法?
答：while 例子：
OPEN c1;
SET at_end = 0;
SET numrec = 0;
WHILE at_end = 0 DO
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
SET at_end = 1;
END IF;
END WHILE;
CLOSE c1;
33．Db2的循环控制语句repeat用法？
答：repeat例子
SET numrec = 0;
fetch_loop:
REPEAT
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
END IF;
UNTIL SQLCODE <> 0
END REPEAT fetch_loop;
34．Db2的循环控制语句for用法？
答：for 例子
FOR each_record AS
cursor1 CURSOR FOR
SELECT cusnbr, cuscrd FROM ordapplib.customer
DO
UPDATE ordapplib.customer
SET cuscrd = cuscrd * 1.1
WHERE CURRENT OF cursor1;
END FOR;
34．Sybase 中循环控制Break,Continue在Db2用法？
答：在db2中 Break 转换为leave lab, Continue 转换为ITERATE lab
等同于GOTO语句
举例如下：
============== leave 例子
OPEN c1;
SET at_end = 0;
SET numrec = 0;
fetch_loop: 1
LOOP
FETCH c1 INTO proc_cusnbr, proc_cuscrd;
IF SQLCODE = 0 THEN
SET proc_cuscrd = proc_cuscrd * 1.2;
UPDATE ordapplib.customer
SET cuscrd = proc_cuscrd
WHERE CURRENT OF c1;
SET numrec = numrec + 1;
ELSE
LEAVE fetch_loop; 2
END IF;
END LOOP fetch_loop;
CLOSE c1;
============ iterate 例子
BEGIN
OPEN c1;
ins_loop: 1
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; 2
END IF;
INSERT INTO sampledb02.deptnew (deptno, deptname, admrdept)
VALUES (v_dept, v_deptname, v_admdept);
END LOOP;
CLOSE c1;
END;
35．在Db2存储过程中使用滚动游标（scrollable cursor）？
答：例子
CREATE PROCEDURE MYMAX
( IN fld_name CHAR(30),
IN file_name CHAR(128),
INOUT max_value INTEGER)
LANGUAGE SQL
BEGIN atomic
DECLARE sql_stmt CHAR(256);
DECLARE not_found
CONDITION FOR '02000';
DECLARE c1 DYNAMIC SCROLL CURSOR FOR s1; -- 声明动态滚动游标
DECLARE CONTINUE HANDLER FOR not_found
SET max_value = NULL;
SET sql_stmt = 'SELECT ' || fld_name || ' FROM ' || file_name ||
' ORDER BY 1'; --组合sql语句
PREPARE s1 FROM sql_stmt;
OPEN c1;
FETCH LAST FROM c1 INTO max_value; --转到最后行
CLOSE c1;
END
滚动游标的使用例子：（rpg）
EXEC SQL BEGIN DECLARE SECTION;
char fld_name[ 30 ];
char file_name[ 128 ];
integer max_value;
short ind3;
EXEC SQL END DECLARE SECTION;
Then the indicator variable is used in the call statement:
EXEC SQL
CALL MYMAX( :fld_name, :file_name, :max_value :ind3);
36．db2中存储过程中使用动态游标（dynamic cursor）？
答：使用PREPARE ， EXECUTE ，EXECUTE IMMEDIATE语句
例子：
CREATE PROCEDURE DYNSQLSAMPLE()
LANGUAGE SQL
BEGIN
DECLARE stmt VARCHAR(256);
SET stmt = 'UPDATE employee SET salary = salary * 1.1 WHERE empno = ?'; 1
PREPARE s1 FROM stmt;
ins_loop:
FOR each_department AS
c1 CURSOR FOR
SELECT mgrno FROM department WHERE mgrno IS NOT NULL
DO
EXECUTE s1 USING mgrno;
END FOR;
END;
EXECUTE IMMEDIATE statement 例子：
PREPARE s1 FROM ‘UPDATE employee SET salary = salary * 1.1 WHERE
empno IN (SELECT DISTINCT mgrno FROM department WHERE mgrno IS NOT NULL);
EXECUTE s1;
等同于
EXECUTE IMMEDIATE ‘UPDATE employee SET salary = salary * 1.1 WHERE
empno IN (SELECT DISTINCT mgrno FROM department WHERE mgrno IS NOT NULL);
最基本动态游标语句
...
DECLARE stmt VARCHAR[256];
...
SET stmt = ‘SELECT COLUMN1, COLUMN2, COLUMN3 FROM TBL1’;
PREPARE PreparedStatement FROM s1;
DECLARE Cursor1 CURSOR FOR PreparedStatement;
...
37．Db2下支持返回结果集合的存储过程吗？
答：支持，例子1
CREATE PROCEDURE GetCusName()
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT cusnam FROM customer ORDER BY cusnam;
OPEN c1;
SET RESULT SETS CURSOR c1;
END
例子2
CREATE PROCEDURE GETRANKV4R5
(IN proc_year DECIMAL(4,0),
IN proc_month DECIMAL(2,0),
INOUT proc_rank INTEGER)
RESULT SETS 2 ---- 2 两个结果集
LANGUAGE SQL
BEGIN
...
DECLARE c1 DYNAMIC SCROLL CURSOR FOR s1;
DECLARE c2 DYNAMIC SCROLL CURSOR FOR s2;
...
SET RESULT SETS CURSOR c1, CURSOR c2;
END
38．DB2数据库中一个表的行长度、列数以及每页行数在表空间中的限制
答：在DB2数据库中一个表的每行长度、列数以及每页行数在表空间中的限制如下：
[平台] Windows 9x/NT/2000, Unix, Linux
[版本] 6.x/7.x
表空间页面大小 表空间中行长度限制（bytes）表空间中列数限制 表空间中每页最大行数
4K 4005 500 255
8K 8101 1012 255
16K 16293 1012 255
32K 32677 1012 255
注：表空间页面大小只有4K，8K，16K，32K四种。
39．某些SQL语句可能非常复杂,比如嵌套调用多个表或触发许多触发器,在对这样的SQL语句进行编译时,出现SQL0101N错误,如何处理
答：对于一个复杂的SQL语句,在调用多个表或触发多个触发器时, 可能会占用
大量的系统资源.
当出现SQL0101N错误时, 首先需要确认系统中没有递归的触发器存在.
之后可通过增加如下参数的值来解决此问题:
1)STMTHEAP
2)APPLHEAPSZ
3)PCKCACHESZ
答：数据库建立时日志方式默认是循环日志模式（Circular Log），这时是无法做联机备份的。所以，希望实施联机备份，首先要将日志方式改为归档日志模式（Archival Log）。
以sample数据库为例，可以在控制中心中改变sample数据库的配置参数LOGRETAIN为Recovery，或在命令行下用 db2 update db cfg for sample using LOGRETAIN on。改变此参数后，再次连接数据库会显示数据库处于备份暂挂（BACKUP PENDING）状态。这时，需要做一次对数据库的脱机备份。在控制中心中选择对数据库进行脱机备份或在命令行下用 db2 backup db sample 实施。此后数据库就可以进行联机备份了。
可以选择在控制中心中对数据库进行联机备份，或在命令行下用 db2 backup db sample online 实施。
注意: 利用联机备份得到的IMAGE文件进行恢复时，还需要相关的日志文件。
qq:50839655
email:zhangtao@xhzq.com,dekker99@163.com
