데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

V9.5부터는 관련 함수를 지원하네요.

Global session variable support

DB2 Viper 2 extends SQL with a new data object called global session variable. Global session variables have a schema qualified, persistent definitions in the catalog, but their content is private to any given session. Global session variables facilitate porting of package variables when mapping a package to a DB2 schema

아래참조해보세요.

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0707rielau/

이전 버전이면 temporary table을 사용해보세요.

-------------------------
Nested tables and varrays

To understand how nested tables and varrays can be converted to DB2, first let
us address the difference between Oracle nested tables and varrays.
Items of type TABLE are called nested tables. Within the database, they can be
viewed as one-column database tables. Oracle stores the rows of a nested table
in no particular order. But, when you retrieve the nested table into a PL/SQL
variable, the rows are given consecutive subscripts starting at 1. Nested table
has no upper bound, while the size of variable arrays is fixed. The second
important difference, the variable arrays must be dense (have consecutive
subscripts). So, you cannot delete individual elements from an array. Initially,
nested tables are dense, but they can be sparse (have nonconsecutive
subscripts).
As DB2 does not support collections, the most generic way to convert a nested
table is by using DB2 Global Declared Temporary Table (DGTT), where first
column stores the value of the subscript, and the second column stores the value
of Oracle nested table.
Let us clarify this with an example (Example 5-15) that filling nested table
EmpList with names of the Employees for the given department from table
emp_table.

Note: DB2 temporary tables are not similar to Oracle temporary tables. DB2
temporary tables are memory bound (provided sufficient memory is available),
visible only to the connection that declares it, and exist only for as long as a
connection is maintained (or dropped). If you disconnect, the table is
automatically cleaned up.

Tip: To use DGTTs, you must create a user temporary table space (none
exists by default). In the simplest case, you can use:
create user temporary tablespace usertemp1 managed by system using
('usertemp1')
The size of the buffer pool associated with this tablespace will affect how
memory-bound DGTTs are at runtime.

Example 5-15 Oracle code using nested table
DECLARE
TYPE EmpList IS TABLE OF emp_table.ename%TYPE ;
CURSOR c1 IS
SELECT emp_name
FROM emp_table
WHERE dept = v_dept;
EmpName emp_table.ename%TYPE;
empNum NUMBER;
BEGIN
LOOP
FETCH c1 INTO EmpName;
WHEN c1%NOTFOUND EXIT;
empNum := empNum + 1;
EmpList(empNum):= EmpName;
END LOOP;
CLOSE c1;
END;

The same can be implemented in DB2 using DGTT as shown in Example 5-16.

Example 5-16 DB2 UDB code using DGTT
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_empname varchar(30);
DECLARE v_num INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT emp_name
FROM emp_table
WHERE dept = v_dept;
DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_emp_list
(num integer, EmpName varchar(30))
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;
OPEN c1;
WHILE (SQLCODE = 0) DO
FETCH c1 INTO v_empname;
SET v_num = v_num +1;
INSERT INTO SESSION.temp_emp_list
VALUES (v_num,v_empname);
END WHILE;
CLOSE c1;

Or better yet, the code is more efficient if converted as follows (Example 5-17).

Example 5-17 Efficient DB2 UDB code using DGTT
DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_emp_list
(num integer,EmpName varchar(30))
WITH REPLACE
ON COMMIT PRESERVE ROWS
NOT LOGGED;
INSERT INTO session.temp_emp_list
SELECT row_number() over(), emp_name
FROM emp_table
WHERE dept = v_dept;

To convert Oracle varrays, you can also use DGTT, or sometimes the redesign
can help achieve the

벽타는 냥이님이 2008-02-20 16:36에 작성한 댓글입니다.
이 댓글은 2008-02-21 11:06에 마지막으로 수정되었습니다. Edit Ｘ