If you think you know all about APPLCOMPAT, then please read this!

Introduction:

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:

  1. One which describes the environment under which the advanced trigger is created. This value is linked by the ENVID column in SYSIBM.SYSTRIGGERS and points to the appropriate entry in SYSIBM.SYSENVIRONMENT with the column APPLCOMPAT. This value is the APPLCOMPAT value of the package which creates the trigger.
  2. One which describes the environment under which the advanced trigger can run. This value is in the column APPLCOMPAT of SYSIBM.SYSPACKAGE. This value is the APPLCOMPAT value which is specified in the CREATE TRIGGER statement. If not specified this value is fixed to V12R1M500 independent of any DSNZPARM settings.

 

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.

Scenarios:

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.

What happens?

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.

What happens?

Correct it does not work, because:

DSNT903I -DB2A DSNTBRB2 REBIND TRIGGER PACKAGE ERROR              

         REBIND TRIGGER PACKAGE IS INVALID FOR AN ADVANCED TRIGGER.

         PACKAGE= atrigg1

3) But perhaps REBIND PACKAGE command might do it.

What happens?

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            

or

ALTER TRIGGER atrigg1 REGENERATE VERSION V1 USING APPLICATION COMPATIBILITY V12R1M505

 

You try “REGENERATE” because this sounds promising:

What happens?

DSNT408I SQLCODE = -4743, ERROR:  ATTEMPT TO USE A FUNCTION WHEN THE APPLICATION COMPATIBILITY SETTING IS SET FOR A PREVIOUS LEVEL

Why?

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:

DSNT408I SQLCODE = -20100, ERROR:  AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT.  INFORMATION RETURNED: SECTION         NUMBER : 2 SQLCODE -20555, SQLSTATE 428I6, AND MESSAGE TOKENS  5     

For an explanation see Summary.

6) Hence you run

ALTER TRIGGER atrigg1 APPLCOMPAT V12R1M505   

in DSNTEP2.

What happens?

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.

Summary:

What you encountered with advanced triggers could also happen with basic triggers. Two exceptions:

  1. The APPLCOMPAT of the basic trigger package is controlled by the setting of APPLCOMPAT in DSNZPARM
  2. REBIND TRIGGER PACKAGE works for a basic trigger.

 

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.

 

Views: 992

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Latest Activity

Carol Davis-Mann posted a blog post

Around the World with Triton’s Digital Duo

As the world mourns the loss of the digital dance duo Daft Punk, Triton Consulting is sending its very own duo of digital pioneers Mark Gillis and Damir Wilder around the world – virtually.IBM Champions Mark and Damir were delighted to get the call that they had been selected to present at IDUG Australasia and IDUG North America Tech Conferences.Eagerly planning which IDUG polo shirts to pack, it wasn’t long before reality set in……this year’s IDUG conferences would be virtual ones.…See More
Thursday
William Shipley liked Jonathan Sloan's event IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update
Feb 26
Carol Davis-Mann posted an event

Db2 for z/OS: The Evolution Engine Webcast at United Kingdom

March 16, 2021 from 11am to 11:30am
Join Julian Stuhler, IBM Gold Consultant and Solutions Delivery Director at Triton Consulting for his first IBM hosted webcast of 2021.Julian will be presenting Db2 for z/OS: The Evolution Engine on Tuesday, March 16th at 15:00 GMT (11:00 AM EDT).Presentation OverviewFor over 35 years, Db2 for z/OS has been the engine at the heart of many of the critical applications that run the world around us.…See More
Feb 25
Carol Davis-Mann posted a blog post

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two

