VARRAY feature in DB2

krReddy · #1 (**permalink**) 08-16-07, 10:09

Hi ,

We are in the process of migrating Oracle DB to DB2 , We are struck up at VARRAY migration . I need ur help in migratingg this .\
My Oracle package with VARRAY is as below

PHP Code:


			
[QUOTE]

CREATE OR REPLACE PACKAGE BODY My_Varray_Package

IS

TYPE myvarray IS VARRAY(255) OF mytab%ROWTYPE;



PROCEDURE myproc (

abc IN myvarray

)

is 

j integer;

a myvarray;

begin

select * from mytab1;

end;

FUNCTION myfunc

(  

a IN type_abc

) RETURN tempvariable

IS

myvar           myvarray;



BEGIN

myvar1 := myvarray();

end;

end;[/QUOTE]

Immediate help is highly appreciated

grofaty · #2 (**permalink**) 08-17-07, 04:13

Hi,
what is your DB2 database version (command: db2level), database type (command: db2licm -l), database fixpack level (command: db2licm -l), operating system name and version?
Regards,
Grofaty

krReddy · #3 (**permalink**) 08-17-07, 06:04

We are trying to migrate to DB2 8 on Windows

krReddy · #4 (**permalink**) 08-17-07, 06:06

is there anything like ARRAY in DB2

stolze · #5 (**permalink**) 08-17-07, 13:31

Yes, tables.

db2hrishy · #6 (**permalink**) 08-20-07, 06:33

Hi

How about global temporary tables

regards
Hrishy

sushma007 · #7 (**permalink**) 08-21-07, 07:10

Suppose i have an Oracle procedure like

Quote:

create type varray1 is varray(12) of varchar(12);
create or replace procedure proc
as
v1 varray1 ;
begin
v1 := varray1('a','b');
dbms_output.put_line('initial varray(1) and v1(1) ' || v1(1));
v1(1) := 'z';
dbms_output.put_line('final varray(1) and v1(1) ' || v1(1));
end;

wat will be the DB2 equivalent

stolze · #8 (**permalink**) 08-21-07, 13:51

Your code is purely procedural. I would rather do something like that in an application because relational database systems were not (originally) designed for that. But that's beside the point...

Assuming that you have a function like PUT_LINE, I would do this:

Code:

CREATE PROCEDURE ...
   BEGIN
      DECLARE v1_1 VARCHAR(12) DEFAULT 'a'
      DECLARE v1_2 VARCHAR(12) DEFAULT 'b';
      DECLARE res INT;
      SET res = PUT_LINE('initial varray(1) and v1(1) ' || v1_1);
      SET v1_1 = 'z';
      SET res = PUT_LINE('initial varray(1) and v1(1) ' || v1_1);
   END@

If the array is too big, a temp table can hold the elements:

Code:

CREATE PROCEDURE ...
   BEGIN
      DECLARE res INT;
      -- storage for the array
      DECLARE GLOBAL TEMPORARY TABLE session.t1 ( id INT, value VARCHAR(12) );
      -- initialize array elements
      INSERT INTO session.t1 VALUES (1, 'a'), (2, 'b');
      SET res = put_line('initial varray(1) and v1(1) ' || ( SELECT value FROM session.t1 WHERE id = 1 ) );

      -- change an array element
      UPDATE session.t1 SET value = 'z' WHERE id = 1;
      SET res = put_line('initial varray(1) and v1(1) ' || ( SELECT value FROM session.t1 WHERE id = 1 ) );
      DROP TABLE session.t1;
   END@

Granted, the syntax is a bit more verbose, but you have the same functionality. (In fact, you could use UDFs to reduce the syntax a bit more.)

Internet Services

Web Development

Digital Marketing

Consumer Tech