Administration Guide

Altering a Database

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:

• Dropping a Database
• Altering a Nodegroup
• Altering a Table Space
• Dropping a Schema
• Modifying a Table in Both Structure and Content
• Altering a User-Defined Structured Type
• Deleting and Updating Rows of a Typed Table
• Renaming an Existing Table
• Dropping a Table
• Dropping a Trigger
• Dropping a User-Defined Function (UDF), Type Mapping, or Method
• Dropping a User-Defined Type (UDT) or Type Mapping
• Altering or Dropping a View
• Dropping a Summary Table
• Altering or Dropping a Server
• Altering or Dropping a Nickname
• Dropping an Index, Index Extension, or an Index Specification
• Statement Dependencies When Changing Objects

Dropping a Database

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 removed (uncataloged) from the database directories.

To drop a database using the Control Center:

Expand the object tree until you see the Databases folder. Right-click the database you want to drop, and select Drop from the pop-up menu. Click on the Confirmation box, and click Ok.

To drop a database using the command line, enter:

   DROP DATABASE <name>

The following command deletes the database SAMPLE:

   DROP DATABASE SAMPLE

Altering a Nodegroup

Details on altering a nodegroup are found in Chapter 29, Scaling Your Configuration Through Adding Processors.

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. See Chapter 30, Redistributing Data Across Database Partitions for additional information on this topic. Also, refer to the Command Reference.

Altering a Table Space

When you create a database, you create at least three table spaces: one catalog table space (SYSCATSPACE); one user table space (with a default name of USERSPACE1); and one system temporary table space (with a default name of TEMPSPACE1). You must keep at least one of each of these table spaces. You 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 system temporary table space. You also cannot change the page size or the extent size of a table space after it has been created.

This section discusses how to change table spaces as follows:

• Adding a Container to a DMS Table Space
• Modifying Containers in a DMS Table Space
• Dropping a User Table Space
• Dropping a System Temporary Table Space.

See Designing and Choosing Table Spaces for design information on table spaces.

Adding a Container to a DMS Table Space

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

To add a container to a DMS table space using the Control Center:

Expand the object tree until you see the Table Spaces folder. Right-click the table space where you want to add the container, and select Alter from the pop-up menu. Click Add, complete the information, and click Ok. If the table space is in a partitioned database environment, click Advanced if you need to change performance parameters for the table space. Click Ok.

To add a container to a DMS table space using the command line, enter:

   ALTER TABLESPACE <name>
      ADD (DEVICE '<path>' <size>)

The following example illustrates how to add two new device containers (each with 10 000 pages) to a table space on a UNIX-based system:

    ALTER TABLESPACE RESOURCE
      ADD (DEVICE '/dev/rhd9'  10000,
           DEVICE  '/dev/rhd10' 10000)

Note that the ALTER TABLESPACE statement allows you to change other properties of the table space that can affect performance. See Table Space Impact on Query Optimization for more information.

Modifying Containers in a DMS Table Space

You can increase the size of the containers in a DMS table space (that is, one created with the MANAGED BY DATABASE clause) by resizing one or more containers or by extending one or more containers associated with the table space.

To resize one or more containers in a DMS table space using the command line, enter:

   ALTER TABLESPACE <name>
      RESIZE (DEVICE '<path>' <size>)

The following example illustrates how to increase two device containers (each already existing with 1 000 pages) in a table space on a UNIX-based system:

    ALTER TABLESPACE HISTORY
      RESIZE (DEVICE '/dev/rhd7'  2000,
              DEVICE  '/dev/rhd8' 2000)

Following this action, the two devices have increase from 1 000 pages in size to 2 000 pages. Similar to adding new containers, the contents of the table space are re-balanced across all containers. Access to the table space is not restricted during the re-balancing.

To extend one or more containers in a DMS table space using the command line, enter:

   ALTER TABLESPACE <name>
      EXTEND (DEVICE '<path>' <size>)

The following example illustrates how to increase two device containers (each already existing with 1 000 pages) in a table space on a UNIX-based system:

    ALTER TABLESPACE HISTORY
      EXTEND (DEVICE '/dev/rhd11'  1000,
              DEVICE  '/dev/rhd12' 1000)

