DB2 locking UDF dll

[W Gemini]

Meg wrote:

> Hello,
> I'm working on coding a UDF for DB2. Everything is (finally) set up
> and working ok. But I'm changing the actual c-code for the function
> as I go. Once I compile and link the project into a .dll, I copy the
> dll into sqllib/function. However, the problem is that once I run
> that function from DB2 in order to test thigns out, the dll in
> sqllib/function is marked as in use. Because of this, I can recompile
> the dll but can't copy it into sqllib/function without first rebooting
> in order to get DB2 to "let go" of the dll.....should I be
> disconnecting from the database or something? all i'm doing is
> connecting to the database and then using VALUES() to test the
> function...
> thanks in advance,
> meg

Which version of db2 are you using? For v8, try set KEEP_FENCED to no
(db2 update dbm cfg using KEEP_FENCED NO). This way, the fenced mode
process would terminate everytime you disconnect. However, you will
suffer a significant performance penalty, so this should only be done on
your development machine and once the udf is finalized, turn that flag
back on.

Another alternative is to use ALTER FUNCTION to change the external name
to point to a different library.

[Fan Ruo Xin]

>
> Which version of db2 are you using? For v8, try set KEEP_FENCED to no
> (db2 update dbm cfg using KEEP_FENCED NO). This way, the fenced mode
> process would terminate everytime you disconnect. However, you will
> suffer a significant performance penalty, so this should only be done on
> your development machine and once the udf is finalized, turn that flag
> back on.
>

That’s a good point. In V8, there are more things you should take care:

====
If keepfenced is set to no, and the routine being executed is not
threadsafe, a new fenced mode process is created and destroyed for each
fenced mode invocation. If keepfenced is set to no, and the routine being
executed is threadsafe, the fenced mode process persists, but the thread
created for the call is terminated. If keepfenced is set to yes, a fenced
mode process or thread is reused for subsequent fenced mode calls. When the
database manager is stopped, all outstanding fenced mode processes and
threads will be terminated.
=====

We can see even you set KEEPFENCED as NO, but the UDF routine is THREADSAVE
(default). That means even all the connections who called fenced UDFs are
terminated, the fenced mode process still keep alive . So you can kill this
process – db2fmp, or the better and safer way is you shutdown the db2
server.


Regards,
FRX

[W Gemini]

Fan Ruo Xin wrote:

>

>>Which version of db2 are you using? For v8, try set KEEP_FENCED to no
>>(db2 update dbm cfg using KEEP_FENCED NO). This way, the fenced mode
>>process would terminate everytime you disconnect. However, you will
>>suffer a significant performance penalty, so this should only be done on
>>your development machine and once the udf is finalized, turn that flag
>>back on.
>>

>
>
> That’s a good point. In V8, there are more things you should take care:
>
> ====
> If keepfenced is set to no, and the routine being executed is not
> threadsafe, a new fenced mode process is created and destroyed for each
> fenced mode invocation. If keepfenced is set to no, and the routine being
> executed is threadsafe, the fenced mode process persists, but the thread
> created for the call is terminated. If keepfenced is set to yes, a fenced
> mode process or thread is reused for subsequent fenced mode calls. When the
> database manager is stopped, all outstanding fenced mode processes and
> threads will be terminated.
> =====
>
> We can see even you set KEEPFENCED as NO, but the UDF routine is THREADSAVE
> (default). That means even all the connections who called fenced UDFs are
> terminated, the fenced mode process still keep alive . So you can kill this
> process – db2fmp, or the better and safer way is you shutdown the db2
> server.
>
>
> Regards,
> FRX
>
>

THREADSAFE is not the default for C UDFs. But I agree, the best way is
to shutdown the db2 server which would bring down all fmp processes that
are current running.