RE: SQL query optimization -- RPG400-L

This message should have explanation (in the second level text) of why
optimizer chose not to use access path in favour of sort:
      All access paths were considered for file QACAT00.

    Alexei Pytel




                      "Smith, Mike"
                      <Mike_Smith@RGCRes        To:       
"'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
                      ources.com>               cc:
                      Sent by:                  Subject:  RE: SQL query 
optimization
                      rpg400-l-admin@mid
                      range.com


                      02/11/2002 12:31
                      PM
                      Please respond to
                      rpg400-l





Ok Darren,

here is the actual select statement that is being declared.

'SELECT QCCAT, QCCACT FROM QACAT00   ORDER BY QCCAT '

here is part of the joblog when i run it through debug
 Embedded SELECT completed.
 PREPARE of statement #SEL completed.
 Unable to retrieve query options file.
 All access paths were considered for file QACAT00.
 Arrival sequence access was used for file QACAT00.
 Temporary result file built for query.
 ODP created.
 Blocking used for query.
 Cursor CTLCURS opened.


The keyfield in the file is 'QCCAT'

here is the code that builds and opens the cursor.

 *****************************************************************
 C     $CURSOR       BEGSR
 C                   IF        #POSTO > *BLANKS
 C                   EVAL      #SELCT  = %REPLACE(#WHERE:
 C                                                SQLARY(1):
 C                                                %SCAN('#':SQLARY(1)):
 C                                                1)
 C                   ELSE
 C                   EVAL      #SELCT  = %REPLACE(' ':
 C                                                SQLARY(1):
 C                                                %SCAN('#':SQLARY(1)):
 C                                                1)
 C                   ENDIF
 C                   EVAL      #SELCT2 = %REPLACE(#SORTFLD:
 C                                                #SELCT:
 C                                                %SCAN('@':#SELCT))
 C/EXEC SQL
 C+  PREPARE #SEL FROM :#SELCT2
 C/END-EXEC
 C/EXEC SQL
  +  Declare CTLCURS SCROLL Cursor  For #SEL
 C/END-EXEC

  *OPEN THE CURSOR
 C/EXEC SQL
  +  Open CTLCURS
 C/END-EXEC
  * if already opened close the cursor then reopen
 C                   IF        SQLCOD = -00000502
 C                   EXSR      $CLOSECURS
  *OPEN THE CURSOR
 C/EXEC SQL
  +  Open CTLCURS
 C/END-EXEC


-----Original Message-----
From: darren@dekko.com [mailto:darren@dekko.com]
Sent: Monday, February 11, 2002 1:21 PM
To: rpg400-l@midrange.com
Subject: Re: SQL query optimization



>Why is it not using the access path of my logical? (Mike)

>> Could be many reasons. (Gary)

Quite right Gary.  Mike, if you could paste in the actual statement and
identify the key fields in the file perhaps we could narrow down the
reasons for you.


_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.