Following this action, the two devices have increased from 1 000 pages in size to 2 000 pages. Similar to adding new containers, the contents of the table space are re-balanced across all containers. Access to the table space is not restricted during the re-balancing.

Note:

You cannot reduce the size of the containers.

Note that the ALTER TABLESPACE statement allows you to change other properties of the table space that can affect performance. See Table Space Impact on Query Optimization for more information.

Renaming a Table Space

You can give an existing table space a new name without being concerned with the individual objects within the table space. When renaming a table space, all the catalog records referencing that table space are changed.

You cannot rename the SYSCATSPACE table space.

You cannot rename a table space that is in a "roll-forward pending" or "roll-forward in progress" state.

When restoring a table space that has been renamed since it was backed up, you must use the new table space name in the RESTORE DATABASE command. If you use the previous table space name, it will not be found. Similarly, if you are rolling forward the table space with the ROLLFORWARD DATABASE command, ensure that you use the new name. If the previous table space name is used, it will not be found.

Dropping a User Table Space

When you drop a user table space, you delete all the data in that table space, free the containers, remove the catalog entries, and cause all objects defined in the table space to be either dropped or marked as invalid.

You can reuse the containers in an empty table space by dropping the table space, but you must COMMIT the DROP TABLESPACE command before attempting to reuse the containers.

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.

To drop a user table space using the Control Center:

Expand the object tree until you see the Table Spaces folder. Right-click on the table space you want to drop, and select Drop from the pop-up menu. Check the Confirmation box, and click Ok.

To drop a user table space using the command line, enter:

   DROP TABLESPACE <name>

The following SQL statement drops the table space ACCOUNTING:

   DROP TABLESPACE ACCOUNTING

Dropping a System Temporary Table Space

You cannot drop a system temporary table space without first creating another system temporary table space because the database must always have at least one system temporary table space. If you wish to, for example, add a container to an SMS Temporary Table Space, you must add a new system temporary table space first and then drop the old system temporary table space.

To drop a system table space using the Control Center:

Expand the object tree until you see the Table Spaces folder. If there is only one other system temporary table space, right-click the Table Spaces folder, and select Create --> Table Space Using Wizard from the pop-up menu. Otherwise, skip to step four. Follow the steps in the wizard to create the new system temporary table space if needed. Click again on the Table Spaces folder to display a list of table spaces in the right side of the window (the Contents pane). Right-click on the system temporary table space you want to drop, and click Drop from the pop-up menu. Check the Confirmation box, and click Ok.

If you only have one system temporary table space, before deleting it, you must create another. This can be done using the command line by entering:

   CREATE SYSTEM TEMPORARY TABLESPACE <name>
      MANAGED BY SYSTEM USING ('<device>')

Then, to drop a system table space using the command line, enter:

   DROP TABLESPACE <name>

The following SQL statement creates a new system temporary table space called TEMPSPACE2:

   CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2
      MANAGED BY SYSTEM USING ('d')

Once TEMPSPACE2 is created, you can then drop the original system 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 before attempting to reuse the containers.

Dropping a User Temporary Table Space

You can only drop a user temporary table space if there are no current declared temporary tables defined in that table space. When you drop the table space, no attempt is made to drop all of the declared temporary tables in the table space.

Note:

A declared temporary table is implicitly dropped when the application that declared it disconnects from the database.

Dropping a Schema

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.

To drop a schema using the Control Center:

Expand the object tree until you see the Schemas folder. Right-click on the schema you want to drop, and select Drop from the pop-up menu. Check the Confirmation box, and click Ok.

To drop a schema using the command line, enter:

   DROP SCHEMA <name>

In the following example, the schema "joeschma" is dropped:

   DROP SCHEMA joeschma RESTRICT

The RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database.

Modifying a Table in Both Structure and Content

Tasks that are required for modifying the structure and content of the table include the following:

• Adding Columns to an Existing Table
• Modifying a Column Definition
• Altering a Constraint
• Adding a Constraint
• Dropping a Constraint
• Declaring a Table Volatile
• Changing Partitioning Keys
• Changing Table Attributes
• Refreshing the Data in a Summary Table

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

