473,320 Members | 1,993 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.

SQL Functions & the Package cache...

Hi,

V8.1 fp4a

We have been performing some performance measurements on SQL functions
and have observed the following and need to verify if this is expected
behaviour.

Our SQL Functions contain SEVERAL select statements, and it appears that the
prep time for statements where the functions are referenced, consume
a couple of seconds prep time EVERY time they are run. i.e they are clearly not
utilising the package cache.

Note, the query containing the reference to the function is identical
each time it is run, so why isn't the whole query still in the package cache?

Is this because the queries in the functions have to be recompiled everytime
they are run because there are multiple of them?

This is costing us dearly in performance and we really need to these queries to
fully utilise the package cache for online performance.

Many Thanks.

Paul.
Nov 12 '05 #1
3 2733
Paul,
SQL Functions are macro expanded into the invoking query.
There is no such concept as preping the body of an SQL Function in DB2
UDB for Multiplatforms.
If all goes right you should get caching.
Have you checked the snapshots? Cpould be be you're thrashing the cache?
Any changes to the underlying schema or environment (SET PATH, SET
SCHEMA, ....)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
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.
Nov 12 '05 #3
Paul,

Beats me. There is a safe way to verify the statement got flushed
however (and for some reason the snapshot doesn't shot what you expect).
flip on db2trc after the flush and rerun the query.
If you see functions starting with sqln.... you reentered the compiler.
If you don't, DB2 didn't flush.

Cheers
Serge

PS: If you see union'ed selects that means you are on FP4(a) and you
benefit from TPC-C. The union represents the BEGIN .. END :-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jim Newton | last post by:
hi all, i'm relatively new to python. I find it a pretty interesting language but also somewhat limiting compared to lisp. I notice that the language does provide a few lispy type nicities, but...
1
by: xixi | last post by:
i am using db2 udb v8.1 on win 64 bit, i found this on my db2diag.log 2003-11-12-13.20.05.550001 Instance:DB2 Node:000 PID:1840(db2syscs.exe) TID:3068 Appid:AC100453.G761.00F8D15749BE...
4
by: Paul Reddin | last post by:
Hi, Does anybody have any relative performance comparisons of SQL Procedures and SQL Functions? In particular, we are trying to understand the significance of the context switching involved...
2
by: xixi | last post by:
we are using db2 udb v8.1 on win64bit 2003 server, i found this in db2diag.log, our application used jdbc for connection to this remote server, why the overflow number keep increasing, what should...
4
by: xixi | last post by:
the formula for package cache hit ratio is 1 - (package cache inserts/package cache lookups), what the result would be a effective ratio? currently we have cache inserts=25, cache lookups=35, so...
10
by: datapro01 | last post by:
Running DB2 8.1.6A on AIX 5.1 We are experience package cache overflows. The high water mark for package cache is showing as 16,108,513 bytes, or approximately 3933 4K pages. The package...
0
by: shterke | last post by:
Good day, I've been monitoring a DB2 system and noticed a low package cache hit ratio, I calculated it based on the formula in the db2 information center: ...
3
by: PaulR | last post by:
Hi, I am trying to understand what invalidates Dynamic Packages in the Package Cache. By monitoring the Size of the Package Cache, it appears the following does 1. Performing a Runstats on...
2
by: Damir | last post by:
Hello! I have noticed that after (sucessfully) executing the command: FLUSH PACKAGE CACHE DYNAMIC the dynamic SQL statement cache is not completely cleared (some of the dynamic SQL statement...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
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: 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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.