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:
Quote:
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
--
Kent Milligan
ISV Enablement - System i
kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
Quote:
Quote:
Quote:
>>ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)
