467,134 Members | 948 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Scratchpad in SQL UDF

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
  • viewed: 2199
Share:
2 Replies

"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
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.

Similar topics

6 posts views Thread by Bob Sparks | last post: by
4 posts views Thread by Rhino | last post: by
5 posts views Thread by claus.hirth@abraxas.ch | last post: by
reply views Thread by null_port@hotmail.com | last post: by
6 posts views Thread by Larry Menard | last post: by
2 posts views Thread by Larry Menard | last post: by
6 posts views Thread by Rhino | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.