RULE 082 THRESHOLD VALUE

Document ID : KB000012551
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

Rule 82 has a statement that "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?      

                                                                              

Answer:

The threshold values are initially set at product installation time and the values are a best estimate from the CA development staff knowledge.  In rule  82, this value has no corresponding DSNZPARM.  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.                                                   

First, this is a value you can control by using Option 5 in the Plan Analyer product.  We talked about this already and this is the easy answer.  However,  if the question is really, 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.