Re: Embedded SQL in a Service Program versus in a Program

Thank you all for your responses. I found the issue. In order to show
you all an example of the program/service program I decided to create a
stripped down version of them. In doing so I realized I was missing one
thing in the program that was in the service program. For these types of
procedures we add an SQL option so that sorting will be case insensitive.
When I add this to the program I end up with the same results as the
service program. And if I remove it from the service program I get the
faster results.

C/EXEC SQL
C+ SET OPTION SRTSEQ=*LANGIDSHR
C/END-EXEC

Eileen Beckwith
Vermont Information Processing
eileen@xxxxxxxxxx



From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)"
<rpg400-l@xxxxxxxxxxxx>
Date: 07/22/2014 06:02 PM
Subject: Re: Embedded SQL in a Service Program versus in a Program
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



Eileen

Forgive my pressing!

You mention "stored procedure" here - what kind is it? An external one
using RPG? So far as I know, a stored procedure is something created
using the CREATE PROCEDURE statement in SQL.

Thx
Vern

On 7/22/2014 4:13 PM, Scott Mildenberger wrote:

Can we see the procedure prototype to see how the parameters are coded?

Scott

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of

Eileen@xxxxxxxxxx

Sent: Tuesday, July 22, 2014 3:07 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Cc: RPG400-L
Subject: Re: Embedded SQL in a Service Program versus in a Program

- which did you run first?
I have done both ways

- did you run CLRPOOL and/or SETOBJACC to get the table out of memory?
Level playing field and all
I did not but I have run this multiple times on mulitple days

and always get the same results.

- it might be helpful to run a monitor over this to see what the

optimizer is doing

I did turn on a SQL Performance Monitor and could not see any

difference except the run times

- when you say "called the service program", do you mean you called the

procedure? I assume it was prototyped

Sorry, I should have said I called the procedure - I have done

it both as a

prototyped procedure and also as a stored procedure. Same

results with both.

Eileen Beckwith
Vermont Information Processing
eileen@xxxxxxxxxx