Re: OPNQRYF access path rebuilds & OPTIMIZATION ??

["Simon Coulter" <shc@xxxxxxxxxxxxxxxxx>]

Hello Chellaprabhu,

Read the DB2 for OS/400 Database Programming manual for more information.

1). It depends.  You can't be __SURE__.  The query optimiser's rules may change 
from release to release.  See 
appendix D.3 for general information about the rules.  Your query will result 
in the optimiser estimating 20% 
of the records in the file will be returned.  That would tend to favour the 
index if many records are in the 
file.  30,000 records is not a lot so the optimiser may choose a table scan 
although key range checking will 
again favour the index if the logical you mention is actually examined by the 
optimizer.  Put the job in debug 
mode and check the optimizer messages to see if it is used.

2). Probably.  If a table scan is chosen as the optimum access method then a 
temporary index will not be 
built.

Other things:  You are specifying KEYFLD(*FILE), ALWCPYDTA(*OPTIMIZE), and 
OPTIMIZE(*FIRSTIO).  These are 
somewhat mutually exclusive.  KEYFLD and ALWCPYDTA(*OPTIMIZE) will favour 
sorting the result set.  However, 
OPTIMIZE(*FIRSTIO) favours not sorting.  Make up your mind.  See Appendix D.5.2.

You could include an ORDERBY clause (on DDNT and ACCTNO) to try forcing the use 
of the index.

You can also use the STRDBMON command to collect statistics for the query.  See 
Appendix D.7.


Regards,
Simon Coulter.

//----------------------------------------------------------
// FlyByNight Software         AS/400 Technical Specialists
// Phone: +61 3 9419 0175      Mobile: +61 0411 091 400
// Fax:   +61 3 9419 0175      E-mail: shc@flybynight.com.au
// 
// Windoze should not be open at Warp speed.
//--- forwarded letter -------------------------------------------------------
> Date: Mon, 26 Oct 98 10:49:51 -0700
> From: Prabhu.Chella@smed.com
> To: MIDRANGE-L@midrange.com
> Reply-To: MIDRANGE-L@midrange.com
> Subject: OPNQRYF access path rebuilds & OPTIMIZATION ??

> 
> 
> 
> I have a logical something like this  ..
> 0004.00        A          K DDNT
> 
> 0005.00        A          K ACCTNO
> 
> 0006.00        A          S DDNT                      VALUES(0 100 200)
> 
> 
> And i use the OPNQRYF this way for selecting only 0, 100  DDNT records
>                                              ........ Ovrride with
> Share(*Yes) ...
> 0004.00                OPNQRYF    FILE((FLPHPA)) OPTION(*ALL) +
> 0005.00                             QRYSLT('DDNT = 0  *or DDNT = 200') +
> 
> 0006.00                             KEYFLD(*FILE) SEQONLY(*NO) +
> 
> 0007.00                             OPNSCOPE(*ACTGRPDFN)
> ALWCPYDTA(*OPTIMIZE) +
> 0007.01                             OPTIMIZE(*FIRSTIO)
>                                            ............. CALL RPG1
> 
> 1 ) In this case can i be sure that it would not rebuild the access path
> every time i call the program ?.
> 2)  And in case i remove the Selects in Logical will OPNQ build the acess
> path every time ?
> 
> 3) And is this a effective method for random file processing( First a
> screen full of records are shown , then  depending on the
> Position to Values entered , the screen is redisplayed , or if a selection
> is made from the records on the screen, you shift to
> update the record).
> 
> Generally it will be more than 20% of the records selected and the number
> of records will be more than 30,000.
> 
> Your guidance will be very helpful ...
> Thanks in advance,
> Chellaprabhu.
> 
> 
> 
> 
> 
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---
> 

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---