Adding Columns to an Existing Table

A column definition includes a column name, data type, and any necessary constraints.

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.

To add columns to an existing table using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to add columns to, and select Alter from the pop-up menu. Check the Columns page, complete the information for the column, and click Ok.

To add columns to an existing table using the command line, enter:

   ALTER TABLE <table_name>
      ADD <column_name> <data_type> <null_attribute>

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)

Modifying a Column Definition

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.

To modify columns of an existing table using the Control Center:

Expand the object tree until you see the Tables folder. In the list of tables in the right pane, right-click on the table for which you want to modify a column, and select Alter from the pop-up menu. Check the Columns page, select the column, and click Change. Type the new byte count for the column in Length, and click Ok.

To modify columns of an existing table using the command line, enter:

   ALTER TABLE ALTER COLUMN
      <column_name> <modification_type>

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.

Altering a Constraint

You can only alter constraints by dropping them and then adding new ones to take their place. For more information, see:

• Adding a Constraint
• Dropping a Constraint

For more information on constraints, see Defining Constraints.

Adding a Constraint

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.

Adding a Unique Constraint

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

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.

To add primary keys using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Primary Key page, select one or more columns as primary keys, and click the arrow to move them. Optional: Enter the constraint name of the primary key. Click Ok.

To add primary keys using the command line, enter:

   ALTER TABLE <name>
      ADD CONSTRAINT <column_name>
      PRIMARY KEY <column_name>

When a foreign key is added to a table, packages and cached dynamic SQL containing the following statements may be marked as invalid:

• Statements that insert or update the table containing the foreign key
• Statements that update or delete the parent table.

See Statement Dependencies When Changing Objects for information.

To add foreign keys using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Foreign Keys page, click Add. On the Add Foreign Keys window, specify the parent table information. Select one or more columns to be foreign keys, and click the arrow to move them. Specify what action is to take place on the dependent table when a row of the parent table is deleted or updated. You can also add a constraint name for he foreign key. Click Ok.

To add foreign keys using the command line, enter:

   ALTER TABLE <name>
      ADD CONSTRAINT <column_name>
       FOREIGN KEY <column_name>
       ON DELETE <action_type>
       ON UPDATE <action_type>

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

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.

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.

To add a table check constraint using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Check Constraints page, click Add. On the Add Check Constraint window, complete the information, and click Ok. On the Check Constraints page, click Ok.

To add a table check constraint using the command line, enter:

   ALTER TABLE <name>
      ADD CONSTRAINT <name> (<constraint>)

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)

Dropping a Constraint

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.

Dropping a Unique Constraint

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

To drop a primary key using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Primary Key page, select the primary key at right to drop, and click the arrow to move it to the Available columns box on the left. Click Ok.

To drop a primary key using the command line, enter:

   ALTER TABLE <name>
      DROP PRIMARY KEY

When a foreign key constraint is dropped, packages or cached dynamic SQL statements containing the following may be marked as invalid:

• Statements that insert or update the table containing the foreign key
• Statements that update or delete the parent table.

See Statement Dependencies When Changing Objects for more information.

To drop a foreign key using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Foreign Keys page, click Add. Select the foreign key at right to drop, and click on the arrow to move it to the Available columns box on the left. On the Foreign Keys page, click Ok.

To drop a foreign key using the command line, enter:

   ALTER TABLE <name>
      DROP FOREIGN KEY <foreign_key_name>

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.

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.

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.) The name of all check constraints on a table can be found in the SYSCAT.CHECKS catalog view. Before attempting to drop a table check constraint having a system-generated name, look for the name in the SYSCAT.CHECKS catalog view.

To drop a table check constraint using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Check Constraints page, select the check constraint to drop, click Remove, and click Ok.

To drop a table check constraint using the command line:

   ALTER TABLE <table_name>
      DROP CHECK <check_constraint_name>

The following SQL statement drops the table check constraint REVENUE from the EMPLOYEE table:

   ALTER TABLE EMPLOYEE
      DROP CHECK REVENUE

Defining a Generated Column on an Existing Table

A generated column is defined on a base table where the stored value is computed using an expression, rather than being specified through an insert or update operation. A generated column can be created when a table is created or as a modification to an existing table.

