细数 DB2 数据库的 Identity 字段 | 隔叶黄莺 Yanbin Blog

2022-10-03 | 阅读(565)

每种数据都有自己独特的自增列的声明方式，如 Oracle 的 Sequence, SQL Server 的 Identity, MySQL 的 auto_increment, PostgreSQL 的 Sequence 或 Serial。和 PostgreSQL 类似，DB2 也提供两种自增列的声明方式，它们是 Sequence 和 Identity。而本文主要着墨于 DB2 的 Identity 字段，并讲述它与 Sequence 的某种联系，以及它对数据表的导入的影响。

DB2 的 Sequence

在 DB2 中声明一个 Sequence 与表的 Identity 字段的参数差不多，我们可以看作 Identity 是一个内联的 Sequence。先来看如何创建一个序列

CREATE SEQUENCE ORDER_SEQ
  START WITH 1
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 24;

CREATE SEQUENCE ORDER_SEQ

START WITH 1

INCREMENT BY 1

NO MAXVALUE

NO CYCLE

CACHE 24;

使用的话，比如 insert, update 记录时用它的 NEXT VALUE FOR ORDER_SEQ 值，我们也可以查询到它的下一个值

SELECT NEXT VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY

1	SELECT NEXT VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY

NEXT VALUE 也会让相应序列往下走一值，这和 Oracle 序列的使用方式是一样的。

声明一个序列的参数这里就不细说了，通过不同的参数可以实现升序或降序的，如果指定了 MAXVALUE，NO CYCLE 的话会出现序列耗尽的情况，而 CYCLE 的话会重复使用序列值, CACHE 是在高并发时预先产生序列值。

我们随时可以通过下面的查询获得序列的配置

SELECT * from sysibm.SYSSEQUENCES WHERE SEQNAME='<SEQ_NAME>'

1	SELECT * from sysibm.SYSSEQUENCES WHERE SEQNAME='<SEQ_NAME>'

比如我们以最简单的方式创建一个序列

CREATE SEQUENCE ORDER_SEQ

1	CREATE SEQUENCE ORDER_SEQ

然后用上面的语句查查询得到该序列的参数为

Name           |Value                     |
---------------+--------------------------+
LASTASSIGNEDVAL|                          |
SEQID          |20                        |
SEQNAME        |ORDER_SEQ                 |
INCREMENT      |1                         |
START          |1                         |
MAXVALUE       |2147483647                |
MINVALUE       |1                         |
CYCLE          |N                         |
CACHE          |20                        |
ORDER          |N                         |
ORIGIN         |U                         |
SEQTYPE        |S                         |

Name |Value |

---------------+--------------------------+

LASTASSIGNEDVAL| |

SEQID |20 |

SEQNAME |ORDER_SEQ |

INCREMENT |1 |

START |1 |

MAXVALUE |2147483647 |

MINVALUE |1 |

CYCLE |N |

CACHE |20 |

ORDER |N |

ORIGIN |U |

SEQTYPE |S |

IBM 官方的相关文档 CREATE SEQUENCE statement

Identity 字段

大约是觉得在插入或更新记录时每次用 next value for order_seq 太麻烦，DB2 也可以直接声明一个带有 IDENTITY 关键字的自增字段，这样在插入记录时自动产生序列值。声明 IDENTITY 字段的格式为

column_name data_type
    GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY
    [( identity_option )]

column_name data_type

GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY

[( identity_option )]

这里的 identity_option 和创建 SEQUENCE 时的选项基本一致。

现在来创建一个带有 IDENTITY 列的表

CREATE TABLE ORDER(
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
        START WITH 1
        NO MAXVALUE,
        CACHE 11
    ) PRIMARY KEY,
   
    NAME VARCHAR(64)
)

CREATE TABLE ORDER(

ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (

START WITH 1

NO MAXVALUE,

CACHE 11

) PRIMARY KEY,

NAME VARCHAR(64)

)

插入记录时不用指定 ID 字段

INSERT INTO ORDER(NAME) VALUES('apples')
INSERT INTO ORDER(NAME) VALUES('beef')

1 2	INSERT INTO ORDER(NAME) VALUES('apples') INSERT INTO ORDER(NAME) VALUES('beef')

查看表中的记录

SELECT * FROM ORDE

ID|NAME  |
--+------+
 1|apples|
 2|beef  |

SELECT * FROM ORDE

ID|NAME |

--+------+

1|apples|

2|beef |

自动产生了 ID 值 1, 2, ...

由于我们指定了 GENERATED ALWASY, 所以不能手工指定 ID 值，如果试图执行

