When I read a question about INCLUDE columns that I received I first thought that indexes were meant. The reason is that for performance reason, to achieve index-only access, additional columns, not contributing to the index key, are added to an index during CREATE INDEX. These columns are called "include columns". However, the question was about include columns for INSERT, UPDATE, and DELETE. So what are they?
In OLTP environments it is often necessary to retrieve data that was just modified, i.e., inserted, updated, deleted. To save CPU cycles and improve response time why not combine the SELECT with the other statement? How this can be done is documented as part of the IUD operations (see links above) and the so-called data-change-table-reference clause. With DB2 you can access the data before (OLD TABLE) or after (NEW TABLE) that change was applied.
db2 => create table ict(id int, s varchar(80))
DB20000I The SQL command completed successfully.
db2 => select * from ict
ID S
----------- --------------------------------------------------------------------
0 record(s) selected.
db2 => select id,s from new table(insert into ict values(1,'Hello World'))
ID S
----------- --------------------------------------------------------------------
1 Hello World
1 record(s) selected.
db2 => select id,s from old table(update ict set s='I am quiet' where id=1)
ID S
----------- --------------------------------------------------------------------
1 Hello World
1 record(s) selected.
db2 => select id,s from new table(update ict set s='Hello again' where id=1)
ID S
----------- ---------------------------------------------------------------------
1 Hello again
1 record(s) selected.
db2 => select id,s from old table(delete from ict where id=1)
ID S ----------- --------------------------------------------------------------------
1 Hello again
1 record(s) selected.
db2 => select * from ict
ID S
----------- ---------------------------------------------------------------------
0 record(s) selected.
So far, so good, but still no include columns. They are needed when you want to return more than what is in the rows that you modify, i.e., more columns are needed in the result set.
db2 => select id, s, ts from new table(insert into ict(id, s) include (ts timestamp) values(1,'Hello World',current timestamp),(2,'Hello again',current timestamp))
ID S TS
----------- ------------------------------------------ --------------------------
1 Hello World 2012-10-24-15.08.06.762079
2 Hello again 2012-10-24-15.08.06.762079
2 record(s) selected.
Of course there are better things to pass around than the timestamp. It could be a sequence value like the insert order, a function call, or whatever you make up.
That's all I wanted to include into this article...