IBM Support

JR27719: VARIABLES IN TRIGGERS NOT PUSHED DOWN TO REMOTE DATA SOURCE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • 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')
    

Local fix

  • 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.
    

Problem summary

  • Users affected: Users of the DB2 for LUW Homogeneous Federation
    Feature or InfoSphere Federation Server
    
    Problem description and summary:
    See error description.
    

Problem conclusion

  • Problem was first fixed in Version 9.1, FixPak 6 (s081007).
    This fix should be applied on the federation server.
    

Temporary fix

  • See LOCAL FIX.
    

Comments

APAR Information

  • APAR number

    JR27719

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-10-26

  • Closed date

    2008-11-02

  • Last modified date

    2008-11-02

  • APAR is sysrouted FROM one or more of the following:

    JR25855

  • 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

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R910 PSN

       UP

  • R950 PSN

       UP

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910"}]

Document Information

Modified date:
07 October 2021