Conference ulysse::rdb_vms_competition

Let's get Dynamic SQL out of the way first, the answer is Yes.


Now, compiled plans.  On DB2 the development process is that when you
compile an embedded SQL program a plan for that query is created.  The
plan is optimized and stored in the database system.  The DBA can examine
the plan to see if they consider it ok (ie, no security violation, no
undue performance impact) and then authorizes its use.  Once that is
done, the owner of the plan can grant execute access for that plan to
anyone they want.  People with execute access to the plan do not need any
access directly to the data, they execute the plan under the rights
granted IT by the DBA.

Realistically the DB2 approach has one real, and one questionable,
benefit.  The real benefit is that it allows an arbitrary set of users to
be given extremely controlled access to a database by only allowing
access through pre-approved plans.  The questionable benefit is that it
allows the DBA to see and semi-freeze the optimization strategy for a
query when the plan is created.  Of course, there are negative
implications here as well and the DBA has to be very pro-active in
monitoring database performance to discover when plans are performing
poorly because they have out-of-date access strategies compiled into
them,

The Rdb model is completely different.  At compile time the embedded (or
module language) SQL query is partially compiled.  It is converted into a
an intermediate form known as Binary Language Representation (BLR) that
can be quickly understood by the database system.  When an application is
run and the query is executed for the first time, a query plan for the
BLR version of the query is created and retained in memory until the
application exits.  Subsequent executions of the query, within that
application instance, reuse the query plan.

The benefit of the Rdb approach is that the database system reacts much
more dynamically to changes in the size and content of the database.  The
DBA does not have to maintain constant oversight to determine if plans
are out of date, nor does a DBA have to be involved in reviewing and
approving every query that is to run against the database.  Note that the
DBA can be involved in these things, though Rdb does not provide DB2-like
enforcement mechanisms for such a process.

From a performance standpoint the Rdb approach tends to be better for
ad-hoc queries.  The approaches are roughly comparable for frequently
accessed static queries (typical of Transaction Processing).  The DB2
approach tends to be better for static queries that are infrequently
accessed, and only accessed once or twice on the occasion they are used.

Hal

>
>    I have an RFI from a customer that I am responding to. The customer
>    asks:
>    
>        "Does your product support compiled plans, dynamic SQL or both?"
>    
>    I believe this was written by a DB2 bigot.
You are probably right about that one.

>    I don't know a lot about DB2
>    but I get the impression that compiled plans has a very special meaning
>    and is not the same as embedded SQL in compliled programs.
Right you are.

  Compiled Plans (or strategies as we call 'em), means that the strategi is
generated at program compilation time, and stored in the database.
This means that when the program runs, it never incurres the overhead of calling
the optimiser.
On the drawback side, any change in indices might invalidate the generated plan,
causing either a new plan generation at the change of metadata, or (as DB2) at
run-time the first time the program is run.

And thirdly (whether this is a drawback or benefit is hard to say) the number of
rows in tables/indices can nevr change the generated plans at run-time.

Rdb uses a slightly different technique.
The strategi (plan) is generated the first time a given user runs the program
and then the optimiser is never called again until image run-down (or rather
detaching from the database.
Note that this is done without you have to think about it.

Benefit: Whenever the program starts the optimisation is done on the latest
possible information on the database
Drawback: If the system tend to be a
   Run the program
   do a query
   stop the program
the overhead is quite dramatical.

On the other end is DYNAMIC SQL (which Rdb also supports causing another
optimisation behaviour than decribed above unless special programmng is done)
where the optimiser is called for each request that is done (Ingres and Oracle
 unless special programming is done.
This causes dramatic overhead in terms of CPU (and some I/O's).

>    I believe
>    that I have an advantage here and want to emphasize it .... so I am
>    looking for some help to make sure I interpret the question correctly.
>    Does the term compiled plan have a specific meaning in DB2?
I think the best system would support all three variations.
a)Compiled Plan (not to confuse with Stored Procedure !!)
    Least Overhead, but worst administration
    Best for large static TP systems
b)Compile the first time
    More overhead, but very little administration
    suits most purposes
c)Compile each time
    Much overhead but no administration
    needed for Dynamic SQL

>    Does DB2 have a dynamic query optimizer?
   Probably they have c) implemented IF they support dynamic SQL (but they have
to do that).

Hope this helps
Cheers
Lars

    Wow! Thanks for both great answers, they will help in my response to
    the RFI.
    
    Since I have found a some folks that know DB2, let me wear out my
    welcome and sneak in a VIDA question. My understanding is that VIDA
    executes dynamic SQL to DB2. Is there anyway to get it to use a
    compiled plan? 
    
    I almost know the answer to this has to be no, since I assume that 
    these compiled plans have a name that identifies them and since VIDA 
    is a transparent access for both the user and the programmer there 
    is no way to supply a plan name. This is just me thinking out loud,
    correct me if I am wrong.
    
    Thanks again,
    
    Sandy

No, VIDA cannot use a compiled plan.

If I make the same DSRI query repeatedly, will VIDA ask DB2 to re-parse and
re-optimize the same SQL repeatedly, or is there a way VIDA can reuse the
original query?

re .-1:

I think you'll have to track down a conference on VIDA and ask the
question there.

    re: .5  If you make the same DSRI query twice, VIDA sends two 
    dynamic SQL requests, each parsed and optimized separately.
    
    VIDA strickly uses dynamic SQL to access DB2.  We have two DB2 plans
    which we use, one for cursor stability and one for repeatable reads.
    These plans identify which tables and views are available to the 
    VIDA users.  If you wanted to, you could set up multiple plans with 
    different access rights and establish access authority using the plans.
    But even though we have DB2 plans, we don't use pre-compiled and 
    pre-optimized queries, since we are using dynamic SQL.