IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
If you think you know all about APPLCOMPAT, then please read this!
Advanced triggers introduced in V12 allow SQL PL statements and this opens “SQL only applications” to triggers. There is reduced need to call a stored procedure, if more complex code is needed inside a trigger.
When an advanced trigger is generated, the trigger gets two APPLCOMPAT flags:
Beginning with Db2 12 APPLCOMPAT setting controls DDL: Function level is no longer applicable.
Beginning with V12R1M505 the WHEN clause of an advanced trigger can refer to an archive-enabled table (AET) or a system-period temporal table (STT).
In the following scenarios the table in the WHEN clause is called archive_enabled1. And you have an equivalent table archive1, prepared for AET usage, but not yet enabled because you are still on V12R1M502. But when you are in V12R1M505 the application wants to go forward.
And you have created two advanced triggers atrigg1 and atrigg2 in V12R1M502 with reference to archive_enabled1 in the WHEN clause. The triggers are valid and usable.
1) You migrated from V12R1M502 to function level V12R1M505 and want to use the new WHEN functionality for an AET of V12R1M505. Therefore, you run ALTER TABLE archive_enabled1 ENABLE ARCHIVE USE archive1.
Correct, the packages of the advanced triggers atrigg1 and atrigg2 are invalidated, because the APPLCOMPAT in SYSIBM.SYSPACKAGE of the triggers have a value of V12R1M502, which does not allow the next functionality. And to become valid packages atrigg1 and atrigg2 must be “re-bound” with APPLCOMPAT V12R1M505.
2) So, you issue a REBIND TRIGGER PACKAGE command with APPLCOMPAT.
Correct it does not work, because:
DSNT903I -DB2A DSNTBRB2 REBIND TRIGGER PACKAGE ERROR
REBIND TRIGGER PACKAGE IS INVALID FOR AN ADVANCED TRIGGER.
3) But perhaps REBIND PACKAGE command might do it.
Correct it does not work because:
DSNT215I -DB2A DSNTBRB2 REBIND FOR
PACKAGE = atrigg1 FAILED BECAUSE AT LEAST ONE BIND OPTION IS NOT ALLOWED FOR A TRIGGER PACKAGE.
4) Looking into SQL Reference you recognize that there are variations of the ALTER TRIGGER statement for advanced triggers: But which one should you use?
ALTER TRIGGER atrigg1 APPLCOMPAT V12R1M505
ALTER TRIGGER atrigg1 REGENERATE VERSION V1 USING APPLICATION COMPATIBILITY V12R1M505
You try “REGENERATE” because this sounds promising:
DSNT408I SQLCODE = -4743, ERROR: ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL
Correct you forgot to bind DSNTEP2 package with APPLCOMPAT(V12R1M505): Application compatibility controls DDL since V12. You re-bind DSNTEP2 package with APPLCOMPAT V12R1M505
5) You run again ALTER TRIGGER atrigg1 REGENERATE … and get:
For an explanation see Summary.
6) Hence you run
ALTER TRIGGER atrigg1 APPLCOMPAT V12R1M505
Correct: atrigg1 trigger is valid again and it has a new assigned ENVID value, which points to V12R1M505.
7) You must fall back to function level V12R1M502 by the ACTIVATE command.
8) Your application people are calling you and complaining that atrigg2 does not run, because package is invalid:
DSNT408I SQLCODE = -723, ERROR: AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN TRIGGER atrigg2.
INFORMATION RETURNED: SQLCODE -904, SQLSTATE 57011, MESSAGE TOKENS 00E30305,00000801,atrigg2.1AD0640D0B8C4B80, SECTION NUMBER 0
Oops … you forgot the second trigger.
How to solve this?
Correct you run ALTER TRIGGER atrigg2 APPLCOMPAT V12R1M505. This is possible because DSNTEP2 is still on APPLCOMPAT V12R1M505.
What you encountered with advanced triggers could also happen with basic triggers. Two exceptions:
The difference between ALTER TRIGGER atrigg1 APPLCOMPAT and ALTER TRIGGER atrigg1 REGENERATE USING APPLICATION COMPATIBILITY is that the second statement re-runs only the CREATE TRIGGER statement. If you need to change the APPLCOMPAT value in the CREATE TRIGGER statement, you must run ALTER TRIGGER atrigg1 APPLCOMPAT V12R1M505 statement.
In case of fallback from a higher function level, you should be prepared to keep a package like DSNTEP2 in another COLLID and in another PLAN with the highest activated APPLCOMPAT: This allows you to be flexible if new functionality should be enabled or new functionality should be re-enabled due to application needs.
Add a Comment