This article provides a guide to using the new enhancements to DB2 9.7 that enable you to make online changes to the database schema. Online change means that the objects being changed remain available for read and write access even during the change process.
Some of the specific new capabilities include:
- Columns can be renamed online using the ALTER TABLE statement.
- OR REPLACE has been added as an option on several CREATE statements.
- CREATE with errors support has been added for the views and inline SQL functions.
- ALTER COLUMN SET DATA TYPE support has been extended.
- Tables can be changed and moved online using the ADMIN_MOVE_TABLE routine.
Prerequisites and system requirements
This article is written for DB2 database administrators. You should understand the basic concepts of tablespaces, tables, and columns.
To use the examples in the article, you must have DB2 9.7 for Linux, UNIX, and Windows. Refer to the Resources section for a link you can use to download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.
Preparing to use the examples
In order to use the examples that demonstrate the new online schema change capabilities in DB2, you first need to create a sample database that will serve as the required infrastructure. The examples use the DB2 SAMPLE database. If you have not already created the DB2 SAMPLE database, do so by following the instructions in "The SAMPLE database" article of the DB2 Information Center (see the Resources section for a link).
Once you have created the SAMPLE database, follow these steps to create the required table and data used in the examples:
- Use the following command to create a table based on the system catalog that simulates customer information:
Listing 1. Create CUSTOMER_INFO table
CREATE TABLE CUSTOMER_INFO( customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_city VARCHAR(128), address_state VARCHAR(25), address_country VARCHAR(30), age VARCHAR(2), customer_type VARCHAR(10), CONSTRAINT customer_id_pk PRIMARY KEY (customer_id) ) IN USERSPACE1;
- Use the following command to
populate the CUSTOMER_INFO table with data using dummy information from the system catalog:
Listing 2. Populate CUSTOMER_INFO table
INSERT INTO customer_info SELECT ROW_NUMBER() OVER () as customer_id , RTRIM(a.tabschema) as first_name, RTRIM(a.tabname) as last_name, CAST(a.colno AS VARCHAR(3)) || ' ' || RTRIM(a.colname) as address_street, RTRIM(a.tabname) as address_city, RTRIM(a.TYPENAME) as address_state, RTRIM(a.TABSCHEMA) as address_country, CAST(CAST(sysfun.rand()*50 AS INTEGER)+1 AS VARCHAR(2)) as age, CASE WHEN MOD(CAST(sysfun.rand()*50 AS INTEGER),3)=0 THEN 'New' ELSE 'Existing' END AS type FROM syscat.columns a ORDER BY sysfun.rand();
- In this final step, you create a view and function on top of the CUSTOMER_INFO table.
Note the order in which you create the objects. You create the EXISTING_CUSTOMERS view before you create the FULL_NAME function that the view depends on. This is significant because by default, this would cause the CREATE OR REPLACE VIEW statement to fail with an SQL0440N error.
However, beginning with version 9.7 you can configure DB2 to allow creation of objects with certain types of errors such as missing dependent objects. This capability can be useful for database object creation, design, and modification because it permits you to have a random order of CREATE commands. It also allows you to verify the syntax of new views and procedures without creating the objects they depend on.
In order to enable this feature, use the following command to change the AUTO_REVAL dynamic database configuration parameter to have a value of DEFERRED_FORCE.
Listing 3. Setting AUTO_REVAL configuration parameter
db2 update db cfg using AUTO_REVAL DEFERRED_FORCE
After changing this parameter, when you create the EXISTING_CUSTOMER view you will receive an SQL20480W warning and the view is initially marked as invalid. However, if the function that the view is dependent on does exist the next time the view is used, the view is automatically revalidated.
Use the following commands to create the new view and function. Note that the commands take advantage of the new CREATE OR REPLACE syntax that is applicable to functions, procedures, views, modules, aliases, triggers, variables, and nicknames. As the name implies, this syntax creates the object, or if it exists already, replaces it. In other words, for existing objects it combines both DROP and CREATE in one command plus it preserves the existing privileges granted on the object.
Listing 4. Create function and view
CREATE OR REPLACE VIEW existing_customers AS SELECT full_name(customer_id) AS full_name, address_city, address_state FROM customer_info WHERE customer_type='Existing'; CREATE OR REPLACE function full_name(p_customer_id INTEGER) RETURNS VARCHAR(100) return SELECT first_name || ', ' || last_name FROM customer_info WHERE customer_id=p_customer_id;
Making online modifications to the table definition
There are two significant enhancements in DB2 9.7 related to online modifications to the table definition:
- First, you can rename columns online, while workload is running against the table, without any disruption to your users.
- Second, DB2 9.7 has extended its support for changing column data types on existing tables.
The following example demonstrates how to rename a column using the ALTER TABLE command while keeping the table fully accessible:
Listing 5. Rename column example
ALTER TABLE customer_info RENAME COLUMN age TO customer_age ;
The ALTER COLUMN SET DATA TYPE option on the ALTER TABLE statement has been extended to support all compatible types. For example, you can now change a column defined with a data type of INTEGER to have a data type of VARCHAR, or you can change a data type of TIMESTAMP to DATE. Refer to the "Casting between data types" article of the DB2 Information Center for a complete list of compatible data types (see the Resources section for a link).
During the ALTER TABLE operation with the ALTER COLUMN SET DATA TYPE option, DB2 performs a complete validation to ensure the data in the column is compatible with new data type and there is no truncation, overflow, or any other type of error. The column default values are also verified to ensure that they conform to the new data type. If the column type and the data content are compatible, the data type is successfully altered. Otherwise, the ALTER command returns an error.
The following example demonstrates how to change the data type of a column named customer_age from VARCHAR(2) to SMALLINT:
Listing 6. Alter column type
ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE SMALLINT;
In most cases, ALTER SET DATA TYPE requires table reorganization (reorg) because it changes the physical row format. You can use the ADMIN_REVALIDATE_DB_OBJECTS routine to automatically determine if the table requires reorg or not:
Listing 7. Table revalidation
CALL ADMIN_REVALIDATE_DB_OBJECTS('TABLE', 'DB2INST1', 'CUSTOMER_INFO');
If you need to have write access to the database available while column data type is changed, you can use of ADMIN_MOVE_TABLE routine described in the next section.
Moving a table online
A new routine in DB2 9.7 named ADMIN_MOVE_TABLE lets you automatically move a table from one tablespace to another or change the column data type definition while keeping the table fully accessible to its users.
Internally, ADMIN_MOVE_TABLE creates a copy of the source table. The data is copied by using either INSERT FROM CURSOR, which is the default, or LOAD if the correct option is set. While the table is being moved, the ADMIN_MOVE_TABLE routine also creates a staging table that keeps track of all changes to the source table. Changes are tracked through triggers that are created on the source table. At the end of the move operation, the ADMIN_MOVE_TABLE routine briefly locks the source table in exclusive mode, catches up with updates that have occurred, and substitutes the source table with the target. The source table is either dropped or kept based on one of the ADMIN_MOVE_TABLE input parameters.
In addition to changing the tablespace location of a table, you can also use the ADMIN_MOVE_TABLE routine to alter multi-dimensional clustering, partitioning keys, range partitioning, and columns data type. ADMIN_MOVE_TABLE moves triggers and views defined on the source table to the target table. However, it does not currently support copying of foreign keys, either parent or child. Therefore, if your source table was either a parent or child in an RI relationship, you need to capture the foreign keys definition and recreate them after the table is moved.
In the following example, an SQL statement captures foreign key information and generates DROP statements:
Listing 8. Create and drop commands for foreign keys
SELECT 'ALTER TABLE ' || RTRIM(a.tabschema)||'.'||RTRIM(a.tabname) || ' ADD CONSTRAINT '||a.constname|| ' FOREIGN KEY (' || fk_colnames || ') REFERENCES ' || RTRIM(reftabschema)||'.'||RTRIM(reftabname) || ' ('||pk_colnames||') ON DELETE ' || CASE deleterule WHEN 'A' THEN 'NO ACTION' WHEN 'C' THEN 'CASCADE' WHEN 'N' THEN 'SET NULL' WHEN 'R' THEN 'RESTRICT' END || ' ON UPDATE ' || CASE updaterule WHEN 'A' THEN 'NO ACTION' WHEN 'R' THEN 'RESTRICT' END || CASE enforced WHEN 'Y' THEN ' ENFORCED ' WHEN 'N' THEN ' NOT ENFORCED ' END || ' QUERY OPTIMIZATION '|| CASE enablequeryopt WHEN 'Y' THEN ' ENABLE ' WHEN 'N' THEN ' DISABLE 'END || ';' FROM syscat.references a, syscat.tabconst b WHERE a.constname=b.constname; SELECT 'ALTER TABLE ' || RTRIM(tabschema)||'.'||RTRIM(tabname) || ' DROP FOREIGN KEY ' || constname || ';' FROM syscat.references WHERE (tabschema='DB2INST1' AND tabname='CUSTOMER_INFO') OR (reftabschema='DB2INST1' AND reftabname='CUSTOMER_INFO');
The next example demonstrates how to move the CUSTOMER_INFO table to a new tablespace:
Listing 9. Moving table with ADMIN_MOVE_TABLE routine
CREATE TABLESPACE new_ts; CALL SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1', 'CUSTOMER_INFO', 'NEW_TS', 'NEW_TS', 'NEW_TS', '', '', '', '', '', 'MOVE'); Result set 1 -------------- KEY VALUE -------------------------------- --------------------------- AUTHID DB2INST1 CLEANUP_END 2009-06-11-188.8.131.528000 CLEANUP_START 2009-06-11-184.108.40.2067000 COPY_END 2009-06-11-220.127.116.111000 COPY_OPTS OVER_INDEX,ARRAY_INSERT COPY_START 2009-06-11-18.104.22.1689000 COPY_TOTAL_ROWS 6745 INDEX_CREATION_TOTAL_TIME 1 INDEXNAME CUSTOMER_ID_PK INDEXSCHEMA DB2INST1 INIT_END 2009-06-11-22.214.171.1247000 INIT_START 2009-06-11-126.96.36.199000 REPLAY_END 2009-06-11-188.8.131.52000 REPLAY_START 2009-06-11-184.108.40.2061000 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 0 STATUS COMPLETE SWAP_END 2009-06-11-220.127.116.111000 SWAP_RETRIES 0 SWAP_START 2009-06-11-18.104.22.1689000 VERSION 09.07.0000 21 record(s) selected.
The next example is a bit more complex. This example moves the same CUSTOMER_INFO table, but it uses the LOAD option instead of the INSERT FROM CURSOR option, which is the default.
Several factors contribute to making the LOAD option faster than INSERT FROM CURSOR option:
- LOAD does not log the changes on the target per record.
- LOAD is highly parallelized for CPU and I/O processing.
- LOAD performs large block writes to the target table. This provides additional I/O efficiencies.
Note that this operation is not recoverable, so if you need to recover your database to the point in time between the table move completion time and following backup, the table may get lost. In order to avoid data loss, you can use the KEEP option. The KEEP option forces the ADMIN_MOVE_TABLE routine to keep the original Also, when using LOAD as the copy option, you must use the FORCE option. The FORCE option requirement serves as a reminder that LOAD is a non-recoverable operation and that if you want recoverability, you must create a backup.
Listing 10. Moving table with LOAD option
CALL SYSPROC.ADMIN_MOVE_TABLE ('DB2INST1', 'CUSTOMER_INFO', 'NEW_TS', 'NEW_TS', 'NEW_TS', '', '', '', '', 'KEEP, COPY_USE_LOAD, FORCE', 'MOVE') Result set 1 -------------- KEY VALUE -------------------------------- ---------------------------- AUTHID DB2INST1 CLEANUP_END 2009-06-11-22.214.171.1240000 CLEANUP_START 2009-06-11-126.96.36.1997000 COPY_END 2009-06-11-188.8.131.524000 COPY_OPTS OVER_INDEX,LOAD,WITH_INDEXES COPY_START 2009-06-11-184.108.40.2063000 COPY_TOTAL_ROWS 6745 INDEX_CREATION_TOTAL_TIME 0 INDEXNAME CUSTOMER_ID_PK INDEXSCHEMA DB2INST1 INIT_END 2009-06-11-220.127.116.116000 INIT_START 2009-06-11-18.104.22.168000 ORIGINAL CUSTOMER_INFOAC61b#o REPLAY_END 2009-06-11-22.214.171.124000 REPLAY_START 2009-06-11-126.96.36.1994000 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 0 STATUS COMPLETE SWAP_END 2009-06-11-188.8.131.52000 SWAP_RETRIES 0 SWAP_START 2009-06-11-184.108.40.206000 VERSION 09.07.0000 22 record(s) selected.
The example in Listing 3 moves the table much faster and saves the original table with the name CUSTOMER_INFOAC61b#o.
The next example demonstrates how to alter the tablespace not only for table data, but also for indexes and LOBs. It also demonstrates how to change properties of the table. The CUSTOMER_INFO table keeps the indexes and LOBs in the USERSPACE1 tablespace, while table data is moved to the NEW_TS tablespace. The example converts the table to use Multi Dimensional Clustering (MDC) using the CUSTOMER_TYPE column as the clustering key and changes the data type of CUSTOMER_AGE column back to VARCHAR(2).
Listing 11. Changing table properties with ADMIN_MOVE_TABLE routine
CALL SYSPROC.ADMIN_MOVE_TABLE( 'DB2INST1', 'CUSTOMER_INFO', 'NEW_TS', 'USERSPACE1', 'USERSPACE1', 'CUSTOMER_TYPE', '', '', 'customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_city VARCHAR(128), address_state VARCHAR(25), address_country VARCHAR(30), customer_age VARCHAR(2), customer_type VARCHAR(10)', '', 'MOVE'); Result set 1 -------------- KEY VALUE -------------------------------- -------------------------------- AUTHID MAKSYMP CLEANUP_END 2009-06-11-14.30.04.438000 CLEANUP_START 2009-06-11-14.30.04.422000 COPY_END 2009-06-11-14.30.03.750000 COPY_OPTS OVER_INDEX,ARRAY_INSERT COPY_START 2009-06-11-14.30.03.219000 COPY_TOTAL_ROWS 6745 INDEX_CREATION_TOTAL_TIME 1 INDEXNAME CUSTOMER_ID_PK INDEXSCHEMA DB2INST1 INIT_END 2009-06-11-14.30.03.125000 INIT_START 2009-06-11-14.30.02.250000 PAR_COLDEF customer_id INTEGER NOT NULL, first_name VARCHAR(128) NOT NULL, last_name VARCHAR(128), address_street VARCHAR(128), address_ci REPLAY_END 2009-06-11-14.30.04.344000 REPLAY_START 2009-06-11-14.30.03.750000 REPLAY_TOTAL_ROWS 0 REPLAY_TOTAL_TIME 0 STATUS COMPLETE SWAP_END 2009-06-11-14.30.04.407000 SWAP_RETRIES 0 SWAP_START 2009-06-11-14.30.04.360000 VERSION 09.07.0000
This article described:
- How to use CREATE OR REPLACE syntax
- How to create certain database objects with errors
- How to rename columns and change column data type
- How to use ADMIN_MOVE_TABLE routing to move tables and modify table properties
The new online schema change features in DB2 9.7 help DBAs and application developers significantly reduce the database downtime associated with planned outages. These features also simplify management of the schema objects and improve your overall productivity.
- On the DB2 for Linux, UNIX, and Windows area on developerWorks, get the resources you need to advance your skills on DB2 9.7.
- "The SAMPLE database" article of the DB2 for Linux, UNIX, and Windows Information Center provides instructions for how to create the SAMPLE database used in the examples this article.
- The "Casting between data types" article of the DB2 for Linux, UNIX, and Windows Information Center provides a complete list of compatible data types supported by the ALTER COLUMN SET DATA TYPE option on the ALTER TABLE statement.
- Find additional information describing how to use DB2 in the DB2 for Linux, UNIX, and Windows Information Center.
- Browse the technology bookstore for books on these and other technical topics.
Get products and technologies
- Download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.