Fixes are available
DB2 Version 9.1 Fix Pack 7 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12 for Linux, UNIX and Windows
Closed as program error.
When running in federated environment and using triggers with variable predicates you might see that these predicates are not pushed down to the remote data source. Because of this the remote data source will return all rows to the federated database server and predicate evaluation will have to take place at the federated database server. This can cause performance issues because all rows being returned each time the trigger gets fired. An example showing this behaviour would be following: -- local federated database server create table LOCALTAB ( ID int, ELEMENT varchar(8), ELEMENTPART char(1) ) -- Set passthru to your favorite back end. set passthru BACKEND create table REMOTETAB (ID int, ELEMENT varchar(8)) insert into REMOTETAB values(1, 'abcd1234') insert into REMOTETAB values(2, 'abcd1235') insert into REMOTETAB values(3, 'abcd1236') insert into REMOTETAB values(4, 'abcd1237') set passthru reset -- local federated database server create nickname REMOTETAB for BACKEND.SCHEME.REMOTETAB create trigger TRIGGER1 after INSERT on LOCALTAB REFERENCING NEW AS N for each row mode db2sql WHEN (N.ELEMENTPART = 'I') BEGIN ATOMIC IF ( select count(*) from REMOTETAB a where a.ELEMENT = N.ELEMENT ) < 1 THEN SIGNAL SQLSTATE '89987' ('Cannot INSERT LOCALTAB because ELEMENT does not exist.'); END IF; END@ Running following insert will result in simple SELECT query without PREDICATE being shipped to the remote data source. insert into LOCALTAB values (1, 'abcdefgh', 'I')
It has been seen that switching from an AFTER-trigger to a BEFORE-trigger can have positive impact on the query shipped to the remote data source. But this is no general workaround as the issue described above can affect AFTER-trigger as well as BEFORE-triggers.
Users affected: Users of the DB2 for LUW Homogeneous Federation Feature or InfoSphere Federation Server Problem description and summary: See error description.
Problem was first fixed in Version 9.1, FixPak 6 (s081007). This fix should be applied on the federation server.
See LOCAL FIX.
Reported component name
DB2 UDB ESE WIN
Reported component ID
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
JR30864 JR30865 JR30866 JR30867 IZ35475 JR30868 JR30869 JR30870
JR30871 JR30872 JR30873 IZ35476 IZ35477 IZ35478 JR30874 JR30899
Fixed component name
DB2 UDB ESE WIN
Fixed component ID
Applicable component levels
07 October 2021