Is there a nested table kind of user defined type in DB2? For example, in oracle I can define a new type as a nested table of integers as:

CREATE TYPE table_type AS TABLE OF INTEGER;

and then use it in a table definition as:

CREATE TABLE R(
a INTEGER,
b table_type)
NESTED TABLE b STORE as b_1;

Can I do something like this in DB2? I am using DB2PE v8.1.3.132 with fixpack 3.

Thank you,
Laura

try to get the Oracle to DB2 UDB Conversion Guide (sg247048) redbook... a link can be found in one of the sticky threads.

there is a discussion in that book regarding the conversion nested tables and arrays

hope that helps

I guess your question should sound rather like this: "I want to accomplish this and that. In Oracle I would use nested tables to do that. What's the best way to do the same thing in DB2?" :-)

Not knowing what is the purpose of using "nested tables" I can only suggest reading about structured user defined types and typed tables in the DB2 SQL reference - may be that's what you need.

romeo, thank you for the pointer. I checked it out, and apparently, there is no equivalent to nested tables in DB2.

n-i, you are right, that should have been the correct phrasing in a normal oracle->db2 migration situation. the problem though is that i'm working on a research prototype, and oracle's nested table were exactly what we would have needed... there was a question of whether or not we could implement our prototype on top of some other RDBMS (DB2 for example) without too much hassle (that is, if the schema design remains the same – perhaps with minor changes- we don’t have to rewrite all our algorithms). That's why I was interested if DB2 supports nested tables as such, and hence the way I phrased my question.

Laura

See, the problem is that very few people attending this discussion know what a "nested table" is. We may be familiar with DB2 but not with Oracle. A concept that Oracle calls "nested tables" may exist in DB2 but most likely it will have a different name. This is why I suggested you stating the problem, not the solution.

As far as I know, the "Nested Table" structure in Oracle, is a ORDBMS property. It is, by my knowledge not possible in DB2. May be an option in v. 8 though.

---

Regards

Filip Poverud