Modifying an optimization profile

When you make a change to an optimization profile there are certain steps that need to be taken in order for the changes in the optimization profiles to take effect.

About this task

When an optimization profile is referenced, it is compiled and cached in memory; therefore, these references must also be removed. Use the FLUSH OPTIMIZATION PROFILE CACHE statement to remove the old profile from the optimization profile cache. The statement also invalidates any statement in the dynamic plan cache that was prepared by using the old profile (logical invalidation).


To modify an optimization profile:

  1. Edit the optimization profile XML file on disk, make the necessary changes, save the file to disk.
  2. Validate that the changes made to the file are well formed XML as defined in the current optimization profile schema (COPS) in the DB2OptProfile.xsd file, which is located in the misc subdirectory of the sqllib directory.
  3. Update the existing row in the SYSTOOLS.OPT_PROFILE table with the new profile.
  4. Ensure the new optimization profile is used:
    • If you did not create triggers to flush the optimization profile cache, issue the FLUSH OPTIMIZATION PROFILE CACHE statement. The statement removes any versions of the optimization profile that might be contained in the optimization profile cache.

      When you flush the optimization profile cache, any dynamic statements that were prepared with the old optimization profile are also invalidated in the dynamic plan cache.

    • If you have bound an optimization profile to a package of static statements, then you will need to re-bind the package, using the OPTPROFILE command parameter again to specify the modified optimization profile.


Any subsequent reference to the optimization profile causes the optimizer to read the new profile and to reload it into the optimization profile cache. Statements prepared under the old optimization profile are logically invalidated. Calls made to those statements are prepared under the new optimization profile and recached in the dynamic plan cache.