Step 2: Editing a pattern file

About this task

When you have performed Step 1: Selecting a pattern file, the second page of the object placement wizard is displayed:
Figure 1. Object Placement – The Pattern File Editing window
This figure is a graphical representation of the Pattern File Editing window from an object placement.
This page shows the object placement rules from the selected pattern file.
  • The list of rules acts like a series of filters. The object placement wizard processes all objects (table spaces and index spaces) through these rules, starting at the top of the list. If the characteristics of an object match the criteria for a specific buffer pool, the object is assigned to this buffer pool.
  • Each rule specifies a series of object characteristics as criteria for a buffer pool. If an object matches all criteria of a rule, the wizard recommends it for placement in the corresponding buffer pool. If an object does not match all criteria of a rule, it is passed to the next rule for evaluation.
  • An object matches a rule if Page Size, Seq Access, Change Rate and Size match, and if an object is one of the selected data types Data, Index, LOB, or Sort/Temp. (Selected here means that a check box is marked. If all check boxes of a rule are cleared, an object does never match the rule.)
  • In summary, the rules causes a 1:n mapping of buffer pools to objects. Objects with similar characteristics are assigned to the same buffer pool.
You can use this page to adjust the object placement rules for this session, and you can save the rules under a user-defined name for future use with performance data from the same DB2® subsystem.
  • You can add more placement rules by clicking Add, and you can delete selected rules by clicking Remove.
  • You can define more than one rule for a specific buffer pool, which permits a buffer pool to contain objects with different characteristics.
  • You can also change the position of a placement rule in the list by clicking Move Up or Move Down.
  • To change one of a rule's values, double-click the value and edit it. Then press Enter or select a different field.
If you adjust rules in pattern files:
  • Ensure that all objects are covered by at least one rule. Otherwise, you cannot complete the next step.
  • Place rules with restrictive criteria at the top of the list, and those with more general criteria at the bottom. Otherwise, the more specific rule might never become active.

    Example: Assume that you want objects with a Change Rate above 80 percent to be assigned to buffer pool BP3, all others to BP4. The recommended way is to specify the first rule for BP3 with a criteria range of 80-100 percent, followed by another rule for BP4 with a criteria range of 0-80 percent. Alternatively, the second rule could also have a criteria range of 0-100 percent, which matches everything that did not match the first rule.

  • If criteria ranges are specified, as in 50-80 percent, the algorithms consider the lower bound as inclusive (≥), the upper bound as exclusive (<).

    Example: If two rules have specified criteria ranges of 50-80 and 80-90 percent, the first rule matches values equal or greater (≥) 50 and less than (<) 80, and the second rule matches values equal or greater (≥) 80 and less than (<) 90. The value 100 as an upper bound is an exception; it is interpreted as less or equal (≤) 100. Successive range specifications of, for example, 0-80 and 81-100 percent are likely to be erroneous.

  • The specification of overlapping ranges in different rules for the same criterion is allowed. Nevertheless, the first rule has priority and consequently reduces the effect of any following rule to the non-overlapping part of the range.

    Example: Assume a single criterion where a rule specifies a criteria range of 10-70 percent, and a subsequent rule specifies a criteria range of 50-100 percent. Here, the overlapping range from 50 to 70 percent in the second rule is without effects because the first rule has priority. The second rule only gets objects for evaluation in the range above 70 percent.

  • Note that Db2 catalog objects and directory objects always remain in their original buffer pool (which is BP0 for Db2 Version 7 and earlier). These objects are not taken into account by the object placement algorithms.
The following list describes the elements of a rule, as shown on the page:
Rule
Shows the numerical sequence in which the object placement rules are applied to each object.

You can change the sequence by selecting the rule, and clicking Move Up or Move Down.

