ALLOW_DDL_CHANGES_WHILE_OPEN QAQQINI option

For a very long time on IBM i, we have allowed the UPDPGM and the CRTPGM REPLACE(*YES) to replace a running application program (the existing program is moved to QRPLOBJ and renamed). This allows application programmers and software providers to update a program object without having to end applications. However,  these techniques do not work for trigger programs.

By default, when users need to add or make updates to a trigger, Db2 for i requires an *EXCL lock on the file object. Furthermore, even for native triggers (triggers added via the ADDPFTRG command), users are prevented from using UPDPGM and the CRTPGM REPLACE(*YES) on the trigger program if its file is open. This means that by default, to promote trigger changes, application and user activity related to the file has to be quiesced.

A new QAQQINI option (ALLOW_DDL_CHANGES_WHILE_OPEN) will now allow trigger changes to be promoted, even if other jobs have the physical file open (directly or indirectly).

ALLOW_DDL_CHANGES_WHILE_OPEN applies to:

• SQL CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER, COMMENT ON TRIGGER, and LABEL ON TRIGGER statements
• CL ADDPFTRG, RMVPFTRG, and CHGPFTRG commands

UPDPGM and CRTPGM REPLACE(*YES) on a native trigger program will still be prevented but a user can remove the trigger and add a new trigger program.

QAQQINI controls are documented here:  https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/qryopt.htm

Considerations:

Before using this option, one should carefully consider whether it is acceptable for currently open cursors to continue to operate as if the trigger operation had not occurred until they are closed. For example:

• Any currently open cursors will ignore the create of a new trigger or a new enable of a trigger.
• Any currently open cursors will continue to use an old trigger program even though it may have been dropped, disabled, or changed.

When the trigger operation is performed, any SQL pseudo-closed cursors will be closed and pseudo-closes will be prohibited until the trigger operation is complete. This is done to minimize the number of open cursors that will continue using an old trigger or ignore a new or enabled trigger.

If after careful consideration, it is determined that it is not acceptable for currently open cursors to continue to operate as if the trigger operation had not occurred, then one of two techniques will need to be used.

• Wait until an *EXCL lock can be acquired on the physical file and all its logical files, then perform the trigger operation. This means that any applications that use the physical file or logical file will likely need to end before you can perform the operation. 
• Use the QAQQINI PREVENT_ADDITIONAL_CONFLICTING_LOCKS *YES option in the job that you are attempting to perform the trigger operation. This option will prevent any new requests for locks on the file from succeeding until the *EXCL lock is successful. This would likely surface as a lock time-out to applications.

​​