473,890 Members | 1,354 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Equivalent of VARRAYs in DB2

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
Nov 12 '05
20 6497
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_a rray(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_A RRAY 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
Nov 12 '05 #21

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
7374
by: John | last post by:
Is there an equivalent of COM on Linux that I can get through Python. My need is to have some sort of language independent component framework. I can think of CORBA but I have to have a server running. I prefer not to. I just need Python components for local consumption in other languages. I remember Gnome libs having some thing like this. Any thoughts?
2
3284
by: Michael Foord | last post by:
Please pardon my ignorance on this one - but I'm not certain how the sign bt is treated in python bitwise operators. I've trying to convert a javascript DES encryption routine into python. Javascritp has >>> and >>. >>> is a zero fill bit shift whereas >> is a sign propagating bit shift. My understanding is that the python >> is equivalent to the javascript >> - but python has no equivalent to >>>. Would a >>> 3 in javascript be...
3
2305
by: Robert Dodier | last post by:
Hello, Here's a thought that I'm sure has already occurred to someone else, I just can't find any record of it yet. An XML document is just a more verbose and clumsy representation of an ordinary Lisp S-expression. So it's easy enough to translate some XML into equivalent Lisp. Now I turn it over to the Lisp parser, which creates the equivalent of the DOM for me.
1
3838
by: Vannela | last post by:
Is there any equivalent control in .NET for the Power builder DataWindow control? I am explaining the Datawindow architecture to some extent. Power Builder DataWindow Control has got different presentation styles and different data sources. Presentation styles like tabular format , graph format, grid format, freeform format, Composite format(somthing
0
1998
by: richasaraf | last post by:
Hey Guys !!!! Please help me out....... i have to make a small application using VARRAYS in Oracle 9i.... Can you guys suggest me something good....... Thanks in advance... and hoping for a quick suggestion from all you guys ! :)
7
3733
by: Tim Conner | last post by:
Hi, I am an ex-delphi programmer, and I having a real hard time with the following simple code (example ): Which is the equivalent to the following code ? var chars : PChar; sBack, s : String;
10
7341
by: karch | last post by:
How would this C# contruct be represented in C++/CLI? Or is it even possible? PolicyLevel level = (enumerator->Current) as PolicyLevel; Thanks, Karch
9
4059
by: Alan Silver | last post by:
Hello, I'm converting some old VB6 code to use with ASP.NET and have come unstuck with the Asc() function. This was used in the old VB6 code to convert a character to its ASCII numeric equivalent. Is there such a function available in C#? I can see that VB.NET has one, but I couldn't see how to get at it in C#. For example, if I have ...
14
2563
by: grid | last post by:
Hi, I have a certain situation where a particular piece of code works on a particular compiler but fails on another proprietary compiler.It seems to have been fixed but I just want to confirm if both statements are similar : *((char **)v)++ == *((char **)v++) Where v is a pointer to an array of characters,defined as char *v;
0
9829
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10836
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10926
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9643
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6064
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4683
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.