RE: SQL Syntax -- MIDRANGE-L

Joe, I hear you and feel your pain.  <g>

SQL has the tremendous disadvantage that it is a general-
purpose tool. The optimizer, which is what determines
how a statement will be executed, makes all kinds of
assumptions, based on the way a statement is written.
Different optimizers do better for some kinds of
statements. And there is extensive work going on now
(and starting to show up in V5R2) to make iSeries SQL
run better - approaching competitive product speeds in
complex queries was a goal that, I hope, will get there.

See inline
> > 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.

And I know this was done at a large bank in Minnesota some years ago - stupid 
even gets into production sometimes.

> 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.

It takes awhile to become fluent in any methodolgy - SQL is new enough to us in 
the 400 world that it feels more daunting for now.

> > 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.

Did you see the graphical presentation? Is it helpful?

> > 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.

Generally, that's right. Again, it is the general nature of SQL that makes it 
more difficult to tune. Convenience in programming almost always in an inverse 
relationship with performance. Or, end-user ease-of-use is in an inverse 
relationship with developer effort.

> > 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.

If I may put in a plug, we (Centerfield Technology) have long had a set of 
products that address some of these concerns. Things that can recommend indexes 
(and build, if you choose), a Visual Explain-like tool (it came out before the 
OpsNav one) that lets you adjust all the exposed knobs in QAQQINI, and other 
areas, and you will see what the potential (I know, you never know for sure, 
right?) benefit is.

Thanks

Vern