| re: SQL Functions & the Package cache...
Serge,
Thanks again for your reposnse.
I think all is becoming clear now...
Today we actually looked at the "optimiser rewritten" SQL of one of
our SQL functions, from within Visual Explain. This also made it all
much clearer.
The optimiser rewrites all the conditional logic into pure SQL, i.e
union'd selects etc..., and so what we thought was being processed
with conditional logic around multiple statements, is actually one big
select internally.
So, now we fully understand the implications on the function simply
becoming part of the calling statement.
As a result, I also looked a bit more deeply into the package snapshot
information (As you suggested) and actually saw something very
confusing...
If I execute a query and perform
db2 "get snapshot for dynamic sql on jbsukd17"
I see the query, and if I run it multiple times, I see the "number of
executions" increment and "number of compilations" remain at 1 -
exactly the behaviour I would expect.
However, if I now execute
db2 "flush package cache dynamic"
I would expect a couple of things to happen, (neither of which does)
1. The snapshot to maybe return nothing - it doesn't, everything is as
before
the flush ??
and/or
2. The "number of compilations" to be incremented on the next running
- it isn't.
So, it appears that the flush isn't actually flushing anything,
because no
recompilation is taking place and nothing is disappearing from the
snapshot
information ?? any ideas?
Thanks.
Paul.
| 
