table sacn on a create index will not be reported as table scan happening

Harishkumar .Pathangay

table sacn on a create index will not be reported as table scan happening

execute create index [2 columns] on a table with total 5 columns. Monitor the table scans monitor element using MON_GET_TABLE or db2pd -tcbstats option. It will not report the table is getting scanned. Obviously you cannot create index without scanning the table, it reports number of rows read, but will not report that a table scan is going on. Any one faced this before? Any Doc link indicating such behavior?

thanks, harish p

P.S : I am not marketing myself. Issues just happen to me. KC is huge, I thought I read somewhere about it. Not remembering.

Michael Hannan

RE: table sacn on a create index will not be reported as table scan happening
(in response to Harishkumar .Pathangay)

Tablespace Scan is an access path term (at least on DB2 z/OS). So Create Index does not have an access path. Neither does Reorg or Image Copy, yet they scan.

Worst of all, Delete parent row of an R.I. constraint may cause child table to be scanned but is not a TS scan from an access path point of view.

Perhaps lack of an access path could be relevant.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Harishkumar .Pathangay

table sacn on a create index will not be reported as table scan happening
(in response to Michael Hannan)
Thank you for your helpful inputs.

On Tue 21 May, 2019, 07:59 Michael Hannan, <[login to unmask email]> wrote:

> Tablespace Scan is an access path term (at least on DB2 z/OS). So Create
> Index does not have an access path. Neither does Reorg or Image Copy, yet
> they scan.
>
> Worst of all, Delete parent row of an R.I. constraint may cause child
> table to be scanned but is not a TS scan from an access path point of view.
>
> Perhaps lack of an access path could be relevant.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message-----
>

Harishkumar .Pathangay

RE: table sacn on a create index will not be reported as table scan happening
(in response to Harishkumar .Pathangay)

hi Michael,

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

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 below.]

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 I/O. 

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

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

thanks,

harish pathangay 

Dave Nance

table sacn on a create index will not be reported as table scan happening
(in response to Michael Hannan)
I have to disagree with you on the RI statement. if there is an index to support the check, then it is used and a tablespace scan is not used.
 David Nance


From: Michael Hannan <[login to unmask email]>
To: [login to unmask email]
Sent: Monday, May 20, 2019 9:29 PM
Subject: [DB2-L] - RE: table sacn on a create index will not be reported as table scan happening

Tablespace Scan is an access path term (at least on DB2 z/OS). So Create Index does not have an access path. Neither does Reorg or Image Copy, yet they scan.
Worst of all, Delete parent row of an R.I. constraint may cause child table to be scanned but is not a TS scan from an access path point of view.Perhaps lack of an access path could be relevant.Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email] has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Ray Lopez

RE: table sacn on a create index will not be reported as table scan happening
(in response to Michael Hannan)

Hi Michael, this is a bit off topic, but I agree on the "Delete parent row of an R.I. constraint may cause child table to be scanned" but it does not show up in DB2 Explain.   This has caused grief for us on our applications that are heavy on RI.  We have had to develop monitoring to identify Foreign Key relationships that are not supported by an Index, so we can create them.  Thereby heading off the performance problems.   It would be great if DB2 Explain could be enhanced to show the RI caused TS scans.   If anybody wants the SQL we developed for that, let me know. 

na

Harishkumar .Pathangay

table sacn on a create index will not be reported as table scan happening
(in response to Dave Nance)
The issue is not about that. You have missed the context. Kindly request
you to go through full thread to get a full picture of discussion.

On Tue 21 May, 2019, 19:44 Dave Nance, <[login to unmask email]> wrote:

> I have to disagree with you on the RI statement. if there is an index to
> support the check, then it is used and a tablespace scan is not used.
>
>
> David Nance
>
>
>
> ------------------------------
> *From:* Michael Hannan <[login to unmask email]>
> *To:* [login to unmask email]
> *Sent:* Monday, May 20, 2019 9:29 PM
> *Subject:* [DB2-L] - RE: table sacn on a create index will not be
> reported as table scan happening
>
> Tablespace Scan is an access path term (at least on DB2 z/OS). So Create
> Index does not have an access path. Neither does Reorg or Image Copy, yet
> they scan.
> Worst of all, Delete parent row of an R.I. constraint may cause child
> table to be scanned but is not a TS scan from an access path point of view.
> Perhaps lack of an access path could be relevant.
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message-----
>

Michael Hannan

RE: table sacn on a create index will not be reported as table scan happening
(in response to Dave Nance)



In Reply to Dave Nance:

I have to disagree with you on the RI statement. if there is an index to support the check, then it is used and a tablespace scan is not used.
 David Nance

I did say "may" and did not say "must". You state the obvious.

Anyway my main idea was just that a Create Index may not be counted as a application "Tablespace Scan" even if it is one. Just speculating without knowing the platform.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 22, 2019 - 10:47 AM (Europe/Berlin)

Jeff Goss

RE: table sacn on a create index will not be reported as table scan happening
(in response to Harishkumar .Pathangay)



In Reply to Harishkumar .Pathangay:

execute create index [2 columns] on a table with total 5 columns. Monitor the table scans monitor element using MON_GET_TABLE or db2pd -tcbstats option. It will not report the table is getting scanned. Obviously you cannot create index without scanning the table, it reports number of rows read, but will not report that a table scan is going on. Any one faced this before? Any Doc link indicating such behavior?

thanks, harish p

P.S : I am not marketing myself. Issues just happen to me. KC is huge, I thought I read somewhere about it. Not remembering.

Aside: Firefox seems to be doing something weird on this webpage so I'll try again briefly from Safari. Apologies if this ends in a double post.

I didn't see on a cursory look in the docs that it is explicitly called out, but no, internal scans are not counted by design.  These scans should set an attribute to the data manager layer to indicate that they are internal (in general, not from a section) and if so we do not increment the counter. Collecting stats would be another example of something that won't be counted, whether explicitly from a user or through auto stats.

Michael Hannan

RE: table sacn on a create index will not be reported as table scan happening
(in response to Ray Lopez)

In Reply to Ray Lopez:

Hi Michael, this is a bit off topic, but I agree on the "Delete parent row of an R.I. constraint may cause child table to be scanned" but it does not show up in DB2 Explain.   This has caused grief for us on our applications that are heavy on RI.  We have had to develop monitoring to identify Foreign Key relationships that are not supported by an Index, so we can create them.  Thereby heading off the performance problems.   It would be great if DB2 Explain could be enhanced to show the RI caused TS scans.   If anybody wants the SQL we developed for that, let me know. 

na

Yes I have built my own queries as well to display all Foreign keys not supported by an index and also to show all the hierarchical R.I. Paths downward, showing CASCADE Rels in particular, and lack of an FK index. The site wants to do a Delete cleanup of old data starting somewhere in the hierarchy.

In the early days, queries published for this purpose did not actually work, but were used at many sites. The original queries checked that an index existed with the foreign key column in the correct position, but did not insist that those FK columns matched, were all in the same index. Ha ha. It was easy to point out DB2 Catalog FK indexes missing that the original queries failed to identify.

Good idea though. I should enhance my Explain reports for INSERTs/UPDATEs and DELETEs to have an additional R.I. Check report.

Insert to one table could cause a lookup up check to many parent tables, same for update of FK columns (tricky to know which columns are updated in my query), and similarly a Delete of parent could check many child tables.

Cascade Deletes are the worst to show the possibly extended impact when FK indexes are missing, and depending if any rows found to cascade delete..

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd