473,573 Members | 5,106 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL0374N on METHOD for structured-type (V8.1. fixpak 8)

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?

Nov 12 '05 #1
3 2021
_l*****@yahoo.c om wrote:
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.
Could you elaborate a bit why you think you need to run the insert/update in
the method?
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..."
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
@
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.


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
Nov 12 '05 #2
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.

Nov 12 '05 #3
_l*****@yahoo.c om wrote:
Thanks for your reply Knut.

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

<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
Nov 12 '05 #4

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

Similar topics

3
457
by: Edmond Neo | last post by:
I use structured storage to store large amounts of data in various streams. I realize that I can call structured storage through a wrapper in .NET, but I'm concerned that there is a performance issue if large amounts of data are passed through the wrapper Does anyone know what is the peformance penalty of writing streams via a wrapper to the...
2
1605
by: Ganesh | last post by:
Hi, I have some general questions about structured programming. I am trying to understand how the programs look like without structured programming? What constitute a structured program? How has it improved the programming in general? How did the programs look like before the advent of structured programming? Ganesh
3
1282
by: ambika | last post by:
Hello, I have a very basic doubt. Why is C called a structured programming language??why structured? C++ is called a Object Oriented language 'cos it obeys the OOP's concepts..Why is C called a structured programming lang?? Thanks to all those who are gonna answer.. -ambika
3
1800
by: HALLES | last post by:
HELLO ! in upper case: i mean to be heard ;o) Compilers are good ! Myself, i used TP6 and TP7 to work on dBASE V files, once ... a long time ago. I was unaware of Internet Usenet world, fool of me !
3
2742
by: R. P. | last post by:
Subject: XSLT to transform a flat XML file into a structured text file I have an XML file that lists the PDF file segment names and titles of a larger document and looks something like this: <DOCUMENT> ...... ...... some lead elements ...... <SEGMENT_LIST>
1
1698
by: peter | last post by:
Hi, I want to test using structured UDT, so I was wondering if there is some sample that give me quick understanding of what is possible for generating structured using the method specification with java.. Note, I have used structured UDTs and have generated java UDFs. I am looking at this area to address an issue I have with variable...
12
5801
by: Steve | last post by:
I have been studying the Adjacency List Model as a means of achieving a folder structure in a project I am working on. Started with the excellent article by Gijs Van Tulder http://www.sitepoint.com/article/hierarchical-data-database My database has this basic structure: Id FolderName
4
1802
by: rhino | last post by:
I've been looking at the information on Structured Types in the certifcation guide and have previously read it in the DB2 manuals. As neat as Structured Types are, I'm very curious to know if they are being heavily used in the real world? Or are they just a "nice to have" feature that no one actually uses? I'm also curious to know if anyone...
5
2331
by: George2 | last post by:
Hello everyone, I am learning set_se_translator, and there are some good resources about how to translate structured exception into C++ exception, like, http://www.codeproject.com/KB/cpp/seexception.aspx 1.
1
9619
AmberJain
by: AmberJain | last post by:
HELLO, First of all, I accept that this is a too simple question but I got different opinions in different books and so I'm posting it here...... The question is simple.....Is C a block structured language? But the answers by different authors differ like a pendulum (some say YES while others say NO to this question) For...
0
7792
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7709
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...
0
8218
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...
1
7800
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...
0
8091
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...
1
5605
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...
0
3738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2228
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
1
1330
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.