RE: Creating SQL Index

[Kurt Anderson <kurt.anderson@xxxxxxxxxxxxxx>]

Well, I'm sufficiently happy with where I got so far.  I changed the fetch to do one row at a time (doing multiple rows didn't seem to make sense when I update based on the cursor).  I did end up with a time savings, so I got what I was hoping for.

I've saved the indexing link for when I have a little more time (and also as a reference), thanks.

Thanks for the help,
Kurt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, June 23, 2010 8:43 AM
To: Midrange Systems Technical Discussion
Subject: Re: Creating SQL Index

Yes the WHERE is more important than the ORDER BY.

Consider that without an index to use for the WHERE, the system will
have to look at the entire table.

But for the ORDER BY, if a suitable index doesn't exist it only has to
build one for the subset of records selected by the WHERE.

If you haven't read this, you need to.
"Indexing and Statistics Strategies for DB2 UDB for iSeries"

https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/servers/enable/site/bi/strategy/index.html

Lastly, just because there's a lot of "stuff" going on after the read
doesn't mean you can't use Option #1.

Consider one or more User Defined Functions (UDFs) possibly User
Defined Table Functions (UDTF) that would do the "stuff" and
optionally return values you need in to update the record with.  ie:

   Update CdrMstP a
         Set StmDte = DetermineStatementDate(Cust#),
             Corp# = AssignCorp(Cust#)
             Loc# = AssignLoc(Cust#)
             Dept# = AssignDept(Cust#)
             PrtFlg = DeterminePrintFlag(Cust#)
             SvPrtFlg = DetermineSvPrtFlg(Cust#)
        Where CalDte <= :CutOff
              and StmDte = '0001-01-01'



HTH,
Charles


On Tue, Jun 22, 2010 at 6:59 PM, Kurt Anderson
<kurt.anderson@xxxxxxxxxxxxxx> wrote:
> So it's the Where statement I need to pay attention to in addition to the Order By when determining which fields to make the key fields in my index?
>
> I gave #2 a try and it worked.  I can't use #1 because after the read there is a lot of stuff happening before the update.
>
> I changed the Select to also have:
> For Update of StmDte, Corp#, Loc#, Dept#, PrtFlg, SvPrtFlg
>
> Although in doing this it was no longer fetching for 250 like my statement requests, and instead was fetching 2 at a time.
>
> -Kurt