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.
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 tableCREATE 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 tableINSERT 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 parameterdb2 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 viewCREATE 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.
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-13.24.45.438000
CLEANUP_START 2009-06-11-13.24.45.407000
COPY_END 2009-06-11-13.24.44.641000
COPY_OPTS OVER_INDEX,ARRAY_INSERT
COPY_START 2009-06-11-13.24.43.829000
COPY_TOTAL_ROWS 6745
INDEX_CREATION_TOTAL_TIME 1
INDEXNAME CUSTOMER_ID_PK
INDEXSCHEMA DB2INST1
INIT_END 2009-06-11-13.24.43.797000
INIT_START 2009-06-11-13.24.43.250000
REPLAY_END 2009-06-11-13.24.45.250000
REPLAY_START 2009-06-11-13.24.44.641000
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2009-06-11-13.24.45.391000
SWAP_RETRIES 0
SWAP_START 2009-06-11-13.24.45.329000
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-13.36.43.360000
CLEANUP_START 2009-06-11-13.36.43.297000
COPY_END 2009-06-11-13.36.42.704000
COPY_OPTS OVER_INDEX,LOAD,WITH_INDEXES
COPY_START 2009-06-11-13.36.40.563000
COPY_TOTAL_ROWS 6745
INDEX_CREATION_TOTAL_TIME 0
INDEXNAME CUSTOMER_ID_PK
INDEXSCHEMA DB2INST1
INIT_END 2009-06-11-13.36.40.266000
INIT_START 2009-06-11-13.36.39.172000
ORIGINAL CUSTOMER_INFOAC61b#o
REPLAY_END 2009-06-11-13.36.43.125000
REPLAY_START 2009-06-11-13.36.42.704000
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 0
STATUS COMPLETE
SWAP_END 2009-06-11-13.36.43.250000
SWAP_RETRIES 0
SWAP_START 2009-06-11-13.36.43.125000
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.
Learn
- 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.
Discuss
Maksym Petrenko is part of DB2 Beta Enablement Team in IBM's Toronto Lab. He assists early adopters with moving their applications to the latest and greatest DB2 codebase. Maksym has worked with DB2 since 2001 as a developer, technical support analyst and lab services consultant. His experience includes supporting clients with installation, configuration, application development, and performance issues related to DB2 Databases on Windows, Linux, and UNIX platforms. Maksym is a certified DB2 Advanced Database Administrator and DB2 Application Developer.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