Name
Shows a buffer pool name, such as BP0 or BP16K9.
Page
Shows the size of each buffer pool page. The size is implicitly extracted from the name of the buffer pool. Buffer pool BP32K, for example, always has a page size value of 32 KB.
Seq Access
Specify a percentage range as criteria for an object's sequential accesses. Objects with sequential accesses within the specified range, out of all accesses, are assigned to the specific buffer pool (if the other criteria are also met).

Example: A percentage range of 50-80 considers objects that are on average accessed sequentially 50 to less than 80 times out of 100 accesses.

You can also use the following notations to specify percentage ranges:
  • -50 is the same as 0 to <50%
  • 50- is the same as 50 to ≤100%
  • all is the same as 0 to ≤100%. If the Assign objects not accessed during data collection check box is selected, all also includes objects for which no sequential access characteristics could be determined. (These objects are marked as N/C (not calculated), which means a formula could not be applied because of missing data.)
Change Rate
Specify a percentage range as criteria for an object's change rate. Objects with a change rate within the specified range, out of all accesses, are assigned to the specific buffer pool (if the other criteria are also met).

Example: A percentage of 50-80 considers objects that are changed 50 to less than 80 times out of 100 accesses.

You can also use the following notations to express percentage ranges:
  • -50 is the same as 0 to <50%
  • 50- is the same as 50 to ≤100%
  • all is the same as 0 to ≤100%. If the Assign objects not accessed during data collection check box is selected, all also includes objects for which no change rate characteristics could be determined. (These objects are marked as N/C (not calculated), which means a formula could not be applied because of missing data.)
Size
Specify a size range as criteria for an object's size. Objects with a size within the specified range are assigned to the specific buffer pool (if the other criteria are also met). Sizes are expressed as numbers of buffer pool pages. One buffer pool page can be 4, 8, 16, or 32 KB, depending on the page size of the buffer pool.

Example: A size range of 0-12 considers objects with a size of less than 12 buffer pool pages. For a buffer pool having a page size of 4 KB this affects objects having a size of less than 48 KB.

You can also use the following notations to express size ranges:
  • -12 is the same as 0 to <12 pages
  • 50- is the same as 50 to the maximum object size pages
  • all is the same as 0 to the maximum object size pages, and includes also objects for which the size could not be determined (marked as ?).
Data
Select this check box if you want table space objects to be assigned to the specific buffer pool (if the other criteria are also met). Note that this field does not include LOB and Sort/Temp data. These must be explicitly selected if required.
Index
Select this check box if you want index space objects to be assigned to the specific buffer pool (if the other criteria are also met).
LOB
Select this check box if you want table space objects of data type LOB to be assigned to the specific buffer pool (if the other criteria are also met).
Sort/Temp
Select this check box if you want database objects of type Sort (work files) or Temp to be assigned to the specific buffer pool (if the other criteria are also met). This should only be selected for buffer pools having a page size of 4 KB or 32 KB.
Comment
Initially, this column shows a descriptive text for each rule. You can edit this information.
  1. Review the rules on this page. Adjust them as required.
  2. Use the Assign objects not accessed during data collection check box to indicate whether you want to include unused objects in the rule processing. Unused objects are table spaces and index spaces that are defined in the database catalog, but did not show any access or change information during the time trace data was collected. Note that unused (inactive) objects can be explicitly excluded from bpd files. If such bpd file was opened, the use of the check box has no effect.

    If this check box is selected, unused objects are considered by the object placement rules. They match the all criteria for Seq Access and Change Rate. (Unused objects are marked as N/C (not calculated) in Step 3: Assigning objects to buffer pools, because they have no sequential access or change rate characteristics.)

    If this check box is cleared, unused objects are left in their current buffer pools.

    Note that any change that you make to this selection is retained for the next time you use the wizard.

  3. If you want to save the pattern file, click the icon to the right of the current pattern file name and save it under a user-defined name. The file name extension should be pat.
  4. Click Next and continue with Step 3: Assigning objects to buffer pools, or click Back to return to the previous page.