× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.







As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.