IDC资讯中心
31．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
40．如何实施联机备份？
答：数据库建立时日志方式默认是循环日志模式（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
msn:dekker@hotmail.com
email:zhangtao@xhzq.com,dekker99@163.com
