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


  1.  Home
  2. RPG400-L
  3. July 2004

RE: SQL embeded in RPG with passed parameters won't use indexes

Is there a link or is it available only in hardcopy?  I'd like to give it a 
look.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Haas, Matt
Sent: Thursday, July 08, 2004 4:13 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes


There's a database book that explains how of this happens if you really want to 
know. When I started doing embedded SQL, it was recommended to me that I read 
it (which I did -- or at least most of it) and it was very helpful in 
understanding how to tune both the SQL statements and the indexes since it 
explains how the query optimizer works.

Matt

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Thursday, July 08, 2004 5:00 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes


And I am not so sure if it's the first execution, or every execution.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





<Rick.Chevalier@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
07/08/2004 02:47 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL embeded in RPG with passed parameters won't use indexes






Rob,

You might be right about 'first execution'.  I had forgotten, but we had a 
similar occurrence in our shop a couple months back.  A production program 
changed but no one could find records in change management (Aldon) showing 
a promotion.  After a call to IBM it was determined that the program had 
embedded SQL and the optimizer was updating the access plan information. 

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
Sent: Thursday, July 08, 2004 1:18 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes


He speaketh the truth about it being attached to the program.  However I 
am not so sure it's at compile time versus 'first execution'.  Here's my 
train of thought.  We have a change management system (Soft Landing's 
Turnover).  Very often we get the warning "This program has been changed 
outside of Turnover."  And almost always it's an embedded sql program that 

attached the access plan to the program.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





<Rick.Chevalier@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
07/08/2004 01:01 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL embeded in RPG with passed parameters won't use indexes






My understanding is that it would only be created at run time if the 
statement is dynamically prepared.  That's why I wasn't sure it would 
apply in your case. 

Having a static statement (even though it contains variables) allows the 
pre-compiler to create the access plan and attach it to the program at 
compile time providing a performance boost.

I'm sure there are others out there who could provide a more in depth 
description.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne
Sent: Thursday, July 08, 2004 12:03 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes


Rick,
                 An earlier suggestion solved the problem, but I am 
interested in
your statements about preparing the statement. It seems like the access 
plan
for my program is built at run time even without a prepare statement, at
least judging from the job log, do you know under what conditions the 
access
plan is built at compile time?

Chris 

-----Original Message-----
From: Rick.Chevalier@xxxxxxxxxxxxxxx 
[mailto:Rick.Chevalier@xxxxxxxxxxxxxxx]

Sent: Thursday, July 08, 2004 9:40 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL embeded in RPG with passed parameters won't use indexes

Chris,

I may be off base here but try preparing the statement before declaring 
the
cursor.  I suspect that the pre-compiler can't generate the access plan
properly at compile time.  Changing the access plan generation to happen
during your program by using a prepare might cause it to generate a better
access plan.

I know the prepare is not required with the way you are handling the SQL
statement currently, but it might make a difference.

HTH,

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne
Sent: Wednesday, July 07, 2004 1:10 PM
To: 'rpg400-l@xxxxxxxxxxxxx'
Subject: SQL embeded in RPG with passed parameters won't use indexes


Hello,
                 I have an RPG ILE program in which I embedded a cursor. I 

created a
index on date (dtmv95) for the cursor to hit when I open the file. It 
worked
like a charm. But then I changed the date range in the cursor from being
static to being brought in by a prompt. Now my program will not use my 
index
when it opens the cursor, it fails with reason code 6 which means that 
"The
keys of the access path did not match the fields specified for the join
criteria."  I think that the optimizer is not 'seeing' the date criteria 
in
my select because they depend on variables, but I have no idea what to do
about it. Does anyone out there have any experience with this problem?

Below is the code

H  dftactgrp(*no) actgrp(*caller) 
dmain             pr                  extpgm('labor') 
d startdate                     15p 5 
d enddate                       15p 5 
 
dmain             pi 
d startdate                     15p 5 
d enddate                       15p 5 
 
d thearray        ds 
d cono                           2a 
d item                          16a 
d pdate                          7p 0 
d labor_$                        6p 0 
d shift                          2p 0 
d pgmn                           1p 0 
 
