Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Thumbs down Unanswered: VARRAY feature in DB2

    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
    (255OF 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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Oct 2006
    Posts
    83
    We are trying to migrate to DB2 8 on Windows

  4. #4
    Join Date
    Oct 2006
    Posts
    83
    is there anything like ARRAY in DB2

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jun 2004
    Posts
    115
    Hi

    How about global temporary tables

    regards
    Hrishy

  7. #7
    Join Date
    Aug 2007
    Posts
    64
    Suppose i have an Oracle procedure like

    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

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •