DB2 - L

 View Only
  • 1.  Db2 12 for z/OS package dependency

    Posted Mar 16, 2022 02:31 PM

    Here is an interesting situation.

     

    I have two tables

    TAB1

    TAB2

     

    TAB1 has two triggers defined on it. An AFTER UPDATE trigger and an AFTER DELETE trigger. Both triggers insert into TAB2.

     

    I have a package dependency as indicated by system catalog table SYSPACKDEP for both triggers on TAB1 and the index on TAB1

    I have a package dependency as indicated by system catalog table SYSPACKDEP for the DELETE trigger on TAB2 and the index on TAB2.

    I have a package dependency as indicated by system catalog table SYSPACKDEP for the UPDATE trigger on only the index on TAB2 and NOT the TAB2 table.

     

    Is this to be expected? Anyone else experience this?

     

    Thanks,

    Dan Luksetich

     



  • 2.  RE: Db2 12 for z/OS package dependency

    Posted Mar 17, 2022 02:34 AM
    Has the update-trigger an index-only accespath ? 
    Greetings
    Patrick





  • 3.  RE: Db2 12 for z/OS package dependency

    Posted Mar 17, 2022 08:19 AM
    No, it's an insert




  • 4.  RE: Db2 12 for z/OS package dependency

    Posted Mar 18, 2022 03:34 AM
    Well, the access path of an INSERT can be (and actually: should be) index-only! And the index should be the cluster index.
    You can easily check that by running EXPLAIN on an insert statement.
    The access path always only covers the "read" part of what happens.

    For an insert, this is the access path:
    - take from the to be inserted values the field(s) corresponding to the cluster sequence.
    - look up that value in the cluster index, or of the value closest to it
    - take the RID of that (closest) value.

    There stops the "access path" and begins the real INSERT: go to the tablespace (partition), obtain the (spacemap page of the page of the) RID, insert there if enough room, etc.

    ------------------------------
    Peter Vanroose
    ABIS Training & Consulting
    https://www.abis.be/html/enDB2Calendar.html
    ------------------------------



  • 5.  RE: Db2 12 for z/OS package dependency

    Posted Mar 18, 2022 07:11 AM
    Then why does the UPATE trigger have a dependency while the DELETE trigger does not. They both do the same insert?




  • 6.  RE: Db2 12 for z/OS package dependency

    Posted Mar 18, 2022 04:04 AM
    Btw, why not use temporal tables instead of triggers for this use case? :)

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2022 IBM Champion
    ------------------------------



  • 7.  RE: Db2 12 for z/OS package dependency

    Posted Mar 18, 2022 07:13 AM
    Pre-dates temporal. Once it was available, I jumped on it. Now, I have it all over the place and it works great, but as you know making these types of changes are difficult to navigate.