Equivalent of VARRAYs in DB2

[Praveen]

Hi All,

I have an application for that we used oracle 8i as the db and we have
written procedures using VARRAYs. Now, the requirement came to migrate
it on DB2 UDB. Can anyone pls tell me is there any equivalent or
alternative to VARRAYs in DB2?

Thanks in advance,
Praveen

[Mark A]

"Praveen" <sp**********@yahoo.com> wrote in message
news:98**************************@posting.google.c om...

Hi All,

I have an application for that we used oracle 8i as the db and we have
written procedures using VARRAYs. Now, the requirement came to migrate
it on DB2 UDB. Can anyone pls tell me is there any equivalent or
alternative to VARRAYs in DB2?

Thanks in advance,
Praveen

Can you tell us what VARRAY does?

[Serge Rielau]

You can use global temporary tables with NOT LOGGED ON COMMIT RETAIN
ROWS option. If the array is not too big you will end up with one page
pinned in the bufferpool. So it's going to be I/O free.
If you are doing a smart migration this performs very well (with smart I
mean you migrate the function and not line by line (emulation)).
Ideally you want to turn loops over the array into selects and seached
updates.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Mark A wrote:

"Praveen" <sp**********@yahoo.com> wrote in message
news:98**************************@posting.google.c om...

Hi All,

I have an application for that we used oracle 8i as the db and we have
written procedures using VARRAYs. Now, the requirement came to migrate
it on DB2 UDB. Can anyone pls tell me is there any equivalent or
alternative to VARRAYs in DB2?

Thanks in advance,
Praveen

Can you tell us what VARRAY does?

A user defined data type corresponding to a variable length array.

http://www.psoug.org/reference/varray.html

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Mark A]

> > Can you tell us what VARRAY does?

A user defined data type corresponding to a variable length array.

http://www.psoug.org/reference/varray.html

--
Daniel Morgan

Sounds like 2nd normal form. Another violation of the relational standard by
Oracle.

[Daniel Morgan]

Mark A wrote:

Can you tell us what VARRAY does?

A user defined data type corresponding to a variable length array.

http://www.psoug.org/reference/varray.html

--
Daniel Morgan

Sounds like 2nd normal form. Another violation of the relational standard by
Oracle.

Oracle is not a pure relational database. Rather it is object-relational
and has been since, IIRC version 8.0 many years ago. VARRAYs are used
when creating object tables, object views, and working with their data.

Just because you have a hammer in your toolbox doesn't mean you have to
use it.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Mark A]

"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1075997670.868621@yasure...

Mark A wrote:

Can you tell us what VARRAY does?

A user defined data type corresponding to a variable length array.

http://www.psoug.org/reference/varray.html

--
Daniel Morgan

Sounds like 2nd normal form. Another violation of the relational standard by Oracle.

Oracle is not a pure relational database. Rather it is object-relational
and has been since, IIRC version 8.0 many years ago. VARRAYs are used
when creating object tables, object views, and working with their data.

Just because you have a hammer in your toolbox doesn't mean you have to
use it.

--
Daniel Morgan

Well, at least you admitted that Oracle is not relational. I don't consider
relational to be hammer.

[Serge Rielau]

Actually I'm in with Daniel for a change (hear, hear).
He was referring to VARRAY as a hammer, btw.
While I would not push arrays in persistent objects like tables, the
procedural(!) part of the DBMS offering which includes in DB2 SQL PL can
benefit from arrays. I'd rather bite my tongue than yell at Oracle for
having arrays, well knowing that they may loom over my head at least as
local variables and routine arguments in some future.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:

Actually I'm in with Daniel for a change (hear, hear).
He was referring to VARRAY as a hammer, btw.
While I would not push arrays in persistent objects like tables, the
procedural(!) part of the DBMS offering which includes in DB2 SQL PL can
benefit from arrays. I'd rather bite my tongue than yell at Oracle for
having arrays, well knowing that they may loom over my head at least as
local variables and routine arguments in some future.

Cheers
Serge

Thank you, I was hoping you would comment on this. That's at least 1
beer I owe you - IOUG this year ?

[Mark Townsend]

Serge Rielau wrote:

Actually I'm in with Daniel for a change (hear, hear).
He was referring to VARRAY as a hammer, btw.
While I would not push arrays in persistent objects like tables, the
procedural(!) part of the DBMS offering which includes in DB2 SQL PL can
benefit from arrays. I'd rather bite my tongue than yell at Oracle for
having arrays, well knowing that they may loom over my head at least as
local variables and routine arguments in some future.

Cheers
Serge

Thank you, I was hoping you would comment on this. That's at least 1
beer I owe you - IOUG this year ?

[Mark A]

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:40**************@comcast.net...

Serge Rielau wrote:

Actually I'm in with Daniel for a change (hear, hear).
He was referring to VARRAY as a hammer, btw.
While I would not push arrays in persistent objects like tables, the
procedural(!) part of the DBMS offering which includes in DB2 SQL PL can
benefit from arrays. I'd rather bite my tongue than yell at Oracle for
having arrays, well knowing that they may loom over my head at least as
local variables and routine arguments in some future.

Cheers
Serge

Thank you, I was hoping you would comment on this. That's at least 1
beer I owe you - IOUG this year ?

Ok, this second time you double posted. Are you confused about something?

[Mark Townsend]

>

Ok, this second time you double posted. Are you confused about something?

You mean your newsgroup server doesn't run on RAC ? Mine does :-) We
also have dual slide projectors as well (seriously)

[Praveen]

Hi Serge,

Thanks for your time..
We have used VARRAYs as a parameters in our procedures. So, is there
any equivalent in DB2?