INSERT INTO ORDER(ID, NAME) VALUES(3, 'nuts')

1	INSERT INTO ORDER(ID, NAME) VALUES(3, 'nuts')

会得到错误

SQL Error [428C9]: A value cannot be specified for column "ID" which is defined as GENERATED ALWAYS.. SQLCODE=-798, SQLSTATE=428C9, DRIVER=4.26.14

如果改为 GENERATED BY DEFAULT, 则插入记录时可使用指定的 ID 值，未指定时则自动生成。但 GENERATED BY DEFAULT 可能造成主键或唯一值冲突，比如我们把前面的创建表 ORDER 的语句改为

DROP TABLE ORDER;

CREATE TABLE ORDER(
    ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
        START WITH 1
        NO MAXVALUE,
        CACHE 11
    ) PRIMARY KEY,
   
    NAME VARCHAR(64)
)

DROP TABLE ORDER;

CREATE TABLE ORDER(

ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (

START WITH 1

NO MAXVALUE,

CACHE 11

) PRIMARY KEY,

NAME VARCHAR(64)

)

接下来我们将要做的测试就是先插入一个指定 ID 为 3 的记录，然后插入后续的记录时用自动的 ID，那么它会从 3 开始，还是仍然从 1 开始呢？如果从 1 开始的话，碰到 3 时怎么办，由于 ID 是个主键，它会跳过 3 还是发生主键冲突呢？

在测试之前先了解一个函数 IDENTITY_VAL_LOCAL(), 它会得到当前连接会话中上一次插入到 IDENTITY 列上的值，通常下一个值就是它 + 1 的值。依次运行下面的语句，每条语句后显示执行完后表中的记录，以及 IDENTITY_VAL_LOCAL() 值

INSERT INTO ORDER(ID, NAME) VALUES(3, 'order0'); -- id:3; IDENTITY_VAL_LOCAL(): 3
INSERT INTO ORDER(NAME) VALUES('order1');  -- id:3,1; IDENTITY_VAL_LOCAL(): 1 
INSERT INTO ORDER(NAME) VALUES('order2');  -- id:3,1,2; IDENTITY_VAL_LOCAL(): 2
INSERT INTO ORDER(NAME) VALUES('order3');  -- id:3,1,2; IDENTITY_VAL_LOCAL(): 3, 插入失败
INSERT INTO ORDER(NAME) VALUES('order4');  -- id:3,1,2;4, IDENTITY_VAL_LOCAL(): 4

INSERT INTO ORDER(ID, NAME) VALUES(3, 'order0'); -- id:3; IDENTITY_VAL_LOCAL(): 3

INSERT INTO ORDER(NAME) VALUES('order1'); -- id:3,1; IDENTITY_VAL_LOCAL(): 1

INSERT INTO ORDER(NAME) VALUES('order2'); -- id:3,1,2; IDENTITY_VAL_LOCAL(): 2

INSERT INTO ORDER(NAME) VALUES('order3'); -- id:3,1,2; IDENTITY_VAL_LOCAL(): 3, 插入失败

INSERT INTO ORDER(NAME) VALUES('order4'); -- id:3,1,2;4, IDENTITY_VAL_LOCAL(): 4

从测试的结果来看，当 IDENTITY 为 GENERATED BY DEFAULT 时，指定了该列的值就用它，没有指定的仍然从原来的位置开始按部就班的产生 ID，而不管当前表中是否存在该 ID 值，所以在插入 order3 时试图使用 ID:3 时产生主键冲突，错误信息是

SQL Error [23505]: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "USER1.ORDER" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=4.26.14

但是不是很要紧，在插入下一条记录时不会再坚持用 ID:3, 而是跳到了 ID:4。所以一个保险的操作是捕获这里的 unique constraint 异常，不断重试就能找到下一个可用的 ID

IDENTITY 与 SEQUENCE 的关系

延续上一个问题，当设置 GENERATED BY DEFAULT 为什么会产生主键(唯一键)冲突，而冲突完之后就相安无事了呢？这就是本文要从 Sequence 说起，而且 Sequence 和 Identity 的选项又那么相近的原因，说到底，表的 Identity 列本质上就是对应一个 Sequence。

注意到前面我们创建表 ORDER 时的 ID 列 Identity 的 CACHE 是 11, 我们根据这个特征到系统表 sysibm.SYSSEQUENCES 看能不能找到相应的 Sequence

SELECT * from sysibm.SYSSEQUENCES WHERE CACHE = 11