D dclcursorC1     pr 
D opencursorC1    pr 
D fetchcursorC1   pr              n 
D UpdateDirect    pr              n 
D UpdateIndirect  pr              n 
D UpdateTemp      pr              n 
D AddRecord       pr 
D closecursorC1   pr 
 
 /free 
  dclcursorC1(); 
  opencursorC1(); 
  dow fetchcursorC1(); 
     if (item = '* LABOR-DIRECT' or item = '*LABOR-DIR PC1' or item = 
      'Labor-DIR PC2' or item = '* LABOR CPR DRT' or item = 
      '* LABOR-DIR WHS' or item = '* LABOR-DIR SEQ' or item = 
      '* LABOR-DIR PNT'); 
       if not(UpdateDirect()); 
          AddRecord(); 
          UpdateDirect(); 
       endif; 
    elseif (item = '* LABOR-INDIR' or item = '*LABOR-IND PC1' or 
     item = '*LABOR-IND PC2' or item = '* LABOR-IND WHS' or 
     item = '* LABOR-IND SEQ' or item = '* LABOR-IND PNT'); 
       if not(Updateindirect()); 
          AddRecord(); 
          UpdateIndirect(); 
       endif; 
    elseif (item = '* LABOR-TEMP' or item = '*LABOR-TEMP PC1' or 
     item = '*LABOR-TEMP PC2' or item = '* LABOR-TEMP WHS' or 
     item = '* LABOR-TEMP SEQ' or item = '* LABOR-TEMP PNT'); 
       if not(UpdateTemp()); 
          AddRecord(); 
           UpdateTemp(); 
        endif; 
     else; 
        //disaster 
     endif; 
  enddo; 
  closecursorC1(); 
  *inlr = *on; 
 /end-free 
 
 
 
 
 *Function definitions 
p dclcursorC1     b 
C/Exec SQL 
C+ DECLARE labor CURSOR FOR 
C+ SELECT 
C+   cono95, 
C+   pnum95, 
C+   dtmv95, 
C+   mqty95, 
C+   nshf62, 
C+   decimal(substr(pgmn35,1,1),1) 
C+ FROM 
C+   OSLD1F3/INP95 T01, 
C+   OSLPDF3/PCP62 T02, 
C+   OSLD1F3/INP35 T03 
C+ WHERE 
C+   CONO95 = CONO35  AND 
C+   PNUM95 = PNUM35  AND 
C+   CONO95 = CONO62  AND 
C+   PNUM95 = COMP62  AND 
C+   TMMV95 >= REFT62-1  AND 
C+   TMMV95 <= REFT62  AND 
C+   REFD95 = REFD62  AND 
C+   CONO95 >= '02'  AND 
C+   CONO95 <= '10'  AND 
C+   (PNUM95 LIKE '* LABOR%'  OR 
C+   PNUM95 like '*LABOR%') and 
C+   DTMV95 >= :startdate and 
C+   dtmv95 <= :enddate 
C+ FOR READ ONLY WITH NC 
C/End-Exec 
p dclcursorC1     e 
 
p opencursorC1    b 
C/Exec SQL 
C+ OPEN labor 
C/End-Exec 
p opencursorC1    e 
p fetchcursorC1   b 
D                 pi              n 
d continueread    s               n 
C/Exec SQL 
C+ FETCH NEXT FROM LABOR INTO :cono, :item, :pdate, :labor_$, 
C+ :shift, :pgmn 
C/End-Exec 
 /free 
  continueread = *on; 
  if (sqlstt = '02000'); 
     continueread = *off; 
  endif; 
  return continueread; 
 /end-free 
p fetchcursorC1   e 
 
p UpdateDirect    b 
D                 pi              n 
d record          s               n 
C/Exec SQL 
C+ UPDATE CPAYNE/PACKOFF SET Direct_labor_$ = :labor_$ WHERE COMPANY 
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER = 
C+ :pgmn 
C/End-Exec 
 /free 
  record = *on; 
  if (sqlstt = '02000'); 
     record = *off; 
  endif; 
  return record; 
 /end-free 
p UpdateDirect    e 
 
p UpdateIndirect  b 
D                 pi              n 
d record          s               n 
C/Exec SQL 
C+ UPDATE CPAYNE/PACKOFF SET Indirect_labor_$ = :labor_$ WHERE COMPANY 
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER = 
C+ :pgmn 
C/End-Exec 
 /free 
  record = *on; 
  if (sqlstt = '02000'); 
     record = *off; 
  endif; 
  return record; 
 /end-free 
p UpdateIndirect  e 
 
p UpdateTemp      b 
D                 pi              n 
d record          s               n 
C/Exec SQL 
C+ UPDATE CPAYNE/PACKOFF SET Temp_labor = :labor_$ WHERE COMPANY 
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER = 
C+ :pgmn 
C/End-Exec 
 /free 
  record = *on; 
  if (sqlstt = '02000'); 
     record = *off; 
  endif; 
  return record; 
 /end-free 
p UpdateTemp      e 
 
p closecursorC1   b 
C/exec sql 
C+ close labor 
C/end-exec 
p closecursorC1   e 
 
p AddRecord       b 
C/Exec SQL 
C+ INSERT INTO CPAYNE/PACKOFF 
C+ (COMPANY, PDATE, SHIFT, PROFIT_CENTER) 
C+ VALUES(:cono,:pdate,:shift,:pgmn) 
C/End-Exec 
p AddRecord       e

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
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.