RE: SQL Syntax

> From: jpcarr@tredegar.com
>
> As you said earlier,  you can write bad native DB2 accesses too.
> Instead of a chain,  I could read the whole file comparing the
> key value to the one just read.
> That would be stupid.

Yeah, but this is similar to Leif's security issue.  One is a programming
flaw - one that can be fixed by the application programmer.  The other is
"under the covers", and requires some sort of SQL troubleshooting to
identify.  Perhaps those are the same to you, but they're not to me.
Because to do the latter, everybody in my shop has to become an SQL
performance expert.


> I didn't say I didn't know, I just didn't know with the info you
> provided.   Go into Ops Nav play with the visual explain. it will
> show/tell you every step of the way what is being built and why.

Not to my eyes, and I'm usually pretty good at reading dense, obtuse log
files.  After wandering around for a bit in OpsNav (how cool - an additional
PC required to debug my code!), I found the Visual Explain.  Nothing in it
told me I was doing anything wrong.  Each contains hundreds of lines with
information such as "Derived Selection Performed YES".  Hmmm.  That's pretty
intuitive.

So I decided to compare the results, one with the logical view in place and
one without.  Since OpsNav doesn't allow me to dump the contents of the
files to an output file, I had to manually review them.  Hundreds of lines.
But I dogged along and found out the crucial difference:

The slow one (without the logical view) says:

ODP Implementation NONREUSABLE
Dynamic Replan Reason Code ACCESS PLAN WAS NOT REBUILT

as opposed to the fast one (with the logical view):

ODP Implementation REUSABLE
Dynamic Replan Reason Code A NEW INDEX EXISTS OVER A TABLE IN THE QUERY

Well, there it is plain as day!  My ODP implementation was NONREUSABLE!
Heck, I shoulda known that.

Yes, I'm being a bit sarcastic, but really, unless you're an SQL expert, how
in the world aere you going to find, among the hundreds of lines of
gobbledygook in the Visual Explanation, the reason why your SQL syntax is
bad?

Finally, just for fun, I went back to my original syntax, the one that was
fast, expecting to see that it was REUSABLE.  And you know what?

It says:

ODP Implementation NONREUSABLE

Huh?  But... but... NONREUSABLE was what made the first one slow.  Or so I
thought.  What the heck is going on here?  Oh wait, the reason code is
different:

Dynamic Replan Reason Code PLAN REBUILT DUE TO SYSTEM PROGRAMMING CHANGES

Evidently NONREUSABLE with PLAN REBUILT DUE TOP SYSTEM PROGRAMMING CHANGES
is better than NONREUSABLE with ACCESS PLAN WAS NOT REBUILT.  Another leff
than intuitive result.  And still, nothing to really explain why one version
take 50 times longer than the other, even though both were NONREUSABLE.

Besides that, how could I have known just from the information in the first
one that it was wrong?

If this doesn't explain my frustration with SQL, then nothing will.  Perhaps
you think this is a normal way to program - write statements, find the ones
that don't perform well, then dig through the SQL performance monitor to
find out which syntax is correct.  Me, I'd much rather have a decent
database and somebody who still remembers how to program.


> With SQL you could add an Index and "It's not stupid anymore"
> with my "read the whole file" scenerio,  "It's stupid for life"

I think I touched on this.  With SQL, even s amart programmer can do stupid
things without knowing it.  With native DB2, it requires aggressive
stupidity to do something that bad.


> The debug info gives suggestions as to what would make it run
> faster.   No message will tell you how to make Native Methods faster.

If you need a debugger to tell you how to write your code, you might want to
find a new line of work.


> I use Native DB2 methods everyday for the last 20 years,
> I also use and really appreciate what SQL can give me
> for adhoc DB searches/retreivals

Here we agree.  SQL is unparalleled for ad hoc.  But native is better for
production in many cases.


> PowerSaw, HandSaw,   I have both in my toolbox and use both.

Obviosuly I wouldn't be asking these questions if I weren't trying to use
SQL.  Unfortunately, it's less a power saw than a nuclear-powered laser
torch with random yields.


> Remember also;   SQL = No Level Checks

For those that think this is a good thing, then I guess that's a plus.

Joe