REBIND TRIGGER PACKAGE (DSN)

The DSN subcommand REBIND TRIGGER PACKAGE rebinds a package that was created when DB2® executed a CREATE TRIGGER statement.

You can use this subcommand to change a limited subset of the default bind options that DB2 used when creating the package. You might also rebind a trigger package to re-optimize its SQL statements after you create a new index or use the RUNSTATS utility. Additionally, you can rebind a trigger package if it has been marked invalid because an index, or another object it was dependent on, was dropped.

If the rebind is successful, the trigger package is marked valid. When REBIND TRIGGER PACKAGE(*) is issued, the rebind will affect all trigger packages that the issuer is authorized to rebind.

Trigger packages cannot be rebound remotely. The location name is permitted when specifying the package name on a REBIND TRIGGER PACKAGE subcommand. However, the location name must not refer to a remote location.

Environment

You can use REBIND TRIGGER PACKAGE through DB2I, or enter the REBIND TRIGGER PACKAGE subcommand from a DSN session that is running in foreground or background.

Data sharing scope: Group

Authorization

To build a package without producing error messages, the package owner must have authorization to execute all SQL statements that are embedded in the package for REBIND TRIGGER PACKAGE.

To execute this subcommand, you must use a privilege set of the process that includes one of the following privileges or authorities:

Ownership of the trigger package
BIND privilege on the trigger package
BINDAGENT privilege from the owner of the trigger package
PACKADM authority on the collection or on all collections
System DBADM authority
SYSCTRL authority
SYSADM authority

When the trigger package is bound, the privileges of the current authorization ID are used when checking authority to bind statements within the triggered action. On REBIND TRIGGER PACKAGE, you need one of the following privileges or authorities:

Ownership of the trigger package
BIND privilege on the trigger package
BINDAGENT privilege from the owner of the trigger package
PACKADM authority on the collection or on all collections
System DBADM authority
SYSCTRL authority
SYSADM authority

Start of change When the EXPLAIN(ONLY) the option is specified, you must have the EXPLAIN privilege. End of change

Syntax

>>-REBIND TRIGGER PACKAGE--------------------------------------->

>--(-+----------------+-+-collection-id-+-.-+-package-id-+-)---->
     '-location-name.-' '-*-------------'   '-*----------'     

>--options-block--+------------------------+-------------------->
                  |              .-NO--.   |   
                  '-CURRENTDATA(-+-YES-+-)-'   

>--+-------------------------------------------------+---------->
   '-EXPLAIN(-+-YES------------------------------+-)-'   
              +-NO-------------------------------+       
              '-Start of changeONLYEnd of change-'       

>--+---------------+--+-----------------------+----------------->
   |       .-I-.   |  '-IMMEDWRITE(-+-NO--+-)-'   
   '-FLAG(-+-W-+-)-'                '-YES-'       
           +-E-+                                  
           '-C-'                                  

>--+---------------------+--+---------------------------+------->
   '-ISOLATION(-+-RR-+-)-'  '-RELEASE(-+-COMMIT-----+-)-'   
                +-RS-+                 '-DEALLOCATE-'       
                +-CS-+                                      
                +-UR-+                                      
                '-NC-'                                      

>--+------------------------+----------------------------------->
   '-SWITCH(-+-PREVIOUS-+-)-'   
             '-ORIGINAL-'       

>--+---------------------------------------------------------+-><
   '-CONCURRENTACCESSRESOLUTION(-+-USECURRENTLYCOMMITTED-+-)-'   
                                 '-WAITFOROUTCOME--------'

Start of change options-block End of change

>>-+--------------------------+--+----------------------+------->
   '-PLANMGMT(-+-BASIC----+-)-'  |          .-NONE--.   |   
               +-EXTENDED-+      '-APREUSE(-+-ERROR-+-)-'   
               '-OFF------'                                 

>--+------------------------+--+--------------------------+----><
   |            .-NONE--.   |  |              .-NONE--.   |   
   '-APCOMPARE(-+-WARN--+-)-'  '-APRETAINDUP(-+-WARN--+-)-'   
                '-ERROR-'                     '-ERROR-'

Option descriptions

TRIGGER PACKAGE

Determines what trigger package or packages to rebind.

The following options identify the location, collection, and package name of the package. You can identify a location and collection. For REBIND TRIGGER PACKAGE, you must identify a trigger package name.

location-name: Identifies the current local location. Remote rebind of a trigger package is not allowed. location-name is the location of the DBMS where the package rebinds and where the description of the package resides.
The default is the local DBMS.
collection-id or *: Identifies the schema-name that already contains the trigger package to rebind. No default exists.
For REBIND TRIGGER, you can use an asterisk (*) to rebind all local packages with the specified package-id in all the collections for which you have bind privileges.
package-id or *: Identifies the name of the trigger package to rebind, as listed in the NAME column of the SYSPACKAGE catalog table. No default exists.
You can use the pattern-matching character (*) to rebind all local triggers in collection-id for which you have bind privileges.

For descriptions of the options that are shown in the syntax diagram, see the topic BIND and REBIND options for packages and plans.

Usage notes

Restrictions on trigger packages: A trigger package can be explicitly rebound, but it cannot be explicitly bound using the BIND PACKAGE subcommand.

A trigger package cannot be explicitly freed using the FREE PACKAGE subcommand or the DROP PACKAGE statement. Use the DROP TRIGGER statement to delete the trigger package.

A trigger package cannot be copied, and it can only be rebound locally. Remote rebind of a trigger package is not allowed.

Rebinding multiple trigger packages: If you rebind multiple trigger packages, DB2 commits each successful rebind before rebinding the next package.

Restriction on trigger names: Although DBCS characters are generally allowed in trigger names, trigger names that contain DBCS characters cannot be used in REBIND TRIGGER PACKAGE operations.

Output

REBIND TRIGGER PACKAGE updates the COLLID and NAME columns in the SYSPACKAGE catalog table.

Example

Enter the following command to rebind trigger package TRIG1 in the ADMF001 collection of packages:

REBIND TRIGGER PACKAGE (ADMF001.TRIG1);

This command produces output that is similar to the following output:

DSNT254I - DSNTBRB2 REBIND OPTIONS FOR
           PACKAGE = STLEC1.ADMF001.TRIG1.()
           ACTION
           OWNER         ADMF001
           QUALIFIER     ADMF001
           VALIDATE      BIND
           EXPLAIN       NO
           ISOLATION     CS
           RELEASE       COMMIT
           COPY
DSNT255I - DSNTBRB2 REBIND OPTIONS FOR
           PACKAGE = STLEC1.ADMF001.TRIG1.()
           SQLERROR      NOPACKAGE
           CURRENTDATA   YES
           DEGREE        1
           DYNAMICRULES  BIND
           NODEFER       PREPARE
           REOPT         NONE
           KEEPDYNAMIC   NO
           DBPROTOCOL    DRDA
           QUERYOPT      1
           PATH
"SYSIBM","SYSFUN","SYSPROC","SYSADM","ADMF001"
DSNT232I - SUCCESSFUL REBIND FOR
           PACKAGE = STLEC1.ADMF001.TRIG1.()