Perform the following steps to define a generated column:

1. Place the table in a check pending state.

      SET INTEGRITY FOR t1 OFF
   

2. Alter the table to add one or more generated columns.

      ALTER TABLE t1 ADD COLUMN c3 DOUBLE GENERATED ALWAYS AS (c1 + c2),
                     ADD COLUMN c4 GENERATED ALWAYS AS 
                        (CASE WHEN c1 > c3 THEN 1 ELSE NULL END))
   

3. At this point there are several ways to complete this task based on the work to be done against the table:
   • The table is very large and you are not confident that you have sufficient log space to complete the task. After loading the data but before you turn the integrity checking back on, you need to:

        COMMIT
     

     Then you need to use the db2gncol utility to set the generated columns. This utility is located under the sqllib directory in the bin subdirectory. You use the utility as follows:

        db2gncol -d <dbname> -s <schema> -t <table_name> 
                 -c <commitcount>
     

     The dbname specifies an alias name for the database in which the table is located. The schema specifies the schema name of the table and is case sensitive. The table_name specifies the table for which new values for its columns generated by expressions are to be computed. Both schema and table_name are case-sensitive. The commitcount is the number of rows to process between each internal commit to clean up the logs. This parameter influences the size of the log space required to perform the generation of the column values.

     There are also two optional parameters that are not shown in the example above. They are -u <username> and -p <password> which identify a user and password. The user must have SYSADM or DBADM authority. If there is no user and password identified, the current user ID will be used.

     If you wish help information on this utility, enter:

        db2gncol -h 
     

     When the help parameter is used, all other parameters are ignored.

     The table is locked for the entire process even though it is in a check pending state. The reason for the lock is that there are other utilities that can access tables that are in a check pending state. The lock prevents conflicts with these other utilities.

   • You anticipate that the log space for updating the generated columns is expected to be sufficient for SET INTEGRITY. This will normally be the case. After loading the data you recompute and reassign the values for the generated columns using:

        SET INTEGRITY FOR t1 IMMEDIATE CHECKED
           FORCE GENERATED
     

     Note:

     Exception tables can be used at this point.

   • The table is very large, you are not confident you will have sufficient log space to complete the task, and you do not choose the first method presented above. After loading the data but before you turn the integrity checking back on, you need to:
     1. Get an exclusive lock on the table. This prevents all but uncommitted read transactions from accessing the table.

           LOCK TABLE t1
        

     2. Move the table to the online state with the data unchecked.

           SET INTEGRITY FOR t1 ALL IMMEDIATE UNCHECKED
        

     3. Update the generated columns using intermittent commits and predicates to avoid the logs filling up.

           UPDATE t1 SET (c3, c4) = (DEFAULT, DEFAULT) WHERE <predicate>
        

     4. Bring up the table online and check its integrity.

           SET INTEGRITY FOR t1 OFF
           SET INTEGRITY FOR t1 IMMEDIATE CHECKED
        

     5. Unlock the table by completing the transaction using a commit statement.

           COMMIT
        

   • You know that the table was created with the not logged initially option. In this way, logging for the table is turned off with the usual implications and risks while working with the generated column values.
     1. Activate the not logged initially option.

           ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY
        

     2. Generate the values.

           SET INTEGRITY FOR t1 IMMEDIATE CHECKED FORCE GENERATION
        

     3. Turn the not logged initially off again by committing the transaction.

           COMMIT
        

The values for generated columns can also simply be checked by applying the expression as if it is an equality check constraint:

   SET INTEGRITY FOR t1 IMMEDIATE CHECKED

If values have been placed in a generated column using LOAD for example, and you know that the values match the generated expression, then the table can be taken out of the check pending state without checking or assigning the values:

   SET INTEGRITY FOR t1 GENERATED COLUMN IMMEDIATE UNCHECKED

Generated columns may only be defined on data types for which an equal comparison is defined. The excluded data types for the generated columns include: Structured types, LOBs, CLOBs, DBCLOBs, LONG VARCHAR, LONG VARGRAPHIC, and user-defined types defined using the same excluded data types.

