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 more information, see Automatic rebinds.

FL 504 Db2 uses autobind phase-in for packages that are marked with statement-level invalidation. Packages with invalid statements can be allocated, and valid statements can execute without waiting for the rebind to complete. However, invalid statements must go through incremental bind before they execute. For more information, see Autobind phase-in for packages with statement-level invalidation and Enabling autobind phase-in for packages invalidated at the statement level.

How Db2 marks invalid packages

In most cases, Db2 marks a package that must be automatically rebound as invalid by setting VALID='N' in the SYSIBM.SYSPLAN and SYSIBM.SYSPACKAGE catalog tables. Start of change FL 502 However, if statement-level invalidation is enabled for a package, Db2 can mark the package invalid at a statement level by setting VALID='S' in the SYPACKAGE catalog table only. End of change

Actions that cause Db2 to invalidate packages

Db2 marks packages invalid when they depend on the target object, and sometimes on related objects that are affected by cascading effects, of the actions that are listed in the following table.

Object or operation	Changes that invalidate packages
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 Altering a column Renaming a column (Cascading effects apply. See Cascading effects on packages of renaming a column.) Altering a table column such that a view cannot regenerate Altering the AUDIT attribute. Dropping a column. For pending definition changes, the package invalidation occurs when the pending definition change is applied to the table. 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 for a table 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 FL 500 Converting tables in partition-by-growth (PBG) table spaces to partition-by-range (PBR)
Table spaces	Changing the SBCS CCSID attribute Increasing the MAXPARTITIONS attribute Changing the SEGSIZE attribute to convert the table space to a partition-by-range (UTS) table space Changing the DSSIZE attribute of a partitioned table space Changing the buffer pool page size Materializing pending definition changes to table spaces with the REORG TABLESPACE utility. For more information, see Pending data definition changes. Altering partitions of partition-by-range (PBR) or partitioned (non-UTS) table spaces, including: adding partitions, altering limit keys, or rotating partitions. FL 500 Converting tables in partition-by-growth (PBG) table spaces to partition-by-range (PBR)
Partitions	Altering partitions of partition-by-range (PBR) or partitioned (non-UTS) table spaces, including: adding partitions, altering limit keys, or rotating partitions. FL 500 Converting tables in partition-by-growth (PBG) table spaces to partition-by-range (PBR)
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 Materializing pending definition changes to indexes with the REORG INDEX utility. For more information, see Pending data definition changes.
Views	Altering a view to regenerate it Altering a table column such that a view cannot regenerate
Packages	Dropping the package Dropping a package that provides the execute privilege for a plan
Routines	Regenerating procedures. For more information, see the information about invalidation of packages in ALTER PROCEDURE (SQL - native). Altering an external function Altering an inlined SQL scalar function Altering a version of a compiled SQL scalar function to change certain options that are specified for the active version. For more information, see the information about invalidation of packages in ALTER FUNCTION (compiled SQL scalar). Altering a procedure with the ACTIVATE VERSION `routine-version-id` option, if the value of `routine-version-id` is different from the current active version of the procedure. For more information, see the information about invalidation of packages in ALTER PROCEDURE (SQL - native). Altering SQL table functions: Altering the SECURED or NOT SECURED attribute Altering the DETERMINISTIC or NOT DETERMINISTIC attribute, regardless of whether RESTRICT is specified Regenerating a table function
Dropping objects	Dropping the package Dropping a package that provides the execute privilege for a plan Dropping objects such as aliases, functions, global variables, indexes, materialized query tables, roles, sequences, synonyms, tables, table spaces, triggers, views Dropping a clone table Dropping a column. For pending definition changes, the package invalidation occurs when the pending definition change is applied to the table. Dropping row permissions or column masks if column access control is enforced for a table
Authorization and access control changes	Revoking authorization from the package owner to access a table, index, or view Revoking authorization from the package owner to execute a stored procedure, if the package uses the CALL `procedure-name` form of the CALL statement to call the stored procedure Enabling or disabling masks if column access control is in effect Dropping a package that provides the execute privilege for a plan Dropping row permissions or column masks if column access control is enforced for a table Activating or deactivating row-level access control for a table Activating column-level access control if the table has an enabled column, or deactivating column-level access control
Utility operations	Materializing pending definition changes to table spaces with the REORG TABLESPACE utility. For more information, see Pending data definition changes. Materializing pending definition changes to indexes with the REORG INDEX utility. For more information, see Pending data definition changes. Running the REORG utility with the REBALANCE keyword Running the REPAIR utility on a database with the DBD REBUILD option

Tip:

Some alterations do not invalidate packages that depend on the required objects. However, you might sometimes still need to rebind packages for the application to pick up the changes. For more information, see Changes that might require package rebinds. End of change

Cascading effects on packages of renaming a column

ALTER TABLE RENAME COLUMN 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.

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'.