RULE 082 active pages threshold value in Plan Analyzer

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Plan Analyzer for Db2 for z/OS (PPA) rule 82 has a statement:

Your installation has set a threshold of &THRESHOLD1 for the maximum number of active pages
for partitioned tablespace accessed by an SQL statement that is not utilizing parallel I/O processing.

How is this value initially set and what Db2 DSNZPARM does it equate to?                                                                        

Resolution

The threshold values are initially set at product installation time and the values are a best estimate from the Plan Analyzer development staff knowledge.
In rule 82, this value has no corresponding DSNZPARM value. It is simply a value that says if an SQL query is going to read 10 or more pages,
please let us know.  These pages could be read via an index or a tablespace scan. This is not determined by this rule.                                                   

This is a value you can control by using Option 5 in the Plan Analyzer product. 
If the question is what parameter in Db2 equates to the value 10 that is in the Rule 82, the answer is none.

This value is an approximate value for the number of active pages in a tablespace that would be read if this query were run.
It also implies that a table scan is being performed by this query.  So, another way of stating this rule would be to say that
this query can not scan more than the threshold.  So if it is OK to scan up to 50 pages before you want to be notified of this
situation, than the threshold for rule 82 and 88 would be set to a value of 50.  As far as the threshold checked in rule 0082 or 0088,
this value (threshold) that you set is checked against the NACTIVE value in the Db2 catalog for SYSTABLESPACE.
Each shop may or may not have a standard for the number of active pages per tablespace.