SYBASE 数据库迁移到AS 400 db2的FAQ(四)

类别:数据库 点击:0 评论:0 推荐:
 

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:[email protected]

email:[email protected],[email protected]

本文地址:http://com.8s8s.com/it/it21096.htm