Overriding the RELEASE(DEALLOCATE) option for packages by using profile tables

You can create profiles to specify that Db2 overrides the RELEASE(DEALLOCATE) bind option for specific packages and uses RELEASE(COMMIT) behavior to release the packages. COMMIT is the only option that can be specified.

Before you begin

FL 500 Activate function level 500 or higher.

If the Db2 profile tables and related objects does not exist on the Db2 subsystem, you must create them. For a list of the objects and how to create them, see Profile tables.

The distributed data facility (DDF) must be loaded, with the DDF subsystem parameter set to AUTO or COMMAND. See DDF STARTUP OPTION field (DDF subsystem parameter).

This task describes one of several uses for profile tables. For an overview of how to use profile tables and a summary of the different uses, see Monitoring and controlling Db2 by using profile tables.

About this task

A high-performance DBAT is a database access thread that stays associated with a remote connection at transaction boundaries, rather than being pooled. With high-performance DBATs, the amount of allocation and deallocation processing is reduced because the copy of the package remains allocated until the DBAT terminates. However, the same behaviors that make high-performance DBATs advantageous for performance can also make package and DDL management more difficult. The packages remain allocated and locks remain held for a longer duration. One option to allow DDL or BIND to break in, is to temporarily disable the high-performance DBATs, by issuing the -MODIFY DDF command with the PKGREL(COMMIT) option, as described in Controlling deallocation for high-performance DBATs, but that approach affects the entire subsystem. A better option is to use a profile to override the release behavior more selectively, for specific packages that hold locks on the objects that you want to alter or rebind. For more information about this use case, see Example: creating a profile that overrides package release behavior to enable break-in for a DDL statement.

When a profile overrides the release behavior for packages, the override applies to newly loaded packages. For packages already loaded when the -START PROFILE command is issued, the release behavior is overridden at the end of a transaction.

Profiles for the RELEASE_PACKAGE keyword can apply to local applications only, remote applications only, or both. For more information, see the description of the ATTRIBUTE2 column.

Procedure

To specify by using a profile that Db2 releases RELEASE(DEALLOCATE) packages at commit, complete the following steps:

In the SYSIBM.DSN_PROFILE_TABLE table, insert a row to create the profile and specify its filtering criteria:
1. In the PROFILEID column, specify a unique value or accept the generated default value. This value identifies the profile and the relationship between DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES rows.
2. Specify the filtering criteria of the profile.
  You can specify values in the columns from one of the following filtering categories.
  For local threads and remote threads (DBATs), you can use the following filtering categories:
  - AUTHID, ROLE, or both
  - COLLID, PKGNAME, or both
  - One of CLIENT_APPLNAME, CLIENT_USERID, or CLIENT_WRKSTNNAME
  You can also specify the following filtering categories for DBATs, but not for local threads:
  - LOCATION only
  - PRDID only
  The filtering values are not case-sensitive, and profiles can match regardless of the case of the input values.
  Other filtering columns must contain the null value.
  
  Tip: If you create multiple profiles with overlapping filtering criteria, Db2 applies only one profile from each filtering category, based on a specific order of precedence. If multiple DSN_PROFILE_TABLE rows specify the same filtering criteria, only the newest is row is accepted when you start the profiles, and the other duplicates are rejected. Also, exact values take precedence over values that use an asterisk (*) wildcard. However, profiles from different filtering categories can all apply. For more information about these rules, see How Db2 applies multiple matching profiles for threads and connections.
3. In the PROFILE_ENABLED column, specify 'Y' so that the profile is enabled when profiles are started.
  If the PROFILE_AUTOSTART subsystem parameter setting is YES, the profile starts when you issue a START PROFILE command or when Db2 starts.
Insert one or more SYSIBM.DSN_PROFILE_ATTRIBUTES table rows:
1. Specify the PROFILEID value from the DSN_PROFILE_TABLE row that specifies the filtering criteria for this profile.
  
  Tip: Use the same PROFILEID value for any DSN_PROFILE_ATTRIBUTES rows that require the same filtering criteria. If multiple DSN_PROFILE_TABLE rows contain exactly matching filtering criteria, only the newest duplicate row is accepted when you start the profiles, and the others are rejected and disabled.
