By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,227 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,227 IT Pros & Developers. It's quick & easy.

Scratchpad in SQL UDF

P: n/a
Is it possible to use a scratchpad in a SQL UDF? I have a partitioned
environment and therefore cannot have an external UDF that reads SQL.
My UDF needs to perform a lookup in a couple of tables. It needs to
be called in a query that will return millions of rows. Hitting the
database for row is very slow. Using a scratchpad to keep the first
database hit in memory would be the best option.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Thomas" <tg*****@hotmail.com> wrote in message
news:8d**************************@posting.google.c om...
Is it possible to use a scratchpad in a SQL UDF? I have a partitioned
environment and therefore cannot have an external UDF that reads SQL.
My UDF needs to perform a lookup in a couple of tables. It needs to
be called in a query that will return millions of rows. Hitting the
database for row is very slow. Using a scratchpad to keep the first
database hit in memory would be the best option.


As usual with DB2, the answer is "it depends".

What version of DB2 are you using? What platform are you using? What
language do you want to use for your UDF?

Rhino
Nov 12 '05 #2

P: n/a
Thomas,

Have you actually tried to do teh lookup inside the SQL UDF?
DB2 shouldt be smart enough to realize that the result will always be
the same for the duration of the state.
There are two things DB2 looks for when forcing re-evaluation of
statements in an SQL function (or trigger):
1. Changed correlation values
(shouldn't happen since otherwise you couldn't cache the result
in your desired scratchpad either)
2. R/W conflicts
Doesn't happen in your UDF since you (in general) can't write

In DB2 + DPF I would _strongly_ encourage you to attempt reducing the
SQL UDF to a single RETURN statement.
Otherwise you will nail the logic onto the coordinator.

Parallelizing logic with local variables is one tough nut to crack :-(

Cheers
Serge
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.