RE: Embedded sql without index faster than with index

David,

A couple of points about embedded SQL...

1) Never use * in your field list. This will mess you up if you ever
add a field to the file and then fail to recompile the program. Using a
defined list of fields, you program will continue to operate
successfully without recompile...

2) Static SQL will usually perform much better than Dynamic SQL. The
real advantage comes from being able to store the "access plan" used by
the optimizer to perform its work efficiently. If DB2 is able to cache
its optimal access plan, then you don't need to optimize the statement
each time it executes. MUCH more efficient...

3) Static SQL is simpler, involving fewer steps to get the statement
executed.

Here's how I might code your statement:

Exec SQL Declare MyCursor as
SELECT Fld1, Fld2, Fld3, ...
FROM MYFILE
WHERE ( :wDate1 = '0001-01-01' or DATE >= : wDate1) AND
( :wDate2 = '0001-01-01' or DATE <= : wDate2) AND
( :wClientN1 = '' or CLIENT >= :wClientN1 ) AND
( :wClientN2 = '' or CLIENT <= :wClientN2 ) AND
( :wAmount1 = 0 or AMOUNT >= :wAmount1 ) AND
( :wAmount2 = 0 or AMOUNT <= :wAmount2 );

Typically, I would use this for an inquiry application. Put the
"filter" fields in the SFL header format. Leave the fields blank (or
default value) to skip that value in your select WHERE criteria...

Hth,
-Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Monday, March 07, 2011 9:47 AM
To: RPG programming on the IBM i / System i
Subject: RE: Embedded sql without index faster than with index


-----Message d'origine-----

[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Schutte,
Michael D

What happens if you code it like so.

Select fields from files where :inputclientNbr in (0,
clientNumber) and :inputAmt in (0, amount)

When inputClientNbr host variable is 0, then all clients
would end up getting selected, otherwise only the
inputclientnumber is selected.

When inputAmt is 0, then all transaction amounts will be
selected, otherwise only that transaction amount would be.

User can enter one client number, an upper and a lower amount and a
start and end date, hence I used between.

I'm building my cursor like this :

wSql =
'SELECT * FROM MYFILE +
WHERE +
DATE BETWEEN ''' + wDate1 + ''' AND +
''' + wDate2 + ''' AND +
CLIENT BETWEEN ' + wClientN1 + ' AND +
' + wClientN2 + ' AND +
AMOUNT BETWEEN ' + wAmount1 + ' AND +
' + wAmount2 + ';

EXEC SQL
PREPARE S1 FROM : wSql;


How would the syntax be if I used host variables?