PPA Packages preforming FULL TABLE SCAN on a given DB2 table

Products

• CA Bind Analyzer for DB2 for z/OS
• CA Detector
• CA SQL-Ease for DB2 for z/OS
• CA Sysview Performance Management Option for DB2 for z/OS
• CA Database Detector for DB2 for z/OS
• CA Plan Analyzer for DB2 for z/OS
• CA Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

Can CA Plan Analyzer be used to create a report of all packages preforming FULL TABLE SCAN on a given DB2 table?

Environment

Release:
Component: ANLDB2

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.