Thanks
praveen

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...

You can use global temporary tables with NOT LOGGED ON COMMIT RETAIN
ROWS option. If the array is not too big you will end up with one page
pinned in the bufferpool. So it's going to be I/O free.
If you are doing a smart migration this performs very well (with smart I
mean you migrate the function and not line by line (emulation)).
Ideally you want to turn loops over the array into selects and seached
updates.

Cheers
Serge

[Serge Rielau]

You're welcome. I'd love to come to IOUG since IDUG keeps rejecting my
submissions. I plan to talk about Oracle -> DB2 migrations this year.
At IDUG that's preaching too the choir.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

No. If you use c-procedures or UDF, you could simply cast the array to a
base dataype (like a varchar for bit data) and undo it in the body.
In SQL PL you have two choices:
Global temp tables or wrapping the array in another datatype.
The SET and read would best be handled with unfenced c-udf in the later
case.
How big are your arrays? Are they arrays of rows, base datatypes, which,
and are they variable size or fixed?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Anton Versteeg]

AT IOUG that is preaching to the devil? :)

Serge Rielau wrote:

You're welcome. I'd love to come to IOUG since IDUG keeps rejecting my
submissions. I plan to talk about Oracle -> DB2 migrations this year.
At IDUG that's preaching too the choir.

Cheers
Serge

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[Serge Rielau]

Which is what missionaries do ;-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PM \(pm3iinc-nospam\)]

He's just posting an array of 2 messages... ;-)

PM

"Mark A" <[e-mail]> a écrit dans le message de
news:1D*****************@news.uswest.net...

"Mark Townsend" <ma***********@comcast.net> wrote in message
news:40**************@comcast.net...

Serge Rielau wrote:

Actually I'm in with Daniel for a change (hear, hear).
He was referring to VARRAY as a hammer, btw.
While I would not push arrays in persistent objects like tables, the
procedural(!) part of the DBMS offering which includes in DB2 SQL PL can benefit from arrays. I'd rather bite my tongue than yell at Oracle for
having arrays, well knowing that they may loom over my head at least as local variables and routine arguments in some future.

Cheers
Serge

Thank you, I was hoping you would comment on this. That's at least 1
beer I owe you - IOUG this year ?

Ok, this second time you double posted. Are you confused about something?

[Praveen]

Serge,

We are using arrays of size 100 of type varchar(100). Each varray
contains arround 50 elements(string). Each row one element.

create or replace type v_loc_array is varray(100) of varchar2(100);

Serge, I didn't get about "In SQL PL you have two choices:" in your
response, Can u please give me samll example?

Thanks,
Praveen

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c0**********@hanover.torolab.ibm.com>...

No. If you use c-procedures or UDF, you could simply cast the array to a
base dataype (like a varchar for bit data) and undo it in the body.
In SQL PL you have two choices:
Global temp tables or wrapping the array in another datatype.
The SET and read would best be handled with unfenced c-udf in the later
case.
How big are your arrays? Are they arrays of rows, base datatypes, which,
and are they variable size or fixed?

Cheers
Serge

[Daniel Morgan]

Praveen wrote:

Serge,

We are using arrays of size 100 of type varchar(100). Each varray
contains arround 50 elements(string). Each row one element.

create or replace type v_loc_array is varray(100) of varchar2(100);

Serge, I didn't get about "In SQL PL you have two choices:" in your
response, Can u please give me samll example?

Thanks,
Praveen

I didn't get Serge's advice either. It didn't seem to relate to either
Oracle or answer your question about how to solve the problem now that
you are in DB2.

I suspect from the reaction to Oracle's object features you may need to
recode this as a parent-child relationship using two independent tables
with a referential integrity constraint.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

Let me explain:
Option one (I'm not 100% sure on the excact syntax):
DECLARE GLOBAL TEMPORARY TABLE v_loc_array
(idx SMALLINT NOT NULL,
val VARCHAR(100)) NOT LOGGED ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.v_loc_array(idx) WITH rec AS (recursive query to
produce as many rows as you wish) SELECT rec.idx FROM rec;

If you do not require to access by index then you can scratch the idx
column. In this case usually a lot of procedural goo simply goes away.

Then
UPDATE SESSION.V_LOC_ARRAY SET val = 'Hello' WHERE idx = 5;

Start of with that, then stare at it and rewrite obvious like combining
loops.

Option 2 (I woudl prefer C:
CREATE DISTINCT TYPE v_loc_array AS VARCHAR(11000) FOR BIT DATA
WITH COMPARISONS;
CREATE FUNCTION set(this v_loc_array, idx SMALLINT, val VARCHAR(100))
RETURNS v_loc_array
LANGUAGE C
UNFENCED -- after thorough testing and talkking to your DBA
....

CREATE FUNCTION get(this v_loc_array, idx SMALLINT)
RETURNS VARCHAR(100)
LANGUAGE C
.....

-- Not sure when UDT support was introduced.. may need to fallback
-- to VARCHAR FOR BIT DATA here depending on your version.
-- Cosmetic anyway.
CREATE PROCEDURE p1(INOUT myarray v_loc_array)
....

SET val = GET(myarray, 5);
SET myarray = SET(myarray, 5, 'Hello');
This is what you might call a "black box structured datatype".
I.e. DB2 has no clue it's dealing with an array.

Option 2 is a lot prettier to look at, but requires some C skills
Option 1 _can_ give you big benefits for optimization if you are willing
to do some optimization while porting.

On teh bright side for Option 2: You can publich on developerWorks (or
DevX) and get good money for a couple of C routines with a page of
instructions and the next posetr will not have to redo your efforts :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab