Running DB2 ESE V8.1.8 on WinXP.
This is Fixpak 8.

Have a structured-type and some methods for that type.
One of my methods needs to do insert / update on tables.
The type specification includes "LANGUAGE SQL...CONTAINS SQL".

But I get SQL0374N "The MODIFIES SQL DATA clause has not been
specified for the CREATE FUNCTION statement for LANGUAGE function..."

My type specification looks like (includes the method details):

CREATE TYPE my_type (...)
....
METHOD mytype_UPDATE(...signature details...)
RETURNS my_type
SPECIFIC XXX_UPDATE
SELF AS RESULT
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL

It is in the the subsequent method body, defined
as follows:

CREATE SPECIFIC METHOD XXX_UPDATE
BEGIN ATOMIC
IF (...)
THEN UPDATE...
ELSE ...
END IF
END%


that I get the SQL0374N error. There are presently no other
methods or functions explcitly created for this structured-type.

The documentation suggests that I cannot specify MODIFIES SQL DATA
on the CREATE TYPE statement where the methods get specified.

Any suggestions?

_link98@yahoo.com wrote:
[color=blue]
> Running DB2 ESE V8.1.8 on WinXP.
> This is Fixpak 8.
>
> Have a structured-type and some methods for that type.
> One of my methods needs to do insert / update on tables.[/color]

Could you elaborate a bit why you think you need to run the insert/update in
the method?
[color=blue]
> The type specification includes "LANGUAGE SQL...CONTAINS SQL".
>
> But I get SQL0374N "The MODIFIES SQL DATA clause has not been
> specified for the CREATE FUNCTION statement for LANGUAGE function..."[/color]

That's correct. CONTAINS SQL means that the method has SQL statements but
it does *not* access any tables in any way - neither reading from nor
writing to the tables/view/nicknames/aliases/... This is good for simple
calculations like in MAX() functions:

CREATE FUNCTION MAX(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
RETURN CASE WHEN a > b THEN a ELSE b END
@
[color=blue]
> My type specification looks like (includes the method details):
>
> CREATE TYPE my_type (...)
> ...
> METHOD mytype_UPDATE(...signature details...)
> RETURNS my_type
> SPECIFIC XXX_UPDATE
> SELF AS RESULT
> LANGUAGE SQL
> DETERMINISTIC
> NO EXTERNAL ACTION
> CONTAINS SQL
>
> It is in the the subsequent method body, defined
> as follows:
>
> CREATE SPECIFIC METHOD XXX_UPDATE
> BEGIN ATOMIC
> IF (...)
> THEN UPDATE...
> ELSE ...
> END IF
> END%
>
>
> that I get the SQL0374N error. There are presently no other
> methods or functions explcitly created for this structured-type.
>
> The documentation suggests that I cannot specify MODIFIES SQL DATA
> on the CREATE TYPE statement where the methods get specified.[/color]

One of the questions that comes to mind is why you want to explicitly tie
the method to a certain table. You could (and usually do) have many
different tables.

Basically, only table functions and stored procedures are allowed to modify
the data in existing tables.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

Thanks for your reply Knut.

I'm not familiar with DB2's object-relational capabilities, having
avoided them in the past for various reasons.

I have an Oracle 9 application (designed by others) that I'm trying to
migrate to DB2, and it is the object-relational aspects of that
application that is causing trouble (other parts of the application
migrated fairly easily).

In that Oracle application, the JAVA GUI invokes SQL/PL functions,
supplying a single object as a parameter, and there is a strucured type
in the database to map on to that Java object, and database tables
contain a column which can contain one of the structured types (i.e.
the objects concerned are stored in a combination of typed tables, and
regular tables where one column is a structured type).
The SQL/PL function then invokes member-procedures (also SQL/PL) for
that object /structured-type, which then perform DML on the tables. The
GUI user thus has no write-permission on the tables, and has no
knowledge of how the methods operate on the tables.



I'm considering ways to achieve similar results with DB2 v8.1 (fixpak
8), soon to be fixpak9, with the minimum amount of changes. But I'm not
sure that it's possible without redesign. I was thinking of using db2
structured types in place of the Oracle objects. The difficulty lies
with the structured-type handling in db2, the philosophy seems
different also.


Some observations, In DB2 v8.1.8 , I cannot pass a structured-type as a
parameter to an SQL/PL procedure (SQL0789N) - must decompose first. In
DB2 v8.1.8, there are length restrictions (18 bytes) on parameter-names
to methods, and it seems (in v8.1.8) that the name of a structured-type
itself is limited to 18 chars, so you cannot easily make your
structured-type attributes map 1:1 to table columns where needed. In
DB2, an SQL function can accept a structured-type as a parameter, but
neither SQL functions nor methods can modify table-contents. And while
an SQL function can call an SQL procedure (which itself might modify
data), the DB2 v8.1.8 prevents that combination with SQL0374N, which
seems consistent.

I guess that I'll have to get rid of the structured types, but wonder
if there are other options.

_link98@yahoo.com wrote:[color=blue]
> Thanks for your reply Knut.
>
> I'm not familiar with DB2's object-relational capabilities, having
> avoided them in the past for various reasons.
>[/color]
<snip>
Good strategie...
OO was en vogue of the late 90s. Along came XML....

In principle DB2 for LUW supports MODIFIES SQL DATA only in table
functions (since V8.1.4). We're kind of trying to hold the (semantic)
front there although DB2 for zOS has wandered into MODIFIES in scalar
functions in very specific contexts (like UPDATE SET clause and INSRET
VALUES clause). Either way METHODS are, at the end of the day, just
scalar functions and thus not allowed.

If you get rid of the OO-nes you'll be rewarded with a more mainstream
implementation. Obviously the more mainstream the more performance and
stability.....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab