Reset table autoincrement column

关键字: 工具技师 DB2

创建自动增长列

create table table_name (
  field_name int not null generated by default as identity
)

查看自动增长列当前值

select * from sysibm.syssequences where seqid = (
  select seqid from syscat.colidentattributes where tabname = 'XXX'
)

重置自动增长列开始值

alter table XXX alter XXX_id restart with 123;

创建自动增长数列

有时候需要给查询的语句标记上递增的序号。例如第一行标记为1、第二行标记为2……

该方法性能略微优于子查询,但其实一样的差,即不建议使用!

create sequence order_seq start with 100 increment by 1 nomaxvalue nocycle cache 24;
insert into joe_test (
  id,
  name
)
select
  nextval for order_seq as id,
  name
from
  name_temp;
drop sequence order_seq;
redraiment一些DB2的使用技巧