Can I run Warehouse Manager SQL steps without having to use journaling and commitment control ?
The answer is yes. A special design change has been introduced into the V7.2 FP11 code base, which allows warehouse iSeries agent users to switch journaling and commitment control off for SQL steps at the step level. Tests show that this can improve performance by up to 300%.
The new functionality allows users to run steps:
· With full commitment control
· Without both journaling and commitment control (Autocommit Off)
· Without both journaling and commitment control (Autocommit On)
Clearly there are performance / recoverability considerations to take into account with each option, but users now have more flexibility in terms of how they wish to deploy their processes.
Requirement: This change has been incorporated though the use of environment variables.
Two new system level environment variables have been introduced: SQL_COMMIT and SQL_COMMIT_PREFIX. The two environment variables can be created using the following commands:
· ADDENVVAR ENVVAR(SQL_COMMIT) VALUE(*NULL) LEVEL(*SYS)
· ADDENVVAR ENVVAR(SQL_COMMIT_PREFIX) VALUE(*NULL) LEVEL(*SYS)
In order to change the behaviour for individual steps within a process, you need to use a combination of naming convention and these environment variables, in order to achieve what you want to achieve.
The first environment variable that the WHM agent will check is SQL_COMMIT_PREFIX. The first character of the step name will be compared to SQL_COMMIT_PREFIX. If there is a match then commitment control will be enforced. If there is no match, or SQL_COMMIT_PREFIX=*NULL then the environment variable SQL_COMMIT will be checked.
SQL_COMMIT may be set to one of two values: ‘ONE’ or ’TWO’.
With SQL_COMMIT set to "ONE" autocommit is OFF and commit is NONE
With SQL_COMMIT set to "TWO" autocommit is ON and commit is NONE
Please see the table below for more details:
Auto Commit setting
|Off||None||Statements do not run under commitment control and you do not need to journal database files. LOB locators cannot be used. If a statement fires a trigger or UDF that makes changes under commitment control, those changes will be rolled back.|
|On||None||Statements do not run under commitment control and you do not need to journal database files. LOB locators can be used. If a statement fires a trigger or UDF that makes changes under commit, those changes are committed automatically by the database.|
Users are recommended to study the impact of No journaling carefully, before using these environment variables. Additional questions are answered at DB2 UDB for iSeries SQL CLI FAQ website: