Starting and stopping profiles

You must enable and start profiles before Db2 can use the information in the profile tables. After you modify an existing, active profile row, or you insert a new profile row, you need to issue the -START PROFILE command again to apply the changes.

Before you begin

Before you can start profiles the following prerequisites must be met:
  • A set of profiles exist on the Db2 subsystem.
  • The SYSIBM.DSN_PROFILE_TABLE and SYSIBM.DSN_PROFILE_ATTRIBUTES contain rows of data that define valid profiles and the action that Db2 takes when a context meets the definition of a profile.
Tip: For best results, do not issue a STOP PROFILE command when you add or modify existing profiles. Use the STOP PROFILE command only if you intend to disable all existing profiles.

About this task

A system profile is a set of criteria that identifies a specific context on a Db2 subsystem. Examples include threads, connections, or SQL statements that have certain attributes.
Important: If the value of the PROFILE_AUTOSTART subsystem parameter is set to YES, the START PROFILE command is issued automatically as part of Db2 start processing.

Db2 supports a maximum of 4096 active DSN_PROFILE_TABLE rows.

Procedure

To start or stop profiles, complete the following steps:

  • To start a profile, issue a START PROFILE command.
    Important: The START PROFILE command has member scope in Db2 data sharing, so is best to run the command on each member to ensure consistent results from all members.
    Db2 activates the functions specified in the profile tables for every valid row of the SYSIBM.DSN_PROFILE_TABLE table that contains PROFILE_ENABLED='Y'. Profiles in rows that contain PROFILE_ENABLED='N' are not started.
  • When you no longer want Db2 to apply the actions that you specified in the profile tables, disable the profile with one of the following actions:
    Option Description
    To disable the a specific profile Delete that row from DSN_PROFILE_TABLE, or change the PROFILE_ENABLED column value to 'N'. Then, reload the profile table by issuing the following command:
    START PROFILE
    To disable all actions that are specified in the profile tables Issue the following command:
    STOP PROFILE
    End program-specific programming interface information.

What to do next

After you modify an existing, active profile row, or insert a new profile row, you must issue the -START PROFILE command again to apply the changes. For profiles that affect access path selection, you must invalidate the statements in the dynamic statement cache before changes to profile attribute values are applied for those statements.

Important: Always check the status of any newly added profile table rows in the STATUS columns of DSN_PROFILE_HISTORY and DSN_PROFILE ATTRIBUTES_HISTORY tables after you issue the START PROFILE command. Successful completion of the START PROFILE command does not imply that all profiles started successfully. If the STATUS column of either history table contains a value that does not start with 'ACCEPTED', further action is required to enable the profile or the keyword action.