SQL PROCEDURE on the iSeries

[jmuehe]

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.GETA CTIVEITEMSKUS9_ 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.GETA CTIVEITEMSKUS9_ 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

[Kent Milligan]

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
jm****@chefscat alog.com wrote:

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.GETA CTIVEITEMSKUS9_ 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.GETA CTIVEITEMSKUS9_ 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

--
Kent Milligan
ISV Enablement - System i
km***@us.eye-bee-m.com (spam trick) GO HAWKEYES!!

>>ibm.com/iseries/db2

(opinions stated are not necessarily those of my employer)

[jmuehe]

On Nov 4, 8:16*am, Kent Milligan <[e-mail]:

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

jmu...@chefscat alog.com wrote:

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.GETA CTIVEITEMSKUS9_ 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.GETA CTIVEITEMSKUS9_ 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

--
Kent Milligan
ISV Enablement - System i
[e-mail] (spam trick) * GO HAWKEYES!!
*>>ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)- Hide quoted text -

- Show quoted text -

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.