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

DB2 UDF as module (was:DB2 UDF timeout)

P: n/a
Hello,

[this is the followup of the "DB2 UDF timeout"
thead - i've decided to replace the subject
though because of the new topic]
A note: by thread i always mean the worker thread on which SP is
running on.

ok, if there is no UDF execution timeout thing what about the following
theme:

Treat UDF as module ie once it is started it enters potentailly
endless loop and waits for new db oriented "tasks" via some system
wait object like semaphore, gets input for job via, for example, shared
mem and
returns the result via shared mem too.
>From the CLI view the UDF is always running while in reality
the UDF's thead will enter wait state waiting for new work task from
time to time.

So will db2 will be still healthy with this theme?

another question - on whole db2 engine shutdown will the current
UDF's statement against db will be aborted with error code like
QUERY_ABORTED - to give the chance for the dll to free allocated
resources to unload cleanly?

If yes, then why not to extend a "SQLUDF_DBINFO" context struct that is
passed to UDF routine - it will be great to add a volatile variable to
it say:

volatile int is_closing;

which becomes "1" when the db2 engine enters some sort of "closing"
state for at least several seconds for all aware UDF to end.
regards, dmitry

Sep 11 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
dm***********@mail.ru wrote:
A note: by thread i always mean the worker thread on which SP is
running on.
You shouldn't consider it as a "thread". DB2 may execute an external
routine (UDF or SP) in the main thread, i.e. there don't have to be other
threads in the same process. Or do you mean "sessions"?
ok, if there is no UDF execution timeout thing what about the following
theme:

Treat UDF as module ie once it is started it enters potentailly
endless loop and waits for new db oriented "tasks" via some system
wait object like semaphore, gets input for job via, for example, shared
mem and
returns the result via shared mem too.
That sounds like a typical daemon process. I would start only it through a
UDF but not have it run inside a UDF.
>>From the CLI view the UDF is always running while in reality
the UDF's thead will enter wait state waiting for new work task from
time to time.
From DB2's point of view, the UDF _is_ always running. It doesn't matter if
the UDF sleeps in between. In fact, any UDF is forced off the processor
when the current time slice expires. So you have a forced sleep. What
does matter is that DB2 doesn't get the control back from the UDF.
So will db2 will be still healthy with this theme?
I'm not sure. One issue I see with that, however: In order to invoke the
function, DB2 will have to
- an open session running
- a transaction initiated
- do some lookups on the catalog for function resolution

All this allocates resources that are never freed since you don't intend to
regularly terminate the UDFs processing.
another question - on whole db2 engine shutdown will the current
UDF's statement against db will be aborted with error code like
QUERY_ABORTED - to give the chance for the dll to free allocated
resources to unload cleanly?
This is not only relevant on engine shutdown. A simple FORCE will already
have to be dealt with.

The control is currently inside the UDF. DB2 doesn't go out of its way to
somehow send information to the UDF the way you describe.

The assumption is that DB2 can unload the library safely with "dlclose".
(The library is loaded with "dlopen".) DB2 keeps track of loaded libraries
and only unloads them if they are not in use. So that means, a UDF should
always properly cleanup after itself on each call or in the last call (in
FINAL CALL has been used).
If yes, then why not to extend a "SQLUDF_DBINFO" context struct that is
passed to UDF routine - it will be great to add a volatile variable to
it say:

volatile int is_closing;

which becomes "1" when the db2 engine enters some sort of "closing"
state for at least several seconds for all aware UDF to end.
That would be possible.

But the real question is: what would that be good for? What exactly do you
want to do? Maybe there are other solutions available already?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 11 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.