ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
PPA Packages preforming FULL TABLE SCAN on a given DB2 table
Article ID: 127513
Updated On:
Products
Bind Analyzer for DB2 for z/OS
SQL-Ease for DB2 for z/OS
SYSVIEW Performance Management Option for DB2 for z/OS
Plan Analyzer for DB2 for z/OS
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
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.
Feedback
Yes
No
Powered by
