DB2 9.7: Online schema change

Minimize planned outages when making changes to your database objects

IBM® DB2® 9.7 introduces enhancements that allow you to make changes to the database objects while keeping these objects fully accessible to the database users during the modification. These enhancements allow database administrators to make vital changes to the database schema on the fly without any disruption to users. This article includes examples that demonstrate how to use the ALTER TABLE command to rename columns and change column data types. Other examples demonstrate how to use the ADMIN_MOVE_TABLE routine to move and modify tables while keeping them accessible.

Maksym Petrenko, DB2 Open Database Technologies, IBM  

Maksym Petrenko photoMaksym 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.



30 July 2009

Also available in Spanish

Overview

Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See DB2 fundamentals for Oracle professionals: Introduction to DB2

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:

  1. 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;
  2. 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();
  3. 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-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

Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=416917
ArticleTitle=DB2 9.7: Online schema change
publish-date=07302009