PPA Packages preforming FULL TABLE SCAN on a given DB2 table
book
Article ID: 127513
calendar_today
Updated On:
Products
CA Bind Analyzer for DB2 for z/OSCA DetectorCA SQL-Ease for DB2 for z/OSCA Sysview Performance Management Option for DB2 for z/OSCA Database Detector for DB2 for z/OSCA Plan Analyzer for DB2 for z/OSCA 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.