 | 级别： 初级 Maksym Petrenko, DB2 Open Database Technologies, IBM
2009 年 9 月 07 日 IBM® DB2® 9.7 引入了新的增强，允许您更改数据库对象，同时确保在修改数据库期间用户可以完全访问这些对象。这些增强允许数据库管理员在不影响用户的情况下动态地对数据库模式进行重要修改。本文提供了若干示例，演示如何使用 ALTER TABLE 命令重命名列、修改列数据类型，以及使用 ADMIN_MOVE_TABLE 例程移动表。其他示例演示了如何使用 ADMIN_MOVE_TABLE 例程移动和修改表，同时保持可访问性。
概述
本文提供了有关使用 DB2 9.7 新增强的指南，使您能够对数据库模式进行在线 更改。在线更改意味着被修改的对象仍然可以进行读写访问，甚至在修改期间也是这样。
其中一些特定的新功能包括：
- 使用 ALTER TABLE 语句在线重命名列。
- OR REPLACE 作为选项被添加到多个 CREATE 语句中。
- 为视图和内联 SQL 函数添加了带有错误支持的 CREATE。
- 扩展了 ALTER COLUMN SET DATA TYPE 支持。
- 可以使用 ADMIN_MOVE_TABLE 例程在线修改和移动表。
先决条件和系统需求
本文专门为 DB2 数据库管理员编写。您应当理解表空间、表和列的基本概念。
要使用本文的示例，您必须安装 DB2 9.7 for Linux, UNIX, and Windows。使用 参考资料 小节提供的链接下载 DB2 9.7 for Linux, UNIX, and Windows 的免费试用版。
为使用示例做准备
要使用演示 DB2 的新的在线模式变更功能的示例，首先需要创建一个用作必要基础设施的样例数据库。示例使用了 DB2 SAMPLE 数据库。如果尚未创建 DB2 SAMPLE 数据库，那么请按照 DB2 Information Center 的 “The SAMPLE database” 一文中的说明创建数据库（见 参考资料 小节获得链接）。
一旦创建了 SAMPLE 数据库后，遵循这些步骤创建必要的表和数据，供示例使用：
- 使用以下命令，根据模拟客户信息的系统目录创建一个表：
清单 1. 创建 CUSTOMER_INFO 表
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; |
- 使用以下命令，用来自系统目录的虚构信息向 CUSTOMER_INFO 表填充数据：
清单 2. 填充 CUSTOMER_INFO 表
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(); |
- 在最后一步中，您在 CUSTOMER_INFO 表的基础上创建了一个视图和函数。
注意创建对象时使用的顺序。您首先创建 EXISTING_CUSTOMERS 视图，然后创建视图所依赖的 FULL_NAME 函数。这一点非常重要，因为在默认情况下，这将造成 CREATE OR REPLACE VIEW 语句失败，并发生 SQL0440N 错误。
然而，从版本 9.7 开始，您可以配置 DB2 来允许创建具有某些错误类型（比如缺失依赖对象）的对象。这个功能对于数据库对象的创建、设计和修改都很有用，因为它允许您按照随机的顺序使用 CREATE 命令。您还可以检查新的视图和过程的语法，而不需要创建所依赖的对象。
要启用这个特性，需要使用以下命令修改 AUTO_REVAL 动态数据库配置参数，将值设置为 DEFERRED_FORCE。
清单 3. 设置 AUTO_REVAL 配置参数
db2 update db cfg using AUTO_REVAL DEFERRED_FORCE |
修改该参数后，在创建 EXISTING_CUSTOMER 视图时，将收到一个 SQL20480W 警告，并且视图最初被标记为无效。然而，如果视图所依赖的函数在下一次使用视图时仍然存在，那么该视图将被自动重新进行验证。
使用以下命令创建新的视图和函数。注意，这些命令利用了新的 CREATE OR REPLACE 语法，此语法适用于函数、过程、视图、模块、别名、触发器、变量和昵称。顾名思义，这个语法创建对象，如果对象已存在的话，那么将替代对象。换言之，对于已经存在的对象，它将在同一个命令中结合使用 DROP 和 CREATE，并保留分配给该对象的已有特权。
清单 4. 创建函数和视图
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; |
|
|
对表定义进行在线修改
DB2 9.7 新增的两项重要增强均与在线修改表定义相关：
- 首先，可以以在线的方式重命名列，同时仍可以针对表运行工作负载，不会对用户产生任何干扰。
- 其次，DB2 9.7 扩展了它修改已有表中的列数据类型的支持。
下面的示例演示了如何使用 ALTER TABLE 命令重命名一个列，同时保持表具有完整的可访问性：
清单 5. 重命名列的示例
ALTER TABLE customer_info RENAME COLUMN age TO customer_age ; |
ALTER TABLE 语句中的 ALTER COLUMN SET DATA TYPE 选项进行了扩展，可以支持所有兼容的类型。例如，现在可以修改一个数据类型为 INTEGER 的列，从而拥有一个 VARCHAR 数据类型，或者将数据类型从 TIMESTAMP 修改为 DATE。参考 DB2 Information Center 中的 “Casting between data types” 一文，获得兼容数据类型的完整列表（见 参考资料 小节获得链接）。
在使用 ALTER COLUMN SET DATA TYPE 选项执行 ALTER TABLE 操作期间，DB2 将执行一次完整的验证，确保列数据与新数据类型兼容，并且没有发生截断、外溢或任何其他类型的错误。列默认值也进行了验证，确保它们遵守新的数据类型。如果列类型和数据内容是兼容的，那么就能够成功更改数据类型。否则，ALTER 命令将返回一个错误。
以下示例演示了如何将名为 customer_age from VARCHAR(2) 的列的数据类型修改为 SMALLINT：
清单 6. 修改列类型
ALTER TABLE customer_info ALTER COLUMN customer_age SET DATA TYPE SMALLINT; |
大多数情况下，ALTER SET DATA TYPE 需要对表执行重组（reorg），因为它修改了物理行格式。可以使用 ADMIN_REVALIDATE_DB_OBJECTS 例程来自动判断是否需要对表执行重组：
清单 7. 对表进行重新验证
CALL ADMIN_REVALIDATE_DB_OBJECTS('TABLE', 'DB2INST1', 'CUSTOMER_INFO'); |
如果需要在修改列数据类型期间对数据库执行写访问，可以使用下一小节描述的 ADMIN_MOVE_TABLE 例程。
以在线方式移动表
DB2 9.7 中名为 ADMIN_MOVE_TABLE 的新例程让您能够自动将表从一个表空间移动到另一个表空间，或者修改列数据类型定义，同时保持表对用户的完全可访问性。
从内部来讲，ADMIN_MOVE_TABLE 创建了源表的一个副本。数据可以通过使用 INSERT FROM CURSOR（默认设置）复制，也可以通过 LOAD 复制（如果设置了正确的选项的话）。尽管已经移动了表，ADMIN_MOVE_TABLE 例程还会创建一个阶段（staging）表，该表将跟踪源表中发生的所有更改。更改是通过在源表中创建的触发器完成跟踪的。在完成移动操作后，ADMIN_MOVE_TABLE 例程将以独占（exclusive）模式暂时锁定源表，同步已经发生的更新，并使用目标表代替源表。根据 ADMIN_MOVE_TABLE 输入参数之一，源表将被删除或保留。
除了修改表的表空间位置外，还可以使用 ADMIN_MOVE_TABLE 例程修改多维集群、分区键、范围分区和列数据类型。ADMIN_MOVE_TABLE 将源表中定义的触发器和视图移动到目标表。然而，不管是父表还是子表，目前还不支持复制外键。因此，如果源表在 RI 关系中为父表或子表，您需要捕捉外键定义并在移动表之后重新创建它们。
在下面的示例中，一个 SQL 语句将捕捉外键信息并生成 DROP 语句：
清单 8. 用于创建和删除外键的命令
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'); |
下一个示例演示了如何将 CUSTOMER_INFO 表移动到新的表空间中：
清单 9. 使用 ADMIN_MOVE_TABLE 例程移动表
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. |
下一个示例稍微复杂一点。该示例移动同一个 CUSTOMER_INFO 表，但是使用了 LOAD 选项，而不是 INSERT FROM CURSOR 选项，后者是默认配置。
一些因素可以使 LOAD 选项比 INSERT FROM CURSOR 选项更快速：
- LOAD 并不会记录目标表中每秒发生的更改。
- LOAD 针对 CPU 和 I/O 处理进行了高度并行化。
- LOAD 对目标表执行大块写操作。这提供了更高的 I/O 效率。
注意，这个操作是不可恢复的，因此如果需要将数据库恢复到完成表移动与备份之间的某个时间点，那么表可能会丢失。要避免数据丢失，可以使用 KEEP 选项。KEEP 选项强制 ADMIN_MOVE_TABLE 例程保持初始值。同样，在使用 LOAD 作为复制选项时，必须使用 FORCE 选项，因为 FORCE 选项提醒您 LOAD 是一个不可恢复的操作，并且如果希望获得可恢复性，必须创建一个备份。
清单 10. 使用 LOAD 选项移动表
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. |
清单 3 中的示例可以更快速地移动表，并保存名为 CUSTOMER_INFOAC61b#o 的初始表。
下一个示例演示了如何不仅为表数据，还为索引和 LOB 修改表空间。它还演示了如何修改表的属性。CUSTOMER_INFO 表在 USERSPACE1 表空间中保留了索引和 LOB，而表数据被移动到 NEW_TS 表空间。该示例通过将 CUSTOMER_TYPE 列作为聚集（clustering）键，把表转换为使用 Multi Dimensional Clustering (MDC)，并且将 CUSTOMER_AGE 列的数据类型修改回 VARCHAR(2)。
清单 11. 使用 ADMIN_MOVE_TABLE 例程修改表属性
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
|
|
|
结束语
本文介绍了以下主题：
- 如何使用 CREATE OR REPLACE 语法
- 如何创建一些带有错误的数据库对象
- 如何重命名列并修改列数据类型
- 如何使用 ADMIN_MOVE_TABLE 例程移动表和修改表属性
DB2 9.7 中的新的在线模式修改特性帮助 DBA 和应用程序开发人员显著减少与计划内宕机有关的数据库宕机。这些特性还简化了模式对象的管理并提高了整体生产力。
参考资料 学习
获得产品和技术
讨论
关于作者 | 
| | Maksym Petrenko 是 IBM 多伦多实验室的 DB2 Beta Enablement 团队的成员。他帮助早期采用者将他们的应用程序迁移到最新、最好的 DB2 代码库中。Maksym 自 2001 年起就从事 DB2 研究，是一名开发人员、技术支持分析师和实验室服务顾问。他的专业知识包括支持客户机安装、配置、应用程序开发，以及 DB2 数据库在 Windows、Linux 和 UNIX 平台上的性能问题。Maksym 是一位经过认证的 DB2 高级数据库管理员和 DB2 应用程序开发人员。 |
对本文的评价
| |
