SQL PROCEDURE on the iSeries

Hello,
What are the optimal settings to use in a CREATE PROCEDURE – when
creating a SQL procedure on the iSeries? To read data? To update?

Here is a pretty standard one of ours. I know the “MODIFIES SQL DATA”
could be changed to “READS SQL DATA” – but would it give much/any
performance boost?

CREATE PROCEDURE APPGENERIC.GETACTIVEITEMSKUS9_ORIG (
IN IN_COMPANY VARCHAR(3) ,
IN IN_OFFER VARCHAR(10) ,
IN IN_CWMXDTA VARCHAR(10) ,
IN IN_CWMXWEB VARCHAR(10) ,
IN IN_CWMXINH VARCHAR(10) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC APPGENERIC.GETACTIVEITEMSKUS9_ORIG
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN

re: SQL PROCEDURE on the iSeries

MODIFIES SQL versus READS SQL is not going to impact performance.

What release level are you on? There were significant enhancements delivered
for SQL Procedure performance in V5R4 & V6R1.

Some of those changes are highlighted in the SQL Procedure Performance white
paper that you can find at:
http://ibm.com/systems/i/db2/awp.html


jmuehe@chefscatalog.com wrote:--
Kent Milligan
ISV Enablement - System i
kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!(opinions stated are not necessarily those of my employer)

re: SQL PROCEDURE on the iSeries

On Nov 4, 8:16*am, Kent Milligan <km...@us.eye-b-m.comwrote:Hello Kent, We are on v5r4. V6R1, not happening anytime soon. Our main
software on the iSeries is a package and the vendor has not given an
estimate on when they think it will be ready.
I went to an IBM thing where they touch upon some of v6r1 benefits.
Looks good.