Plan Analyzer packages performing full table scan on a given Db2 table

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Can Plan Analyzer for Db2 for z/OS (PPA) be used to create a report of all packages preforming a full table scan on a given Db2 table.

Resolution

The recommendation is to perform an explain strategy in PPA expert mode. With expert mode, you can choose to do an explain catalog object table
for a specific table name. Also, you would want to set Dep SQL= Y in the Catalog Object Source, so that only the dependent SQL within the package
gets selected for explain. You do have the ability to refine it even further by specifying a Collection, if not leave the Collid/Package as asterisk and
the entire catalog is searched. Then in the explain options, you would select Access Path filter option and choose Table space Scan(s). This explain
option would give you the ability to further narrow down package statements where a tablespace scan existed. While this approach would significantly
narrow down the result set, there still could be some false positives; however, they could easily be verified by scanning the access path report output to
verify the table name was indeed in a tablespace scan. The false positives are possible if the SQL is referencing multiple tables besides the table in question.