SQL PROCEDURE on the iSeries

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jmuehe@chefscatalog.com
    #1

    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.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
    Tags: None
    • Kent Milligan
      #2
      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@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
      kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
      >>ibm.com/iseries/db2
      (opinions stated are not necessarily those of my employer)

        Comment

        • jmuehe@chefscatalog.com
          #3
          Re: SQL PROCEDURE on the iSeries

          On Nov 4, 8:16 am, Kent Milligan <km...@us.eye-b-m.comwrote:
          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
          km...@us.eye-bee-m.com (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.

            Comment

            Previous template Next
            Working...