Generated columns cannot be used in constraints, unique indexes, referential constraints, primary keys, and global temporary tables. A table created with LIKE and materialized views does not inherit generated column properties.

Generated columns cannot be inserted or updated without the keyword DEFAULT. When inserting, the use of DEFAULT avoids the need to enumerate the columns in the column list. Instead, generated columns can be set to DEFAULT in the values list. When updating, DEFAULT enables the recomputation of generated columns that have been placed online by SET INTEGRITY without being checked.

The order of processing of triggers requires that BEFORE-triggers may not reference generated columns in their header (before update) or in their bodies. In the order of processing, generated columns are processed after BEFORE-triggers.

The db2look utility will not see the check constraints generated by a generated column.

When using replication, the target table must not use generated columns in its mapping. There are two choices when replicating:

• The target table must contain the generated column as a normal column, that is, not a generated column
• The target table must omit the generated column in the mapping

There are several restrictions when working with generated columns:

• Generated columns must not have dependencies on each other.
• The expressions used to create the generated columns must not contain subqueries. This includes expressions with functions that READS SQL DATA.
• No check constraints are allowed on generated columns.
• No unique indexes are allowed on generated columns. This includes unique constraints and primary keys.

Declaring a Table Volatile

A volatile table is defined as a table whose contents can vary from empty to very large at run time. The volatility 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.

To declare a table volatile using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to modify, and select Alter from the pop-up menu. On the Table page, select the Cardinality varies significantly at run time check box, and click Ok.

To declare a table volatile using the command line, enter:

   ALTER TABLE <table_name>
      VOLATILE CARDINALITY

The way to declare a table as "volatile" is to do the following:

   ALTER TABLE TABLENAME
       VOLATILE CARDINALITY

Changing Partitioning Keys

You can only change a partitioning key on tables in single-partition nodegroups. First drop the existing partitioning key, and then create 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.

To change the partitioning key of multiple database partition nodegroups, either:

• Export all of the data to a single-partition nodegroup and then follow the above instructions.
• Export all of the data, drop the table, redefine the partitioning key, and then import all of the data.

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.

Changing Table Attributes

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.

Altering Summary Table Properties

With some restrictions, you can change a summary table to a regular table or a regular table to a summary table. You cannot change other table types; only regular and summary tables can be changed. For example, you cannot change a replicated summary table to a regular table, nor the reverse.

Once a regular table has been altered to a summary table, the table is placed in a check pending state. When altering in this way, the fullselect in the summary table definition must match the original table definition, that is:

• The number of columns must be the same.
• The column names and positions must match.
• The data types must be identical.

If the summary table is defined on an original table, then it cannot be altered into a summary table. If the original table has triggers, check constraints, referential constraints, or a defined unique index, then it cannot be altered into a summary table. If altering the table properties to define a summary table, you are not allowed to alter the table in any other way in the same ALTER TABLE statement.

When altering a regular table into a summary table, the fullselect of the summary table definition cannot reference the original table directly or indirectly through views, aliases, or summary tables.

To change a summary table to a regular table, use the following:

   ALTER TABLE sumtable
      SET SUMMARY AS DEFINITION ONLY

To change a regular table to a summary table, use the following:

   ALTER TABLE regtable
      SET SUMMARY AS <fullselect>

The restrictions on the fullselect when altering the regular table to a summary table are very much like the restrictions when creating a summary table using the CREATE SUMMARY TABLE statement.

Refer to the SQL Reference for additional information on the CREATE SUMMARY TABLE statement.

Refreshing the Data in a Summary Table

You can refresh the data in one or more summary tables 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.

Altering a User-Defined Structured Type

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. Refer to the Application Development Guide for all the information you need on structured types.

Deleting and Updating Rows of a Typed Table

Rows can be deleted from typed tables using either searched or positioned DELETE statements. Rows can be updated in typed tables using either searched or positioned UPDATE statements. Refer to the Application Development Guide for all the information you need on typed tables.

Renaming an Existing Table

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:

• Views
• Triggers
• Referential constraints
• Summary table
• The scope of an existing reference column

Also, there must be no check constraints within the table nor any generated columns other than the identity column. 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.

Packages must be re-bound if they refer to a table that has just been renamed. The packages can be implicitly re-bound if:

