在 DB2 的数据库开发中,经常会因为对表进行约束变更 或 因业务数据的约束违例而导致一些问题,作为一名 DBA经常要去和这些问题打交道。
这些问题大致分为两类:
**1、REORG-recommended 语句问题
2、Check-Pending 约束检查问题**
REORG-recommended 语句是一种特殊类型的 ALTER TABLE 语句,最多可以对一个表运行三次这种类型的语句,超过3次后该表将进入 Reorg-Pending状态,必须对其进行重组后,状态才能恢复至正常。
以下是 REORG-recommended 的 ALTER 语句的完整列表,这些语句会引起版本更改并将表置于 REORG-pending 状态:
ALTER COLUMN SET DATA TYPE, 除了以下情况:
因此,在进行表字段的 NOT NULL约束变更时,一定要注意这个 REORG-recommended 问题。由于限制次数是三次的原因,在变更时 很轻易地就会忽略这个问题。
Check-Pending 约束检查问题,不像 REORG-recommended 问题比较隐蔽,这种问题显而易见,使用 SET INTEGRITY 进行约束检查就可以了。下面我列举几个例子讲讲。
SET INTEGRITY 语句可用于临时暂停表上的约束检查。此语句也可用于执行以下操作:
将一个或多个表从设置完整性挂起状态中脱离出来,而不执行数据验证。
SET INTEGRITY 语句的基本语法是:
SET INTEGRITY FOR [TableName]
[OFF | FULL ACCESS | PRUNE] |
[IMMEDIATE CHECKED | IMMEDIATE UNCHECKED]
AccessMode:在检查数据的完整性设置为 OFF 时,标识是否可以以只读模式访问指定的表。有效的选项是 READ ACCESS 和 NO ACCESS。
SET INTEGRITY 示例 :
1、如果要挂起 Employee 表上的数据完整性检查,可以执行以下语句:
SET INTEGRITY FOR demo.employee OFF;
前面的语句将 Employee 表置于 CHECK PENDING 状态。 在此状态下,将不允许对表进行任何 SELECT、INSERT、UPDATE 和 DELETE 操作。此时允许的唯一操作是 ALTER TABLE 语句。
2、关闭数据完整性检查后,可以使用 IMMEDIATE CHECK 或 UN CHECK 选项打开表上的约束检查,使用以下语句:
SET INTEGRITY FOR demo.employee IMMEDIATE CHECKED;
SET INTEGRITY FOR demo.employee IMMEDIATE UNCHECKED;
IMMEDIATE CHECK 选项启用数据完整性检查,并在一个步骤中执行数据验证。如果存在违反约束的情况,完整性检查将被取消,表将再次处于 CHECK PENDING 状态。 IMMEDIATE UNCHECKED 选项启用了数据完整性检查,但它不执行数据验证。
3、 如果存在数据完整性违例的情况,则可能需要将违规记录放置到单独的表中。为此,可以在 SET INTEGRITY 命令中指定异常表,如下所示在以下代码片段中。 此语句还将表从“检查挂起”状态中带出来:
CREATE TABLE demo.employee_exception LIKE demo.Employee;
DB20000I The SQL command completed successfully
SET INTEGRITY FOR demo.employee IMMEDIATE CHECKED FOR EXCEPTION IN demo.employee USE demo.employee_exception;
SQL3602W Check data processing found constraint violations and moved them to exception tables.SQLSTATE = 01603
4、在范围分区表的情况下,必须在 "ALTER TABLE ... ATTACH" 附加分区 之后将任何新分区联机之前,运行 SET INTEGRITY 语句进行完整性检查。 在范围分区表中由 SET INTEGRITY 语句执行的操作如下:
更新新记录的索引键
以下示例说明在 ALTER TABLE...ATTACH 操作 期间使用 SET INTEGRITY 语句:
-- Create partition table
CREATE TABLE demo.sales
(sale_date DATE CHECK (sale_date >= ' 01/01/2016 ' ),
sale_amount NUMERIC (8,2))
PARTITION BY RANGE (sale_date)
(STARTING ' 01/01/2016 ' ENDING ' 10/31/2017 ' EVERY 1 MONTH);
-- Create a partition for November 2017
CREATE TABLE demo.sales_112017 (
sale_date DATE,
sale_amount NUMERIC (8,2));
-- Populate a few sample records into new November 2017 table
INSERT INTO demo.sales_112017 VALUES ( ' 11/01/2017 ' , 100000);
INSERT INTO demo.sales_112017 VALUES ( ' 11/02/2017 ' , 60000);
-- Attach the table as a new partition
ALTER TABLE demo.sales ATTACH PART " PART37 "
STARTING ( ' 11/01/2017 ' ) ENDING ( ' 11/30/2017 ' ) FROM demo.sales_112017;
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE = 01586
-- Verify the check pending status
SELECT SUBSTR(RTRIM(TABSCHEMA) || ' . ' || RTRIM(TABNAME),1,30) AS CHECK_PENDING_TABLE
FROM syscat.tables
WHERE STATUS = ' C ' ;
CHECK_PENDING_TABLE
---------------------------------------- -
DEMO.SALES
1 record(s) selected.
-- Perform the data integrity check
SET INTEGRITY FOR demo.sales IMMEDIATE CHECKED;
-- Verify the check pending status again
SELECT SUBSTR(RTRIM(TABSCHEMA) || ' . ' || RTRIM(TABNAME),1,30) AS CHECK_PENDING_TABLE
FROM syscat.tables
WHERE STATUS = ' C ' ;
CHECK_PENDING_TABLE
----------------------------- -
0 record(s) selected.
以上这些只是本人日常数据库开发工作中的一点经验分享,希望社区各位老师来多多交流分享。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞5
添加新评论1 条评论
2020-09-17 20:44
kong_fanqing: @KiKibj alter table 修改表结构之后,重点应该是在其后的数据装入后,实际的物理存储顺序的问题,所以需要reorg 进行整理重新排序。底层是物理存储,目的是访问性能,这是我的理解,可能不全面。
atpeace331: @KiKibj 这个机制设计的初衷我目前也没搞得太清楚,估计出于变更时可用性方面的考虑吧