473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
2 2364

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Bob Sparks | last post by:
Can some one post an example, or where to find one, using DBINFO in a SQL procedure? I can't seem to find one anywhere. I don't see the linkage between the structure and the SQL language variables...
4
by: Rhino | last post by:
I've been playing with Java UDFs for the last couple of days and I've got some questions about scratchpads. I'm running DB2 LUW V8 (FP8) on WinXP. Somewhere in the manuals, I found some remarks...
5
by: claus.hirth | last post by:
If I create the function HELLO in schema S01 as follows, @ CREATE FUNCTION S01.HELLO() RETURNS VARCHAR(32) EXTERNAL NAME 'UDFSRVXYZ!sayHelloWorld' LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NO...
0
by: null_port | last post by:
Regarding Peter Bromberg's article on saving web pages as mht's using CDO: http://www.eggheadcafe.com/articles/20040527.asp I've been having fits when calling his sample code:...
6
by: Larry Menard | last post by:
Folks, I know that DB2 does not (yet?) support this, but I wonder if anyone can suggest a work-around. I've seen article...
2
by: Larry Menard | last post by:
Folks, I've got a java UDF that uses a scratchpad (based on Knut's dW article on user-defined aggregate functions). The problem is that when it tries to read from the scratchpad on the first...
6
by: Rhino | last post by:
I'm trying to debug a simple Java UDF written in the DB2General style within Eclipse. I'm getting a java.lang.UnsatisfiedLinkError when I execute the set() method in the UDF. I know that the...
2
by: reppisch | last post by:
Hi Ng, i tried to instantiate a template class in a different compilation unit than it is implemented. It seems while linking it together the linker does not call the compiler to generate the...
18
by: E11esar | last post by:
Hi there. I have written an asp.net / c# web application that subscribes to a web service and all works fine. If I then enclose the page functionality of the web page within AJAX tab and accordion...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.