2. In the KEYWORDS column, specify 'RELEASE_PACKAGE'.
3. In the ATTRIBUTEn columns, specify the attributes of the profile:
ATTRIBUTE1

COMMIT is the only valid value, and it specifies that Db2 uses release commit behavior regardless of value that is specified when a package was bound.

ATTRIBUTE2

For RELEASE_PACKAGE, a value that indicates whether the attribute applies to local or remote threads.

NULL

The row applies to remote threads only. The profile is processed when each package is loaded.

1

The row applies to local threads only. The profile is evaluated when each package is loaded.

2

The row applies to local and remote threads. For remote threads, the behavior is the same as if the value is NULL. For local threads, the behavior is the same as if the value is 1.

ATTRIBUTE3

NULL
Load or reload the profile tables into memory by issuing a START PROFILE command. (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.) For more information, see Starting and stopping profiles.
Check the status of all newly added profiles in the STATUS columns of the DSN_PROFILE_HISTORY and DSN_PROFILE ATTRIBUTES_HISTORY tables.
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.

Example: creating a profile that overrides package release behavior to enable break-in for a DDL statement

You can create a profile to temporarily override the release behavior of packages. The goal is to increase the chances that a data definition (DDL) statement can successfully break in and complete when long-running threads hold locks on the object.

For example, assume that you need to add a column to a table named T1, and you want to reduce lock contention while the change is applied. To reduce contention on the package locks while you are altering the table, you can use the following approach:

Query the SYSPACKDEP catalog table to find packages that depend on table T1. Assume that the schema qualifier for T1 is DBAUSER.

SELECT DCOLLID, DNAME                -- names of the dependent packages
FROM SYSIBM.SYSPACKDEP 
WHERE BQUALIFIER = ‘DBAUSER’ AND BNAME = ‘T1’  -- name of table to be changed

Assume that query returns the following rows that identify the dependent packages as DEALLOC.P1 and DEALLOC.P2:
```
DEALLOC     P1 
DEALLOC     P2  
```
Also assume that these packages are invoked by local applications, and that they are bound with the RELEASE(DEALLOCATE) bind option.
Assume that local thread 1 executes packages DEALLOC.P1 and DEALLOC.P2.
In the SYSIBM.DSN_PROFILE_TABLE table, you insert the following row to create a profile that specifies the collection ID DEALLOC as the filter:
PROFILEID ROLE AUTHID PRDID COLLID PKGNAME

99 null null null DEALLOC null
You also insert the following row in the SYSIBM.DSN_PROFILE_ATTRIBUTES table:
PROFILEID KEYWORDS ATTRIBUTE1 ATTRIBUTE2

99 RELEASE_PACKAGE COMMIT 1
You issue the START PROFILE command to activate, which causes Db2 to reload the profile table into a data structure in memory. Now that the new profile with PROFILEID 99 is activated, the following behaviors take effect:
- The existing local thread 1, which is executing packages DEALLOC.P1 and DEALLOC.P2, is released when the transaction ends at COMMIT or ROLLBACK.
- When a new thread executes the first SQL statement in the DEALLOC.P1 package, the package is loaded and the release commit behavior applies, even though P1 was bound with RELEASE(DEALLOCATE).

PROFILEID	ROLE	AUTHID	PRDID	COLLID	PKGNAME
99	null	null	null	DEALLOC	null

PROFILEID	KEYWORDS	ATTRIBUTE1	ATTRIBUTE2
99	RELEASE_PACKAGE	COMMIT	1

You execute the following SQL statements to give the ALTER TABLE statement for table T1 a higher timeout and deadlock priority:

SET CURRENT LOCK TIMEOUT = 60   -- assuming the subsystem parameter IRLMRWT is 30  
SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY = 255  
ALTER TABLE T1 ADD C1 INT

After the ALTER TABLE completes successfully, you disable or delete the new rows in the profile tables. Then, issue the START PROFILE command again so that Db2 reloads the profile table into a data structure in memory. The profile with PROFILEID 99 is no longer activated, and new local threads can load packages DEALLOC.P1 and DEALLOC.P2 for execution.