DB2 9.7 database online mode change Guide

Today, we mainly describe how to minimize the planned downtime of DB2 9.7 database online mode changes when modifying database objects. We all know that IBM®DB2®9.7 introduces new enhancements that allow you to change database objects and ensure that users have full access to these objects during database modification.

These enhancements allow the database administrator to dynamically modify the database mode without affecting the user. This article provides several examples to demonstrate how to use the alter table command to rename a column, modify the column data type, and use the ADMIN_MOVE_TABLE routine to move the TABLE. Other examples demonstrate how to use the ADMIN_MOVE_TABLE routine to move and modify tables while maintaining accessibility.

Overview

This document provides guidelines for using new enhancements to DB2 9.7, allowing you to make online changes to the database schema. Online modification means that the modified object can still be read and written, even during modification.

Some specific new features include:

Use the alter table statement to rename a column online.

Or replace is added to multiple CREATE statements as an option.

CREATE with error support is added for views and inline SQL functions.

Added support for alter column set data type.

You can use the ADMIN_MOVE_TABLE routine to modify and move tables online.

Prerequisites and System Requirements

This article is specially written for the DB2 database administrator. You should understand the basic concepts of tablespaces, tables, and columns.

To use this example, you must install DB2 9.7 for Linux, UNIX, and Windows. Download the free trial version of DB2 9.7 for Linux, UNIX, and Windows at the link provided in the references section.

Prepare for use example

To use an example that demonstrates the new online mode change feature of DB2, you must first create a sample database that is used as the necessary infrastructure. The example uses the DB2 SAMPLE database. If you have not created a DB2 SAMPLE database, follow The instructions in The "The SAMPLE database" section of DB2 Information Center to create a database. See The reference section for a link ).

Once the SAMPLE database is created, follow these steps to create the necessary tables and data for the example:

Run the following command to create a table based on the system directory where customer information is simulated:

List 1. Create a 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; 
 
 

Run the following command to fill data in the CUSTOMER_INFO table with fictitious information from the system directory:

Listing 2. Filling the 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 the last step, you create a view and function based on the CUSTOMER_INFO table.

Pay attention to the order in which objects are created. Create the existing_mers MERs view, and then create the FULL_NAME function on which the view depends. This is important because, by default, the create or replace view statement fails with the SQL0440N error.

However, from version 9.7, you can configure DB2 to allow the creation of objects with certain error types, such as missing dependency objects. This function is useful for creating, designing, and modifying database objects because it allows you to use the CREATE Command in random order. You can also check the syntax of the new view and process without creating the dependent object.

To enable this feature, run the following command to modify the AUTO_REVAL dynamic database configuration parameters and set the value to DEFERRED_FORCE.

Listing 3. Setting AUTO_REVAL configuration parameters

 
 

  
  
db2 update db cfg using AUTO_REVAL DEFERRED_FORCE  
 
 

After you modify this parameter, you will receive a SQL20480W warning when creating the EXISTING_CUSTOMER view, and the view is initially marked as invalid. However, if the function that the view depends on still exists when you use the view next time, The view will be automatically re-verified.

Use the following command to create a new view and function. Note that these commands use the new create or replace syntax, which applies to functions, processes, views, modules, aliases, triggers, variables, and nicknames. As the name suggests, this syntax creates an object. If the object already exists, it will replace the object. In other words, for an existing object, it combines DROP and CREATE in the same command and retains the existing privileges assigned to the object.

Listing 4. Creating functions and views

 
 

  
  
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;  
 
 

Online modification of table Definitions

Two important enhancements added to DB2 9.7 are related to online table definition modification:

First, you can rename columns online, and still run workloads on tables without interfering with users.

Secondly, DB2 9.7 extends its support for modifying the column data type in an existing table.

The following example demonstrates how to use the alter table command to rename a column and keep the TABLE completely accessible:

Listing 5. Example of renaming a column

 
 

  
  
ALTER TABLE customer_info RENAME COLUMN age TO customer_age ;  
 
 

The alter column set data type option in the alter table statement is expanded to support all compatible types. For example, you can modify a column whose data type is integer to have a varchar data type, or change the data type from TIMESTAMP to DATE. For a complete list of compatible data types, see the "Casting between data types" section in DB2 Information Center ).

During the alter table operation using the alter column set data type option, DB2 performs a complete verification to ensure that the column data is compatible with the new data type, and no truncation, overflow, or any other type of error occurs. Column default values are also verified to ensure they comply with the new data type. If the column type is compatible with the data content, the data type can be changed successfully. Otherwise, the ALTER command returns an error.

The following example shows how to change the data type of a column named customer_age from VARCHAR (2) To SMALLINT:

Listing 6. Modifying the column type

 
 

  
  
ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE SMALLINT;  
 
 

In most cases, alter set data type needs to reorg the table because it modifies the format of the physical row. You can use the ADMIN_REVALIDATE_DB_OBJECTS routine to automatically determine whether to reorganize the table:

Listing 7. reverifying the table

 
 

  
  
CALL ADMIN_REVALIDATE_DB_OBJECTS('TABLE', 'DB2INST1', 'CUSTOMER_INFO');  
 
 

To perform write access to the database during the modification of the column data type, you can use the ADMIN_MOVE_TABLE routine described in the next section.

The above content is an introduction to minimizing the planned downtime of DB2 9.7 online mode change when modifying database objects. I hope you will get some benefits.