Name           |Value                     |
---------------+--------------------------+
LASTASSIGNEDVAL|11                        |
SEQID          |27                        |
SEQSCHEMA      |USER1                     |
SEQNAME        |SQL220930185534460        |
INCREMENT      |1                         |
START          |1                         |
MAXVALUE       |2147483647                |
MINVALUE       |1                         |
CYCLE          |N                         |
CACHE          |11                        |
ORDER          |N                         |
ORIGIN         |S                         |
SEQTYPE        |I                         |
DATATYPEID     |24                        |

SELECT * from sysibm.SYSSEQUENCES WHERE CACHE = 11

Name |Value |

---------------+--------------------------+

LASTASSIGNEDVAL|11 |

SEQID |27 |

SEQSCHEMA |USER1 |

SEQNAME |SQL220930185534460 |

INCREMENT |1 |

START |1 |

MAXVALUE |2147483647 |

MINVALUE |1 |

CYCLE |N |

CACHE |11 |

ORDER |N |

ORIGIN |S |

SEQTYPE |I |

DATATYPEID |24 |

有了，就是 SQL220930185534460 序列，它是用创建时的时间截来命名的。掌握了 Identity 与 Sequence 的关系后，就能很好的帮助我们理解 DB2 在插入 Identity 列的行为了，比如

INSERT INTO ORDER(NAME) VALUES('order5');

1	INSERT INTO ORDER(NAME) VALUES('order5');

就相当于是下面的操作

INSERT INTO ORDER(ID, NAME) VALUES(NEXT VALUE FOR SQL220930185534460, 'order5');

1	INSERT INTO ORDER(ID, NAME) VALUES(NEXT VALUE FOR SQL220930185534460, 'order5');

注：作为 Identity 列背后的匿名 Sequence, DB2 不会让我们直接用它的 NEXT VALUE FOR 它的
SQL Error [428FB]: Sequence "USER1.SQL220930185534460" cannot be used as specified.. SQLCODE=-20142, SQLSTATE=428FB, DRIVER=4.26.14

所以不管上面的插入语句成功与否，对应的序列总是要往下走一位，直到不再有重复 ID 为止

至于 Identity 的 GENERATED ALWAYS 和 GENERATED BY DEFAULT，只是用来限制插入记录时是否能提供自己的 ID 值，如果自己提供了 ID 值的话，将不会调用 NEXT VALUE FOR SQL220930185534460 产生序列值，所以序列仍保持在原来的位置上，后续插入记录时就可能会产生重复 ID。

调整 IDENTITY

当我们的 Identity 列设置为 GENERATED BY DEFAULT 时，插入记录时自己提供了自己的 ID 值，如何避免重复键冲突呢？比如说创建的 Identity 列从 1 开始，先手工插入了一批 ID 从 1 到 100 的记录，将来再添加记录未提供 ID 时将又会从 1 开始生成 ID, 势必产生重复的 ID。要是该字段设置成了主键或唯一键，插入将会失败，所以我们有一种比较粗鲁的办法就是，不断的尝试，直到找到可接受的 ID 为止。大概的思路如下

def add(name):
    try:
        cursor.execute('insert into order(name) values(%s)', name)
    except UniqueConstraintException:
        add(name)

def add(name):

try:

cursor.execute('insert into order(name) values(%s)', name)

except UniqueConstraintException:

add(name)

更简单的方法就是修改表，设置序列的下一个起始值为大于当前表中的最大 ID 值。我们做如下的操作

INSERT INTO ORDER(ID, NAME) VALUES(100, 'order5');       -- 插入记录 ID: 100
SELECT IDENTITY_VAL_LOCAL() AS id FROM SYSIBM.SYSDUMMY1  -- 100

ALTER TABLE ORDER         
  ALTER COLUMN ID         
  RESTART WITH 101;

SELECT IDENTITY_VAL_LOCAL() AS id FROM SYSIBM.SYSDUMMY1  -- 100
INSERT INTO ORDER(NAME) VALUES('order6');       -- 插入记录 ID: 101
SELECT IDENTITY_VAL_LOCAL() AS id FROM SYSIBM.SYSDUMMY1  -- 101

INSERT INTO ORDER(ID, NAME) VALUES(100, 'order5'); -- 插入记录 ID: 100

SELECT IDENTITY_VAL_LOCAL() AS id FROM SYSIBM.SYSDUMMY1 -- 100

ALTER TABLE ORDER

ALTER COLUMN ID

RESTART WITH 101;

SELECT IDENTITY_VAL_LOCAL() AS id FROM SYSIBM.SYSDUMMY1 -- 100

