The CALL SYSPROC.REBIND_ROUTINE_PACKAGE statement in IBM Db2 is used to rebind the package associated with a specific routine, such as a stored procedure or user-defined function (UDF). Rebinding is necessary when you want to refresh or optimize the access plan for a routine after changes to database objects, statistics, or privileges.
Rebind Packages: When you modify the underlying objects used by a routine (like tables, views, or other routines), the associated package might become invalid. This command forces DB2 to rebind the package, which means it recreates the access plan for the routine based on the latest object definitions.
Improve Performance: Rebinding can sometimes improve the performance of your routines, especially if the underlying objects have changed significantly or if the existing access plan is no longer optimal.
CALL SYSPROC.REBIND_ROUTINE_PACKAGE(
'<ROUTINE_SCHEMA>', -- Schema of the routine
'<ROUTINE_SPECIFIC_NAME>', -- Specific name of the routine
'<ROUTINE_TYPE>', -- Routine type ('P' for procedure, 'F' for function, 'M' for method)
'<OPTIONS>' -- Additional rebind options
);
CALL SYSPROC.REBIND_ROUTINE_PACKAGE
(
'P', -- 'P' for procedure, 'F' for function, 'T' for trigger
'SCHEMA_NAME.ROUTINE_NAME', -- Specific name of the routine
'BIND_OPTIONS'
);
<ROUTINE_SCHEMA> –
Specifies the schema of the routine.
Must be in uppercase or use double quotes for case sensitivity.
<ROUTINE_SPECIFIC_NAME> –
The specific name of the routine.
It uniquely identifies the routine even if multiple routines have the same name.
<ROUTINE_TYPE> –
Specifies the type of routine:
'P' – Stored Procedure
'F' – Function
'M' – Method
<OPTIONS> –
Optional string containing bind options.
Example: 'REOPT ONCE' or 'EXPLAIN ALL'.
Use NULL if no specific options are required.
'P': Specifies that the routine is a procedure. Use 'F' for functions and 'T' for triggers.
'SCHEMA_NAME.ROUTINE_NAME': The fully qualified name of the routine.
'BIND_OPTIONS': Optional parameters for binding, such as:
'REOPT ONCE': Reoptimize the access plan once.
'REOPT ALWAYS': Reoptimize the access plan every time the routine is executed.
'APREUSE YES': Allow application reuse of the plan.
CALL SYSPROC.REBIND_ROUTINE_PACKAGE(
'MY_SCHEMA', -- Schema
'MY_PROC', -- Specific routine name
'P', -- Type: Procedure
NULL -- No additional options
);
CALL SYSPROC.REBIND_ROUTINE_PACKAGE(
'P',
'TESTSCHEM.SEARCHTEST_TESTCOUNT',
'ANY'
);
'P': This argument specifies that the routine being rebound is a procedure. Other valid values are:
'F' for functions
'T' for triggers
'TESTSCHEM.SEARCHTEST_TESTCOUNT': This is the fully qualified name of the stored procedure. It includes the schema name (TESTSCHEM) and the procedure name (SEARCHTEST_TESTCOUNT).
'ANY': This is the bind option. It indicates that DB2 should use its default binding behavior. This is a general option that doesn't specify any specific reoptimization or reuse behavior.
CALL SYSPROC.REBIND_ROUTINE_PACKAGE(
'MY_SCHEMA',
'MY_FUNC',
'F',
'REOPT ONCE'
);
After Schema Changes – When the structure of tables, views, or indexes changes.
After Statistics Updates – To optimize performance after RUNSTATS.
Privilege Changes – When user privileges on objects referenced in routines are modified.
Version Upgrades – Following a database upgrade that may affect existing routines.
If the package does not exist, the command automatically creates it by implicitly compiling the routine.
Rebinding can have performance implications, especially if done frequently.
Consider using automatic revalidation instead of manual rebinding for better performance and maintenance in most cases.
This command is a basic example. You can use other bind options like 'REOPT ONCE', 'REOPT ALWAYS', and 'APREUSE YES' to control the reoptimization and reuse behavior of the package.
Be cautious if the routine depends on any dropped objects—it may fail during rebind.
Check SYSCAT.ROUTINES for the SPECIFICNAME if unsure about routine details.
Logs and results can be verified using db2diag.log or SYSCAT.PACKAGES.