Search code examples
sqldb2user-defined-types

DB2 SQL array in attribute-defs of user defined type

I have a question concerning user defined types in DB2(v. 9.7.0.441). I want to create a type which has an attribute-array of another user defined type. Let me show you what I mean by a brief (fictional) example:

This is the UDT I want to use in another type

CREATE TYPE sport AS
(
  Sport VARCHAR(10)
) MODE DB2SQL;

This is the UDT which should use the one above

CREATE TYPE person AS
(
  plays sport ARRAY[3] // 'REF(sport)' or 'plays VARCHAR(10) ARRAY[3]' don't work either  
) MODE DB2SQL;

DB2 just says that the token ARRAY[3] is unexpected.

Any hint what could be wrong here? By now it would be enough to have an CHAR Array in a UDT...

Thanks in advance

Solution

  • Ok ok,

    according to db2's CREATE TYPE (array) statement "an array type can only be used as the data type of:

    • A local variable in a compound SQL (compiled) statement
    • A parameter of an SQL routine
    • A parameter of a Java procedure (ordinary arrays only)
    • The returns type of an SQL function
    • A global variable

    and

    A variable or parameter defined with an array type can only be used in compound SQL (compiled) statements "

    So its just not possible to use an array type inside a user defined type :-/