在 DB2 通用数据库中自动生成数值序列

使用示例，我们的 SQL 专家向您展示了标识列和最近引入的序列对象的典型用法，以自动生成数值。 简介 IBM? DB2? Universal Database?(UDB) for Linux、UNIX? 和 Windows? V8.1 和最新发布的 DB2 Universal Database for z/OS? and OS/390? V8 都支持一组丰富的功能，这组功能可以自动生成数值序列。这些最新的 DB2 UDB 发行版在以前引入了标识列的基础上，又引入了数据库 序列对象。在本文中，我们简要讨论标识列和序列的典型用法，然后提供一些示例，对它们进行演示并比较它们的功能。     DB2 UDB for Linux、UNIX 和 Windows DB2 UDB for z/OS and OS/390 DB2 UDB for iSeries? 标识列 V7 V6 V5R2 序列对象 V7.2 V8（在撰写本文时还是 Beta 版） 计划将来推出发行版 国际 SQL 标准：已计划将标识列和序列对象列入国际 SQL 标准的下一版本。DB2 中实现的支持与所提议的内容紧密匹配，而且该支持已列入国际 SQL 标准的当前草案版本。 回页首 生成数字序列的两种方法 在 DB2 中可以用两种方法自动生成一个数字序列： 定义带有 IDENTITY 属性的列。 创建 SEQUENCE 对象。 IDENTITY 列 当用 IDENTITY 属性定义表的列时，每当将一行插入表时，就会自动为该列生成一个数值。 SEQUENCE 对象 让 DB2 自动生成数字序列的第二种方法是创建 SEQUENCE 对象。可以使用 序列表达式（sequence expression）来引用序列对象。序列表达式可以出现在表达式能够出现的大多数位置。序列表达式可以指定要返回的值是新生成的值还是以前生成的值。 如果序列引用是针对 下一值（next value），就会为序列自动生成一个数值，并将该数值作为序列表达式的结果返回。例如，如果我们假设已经创建了名为 orders_seq 的序列，该序列表达式就返回为序列生成的下一个值： NEXT VALUE FOR orders_seq 如果序列引用是针对 前一值（previous value），那么会将前一个 SQL 语句中为序列生成的数值作为序列表达式的结果返回。该序列表达式返回由序列生成的前一个值： PREVIOUS VALUE FOR orders_seq 注：当 DB2 UDB 引入序列时，曾支持非 SQL 标准语法 NEXTVAL 代替 NEXT VALUE ，以及 PREVVAL 代替 PREVIOUS VALUE 。这些变体继续受到支持。 回页首 在标识和序列之间做出选择 虽然标识和序列都用于生成数值，但您也许要根据特殊情况选择使用一种来代替另一种。 在以下情况下，标识列很有用： 表中只有一列需要自动生成的值 每一行都需要独立的值 用自动生成器来产生表的主键 生成新值的进程与对表的插入操作紧密联系，无论插入操作是如何发生的 在以下情况下，序列对象很有用： 要将从一个序列生成的值存储到多个表中 每个表有多个列需要自动生成的值（可能通过使用同一个序列或多个序列为每一行生成多个值） 生成新值的进程与任何对表的引用无关 与序列对象不同，标识列是在表上定义的，因此需要某些限制。每个表最多只能有一个标识列。当创建一个列作为标识列时，对于该列必须使用确切的数字数据类型。因为标识属性为列生成一个值，这类似于 DEFAULT 子句所做的，所以在定义标识列时不能指定 DEFAULT 子句。标识列被隐式地定义成 NOT NULL 。 回页首 示例 1. 组合客户和供应商表 要说明标识列的典型用法，请考虑这样一个表：它包含了虚构的 Widget 公司的数据库的客户订单信息。该公司希望为插入表中的每一行（订单）自动生成订单号。 用于我们的示例的 DDL 如 清单 1 所示，我们使用了用于订单号的标识列，他们将订单号列定义成主键的一部分。注：该列中的以及它本身的 IDENTITY 属性并没有保证所生成的序列值是唯一的。但是， PRIMARY KEY 约束保证了表中行的唯一性。为了确保只将自动生成的值插入标识列，他们指定了 GENERATED ALWAYS 子句。在每个季度结束时，Widget 公司使用最后一个生成的 order_id 来确定这个季度接了多少订单。选项 NO CACHE 和 ORDER 确保了在系统故障的情况下，不废弃未使用的标识值。Widget 公司计划通过使用 ALTER TABLE 语句，又从 1 重新开始订单号列，以开始一个新的季度。 清单 1中显式地显示了标识列的所有属性，即使所设定的值是未指定值时的缺省值。因为缺省值会因各供应商实现而不同，所以指定所有选项是一个很好的编码习惯。 清单 1. 使用 IDENTITY 列创建订单表 CREATE TABLE customer_orders_t ( order_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER), order_date DATE NOT NULL, cust_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, status CHAR(9) NOT NULL, PRIMARY KEY (order_date, order_id)) 以下是将行插入表的 INSERT 语句的示例。 INSERT INTO customer_orders_t VALUES ( DEFAULT, CURRENT DATE, :cid, :pid, :qty, :cost, 'PENDING') Widget 公司不仅管理客户订单，而且还管理供应商订单。供应商订单在一个独立的供应商订单表中，这个表的定义方式与定义客户订单表的方式非常相似。 CREATE TABLE supplier_orders_t ( order_id INT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER), order_date DATE NOT NULL, supp_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, status CHAR(9) NOT NULL, PRIMARY KEY (order_date, order_id)) 以下是将行插入 supplier_orders_t 表的 INSERT 语句的示例： INSERT INTO supplier_orders_t VALUES ( DEFAULT, CURRENT DATE, :sid, :pid, :qty, :cost, 'PENDING') 组合表 该公司意识到通过将客户订单表和供应商订单表组合成用于客户和供应商的一个总订单表，可以获得更大的效率和协同。客户订单或供应商订单之间唯一的区别是订单是进来还是出去，这反映在 STATUS 字段中。为了组合这些表，并造成最小中断，他们计划逐渐引入这种更改。他们的计划中的步骤包括： 使订单号同步，这些订单号是为每个表生成的，这样在表之间它们就是唯一的。 等待，直到完成所有非同步订单。（或者，他们可以等到一个季度开始，那时将复位订单号。） 逐步停止使用供应商订单表，使用客户订单表来管理来自客户和供应商的所有订单。 清除。 第 1 步：使订单号同步 要使在这两个表中使用的生成的 order_id 号码同步，则更改这两个表，这样可以由用于标识列的 SEQUENCE 对象提供值，而不总是生成值。由单个序列 orders_seq 为这两个表中的标识列生成值。通过引用 NEXT VALUE 表达式中的 orders_seq ，修改每个表的 INSERT 语句以显式地向标识列提供值。 orders_seq 序列的定义方式如下： CREATE SEQUENCE orders_seq AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER 用 SET GENERATED BY DEFAULT子句将客户订单表和供应商订单表改成允许插入操作显式地向标识列提供值。 ALTER TABLE customer_orders_t ALTER COLUMN order_id SET GENERATED BY DEFAULT ALTER TABLE supplier_orders_t ALTER COLUMN order_id SET GENERATED BY DEFAULT 在将两个订单表的全部 INSERT 语句修改为向 order_id 列提供显式值且 orders_seq 序列从适当的值开始的时候，发出 LOCK TABLE 语句来限制对这两个表的插入操作。 以下是如何更改用于供应商和客户订单表的 INSERT 语句： INSERT INTO customer_orders_t VALUES ( NEXT VALUE FOR orders_seq, CURRENT DATE, :cid, :pid, :qty, :cost, 'PENDING') INSERT INTO supplier_orders_t VALUES ( NEXT VALUE FOR orders_seq, CURRENT DATE, :sid, :pid, :qty, :cost, 'PENDING') 那么以下是如何修改 orders_seq 序列，使它从客户订单和供应商订单表的 order_id 标识列所生成的最大值的后一个值开始。首先，使用 SELECT 语句确定该值： SELECT MAX(c.order_id), MAX(s.order_id) FROM customer_orders_t c, supplier_orders_t s 例如，假设以上查询返回两个值：42331 和 57231。那么可以按以下方式改变 orders_seq 序列： ALTER SEQUENCE orders_seq RESTART WITH 57232 COMMIT 语句释放表上的锁，那么又可以对这两个订单表执行插入操作了。所插入的值是从单个序列 orders_seq 生成的，并不是每个 order_id 列拥有由标识属性各自独立地生成的值，因此这些值在两个表中将是唯一的。 请参阅 图 1以获取 第 1 步的图释。 图 1. order_id 序列值覆盖标识列 第 2 步：等待，直到同步了订单为止 Widget 公司并不想等到季度开始（那时订单被复位）；而是决定监控订单的状态。当以下查询的结果是空表时，他们转到 第 3 步： SELECT order_id FROM ( SELECT order_id FROM customer_orders_t WHERE status <> 'COMPLETED' AND order_id < 57232) AS x UNION ALL ( SELECT order_id FROM supplier_orders_t WHERE status <> 'COMPLETED' AND order_id < 57232) 请参阅 图 2以获取 第 2 步的图释。 图 2. 消除了所有潜在的重复 order_id 号码 第 3 步：逐步停止使用供应商订单 要逐步停止使用 supplier_orders_t 表，通过按以下方式重命名该表，就可以使它暂时不可用： RENAME TABLE supplier_orders_t TO supplier_orders_t_old 然后，会创建一个视图来允许对 supplier_orders_t 的现有引用继续访问底层数据： CREATE VIEW supplier_orders_t (order_id, order_date, supp_id, product_id, quantity, price, status) AS SELECT order_id, order_date, cust_id, product_id, quantity, price, status FROM customer_orders_t 现在传统客户和供应商的所有活动订单都在 customer_orders_t 表中进行管理。要使将来更方便、更直观地维护这些表，还需要在 第 4 步中执行一些清除工作。 请参阅 图 3以获取 第 3 步的图释。 图 3. 所有新订单都进入 customer_orders_t 第 4 步：清除 因为现在必须只为一个列（ order_id ）生成订单号，所以可以由标识列生成值，而不是使用单独的序列对象。同样，表暂时不可用，而且标识值被复位成由序列生成的下一个值。 LOCK TABLE customer_orders_t IN EXCLUSIVE MODE VALUES NEXT VALUE FROM orders_seq INTO :nextorder 例如，假设上述查询返回值：64243。那么，可以按以下方式改变 customer_orders_t 表： ALTER TABLE customer_orders_t ALTER COLUMN order_id SET GENERATED ALWAYS RESTART WITH 64243 每个实例（在这些实例中， INSERT 语句使用 orders_seq 序列）同样需要更改，回到使用 DEFAULT ，如以前在 第 1 步中所显示的。现在可以删除该序列： DROP SEQUENCE orders_seq RESTRICT 在归档了旧的供应商订单表中的数据之后，也可以删除该表。 请参阅 图 4以获取 第 4 步的图释。 图 4. 再次使用标识列生成值，从 64243 开始 调优性能 既然使用 customer_orders_t 表的次数几乎是原来的两倍，Widget 公司决定将该表放在并行环境中。为了利用现在可以执行的并行插入操作，他们决定通过高速缓存值来调优该表的标识列的性能。他们确定了高速缓存大小 50 适合于每小时创建的订单数量以及出于任何原因而重新启动数据库系统的频率。他们还更改了如何计算一个季度中实际创建的订单数，因此没有理由强制按顺序生成序列值。仍然继续要满足在一个季度中要生成唯一值的主要需求，因此做出了以下调整来增强用于标识列的序列生成的性能： ALTER TABLE customer_orders_t ALTER COLUMN order_id SET CACHE 50 SET NO ORDER 回页首 示例 2. 收集卫星读数 如 示例 1中所示，序列允许跨多个表生成唯一值。序列还允许为一个表中的多个列自动生成它们的值。 对于该示例，请考虑一个围绕行星或月亮的轨道卫星。这个特定卫星 SAT1 旨在获取它的轨道上的 16 个不同点的数据读数。收集该数据的表有三个列，在这些列中会自动生成值：一列使用标识属性生成读数标识，另两列则从序列中获取它们的值。请参阅 图 5获取说明。 图 5. 卫星和它的轨道上的 16 个数据读取点 用于我们的示例的 DDL 因为读数的数字可能非常大，标识列使用 DECIMAL(31) 数据类型。 CREATE TABLE SAT1_readings ( reading_id DECIMAL(31) NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE NO CYCLE NO CACHE ORDER), orbit_location SMALLINT NOT NULL, horizon_adjustment SMALLINT NOT NULL, planet_image BLOB(100 M)) orbit_location 有一个值，该值从 0 到 15，代表轨道上获取读数的 16 个点。创建以下序列来生成这 16 个值的循环序列： CREATE SEQUENCE orbit_location_seq AS SMALLINT START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 15 CYCLE NO CACHE ORDER horizon_adjustment 值表示卫星与水平面相关的位置在哪里。0 值表示它在水平面上，值 +4 表示它位于水平面上的最高点，值 -4 表示它位于水平面下的最低点。这些序列从值 -4 开始，这既不是最小值，也不是最大值，因为它将用于方程式之中，以便计算水平调整值。序列对于每个轨道周期性循环。计算 horizon_adjustment 值时将使用以下序列： CREATE SEQUENCE horizon_adjustment_seq AS SMALLINT START WITH -4 INCREMENT BY 1 MINVALUE -7 MAXVALUE 8 CYCLE NO CACHE ORDER 填充读数表 每次产生读数时，以下 INSERT 语句就会填充表： INSERT INTO SAT1_readings VALUES ( DEFAULT, NEXT VALUE FOR orbit_location_seq, ABS(NEXT VALUE FOR horizon_adjustment_seq) - 4, :planet_image) 在前 17 次读取和相应的 INSERT 语句之后，为列 reading_id (r_i)、orbit_location (o_l) 和 horizon_adjustment (h_a) 自动生成的值是： r_i 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. o_l 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 0 h_a 0 -1 -2 -3 -4 -3 -2 -1 0 1 2 3 4 3 2 1 0 捕捉附加读数 因为仪器是足够敏感的，在卫星实际到达轨道之前，可以对行星进行读数。仪器的范围允许在卫星进入其轨道之前，当它接近行星时，可以进行 10 次读数。要捕捉这些在正常轨道圈之外的附加读数，序列值从定义循环极限的 MINVALUE 和 MAXVALUE 值的范围外开始。 以下语句改变标识值和序列值以允许读取轨道上的 16 个点之前先获取附加的 10 个读数。RESTART 选项从指定值重新开始标识列或序列，或者当没有指定值时，在创建标识列或序列时从用 START WITH 值指定的值重新开始。 ALTER TABLE SAT1_readings ALTER COLUMN reading_id RESTART ALTER SEQUENCE orbit_location_seq RESTART WITH -10 ALTER SEQUENCE horizon_adjustment_seq RESTART WITH -14 以下的表显示了：如果在相应的 INSERT 语句之前执行了以上三个 ALTER 语句，那么前 17 个读数将会是什么。这 17 个读数包括在轨道之前获得的 10 个读数和在轨道上获得的前七个读数： r_i 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. o_l -10 -9 -8 -7 -6 -5 -4 -3 -2 -1 0 1 2 3 4 5 6 h_a 10 9 8 7 6 5 4 3 2 1 0 -1 -2 -3 -4 -3 -2 回页首 结束语 DB2 支持两种灵活的自动生成数值的方式：标识列（与一个表紧密相连）和序列对象（独立于任何表引用来生成值）。上面的示例说明了标识列和序列对象用于自动生成数值的功能是多么强大以及有多么灵活。这些示例具体演示了标识列和序列的以下功能： 使用主键中的标识列 在两个表的情况下使用序列对象 为标识列显式地指定一个值，而不是生成值 从预先确定的值开始值的序列 从指定值重新开始标识列值 高速缓存标识值以提高性能 定义一个序列或标识列，以允许值循环 为序列指定一个开始值，该值大于为该序列定义的最小值 为序列指定一个开始值，该值是负值 定义一个序列，以生成先增加再减少的值 确保按生成值的顺序返回这些值 我们希望您发现序列和标识会对您的应用程序有用。