Db2 packages get invalidated whenever the changes in database objects such as database, tables, tablespaces, and indexes. DBAs have to modify these objects based on the requirements. It can be simple or in-depth changes that may take you to DROP and re-CREATE the object. For instance, during database upgrade, user applications and routines are marked as invalid due to some function instance on which it depends has been dropped. Sometimes, the rebinding is enough for the invalid package but for some changes you need to bind a new object.
Cascading Effects
The db2 packages becomes invalid when they depend on the target object, and on the objects that are affected by cascading effects of the following actions.
- Altering Tables
An altering table refers to changing the definition of a table with the help of “ALTER TABLE” statement. It includes changing the default value of the rows such as current date, time, and time stamp with a new time, timestamp, and date. It also includes, adding a constraint with delete rule, adding security label, rotating partitions in a partitioned or partition-by range table space, altering the attributes etc.
- Altering Views
Altering views refers to modifying an existing view by changing the reference type column to add a scope and enabling or disabling the view for use while optimizing queries. Existing view can be modified using ALTER VIEW statement.
- Altering Table Spaces
Altering table spaces refers to changes made to an existing table space using ALTER TABLESPACE statement.
- Altering Indexes
Altering Indexes refers to modifying the definition of an index using ALTER INDEX statement. It includes adding a column, modifying an index to regenerate it, modifying attributes and key value of a partitioning index.
- Alter or Regenerating the Procedures
Altering procedures refers to changing the definition of a SQL procedure at the current server using ALTER PROCEDURE statement. Using ALTER PROCEDURE statement, you can change the procedure options, parameter names, and routine body. Also, using the statement, you can define and maintain the additional procedures.
- Altering Functions
Altering functions refers to modifying the properties of an existing function using ALTER FUNCTION statement.
Altering functions includes altering the external functions, in lined scalar function, and SQL table functions.
- 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 such as global variables, aliases, indexes, materialized query tables, roles, tables, table spaces, sequences, triggers, views
- Dropping row permissions or column masks
- Revoking authorization from the package owner to-
- Access a table, index, or view
- Execute a stored procedure
- Running the REORG utility with the REBALANCE keyword
- Running the REPAIR utility on a database with the DBD REBUILD option
- Renaming a Column
Cascading effect involving renaming the column using ALTER TABLE RENAME COLUMN statement will invalidate any package that belongs to that column.
So, the Db2 packages get invalidated whenever the above-discussed modifications are made and as a result of cascading effects.
