You need to do some tasks before making a change to your database. These tasks include:
You can add nodes to a nodegroup, or you can reorganize existing nodes to create a new nodegroup.
When you add a node to a nodegroup, you do not have to drop and re-create the tables and table spaces in the new nodegroup. To add a node to a nodegroup:
(To drop nodes, run the DROP NODE command and then remove the nodename entries from the db2nodes.cfg file.)
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, see Chapter 16. "Redistributing Data Across Database Partitions" and the Command Reference.
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
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 that you cannot drop the catalog table space, SYSCATSPACE; and there must always be at least one temporary table space.
This section discusses how to change table spaces as follows:
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. For more information, see "Table Space Impact on Query Optimization".
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 cannot drop a table space if a table stores at least one of its parts in it and one or more of its parts in another table space. The table must be dropped first.
The following SQL statement drops the table space ACCOUNTING:
DROP TABLESPACE ACCOUNTING
For information on SQL statements, see the SQL Reference.
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 change the specifications of this 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. To ensure that there are no objects in the schema when executing the DROP statement, use the RESTRICT attribute. 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
You should perform one or more of the following tasks when you modify a table as a result of a table design. These tasks include:
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. In addition to adding columns to a table, the ALTER TABLE statement can be used to add or drop a primary or foreign key and to add or drop a table check constraint definition. For more information about the ALTER TABLE statement, see 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, see 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)
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 CONSTRAINTS 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.
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 CONSTRAINTS 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, see 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.
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, see 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.
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.
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 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, see the SQL Reference manual.
A table can be dropped with a DROP TABLE SQL statement. The following statement drops the table called EMPLOYEE:
DROP TABLE EMPLOYEE
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:
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 MIX_INT
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.
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) can be dropped using the DROP statement. 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. All such dependencies 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. See the Command Reference manual for more information on these commands. Dropping a UDF invalidates any packages or cached dynamic SQL statements that used it.
You can drop a user-defined type (UDT) using the DROP DISTINCT TYPE statement. You cannot drop a UDT if it is used in a column definition for an existing table. 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.
For more information about the user-defined types, see the SQL Reference and Embedded SQL Programming Guide manuals.
You may not change a view definition; the view must be dropped and re-created.
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.
For more information on dropping and creating views, see the SQL Reference manual.
When an object is dropped, views can become inoperative if they are dependent on that object.
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 change any clause of an index definition; you must drop the index and create it again. (Dropping an index 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 cannot be explicitly dropped. You must use one of the following methods to drop it:
If you drop a non-primary or non-unique index, any packages and cached dynamic SQL statements that depend on the 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 Embedded SQL Programming Guide discusses how to create packages in detail.
Packages and cached dynamic SQL statements can be dependent on the following types of 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 will be placed in an "invalid" state. If the object that is dropped is a user-defined function, the package is placed in an "inoperative" state.
Packages or cached dynamic SQL statements in an "invalid" state are implicitly rebound the next time they are accessed. They can also be explicitly rebound. If a package or cached dynamic SQL statement was marked invalid because a trigger was dropped, it will be rebound without the trigger.
Packages or cached dynamic SQL statements in an "inoperative" state must be explicitly rebound before they can be used again. See the Embedded SQL Programming Guide for more information about binding and rebinding packages.
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, see the DROP statement in the SQL Reference manual.