• Another table is renamed using the original name of the table, or
• An alias or view is created using the original name of the table.

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.

To rename an existing table using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to rename, and select Rename from the pop-up menu. Type the new table name, and click Ok.

To rename an existing table using the command line, enter:

   RENAME TABLE <schema_name.<table_name> TO <new_name>

The SQL statement below renames the EMPLOYEE table within the COMPANY schema to EMPL:

    RENAME TABLE COMPANY.EMPLOYEE TO EMPL

For more information about the RENAME TABLE statement, refer to the SQL Reference manual.

Dropping a Table

A table can be dropped with a DROP TABLE SQL statement.

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:

• All column names are dropped.
• Indexes created on any columns of the table are dropped.
• All views based on the table are marked inoperative. (See Recovering Inoperative Views for more information.)
• All privileges on the dropped table and dependent views are implicitly revoked.
• All referential constraints in which the table is a parent or dependent are dropped.
• All packages and cached dynamic SQL statements dependent on the dropped table are marked invalid, and remain so until the dependent objects are re-created. This includes packages dependent on any supertable above the subtable in the hierarchy that is being dropped. (See Statement Dependencies When Changing Objects for more information.)
• Any reference columns for which the dropped table is defined as the scope of the reference become "unscoped".
• An alias definition on the table is not affected, because an alias can be undefined
• All triggers dependent on the dropped table are marked inoperative.
• All files that are linked through any DATALINK columns are unlinked. The unlink operation is performed asynchronously which means the files may not be immediately available for other operations.

To drop a table using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the table you want to drop, and select Drop from the pop-up menu. Check the Confirmation box, and click Ok.

To drop a table using the command line, enter:

   DROP TABLE <table_name>

The following statement drops the table called DEPARTMENT:

   DROP TABLE DEPARTMENT

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:

• DROP TABLE HIERARCHY does not activate deletion-triggers that would be activated by individual DROP table statements. For example, dropping an individual subtable would activate deletion-triggers on its supertables.
• DROP TABLE HIERARCHY does not make log entries for the individual rows of the dropped tables. Instead, the dropping of the hierarchy is logged as a single event.

Refer to the SQL Reference for more information on the DROP statement.

Dropping a User-Defined Temporary Table

There are a few considerations to be noted when dropping a user-defined temporary table; that is, one created using the DECLARE GLOBAL TEMPORARY TABLE statement.

When dropping such a table, the table name must be qualified by the schema name SESSION and must exist in the application that created the table.

Packages cannot be dependent on this type of table and therefore they are not invalidated when such a table is dropped.

When a user-defined temporary table is dropped, and its creation preceded the active unit of work or savepoint, then the table is functionally dropped and the application is not able to access the table. However, the table still has some space reserved in its table space and this prevents the user temporary table space from being dropped until the unit of work is committed or the savepoint is ended.

Refer to the SQL Reference for more information on the DROP statement.

Dropping a Trigger

A trigger object can be dropped using the DROP statement, but this procedure will cause dependent packages to be marked invalid, as follows:

• If an update trigger without an explicit column list is dropped, then packages with an update usage on the target table are invalidated.
• If an update trigger with a column list is dropped, then packages with update usage on the target table are only invalidated if the package also had an update usage on at least one column in the column-name list of the CREATE TRIGGER statement.
• If an insert trigger is dropped, packages that have an insert usage on the target table are invalidated.
• If a delete trigger is dropped, packages that have a delete usage on the target table are invalidated.

A package remains invalid until the application program is explicitly bound or rebound, or it is run and the database manager automatically rebinds it.

Dropping a User-Defined Function (UDF), Type Mapping, or Method

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

Dropping a User-Defined Type (UDT) or Type Mapping

You can drop a user-defined type (UDT) or type mapping using the DROP statement. You cannot drop a UDT if it is used:

• In a column definition for an existing table or view (distinct types)
• As the type of an existing typed table or typed view (structured type)
• As the supertype of another structured type

You cannot drop a default type mapping; you can only override it by creating another type mapping.

The database manager attempts 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.

Altering or Dropping a View

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

