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, see Chapter 18. "Redistributing Data Across Database Partitions" and 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:
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. 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 alter the characteristics of a column by increasing the length of an existing VARCHAR column. The number of characters may now increase up to 4000. For example:
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, 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 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 of other than table or 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, see the SQL Reference manual.
After the original creation of a structured type, you may need to alter or delete a structured type. These actions are presented below.
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.
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. However, such a change can only involve modifying the scope of a referenced type column. Also, when altering the characteristics of a column in a table, scope can only be added to an existing reference type column that does not already have a scope defined.
See 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.
See 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.
See SQL Reference for more information on the UPDATE statement.
A table can be dropped with a DROP TABLE SQL statement. The following statement drops the table called EMPLOYEE:
DROP TABLE EMPLOYEE
| Note: | When dropping typed tables, all subtables must be dropped before a supertable can be dropped. |
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:
Refer to the SQL Reference for more information on the DROP statement.
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.
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.
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 TYPE statement. You cannot drop a UDT if it is used:
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.
The ALTER VIEW statement modifies an existing view by altering a reference type column to add a scope. Any other changes you are looking to make on 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.
For more information on dropping and creating views, see 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 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:
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.
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.
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 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 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.