INSERT INTO ORDER(NAME) VALUES('order6'); -- 插入记录 ID: 101

SELECT IDENTITY_VAL_LOCAL() AS id FROM SYSIBM.SYSDUMMY1 -- 101

当修改表的 ID 列 RESTART WITH 101 时，下次不提供 ID 值插入记录时 ID 就是 101, 后面就是 102, 103, ...

再来看一下 RESTART WITH 101 后序列本身的选项值是否有变

SELECT * from sysibm.SYSSEQUENCES WHERE CACHE = 11;

Name           |Value                     |
---------------+--------------------------+
LASTASSIGNEDVAL|111                       |
SEQID          |27                        |
SEQSCHEMA      |USER1                     |
SEQNAME        |SQL220930185534460        |
INCREMENT      |1                         |
START          |1                         |
MAXVALUE       |2147483647                |

SELECT * from sysibm.SYSSEQUENCES WHERE CACHE = 11;

Name |Value |

---------------+--------------------------+

LASTASSIGNEDVAL|111 |

SEQID |27 |

SEQSCHEMA |USER1 |

SEQNAME |SQL220930185534460 |

INCREMENT |1 |

START |1 |

MAXVALUE |2147483647 |

还是老样子，START 仍然为 1, 我们并不需要修改 START 的值。

或者同时修改为 GENERATED ALWAYS

ALTER TABLE ORDER         
  ALTER COLUMN ID         
  SET GENERATED ALWAYS RESTART WITH 10

ALTER TABLE ORDER

ALTER COLUMN ID

SET GENERATED ALWAYS RESTART WITH 10

我们也可以删除 Identity

ALTER TABLE ORDER
  ALTER COLUMN ID
  DROP IDENTITY;

ALTER TABLE ORDER

ALTER COLUMN ID

DROP IDENTITY;

删除后还想再加回来

 ALTER TABLE ORDER
    ALTER COLUMN ID
     SET GENERATED ALWAYS AS IDENTITY
       (START WITH 500 
       INCREMENT BY 1 
       NOCYCLE)

ALTER TABLE ORDER

ALTER COLUMN ID

SET GENERATED ALWAYS AS IDENTITY

(START WITH 500

INCREMENT BY 1

NOCYCLE)

导入表数据与 Identity 列

作为一个非主流数据库，DB2 是本人迄今碰到的最难管理，维护，导出，导入的数据库。DB2 提供的数据库迁移工具很多，但都无法直接满足我从 Windows 平台下 DB2 导出数据到 Linux 平台下 DB2 的需求。导出，导入数据主要有以下三种方式

db2 backup/restore

db2 backup/restore 可以进行完整数据库的备份和恢复，但不能进行跨平台迁移数据库操作，而且进行 offline, online 的备份恢复操作需要先对数据库进行一些操作。本人尝试过 offline 的 backup/restore，需要先断开数据库的所有连接，停止数据库，启动到受限访问模式，完后重新启动到正常模式。

db2move export/import

db2move 只导出导入表数据，schema 需要用 db2lookup 单独导出，所以导入数据库前要用 db2 -tvf orders_ddl.sql 应用 schema。在导入数据时会受到表外键与 Identity 的限制，有外键时需严格遵循主表从表的顺序导入, 有 Identity 列的话需修改为 GENERATED BY DEFAULT, 完后再 RESTART WITH 新值

db2 export/import/load

它和 db2move export/import 很类似，schema 也需要用 db2lookup 单独处理。导出导入表数据，同样需修改 Identity 列为 GENERATED BY DEFAULT，最后用 RESTART WITH 修改 Identity 列。但对有外键约束的表可强行用 load 导入数据，只是会导致表暂时不可用，最后用 db2 set integrity for ORDER immediate checked 激活表。

修改 Identity 列为 GENERATED ALWAYS 或 GENERATED BY DEFAULT 的语句为

ALTER TABLE ORDER         
  ALTER COLUMN ID         
  SET GENERATED ALWAYS;   -- SET GENERATED BY DEFAULT

ALTER TABLE ORDER

ALTER COLUMN ID

SET GENERATED ALWAYS; -- SET GENERATED BY DEFAULT

或同时用 RESTART WITH 重置起始值

关于 DB2 数据库的迁移，导入/导出的具体操作就不在本文的范畴了。

链接：

本文链接 https://yanbin.blog/talk-db2-database-identity-column/, 来自隔叶黄莺 Yanbin Blog

seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...
Zhe on 体验 Python FastAPI 的并发能力及线, 进程模型你在trio下得用 trio.sleep()，调用asyncio.sleep() 就会报错。