SQL0270N How to create Java UDF with SQL?

jorge

I had just recently installed a new database with partitions.
I realized that all of my previous Java UDFs failed to
install on the new database.

Basically, I got SQL0270N with Reason code 59.

I read up on the documentation, and found out that I can't
have SQL in my Java UDFs and Stored procedures anymore? Is
that really true?!?

If so, is there any way of getting around it? My company
has invested a lot of effort into writing this UDFs. It's a
bummer to find out that they won't work anymore.. :(

Any help would be greatly appreciated.

Thanks,
Jorge jk******@ingrian.com

Nov 12 '05 #1

Serge Rielau

jorge wrote:

I had just recently installed a new database with partitions.
I realized that all of my previous Java UDFs failed to
install on the new database.

Basically, I got SQL0270N with Reason code 59.

I read up on the documentation, and found out that I can't
have SQL in my Java UDFs and Stored procedures anymore? Is
that really true?!?

If so, is there any way of getting around it? My company
has invested a lot of effort into writing this UDFs. It's a
bummer to find out that they won't work anymore.. :(

Any help would be greatly appreciated.

Thanks,
Jorge jk******@ingrian.com

There is no limitation on stored procedures.
In DPF CALL and SQL inside external UDF is restricted.
You may be able to use SQL UDF, but in DPF I'd go light on thsoe
fellows. Procedural flow and DPF are no friends
(just having RETURN is OK. It's BEGIN ATOMIC ... END that gets cumbersome.)
What are these UDF doing?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

jorge

Thnaks Serge, for the valuable information.

The external UDF that we have is actually very simple. We have a table
that contains login information to a network appliance. This external
UDF
(written in Java) would first run a SELECT on the login table to
extract
login information. Using this information, it will then connect to the
network appliance and execute some requests. It also performs some
caching of the login information too, so subsequent execution of the
UDF wouldn't have to run the select.

So seems like we have to re-write some of the code. Any idea on how
best we can approach this? Keep in mind that we are just doing one
select; no update no insert.. :)

Any suggestions would be greatly appreciated.

Thanks,
Jorge

Nov 12 '05 #3

Serge Rielau

jorge wrote:

Thnaks Serge, for the valuable information.

The external UDF that we have is actually very simple. We have a table
that contains login information to a network appliance. This external
UDF
(written in Java) would first run a SELECT on the login table to
extract
login information. Using this information, it will then connect to the
network appliance and execute some requests. It also performs some
caching of the login information too, so subsequent execution of the
UDF wouldn't have to run the select.

So seems like we have to re-write some of the code. Any idea on how
best we can approach this? Keep in mind that we are just doing one
select; no update no insert.. :)

Any suggestions would be greatly appreciated.

Thanks,
Jorge

CREATE FUCNTION outerfoo(blah1, .. blahn) RETURNS blurb
RETURN innerfoo((SELECT .....blah1.), blah2, ...blahn)

or RETURN SELECT innerfoo() FROM ..., (SELECT ....)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #4

jorge

Serge,

Thanks a lot for the quick response. That's a great idea.. Just
curious.
We are a 3rd party database application vendor.. I am wondering what
percentage of the DB2 customer uses the DPF feature? I guess you
can't really give me a definitive answer, but from answering questions
on a forum like this, would you say it's 50% or 5%?

I need to decide how quickly I need to move forward on this
migration. :)

Thanks again.
Jorge

Nov 12 '05 #5

Serge Rielau

jorge wrote:

Serge,

Thanks a lot for the quick response. That's a great idea.. Just
curious.
We are a 3rd party database application vendor.. I am wondering what
percentage of the DB2 customer uses the DPF feature? I guess you
can't really give me a definitive answer, but from answering questions
on a forum like this, would you say it's 50% or 5%?

I need to decide how quickly I need to move forward on this
migration. :)

Thanks again.
Jorge

DPB is a feature aimed at BI, not OLTP.
And DPF makes more sense as you approach the TB range.
Because of that there is a quite natural cap.
In terms of number of installations we are talking a small number
compared to the total. In terms of money for IBM its a significant number.

So, take a look at your app and your target audience.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #6