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------------------------------
Original Message:
Sent: Mar 17, 2022 08:18 AM
From: Daniel Luksetich
Subject: Db2 12 for z/OS package dependency
No, it's an insert
Original Message:
Sent: 3/17/2022 2:34:00 AM
From: Patrick Steurs
Subject: RE: Db2 12 for z/OS package dependency
Has the update-trigger an index-only accespath ?
Greetings
Patrick
Original Message:
Sent: 3/16/2022 2:31:00 PM
From: Daniel Luksetich
Subject: Db2 12 for z/OS package dependency
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