There are nearly as many tasks when altering databases as there are in the creation of databases. These tasks update or drop aspects of the database previously created. The tasks include:
Although some of the objects in a database can be altered, the database itself cannot be altered: it must be dropped and re-created. Dropping a database can have far-reaching effects, because this action deletes all its objects, containers, and associated files. The dropped database is uncataloged in the database directories.
The following command deletes the database SAMPLE:
DROP DATABASE SAMPLE
| Note: | If you intend to continue experimenting with the SAMPLE database, you should not drop it. If you have dropped the SAMPLE database, and find that you need it again, you can re-create it. |
To add or drop database partitions from a nodegroup, you can use the ALTER NODEGROUP statement. When adding database partitions, the partitions must already be defined in the node configuration file. Refer to the SQL Reference for details on this statement.
To add a new node to the db2nodes.cfg file, use the START DATABASE MANAGER command or dbstart. The db2nodes.cfg file is not updated with the new node until a db2stop followed by a db2start. Refer to the Command Reference for details on this statement.
Once you add or drop nodes, you must redistribute the current data across the new set of nodes in the nodegroup. To do this, use the REDISTRIBUTE NODEGROUP command. For information, refer to "Redistributing Data Across Database Partitions" in the Administration Guide, Performance and to the Command Reference.
When you create a database, you create at least three table spaces: one catalog table space (SYSCATSPACE); one user table space (default name is USERSPACE1); and one temporary table space (whose default name is TEMPSPACE1). You must keep at least one of each of these table spaces, And can add additional user and temporary table spaces if you wish.
| Note: | You cannot drop the catalog table space SYSCATSPACE, and there must always be at least one temporary table space. You also cannot change the page size. |
This section discusses how to change table spaces as follows:
See Designing and Choosing Table Spaces for design information on table spaces.
You can increase the size of a DMS table space (that is, one created with the MANAGED BY DATABASE clause) by adding one or more containers to the table space.
The following example illustrates how to add two new device containers (each with 40 000 pages) to a table space on a UNIX-based system:
ALTER TABLESPACE RESOURCE
ADD (DEVICE '/dev/rhd9' 10000,
DEVICE '/dev/rhd10' 10000)
The contents of the table space are re-balanced across all containers. Access to the table space is not restricted during the re-balancing. If you need to add more than one container, you should add them at the same time.
Note that the ALTER TABLESPACE statement allows you to change other properties of the table space that can affect performance. Refer to "Table Space Impact on Query Optimization" in the Administration Guide, Performance for more information.
When you drop a user table space, you delete all the data in that table space, free the containers, remove the catalog entries, and all objects defined in the table space are either dropped or marked as invalid.
You can drop a user table space that contains all of the table data including index and LOB data within that single user table space. You can also drop a user table space that may have tables spanned across several table spaces. That is, you may have table data in one table space, indexes in another, and any LOBs in a third table space. You may drop each table space individually as long as the table space with the table data is dropped first. Or, you may drop all three table spaces at the same time in a single statement. All of the table spaces that contain tables that are spanned must be part of this single statement or the drop request will fail. Refer to the SQL Reference for details on how to drop table spaces containing spanned table data.
The following SQL statement drops the table space ACCOUNTING:
DROP TABLESPACE ACCOUNTING
You can reuse the containers in an empty table space by dropping the table space, but you must COMMIT the DROP TABLESPACE command, or have had AUTOCOMMIT on, before attempting to reuse the containers.
You cannot drop the temporary table space, because the database must always have at least one temporary table space. If you wish to, for example, add a container to an SMS Temporary Table Space, you must add a new temporary table space first and then drop the old temporary table space.
The following SQL statement creates a new temporary table space called TEMPSPACE2:
CREATE TEMPORARY TABLESPACE TEMPSPACE2
MANAGED BY SYSTEM USING ('d')
Once TEMPSPACE2 is created, you can then drop the original temporary table space TEMPSPACE1 with the command:
DROP TABLESPACE TEMPSPACE1
You can reuse the containers in an empty table space by dropping the table space, but you must COMMIT the DROP TABLESPACE command, or have had AUTOCOMMIT on, before attempting to reuse the containers.
Before dropping a schema, all objects that were in that schema must be dropped themselves or moved to another schema. The schema name must be in the catalog when attempting the DROP statement; otherwise an error is returned. In the following example, the schema "joeschma" is dropped:
DROP SCHEMA joeschma RESTRICT
Tasks that are required for modifying the structure and content of the table include the following:
Note that you cannot alter triggers for tables; you must drop any trigger that is no longer appropriate (see Dropping a Trigger), and add its replacement (see Creating a Trigger).
When a new column is added to an existing table, only the table description in the system catalog is modified, so access time to the table is not affected immediately. Existing records are not physically altered until they are modified using an UPDATE statement. When retrieving an existing row from the table, a null or default value is provided for the new column, depending on how the new column was defined. Columns that are added after a table is created cannot be defined as NOT NULL: they must be defined as either NOT NULL WITH DEFAULT or as nullable.
Columns can be added with an SQL statement. The following statement uses the ALTER TABLE statement to add three columns to the EMPLOYEE table:
ALTER TABLE EMPLOYEE
ADD MIDINIT CHAR(1) NOT NULL WITH DEFAULT
ADD HIREDATE DATE
ADD WORKDEPT CHAR(3)
A column definition includes a column name, data type, and any necessary constraints.
You can modify the characteristics of a column by increasing the length of an existing VARCHAR column. The number of characters may increase up to a value dependent on the page size used. For example, to increase a column up to 4000 characters, use something similar to the following:
ALTER TABLE ALTER COLUMN
COLNAM1 SET DATA TYPE VARCHAR(4000)
You cannot alter the column of a typed table. However, you can add a scope to an existing reference type column that does not already have a scope defined. For example:
ALTER TABLE ALTER COLUMN
COLNAMT1 ADD SCOPE TYPTAB1
For more information about the ALTER TABLE statement, refer to the SQL Reference manual.
You can only alter constraints by dropping them and then adding new ones to take their place. For more information, see:
For more information on constraints, see Defining Constraints.
You add constraints with the ALTER TABLE statement. For more information on this statement, including its syntax, refer to the SQL Reference manual.
For more information on constraints, see Defining Constraints.
Unique constraints can be added to an existing table. The constraint name cannot be the same as any other constraint specified within the ALTER TABLE statement, and must be unique within the table (this includes the names of any referential integrity constraints that are defined). Existing data is checked against the new condition before the statement succeeds.
The following SQL statement adds a unique constraint to the EMPLOYEE table that represents a new way to uniquely identify employees in the table:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT NEWID UNIQUE(EMPNO,HIREDATE)
Adding Primary and Foreign Keys:
The following examples show the ALTER TABLE statement to add primary keys and foreign keys to a table:
ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_KEY
PRIMARY KEY (PROJNO)
ALTER TABLE EMP_ACT
ADD CONSTRAINT ACTIVITY_KEY
PRIMARY KEY (EMPNO, PROJNO, ACTNO)
ADD CONSTRAINT ACT_EMP_REF
FOREIGN KEY (EMPNO)
REFERENCES EMPLOYEE
ON DELETE RESTRICT
ADD CONSTRAINT ACT_PROJ_REF
FOREIGN KEY (PROJNO)
REFERENCES PROJECT
ON DELETE CASCADE
To add constraints to a large table, it is more efficient to put the table into the check pending state, add the constraints, and then check the table for a consolidated list of violating rows. Use the SET INTEGRITY statement to explicitly set the check pending state: if the table is a parent table, check pending is implicitly set for all dependent and descendent tables.
When a foreign key is added to a table, packages and cached dynamic SQL containing the following statements may be marked as invalid:
See Statement Dependencies When Changing Objects for information.
Adding a Table Check Constraint:
Check constraints can be added to an existing table with the ALTER TABLE statement. The constraint name cannot be the same as any other constraint specified within an ALTER TABLE statement, and must be unique within the table (this includes the names of any referential integrity constraints that are defined). Existing data is checked against the new condition before the statement succeeds.
The following SQL statement adds a constraint to the EMPLOYEE table that the salary plus commission of each employee must be more than $25,000:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT REVENUE CHECK (SALARY + COMM > 25000)
To add constraints to a large table, it is more efficient to put the table into the check-pending state, add the constraints, and then check the table for a consolidated list of violating rows. Use the SET INTEGRITY statement to explicitly set the check-pending state: if the table is a parent table, check pending is implicitly set for all dependent and descendent tables.
When a table check constraint is added, packages and cached dynamic SQL that insert or update the table may be marked as invalid. See Statement Dependencies When Changing Objects for more information.
You drop constraints with the ALTER TABLE statement. For more information on this statement, including its syntax, refer to the SQL Reference manual.
For more information on constraints, see Defining Constraints.
You can explicitly drop a unique constraint using the ALTER TABLE statement. The name of all unique constraints on a table can be found in the SYSCAT.INDEXES system catalog view.
The following SQL statement drops the unique constraint NEWID from the EMPLOYEE table:
ALTER TABLE EMPLOYEE
DROP UNIQUE NEWID
Dropping this unique constraint invalidates any packages or cached dynamic SQL that used the constraint.
Dropping Primary and Foreign Keys:
The following examples use the DROP PRIMARY KEY and DROP FOREIGN KEY clauses in the ALTER TABLE statement to drop primary keys and foreign keys on a table:
ALTER TABLE EMP_ACT
DROP PRIMARY KEY
DROP FOREIGN KEY ACT_EMP_REF
DROP FOREIGN KEY ACT_PROJ_REF
ALTER TABLE PROJECT
DROP PRIMARY KEY
For information about the ALTER TABLE statement, refer to the SQL Reference manual.
When a foreign key constraint is dropped, packages or cached dynamic SQL statements containing the following may be marked as invalid:
See Statement Dependencies When Changing Objects for more information.
Dropping a Table Check Constraint:
You can explicitly drop or change a table check constraint using the ALTER TABLE statement, or implicitly drop it as the result of a DROP TABLE statement. The name of all check constraints on a table can be found in the SYSCAT.CHECKS catalog view.
The following SQL statement drops the table check constraint REVENUE from the EMPLOYEE table:
ALTER TABLE EMPLOYEE
DROP CHECK REVENUE
When you drop a table check constraint, all packages and cached dynamic SQL statements with INSERT or UPDATE dependencies on the table are invalidated. (See Statement Dependencies When Changing Objects for more information.) To drop a table check constraint with a system-generated name, look for the name in the SYSCAT.CHECKS catalog view.
A volatile table is defined as a table whose contents can vary from empty to very large at run time. The volatile or extreme changeability of this type of table makes reliance on the statistics collected by RUNSTATS to be inaccurate. Statistics are gathered at, and only reflect, a point-in-time. To generate an access plan that uses a volatile table can result in an incorrect or poor performing plan. For example, if statistics are gathered when the volatile table is empty, the optimizer tends to favor accessing the volatile table using a table scan rather than an index scan.
To prevent this, you should consider declaring the table as "volatile" using the ALTER TABLE statement. By declaring the table "volatile", the optimizer will consider using index scan rather than table scan. The access plans that use declared volatile tables will not depend on the existing statistics for that table.
The way to declare a table as "volatile" is to do the following:
ALTER TABLE TABLENAME
VOLATILE CARDINALITY
You can only change a partitioning key on tables in single-partition nodegroups. This is done by first dropping the existing partitioning key and then creating another.
The following SQL statement drops the partitioning key MIX_INT from the MIXREC table:
ALTER TABLE MIXREC
DROP PARTITIONING KEY
For more information, see the ALTER TABLE statement in the SQL Reference manual.
You cannot change the partitioning key of a table in a multiple database partition nodegroup. If you try to drop it, an error is returned.
The only methods to change the partitioning key of multiple database partition nodegroups are either:
Neither of these methods are practical for large databases; it is therefore essential that you define the appropriate partitioning key before implementing the design of large databases.
You may have reason to change table attributes such as the data capture option, the percentage of free space on each page (PCTFREE), the lock size, or the append mode.
The amount of free space to be left on each page of a table is specified through PCTFREE, and is an important consideration for the effective use of clustering indexes. The amount to specify depends on the nature of the existing data and expected future data. PCTFREE is respected by LOAD and REORG but is ignored by insert, update and import activities.
Setting PCTFREE to a larger value will maintain clustering for a longer period, but will also require more disk space.
You can specify the size (granularity) of locks used when the table is accessed by using the LOCKSIZE parameter. By default, when the table is created row level locks are defined. Use of table level locks may improve the performance of queries by limiting the number of locks that need to be acquired and released.
By specifying APPEND ON, you can improve the overall performance. It allows for faster insertions, while eliminating the maintenance of information about the free space.
A table with a clustering index cannot be altered to have append mode turned on. Similarly, a clustering index cannot be created on a table with append mode.
You can refresh the data in a summary table by using the REFRESH TABLE statement. The statement can be embedded in an application program, or issued dynamically. To use this statement, you must have either SYSADM or DBADM authority, or CONTROL privilege on the table to be refreshed.
The following example shows how to refresh the data in a summary table:
REFRESH TABLE SUMTAB1
For more information about the REFRESH TABLE statement, refer to the SQL Reference.
After creating a structured type, you may find that you need to add or drop attributes associated with that structured type. This is done using the ALTER TYPE (Structured) statement.
| Note: | You are not allowed to modify a type if any table exists of that type. |
For example, you may find you need to add an attribute to an existing row type:
ALTER TYPE Employee_t ADD ATTRIBUTE DeptNum INT;
This example adds a new attribute, DeptNum, to the Employee_t structured type. Note that ALTER TYPE is only permitted on structured types that are not currently in use as the type of an existing table or subtable.
In a similar way, you might also consider altering a typed table or view. However, the only change that is permitted to a typed table or view is to specify the scope of a reference-type column that does not yet have a scope.
Refer to the SQL Reference for more information on the ALTER TYPE(Structured), ALTER TABLE, and ALTER VIEW statements.
Rows can be deleted from typed tables using either searched or positioned DELETE statements. In addition, since a typed table may have subtables, the ONLY option may be used in the FROM clause if it is desirable to avoid having subtable rows affected by the delete operation. This is applicable to both typed tables and typed views.
Refer to the SQL Reference for more information on the DELETE statement.
Rows can be updated in typed tables using either searched or positioned UPDATE statements. In addition, since a typed table may have subtables, the ONLY option may be used in the FROM clause if it is desirable to avoid having subtable rows affected by the update operation. This is applicable to both typed tables and typed views.
Refer to the SQL Reference for more information on the UPDATE statement.
You can give an existing table a new name within a schema and maintain the authorizations and indexes that were created on the original table.
The existing table to be renamed can be an alias identifying a table. The existing table to be renamed must not be the name of a catalog table, a summary table, a typed table, or an object other than a table or an alias.
The existing table cannot be referenced in any of the following:
Also, there must be no check constraints within the table. Any packages or cached dynamic SQL statements dependent on the original table are invalidated. Finally, any aliases referring to the original table are not modified.
You should consider checking the appropriate system catalog tables to ensure that the table being renamed is not affected by any of these restrictions.
The SQL statement below renames the EMPLOYEE table within the COMPANY schema to EMPL:
RENAME TABLE COMPANY.EMPLOYEE TO EMPL
Packages must be re-bound if they refer to a table that has just been renamed. The packages can be implicitly re-bound if:
One of these two choices must be completed before any implicit or explicit re-binding is attempted. If neither choice is made, any re-bind will fail.
For more information about the RENAME TABLE statement, refer to the SQL Reference manual.
A table can be dropped with a DROP TABLE SQL statement. The following statement drops the table called DEPARTMENT:
DROP TABLE DEPARTMENT
When a table is dropped, the row in the SYSCAT.TABLES catalog that contains information about that table is dropped, and any other objects that depend on the table are affected. For example:
An individual table cannot be dropped if it has a subtable. However, all the tables in a table hierarchy can be dropped by a single DROP TABLE HIERARCHY statement, as in the following example:
DROP TABLE HIERARCHY person
The DROP TABLE HIERARCHY statement must name the root table of the hierarchy to be dropped.
There are differences when dropping a table hierarchy compared to dropping a specific table:
Refer to the SQL Reference for more information on the DROP statement.
A trigger object can be dropped using the DROP statement, but this procedure will cause dependent packages to be marked invalid, as follows:
A package remains invalid until the application program is explicitly bound or rebound, or it is run and the database manager automatically rebinds it.
A user-defined function (UDF), function template, or function mapping can be dropped using the DROP statement.
You can disable a function mapping with the mapping option DISABLE. Refer to the SQL Reference for more information on how to do this.
A UDF cannot be dropped if a view, trigger, table check constraint, or another UDF is dependent on it. Functions implicitly generated by the CREATE DISTINCT TYPE statement cannot be dropped. It is not possible to drop a function that is in either the SYSIBM schema or the SYSFUN schema.
Other objects can be dependent on a function or function template. All such dependencies, including function mappings, must be removed before the function can be dropped, with the exception of packages which are marked inoperative. Such a package is not implicitly rebound. It must either be rebound using the BIND or REBIND commands or it must be prepared by use of the PREP command. Refer to the Command Reference manual for more information on these commands. Dropping a UDF invalidates any packages or cached dynamic SQL statements that used it.
Dropping a function mapping marks a package as invalid. Automatic rebind will take place and the optimizer will attempt to use the local function. In the case where the local function is a template, the implicit rebind will fail.
(For more information, see Statement Dependencies When Changing Objects.)
You can drop a user-defined type (UDT) or type mapping using the DROP statement. You cannot drop a UDT if it is used:
You cannot drop a default type mapping; you can only override it by creating another type mapping.
The database manager will attempt to drop all functions that are dependent on this distinct type. If the UDF cannot be dropped, the UDT cannot be dropped. A UDF cannot be dropped if a view, trigger, table check constraint, or another UDF is dependent on it. Dropping a UDT invalidates any packages or cached dynamic SQL statements that used it.
If you have created a transform for a UDT, and you are planning to drop the UDT, you should consider if it is necessary to drop the transform. This is done through the DROP TRANSFORM statement. Refer to the SQL Reference for details on this statement. Note that only transforms defined by you or other application developers can be dropped; built-in transforms and their associated group definitions cannot be dropped.
For more information about the user-defined types, refer to the SQL Reference and Application Development Guide manuals.
The ALTER VIEW statement modifies an existing view by altering a reference type column to add a scope. Any other changes you make to a view require that you drop and then re-create the view.
When altering the view, the scope must be added to an existing reference type column that does not already have a scope defined. Further, the column must not be inherited from a superview.
The data type of the column-name in the ALTER VIEW statement must be REF (type of the typed table name or typed view name).
Refer to the SQL Reference for additional information on the ALTER VIEW statement.
The following example shows how to drop the EMP_VIEW:
DROP VIEW EMP_VIEW
Any views that are dependent on the view being dropped will be made inoperative. (See Recovering Inoperative Views for more information.)
Other database objects such as tables and indexes will not be affected although packages and cached dynamic statements are marked invalid. See Statement Dependencies When Changing Objects for more information.
As in the case of a table hierarchy, it is possible to drop an entire view hierarchy in one statement by naming the root view of the hierarchy, as in the following example:
DROP VIEW HIERARCHY VPerson
For more information on dropping and creating views, refer to the SQL Reference manual.
Views can become inoperative as a result of a revoked SELECT privilege on an underlying table.
The following steps can help you recover an inoperative view:
If you do not want to recover an inoperative view, you can explicitly drop it with the DROP VIEW statement, or you can create a new view with the same name but a different definition.
An inoperative view only has entries in the SYSCAT.TABLES and SYSCAT.VIEWS catalog views; all entries in the SYSCAT.VIEWDEP, SYSCAT.TABAUTH, SYSCAT.COLUMNS and SYSCAT.COLAUTH catalog views are removed.
You cannot alter a summary table, but you can drop it. The following SQL statement drops the summary table XT:
DROP TABLE XT
All indexes, primary keys, foreign keys, and check constraints referencing the table are dropped. All views and triggers that reference the table are made inoperative. All packages depending on any object dropped or marked inoperative will be invalidated. See Statement Dependencies When Changing Objects for more information on package dependencies.
Summary tables can become inoperative as a result of a revoked SELECT privilege on an underlying table.
The following steps can help you recover an inoperative summary table:
If you do not want to recover an inoperative summary table, you can explicitly drop it with the DROP TABLE statement, or you can create a new summary table with the same name but a different definition.
An inoperative summary table only has entries in the SYSCAT.TABLES and SYSCAT.VIEWS catalog views; all entries in the SYSCAT.VIEWDEP, SYSCAT.TABAUTH, SYSCAT.COLUMNS and SYSCAT.COLAUTH catalog views are removed.
The DROP statement can remove a wrapper from the database. The following example shows how to drop the DRDA wrapper:
DROP WRAPPER DRDA
Any servers, type mappings, function mappings, user mappings, and nicknames that are dependent on the wrapper being dropped will be dropped. Exercise extreme caution when dropping wrappers.
You must hold one of the SYSADM or DBADM authorities to DROP wrappers.
Refer to the SQL Reference for more information on dropping wrappers.
The ALTER SERVER statement modifies an existing server definition in the federated database catalog. Use this statement to:
You cannot use this statement to modify the dbname or node server options.
The following example shows how to alter the ORA1 server:
ALTER SERVER ORA1 OPTIONS (SET CPU_RATIO '5.0')
Servers can be dropped from the federated database. The following example shows how to drop the ORALOC01 Server:
DROP SERVER ORALOC01
Any type mappings, function mappings, user mappings, and nicknames that are dependent on the server being dropped will be dropped. Exercise caution when dropping servers.
You must hold one of the SYSADM or DBADM authorities to ALTER or DROP servers.
For more information on dropping and altering servers, refer to the SQL Reference.
The ALTER NICKNAME statement is used to update locally stored information about a data source table or view. You could use this statement, for example, to change the local name for a column or to map a column data type to a different data type. You can also use this statement to add column options. For more information on ALTER NICKNAME syntax, see the SQL Reference.
When a nickname is dropped, views created on that nickname are marked as inoperative. You cannot alter nickname column names or data types when the nickname is referenced in a view.
You must hold one of the SYSADM or DBADM authorities, or, you must have either the CONTROL or ALL database privilege on the nickname, the ALTERIN (for the current schema) schema privilege, or be the nickname definer at the federated database to use this statement.
The following example shows how to alter the nickname TESTNN, changing the local name of a column from COL1 to NEWCOL:
ALTER NICKNAME TESTNN ALTER COLUMN COL1 LOCAL NAME NEWCOL
The following example shows how to drop the nickname TESTNN:
DROP NICKNAME TESTNN
You specify column information in the form of values that you assign to parameters called column options. You can specify any of these values in either upper- or lowercase. See the "Column Options" section under the "Nickname Characteristics Affecting Pushdown Opportunities" section at the end of the "Environmental Considerations" chapter which describes the values and provides additional information.
You cannot change any clause of an index definition or specification; you must drop the index and create it again. (Dropping an index or an index specification does not cause any other objects to be dropped but may cause some packages to be invalidated.)
The following SQL statement drops the index called PH:
DROP INDEX PH
A primary key or unique key index (unless it is an index specification) cannot be explicitly dropped. You must use one of the following methods to drop it:
Any packages and cached dynamic SQL statements that depend on the dropped indexes are marked invalid. See Statement Dependencies When Changing Objects for more information. The application program is not affected by changes resulting from adding or dropping indexes.
Statement dependencies include package and cached dynamic SQL statements. A package is a database object that contains the information needed by the database manager to access data in the most efficient way for a particular application program. Binding is the process that creates the package the database manager needs in order to access the database when the application is executed. The Application Development Guide discusses how to create packages in detail.
Packages and cached dynamic SQL statements can be dependent on many types of objects. Refer to the SQL Reference for a complete list of those objects.
These objects could be explicitly referenced, for example, a table or user-defined function that is involved in an SQL SELECT statement. The objects could also be implicitly referenced, for example, a dependent table that needs to be checked to ensure that referential constraints are not violated when a row in a parent table is deleted. Packages are also dependent on the privileges which have been granted to the package creator.
If a package or cached dynamic SQL statement depends on an object and that object is dropped, the package or cached dynamic SQL statement is placed in an "invalid" state. If a package depends on a user-defined function and that function is dropped, the package is placed in an "inoperative" state.
A cached dynamic SQL statement that is in an invalid state is automatically re-optimized on its next use. If an object required by the statement has been dropped, execution of the dynamic SQL statement may fail with an error message.
A package that is in an invalid state is implicitly rebound on its next use. Such a package can also be explicitly rebound. If a package was marked invalid because a trigger was dropped, the rebound package will no longer invoke the trigger.
A package that is in an inoperative state must be explicitly rebound before it can be used. Refer to the Application Development Guide for more information about binding and rebinding packages.
Federated database objects have similar dependencies. For example, dropping a server will make any packages or cached dynamic SQL referencing nicknames associated with that server invalid.
In some cases, it will not be possible to rebind the package. For example, if a table has been dropped and not re-created, the package cannot be rebound. In this case, you will need to either re-create the object or change the application so it does not use the dropped object.
In many other cases, for example if one of the constraints was dropped, it will be possible to rebind the package.
The following system catalog views help you to determine the state of a package and the package's dependencies:
For more information about object dependencies, refer to the DROP statement in the SQL Reference manual.