I am not sure about z/OS, but in LUW if you have referential
constraint and delete from parent table, the child record is
deleted and it is reported as table scan on child table as well.
db2pd -tcbstats confirms this behavior.
as you pointed out, it may be because the original query is a
delete query which is explainable statement. this could be the
why I raised the issue is because I was thinking from a
monitoring perspective only and not explain perspective.
Mon_GET_Table or db2pd -tcbstats is reporting whether rows read
or written or direct reads/writes etc. From a monitor stand point,
it should not that much worry about whether I am executing
explainable statements or non-explainable statements. if you read a
row from the table, then rows read should be 1 in monitor element
output.[there could be exceptions for reorg. explanations
it reports rows read definitely, except that it did not treat
all table rows read as a table scan and reported it as a table scan
happening in the table.
if the behavior is like that and I am given to understand that
clearly , that is all my expectation is.
I can give one more example just for discussion, LOAD with
replace should also result in a full table scan [for deleting all
records in table before inserting new data] but it will not report
as such, because it is doing direct I/O and not buffered
the reporting of table scans based on direct I/O or
buffered I/O is understandable to me. because in direct I/O
you are reading directly from data pages in table space. In this
case I did not do a table scan, I was doing page I/O to disk or
container. Where as Buffered I/O makes use of buffer pool. if you
are accessing table via buffer pool then it should return rows
read, written etc.
in classic reorg you are completely creating a shadow copy
of original table and deleting the old table. here underlying
object itself is changing. I cannot even report one full table scan
on a deleted object even though it is the same table from our
perspective. but internally it deleted a table and created a new
one with same data. the concept of table scan itself is getting
weird here because the underlying object is changing
these are just my inputs and thoughts, I am not trying to
say this is the correct way. as you pointed out, TBSCAN is getting
treated as Explain Operator in a Strict sense. if it is the way, it
is perfectly fine with me. I was only sharing my inputs and