Other database objects such as tables and indexes are not affected although packages and cached dynamic statements are marked invalid. See Statement Dependencies When Changing Objects for more information.

Refer to the SQL Reference for additional information on the ALTER VIEW statement.

To alter a view using the Control Center:

Expand the object tree until you see the Views folder. Right-click on the view you want to modify, and select Alter from the pop-up menu. In the Alter view window, enter or modify a comment, and click Ok.

To drop a view using the Control Center:

Expand the object tree until you see the Views folder. Right-click on the view you want to drop, and select Drop from the pop-up menu. Check the Confirmation box, and click Ok.

To drop a view using the command line, enter:

   DROP VIEW <view_name>

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

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.

Recovering Inoperative Views

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:

• Determine the SQL statement that was initially used to create the view. You can obtain this information from the TEXT column of the SYSCAT.VIEW catalog view.
• Re-create the view by using the CREATE VIEW statement with the same view name and same definition.
• Use the GRANT statement to re-grant all privileges that were previously granted on the view. (Note that all privileges granted on the inoperative view are revoked.)

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.

Dropping a Summary Table

You cannot alter a summary table, but you can drop it.

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.

To drop a summary table using the Control Center:

Expand the object tree until you see the Tables folder. Right-click on the summary table you want to drop, and select Drop from the pop-up menu. Check the Confirmation box, and click Ok.

To drop a summary table using the command line, enter:

   DROP TABLE <table_name>

The following SQL statement drops the summary table XT:

   DROP TABLE XT

Recovering Inoperative Summary Tables

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:

• Determine the SQL statement that was initially used to create the summary table. You can obtain this information from the TEXT column of the SYSCAT.VIEW catalog view.
• Re-create the summary table by using the CREATE SUMMARY TABLE statement with the same summary table name and same definition.
• Use the GRANT statement to re-grant all privileges that were previously granted on the summary table. (Note that all privileges granted on the inoperative summary table are revoked.)

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.

Dropping a Wrapper

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.

Altering or Dropping a Server

The ALTER SERVER statement modifies an existing server definition in the federated database catalog. Use this statement to:

• Modify the definition of a specific data source.
• Modify the definition of multiple data sources of a specific type or version.
• Make changes in the configuration of a specific data source. For example, if the DBMS identified by a specific server is migrated to a new workstation with a faster processor, you should update the cpu_ratio server option.

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.

Altering or Dropping a Nickname

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.

Altering a Nickname Column and Dropping a Nickname

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

Altering Nickname Column Options

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.

Dropping an Index, Index Extension, or an Index Specification

You cannot change any clause of an index definition, index extension, or index specification; you must drop the index or index extension 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.)

To drop an index, index extension, or an index specification using the Control Center:

Expand the object tree until you see the Indexes folder. Right-click on the index you want to drop, and select Drop from the pop-up menu. Check the Confirmation box, and click Ok.

To drop an index, index extension, or an index specification using the command line, enter:

   DROP INDEX <index_name>

The following SQL statement drops the index called PH:

   DROP INDEX PH

The following SQL statement drops the index extension called IX_MAP:

   DROP INDEX EXTENSION ix_map RESTRICT

The name of the index extension must identify an index extension described in the catalog. The RESTRICT clause enforces the rule that no index can be defined that depends on the index extension definition. If an underlying index depends on this index extension, then the drop fails.

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:

• If the primary index or unique constraint was created automatically for the primary key or unique key, dropping the primary key or unique key will cause the index to be dropped. Dropping is done through the ALTER TABLE statement.
• If the primary index or the unique constraint was user-defined, the primary key or unique key must be dropped first, through the ALTER TABLE statement. After the primary key or unique key is dropped, the index is no longer considered the primary index or unique index, and it can be explicitly dropped.

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 When Changing Objects

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 no longer invokes 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 invalidates any packages or cached dynamic SQL referencing nicknames associated with that server.

In some cases, it is not 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 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 is possible to rebind the package.

The following system catalog views help you to determine the state of a package and the package's dependencies:

• SYSCAT.PACKAGEAUTH
• SYSCAT.PACKAGEDEP
• SYSCAT.PACKAGES

For more information about object dependencies, refer to the DROP statement in the SQL Reference manual.