16.3.2 Optimizing information collection levels

When collecting optimizing information by retrieval, you can specify the level of information to be collected. This is called optimizing information collection level.

Table 16-4 shows the advantages and disadvantages of each optimizing information collection level.

Table 16-4 Advantages and disadvantages of each optimizing information collection level

Optimizing information collection levelAdvantageDisadvantage
lvl1
  • Optimization precision is comparatively high because this level lets you incorporate the number of rows in the table in the optimization and the data characteristics in the join order.
  • The execution time of pdgetcst is comparatively short.
  • Data updating has effects on the optimization.
  • If the expected access path is not obtained, you must take into account the cost information on the number of rows in the table, thereby making it difficult to predict the access path.
lvl2Optimizing precision is high because this level lets you incorporate the number of rows in the table, maximum and minimum values, and distribution information, in the optimization and data characteristics in the selection of join order and indexes.
  • Data updating has effects on the optimization.
  • The pdgetcst execution time is long.
  • If the expected access path is not obtained, you must take into account all the cost information that has been acquired, thereby making it difficult to predict the access path.
  • If a multi-column index is defined, the optimizing precision is low because the utility obtains information only on the first index component column.

Check the characteristics of the table to be accessed and determine the appropriate optimizing information collection level. In some cases, it may be better to not obtain the optimizing information (to not execute pdgetcst).

If you are not collecting the optimizing information, there is no need to pay attention to the changes made to data by update-SQL. In this case, however, the optimizing precision is not high because the data characteristics are not incorporated in the optimization.

Table 16-5 shows the recommended optimizing information collection level.

Table 16-5 Recommended optimizing information collection level

No.Characteristics of table to be accessedRecommended optimizing information collection level
1Both of the following conditions are satisfied:
  • No application that involves an updating operation is executed
  • The SQL statement to be executed contains columns comprising the index in the search condition.
lvl2
2All of the following conditions are satisfied:
  • Conditions in No. 1 do not apply.
  • Cost-based optimization mode is 2.
  • An application that involves an updating operation and that changes the number of storage rows is not executed.
  • One of the following conditions is true:
    [Figure]Hash join is performed.
    [Figure]Hash execution of subquery is executed.
    [Figure]Join search involving 3 or more tables is performed.
lvl1
3OtherDo not collect optimizing information (do not execute pdgetcst).