a trouble of modifying procedure

[heming_g]

i replace the program file in $db2path/sqllib/function with a new one
,
and call the procedure . it turns out to be the old one , since i
print a massage for notice .
so , now . i have to change my program file name , every time i
modified the procedures .
what's wrong ?

[Shyam Peri]

The behavior which you have been observing, could be valid if the
database manager keeps the fenced processes thread safe.
And if the KEEP_FENCED is enabled, the the fenced mode
processes/threads would be resued for subsequenced calls, and
termination of such processes/threads happens only when database
manager is stopped.

[heming_g]

what benefit would have to be , when KEEP_FENCED is enable . and i
want to make it disabled

[Shyam Peri]

The benefit u would have is by enabling the caching fenced mode
processes is, u would not incorporate the over head of creating &
destroying the processes at every transaction.

[heming_g]

.. . i change the keepfenced to no .
but it still does not work.

[Serge Rielau]

he******@hotmail.com wrote:

. . i change the keepfenced to no .
but it still does not work.

http://www-1.ibm.com/support/docview...id=swg21006542

It appears you may need to DROP the object from DB2...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Knut Stolze]

he******@hotmail.com wrote:

. . i change the keepfenced to no .
but it still does not work.

Is this a trusted (not fenced) or a fenced procedure? KEEP_FENCED=YES only
helps with fenced routines. In the other case, DB2 caches the libraries
for external routines in the hope that it will be needed soon again. This
caching is interfering here.

In such cases, I usually restart DB2. But life might not be that simple for
you. You should try to force the respective agent to get it destroyed and,
thus, the library unloaded.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[heming_g]

ye ,.. finally i finished the debug by changing the filename :( ..
it's awful, from ~00 to ~09 ..
but it's more faster than restart . in my situation

[Brian Tkatch]

You need to DROP and CREATE the function, or at least, GET/PUT. Just
changing the file on disk will not tell the database that new code is
there.

B.

[Knut Stolze]

Brian Tkatch wrote:

You need to DROP and CREATE the function, or at least, GET/PUT. Just
changing the file on disk will not tell the database that new code is
there.

This is not true!! You don't need to drop/create the function as this is a
particular troublesome issues if other objects (views, triggers, functions,
procedures, packages) depend on the function. The only thing you have to
ensure is that the cached version of the library is not used any longer,
and ways to accomplish that were already discussed.

I'm not quite sure if changing the external name via ALTER FUNCTION ...
EXTERNAL NAME '...' and using the same library as before would help as
well. I rather doubt it, however, and using a different library name as
the OP did would be the safest way.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

Ah, ok, then i completely misunderstood how it works. Though, i find it
awkward, if not even scary, that a file on disk can be changed while
the database is up and will affect the functioning of the DB itself.

Could he not just use a dunmmy function with a different filename to
use for ALTER FUNCTION..EXTERNAL NAME, and then switch it back to the
same name?

B.

[Knut Stolze]

Brian Tkatch wrote:

Ah, ok, then i completely misunderstood how it works. Though, i find it
awkward, if not even scary, that a file on disk can be changed while
the database is up and will affect the functioning of the DB itself.

Could he not just use a dunmmy function with a different filename to
use for ALTER FUNCTION..EXTERNAL NAME, and then switch it back to the
same name?

Might work - but only when the library is unloaded.

External routines are implemented in shared libraries. (Windows calls those
DLLs.) To improve performance, DB2 will load the library once and keep it
loaded as long as it is still used. The five most recently used libraries
will be cached anyway (there is a nice description from Sean to be found
here: http://tinyurl.com/d89dx).

And this caching is what you need to deal with. The library management
unloads all libraries (except the 5 most recently used ones) every 10
minutes. So you could force 5 dummy libs to be loaded and then wait 10
minutes.

So far, this has not been a serious issue. Granted, it's rather annoying if
you do development, but in that case you can recycle the instance anyways.

Replacing the library files that is in use is (a) not an issue under Windows
because the OS doesn't allow you to do that (which I don't like a bit), and
(b) only the DBA can do that. So I don't see a problem there either. This
is just a normal operating system thing that can be done with each program
that loads stuff dynamically at run time.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany