Changes that invalidate packages

Changes to your program or database objects can invalidate packages.

A change to your program probably invalidates one or more of your packages. For some changes, you must bind a new object. For others, rebinding is sufficient. A package can also become invalid for reasons that do not depend on operations in your program. For example, when an index is dropped that is used in an access path by one of your queries, a package can become invalid.

DB2® might rebind invalid packages automatically the next time that the package is run. For details, see Automatic rebinds.

DB2 marks packages invalid when they depend on the target object, and sometimes on related objects that are affected by cascading effects, of the following actions:

  • Altering tables:
    • Adding a TIME, TIMESTAMP, or DATE column when the default value for added rows is CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP (p) WITHOUT TIME ZONE, or CURRENT TIMESTAMP (p) WITH TIME ZONE respectively
    • Adding a constraint with a delete rule of SET NULL or CASCADE. Packages that depend on tables that cascade deletes to the altered parent table are also invalidated.
    • Adding a security label
    • Adding, changing, or rotating partitions in a partitioned (non-UTS) or partition-by range (UTS) table space
    • Altering or dropping a column
    • Renaming a column. Cascading effects apply. See Cascading effects on packages of renaming a column.
    • When a column is renamed, cascading effects apply.
    • Altering a column such that a view cannot regenerate
    • Altering the AUDIT attribute
    • Altering for hash organization, or dropping hash organization
    • Adding or removing a BUSINESS_TIME period for temporal versioning
    • Enabling or disabling transparent archiving
    • Adding, altering, or dropping a materialized query table (MQT) definition
    • Dropping a clone table
    • Activating or deactivating row-level access control
    • Activating column-level access control if the table has an enabled column, or deactivating column-level access control
    • For created temporary tables, adding a column
  • Altering table spaces:
    • Changing the SBCS CCSID attribute
    • Increasing the MAXPARTITIONS attribute
    • Changing the SEGSIZE attribute to convert to convert the table space to a partition-by-range (UTS) table space
    • Changing the DSSIZE attribute of a partitioned table space
    • Applying pending definition changes
    • Changing the buffer pool page size
  • Altering indexes:
    • Adding a column
    • Altering an index to regenerate it
    • Altering the PADDED or NOT PADDED attribute
    • Altering a limit key value of a partitioning index
    • Specifying NOT CLUSTER for the partitioning index of a table that uses index-controlled partitioning, to convert the table to use table controlled partitioning
    • Applying pending definition changes
  • Regenerating procedures
  • Altering functions:
    • Altering an external function
    • Altering an inlined SQL scalar function
    • Altering a version of a compiled SQL scalar function to change any option that is specified for the active version
    • Altering SQL table functions:
      • Altering the SECURED or NOT SECTURED attribute
      • If RESTRICT is specified, altering the DETERMINISTIC or NOT DETERMINISTIC attribute
      • Regenerating a table function
  • Enabling or disabling masks if column access control is in effect
  • Dropping the package
  • Dropping a package that provides the execute privilege for a plan
  • Dropping objects, including aliases, indexes, roles, materialized query tables, sequences, tables, table spaces, triggers, views
  • If column access control is enforced for a table, dropping row permissions or column masks
  • Revoking authorization from the package owner to access a table, index, or view
  • Revoking authorization from the package owner execute a stored procedure, if the package uses the CALL procedure-name form of the CALL statement to call the stored procedure
  • Running the REORG utility with the REBALANCE keyword
  • Running the REPAIR utility on a database with the DBD REBUILD option
  • A pre-DB2 10 plan has DBRMs bound directly to it. If the DISALLOW_DEFAULT_COLLID subsystem parmeter is set to NO, automatic rebind binds the DBRMs into packages, and binds the packages into plans with the collection ID DSN_DEFAULT_COLLID_plan-name.

Cascading effects on packages of renaming a column

ALTER TABLE RENAME COLUMN will invalidates any package that depends on the table in which the column is renamed. Any attempt to execute the invalidated package triggers an automatic rebind of the package.

The automatic rebind fails if the column is referenced in the package because the referenced column no longer exists in the table. In this case, applications that reference the package need to be modified, recompiled, and rebound to return the expected result.

The automatic rebind succeeds in either of the following cases:

  • The package does not reference the column. In this case, the renaming of the column does not affect the query results that are returned by the package. The application does not need to be modified as a result of renaming the column.
  • The package does reference the column, but after the column is renamed, another column with the name of the original column is added to the table. In this case, any query that references the name of the original column might return a different result set. In order to restore the expected results, the application would need to be modified to specify the new column name.

Example

The following scenario shows how renaming a column can cause a package to return unexpected results: 
CREATE TABLE MYTABLE (MYCOL1 INT);
INSERT INTO TABLE MYTABLE 
	VALUES (1);
SELECT MYCOL1 FROM MYTABLE -- this is the statement in
													  --  the package MYPACKAGE, 
                           --  the query returns 
	                          --  a value of 1
ALTER TABLE MYTABLE 
   RENAME COLUMN 
    MYCOL1 TO MYCOL2;      -- MYPACKAGE is invalidated
                           --  and automatic rebind
                           --  of MYPACKAGE will fail
                           --  at this point
ALTER TABLE MYTABLE 
   ADD COLUMN MYCOL1 VARCHAR(10);  -- automatic rebind
                                   --  of MYPACKAGE 
                                   --  will be successful
INSERT INTO TABLE MYTABLE (MYCOL1) 
   VALUES ('ABCD');

At this point an application executes MYPACKAGE, which results in a successful automatic rebind. However, the statement in the package will return 'ABCD' instead of the expected '1'.