DB2:将现有列转换为标识

Question

我正在尝试将现有列转换为身份。

到目前为止，我可以通过以下方式轻松做到这一点:
alter table list alter column id set generated always as identity (start with 0);
不幸的是，我需要它从表中已有的最大数据数开始。

我已经尝试了以下但它提示子查询。
alter table list alter column id set generated always as identity (start with (select max(id) from list);
单独运行时子查询返回正常。

Answer 1

我在 blog: How to add IDENTITY column to existing table 上找到了一个有趣的解决方案,

添加非空整数列
您需要为其提供默认值，否则 db2 将拒绝将其设为非空alter table public.clicks add column id integer not null default 0

从列中删除默认值
我不确定为什么需要它，因为互联网上的一些手册省略了这一步，但我无法在 DB2 9.7.3 LUW 上使用它alter table public.clicks alter column id drop default

现在将列设置为始终生成alter table public.clicks alter column id set generated always as identity

重组表以使其可写reorg table public.clicks

现在用生成的实体值替换零update public.clicks set id = default

并可选择为表设置 id 列主键alter table public.clicks add constraint pkey primary key(id)

另一种使用存储过程的解决方案

数据迁移到 DB2 时重启 IDENTITY Column

如果使用 IDENTITY 列创建表，并且您需要确保您的标识列从您加载数据的最后一个最大值开始。 IBM Data Movement Tool 会自动执行此操作，但如果您不使用该工具，并且想要一种将标识列的起始值与数据库中的数据同步的方法，则可以使用此存储过程来同步数据。 link

--#SET TERMINATOR @
CREATE PROCEDURE RESETIDENTITYSTARTVALUE
(
    IN schemaname VARCHAR(128), 
    IN tablename VARCHAR(128)
)
BEGIN
   DECLARE sqlcode INTEGER;
   DECLARE maxid BIGINT;
   DECLARE idcolname VARCHAR(128);
   DECLARE stmttxt VARCHAR(1000);
   DECLARE s STATEMENT;
   DECLARE cur CURSOR FOR s;

   SELECT colname INTO idcolname
   FROM SYSCAT.COLUMNS
   WHERE tabname = tablename
   AND tabschema = schemaname
   AND identity = 'Y';

   IF SQLCODE = 100 THEN
      SIGNAL SQLSTATE '78000'
      SET MESSAGE_TEXT = 'can''t find identity column';
   END IF;

   SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
          schemaname || '"."' || tablename || '"';
   PREPARE s FROM stmttxt;
   SET maxid = 0;
   OPEN cur;
      FETCH cur INTO maxid;
   CLOSE cur;
   SET stmttxt = 'ALTER TABLE "' || schemaname || '"."' || tablename ||
           '" ALTER COLUMN "' || idcolname ||
           '" RESTART WITH ' || CHAR(maxid + 1);
   EXECUTE IMMEDIATE stmttxt;
END
@

db2 connect to sample
db2 -tf sp.sql
db2 terminate

DB2 手册:如果要修改标识列

修改标识列定义

如果您正在重新创建一个表，然后进行导入或加载操作，并且如果您在表中有一个 IDENTITY 列，那么它将被重置为在重新创建表的内容后从 1 开始生成 IDENTITY 值。向这个重新创建的表中插入新行时，您不希望 IDENTITY 列再次从 1 开始。

您不希望 IDENTITY 列中有重复的值。为了防止这种情况发生，您应该:

重新创建表。

使用 MODIFIED BY IDENTITYOVERRIDE 子句将数据加载到表中。数据已加载到表中，但不会为行生成标识值。

运行查询以获取 IDENTITY 列的最后一个计数器值:SELECT MAX(<IDENTITY column>) .这将返回与表的 IDENTITY 列值相同的值。

使用 ALTER TABLE 语句的 RESTART 子句:ALTER TABLE <table name> ALTER COLUMN <IDENTITY column> RESTART WITH <last counter value + 1>

在表中插入一个新行。 IDENTITY 列值将根据 RESTART WITH 子句中指定的值生成。

将所有数据输入表后，使用 SET INTEGRITY 语句设置表的完整性。