冯岩
作者冯岩2020-09-16 16:38
数据库管理员, 银行

DB2 业务规则的应用实践(3)

字数 3912阅读 421评论 1赞 2

在 DB2 的数据库开发中,经常会因为对表进行约束变更 或 因业务数据的约束违例而导致一些问题,作为一名 DBA经常要去和这些问题打交道。

这些问题大致分为两类:
1、REORG-recommended 语句问题
2、Check-Pending 约束检查问题

REORG-recommended 语句问题

REORG-recommended 语句是一种特殊类型的 ALTER TABLE 语句,最多可以对一个表运行三次这种类型的语句,超过3次后该表将进入 Reorg-Pending状态,必须对其进行重组后,状态才能恢复至正常。

以下是 REORG-recommended 的 ALTER 语句的完整列表,这些语句会引起版本更改并将表置于 REORG-pending 状态:

  • DROP COLUMN
  • ALTER COLUMN SET NOT NULL
  • ALTER COLUMN DROP NOT NULL
  • ALTER COLUMN SET DATA TYPE, 除了以下情况:
    • 增加 VARCHAR 或 VARGRAPHIC 列的长度
    • 当 VARCHAR 或 VARGRAPHIC 列上不存在索引时,在不截断现有数据末尾空白的情况下减少列的长度

因此,在进行表字段的 NOT NULL约束变更时,一定要注意这个 REORG-recommended 问题。由于限制次数是三次的原因,在变更时 很轻易地就会忽略这个问题。

SET INTEGRITY 实施约束检查

Check-Pending 约束检查问题,不像 REORG-recommended 问题比较隐蔽,这种问题显而易见,使用  SET INTEGRITY 进行约束检查就可以了。下面我列举几个例子讲讲。

SET INTEGRITY 语句可用于临时暂停表上的约束检查。此语句也可用于执行以下操作:

  • 将一个或多个表放置到设置完整性挂起(set integrity pending)状态。
  • 将一个或多个表置于完全访问(full access)状态。
  • 修剪一个或多个登台表(staging tables)的内容。
  • 通过约束检查执行数据验证,使一个或多个表脱离设置完整性挂起状态。
  • 将一个或多个表从设置完整性挂起状态中脱离出来,而不执行数据验证。

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 语句执行的操作如下:

  • 验证新添加的分区中的数据,以确保所有数据都符合为新分区定义的范围。
  • 更新任何生成列( generated columns ) 并 检查表中可能存在的任何约束。
  • 要更新可在基本表中定义的任何物化查询表(MQTs)
  • 更新新记录的索引键

以下示例说明在 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.

以上这些只是本人日常数据库开发工作中的一点经验分享,希望社区各位老师来多多交流分享。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

2

添加新评论1 条评论

#KiKibj项目经理, 金融业
2020-09-17 20:44
难怪有时候 alter table 修改表结构后,会莫名其妙的需要重新重组。

冯岩@KiKibj 这个机制设计的初衷我目前也没搞得太清楚,估计出于变更时可用性方面的考虑吧

2020-09-18 09:36
Ctrl+Enter 发表

分布式关系型数据库选型优先顺序调查

发表您的选型观点,参与即得50金币。