In DB2 12 for z/OS, DRDA Applications and Application Compatibility Part Two Gareth Copplestone-Jones provides guidance on the implementation of server-side configuration.Server-side configurationWhen considering how to manage managing Application Compatibility – APPLCOMPAT – for your distributed applications which use the NULLID packages, the main alternative to client-side configuration (discussed in the …See More
Feb 25
An event by Jonathan Sloan was featured
Thumbnail

IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update at Webcast - East Coast U.S. time zone

March 9, 2021 from 11am to 12pm
Access your Db2 for z/OS Data from the cloud: Db2 Data Gate and IBM Cloud Pak for Data on IBM Z demo and updateMarch 9th @ 11:00 AM Eastern U.S. timeRegister now @ https://ibm.biz/Db2DGUpdateWoDJoin us to see a demo of Db2 for z/OS Data Gate which can help you reduce the complexity associated with delivering Db2 for z/OS data for new hybrid cloud initiatives. Db2 Data Gate allows you to keep pace with new cloud, analytics and mobile initiatives with…See More
Feb 25
William Shipley liked Surekha Parekh's event Db2 for z/OS: The Evolution Engine - Julian Stuhler
Feb 25
Dragica Smintic liked Surekha Parekh's event Db2 for z/OS: The Evolution Engine - Julian Stuhler
Feb 24
A blog post by Surekha Parekh was featured
Feb 24
An event by Surekha Parekh was featured
Thumbnail

Db2 for z/OS: The Evolution Engine - Julian Stuhler at Webcast

March 16, 2021 from 11am to 12:30pm
Register NowDb2 for z/OS: The Evolution Engine - Julian StuhlerFor over 35 years, Db2 for z/OS has been the engine at the heart of many of the critical applications that run the world around us. From internet banking transactions to airline reservations and insurance quotations, Db2 for z/OS continues to power a huge array of today’s high volume, mission-critical enterprise applications. But how has Db2 remained so relevant…See More
Feb 24
Tom Glaser posted events
Feb 24
Jonathan Sloan posted an event
Thumbnail

IBM Db2 for z/OS Data Gate and IBM Cloud Pak for Data on IBM Z update at Webcast - East Coast U.S. time zone

March 9, 2021 from 11am to 12pm
Access your Db2 for z/OS Data from the cloud: Db2 Data Gate and IBM Cloud Pak for Data on IBM Z demo and updateMarch 9th @ 11:00 AM Eastern U.S. timeRegister now @ https://ibm.biz/Db2DGUpdateWoDJoin us to see a demo of Db2 for z/OS Data Gate which can help you reduce the complexity associated with delivering Db2 for z/OS data for new hybrid cloud initiatives. Db2 Data Gate allows you to keep pace with new cloud, analytics and mobile initiatives with…See More
Feb 24
Michal Bialecki liked Carol Davis-Mann's blog post DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One
Feb 19
A blog post by Carol Davis-Mann was featured

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One

IntroductionThis, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.A very brief history of Application CompatibilityWith the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration…See More
Feb 19
An event by Surekha Parekh was featured
Thumbnail

Business and Technical Value of Top Ten Features in Db2 12 for z/OS Continuous Delivery at Virtual Event

March 23, 2021 from 11am to 12:15pm
REGISTER NOWBusiness and Technical Value of Top Ten Features in Db2 12 for z/OS Continuous Delivery Abstract One of the most valuable resources for modern businesses is business critical data.  Data can be used to provide insight into every facet of the business from operations to market trends. Powerful data enables effective and efficient business decision making and your enterprise data is only as powerful as your ability to harness it. Storing,…See More
Feb 19
Carol Davis-Mann posted a blog post

DB2 12 for z/OS, DRDA Applications and Application Compatibility Part One

IntroductionThis, the first of two articles on how to manage the Application Compatibility level for DRDA applications, provides an introduction to the subject and considers two of the ways of doing this. In the second article Gareth Copplestone-Jones will concentrate on perhaps the most promising method and discusses its drawbacks.A very brief history of Application CompatibilityWith the release of DB2 11 for z/OS, IBM introduced Application Compatibility, which is intended to make migration…See More
Feb 17
An event by Surekha Parekh was featured
Thumbnail

Db2 12 agile, open and secure - Your database of choice for business critical data at Webcast

March 16, 2021 from 11am to 12:30pm
Register Now    PART 2Maria Sueli from the IBM Development Lab Following Julian, Maria Sueli from the IBM Development Lab will share with you how you can remove barriers that are holding your Db2 enterprise data captive in outdated methods and processes.  She will demonstrate how Db2 for z/OS…See More
Feb 17

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Replaying past database activities

Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply

I would need to know who modified table data on a certain day.I do have my backups and database logs available.DB2audit was however not activated at this point.How would i  be able to access this information?Continue

Tags: #DataAccess

Db2 is developer inclusive and preview of Db2 11.5.5

Started by Kip in What's hot ? Nov 4, 2020. 0 Replies

This digital event examines how enterprises can become smarter with a data management system that is developer-inclusive. During this session, participants will get to learn how Db2 integrates with a broad array of programming languages, employs…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service