DB2关于标识列(自增列)的对比试验、使用示例
试验环境：
建表t1、t2分别含有不同类型的标识列id，对比不同点
下面分别对这两种类型的标识列做试验，对于generated by default
再来看看generated always
小结：
自增列有两种类型：generated by default、generated always
相同点：1、即使插入语句失败，标识列仍然自增
2、都可以重置起始值，语句：alter table [table_name] alter [col_name] restart with [x]
不同点：generated by default：可以修改,手工指定标识列的值。
generated always ：不可修改，只能由系统生成。
注意事项：
1、因上述标识列的特性在数据迁移时要特别小心处理！
要仔细分析导入数据与目标库表里的标识列的当前值，以免主键冲突；
如果改变导入数据的标识列，更要考虑导入表子表外键同步更新，所以要研究透彻再动手。
generated by default ... ,该约束性弱，允许重置起始值，在导入数据时注意重新设置目标表的自增列的起始值，
如自增列不连续，就麻烦了，要分段设置导数据，恶梦...
generated always ...约束性强，其值只允许系统根据其定义自动生成，在导入数据时，可以根据实际情况重置起始值后，
再指定"忽略"选项，即modified by IDENTITYIGNORE。
补充：
identityignore 选项还是由系统根据标识列的定义自动生成。应该用LOAD程序的identityoverride选项来导入文件里的内容，
而不是有系统生成这些值，主要是外键关系的影响。
2、在新项目的数据库设计过程，强烈建议用sequence代替标识列！
建表t1、t2分别含有不同类型的标识列id，对比不同点
D:/>db2 connect to test user cwgladm using cwglpass
数据库连接信息
数据库服务器 = DB2/NT 8.2.9
SQL 授权标识 = CWGLADM
本地数据库别名 = TEST
D:/>db2 select char(TABSCHEMA,20),char(TABNAME,30),char(COLNAME,20),GENERATED from syscat.columns where IDENTITY='Y'
1 2 3 GENERATED
-------------------- ------------------------------ -------------------- ---------
0 条记录已选择。
D:/>db2 create table t1(id bigint not null generated by default as identity ^
More? (start with 1,increment by 1) primary key, ^
More? name varchar(10))
DB20000I SQL 命令成功完成。
D:/>db2 create table t2(id bigint not null generated always as identity ^
More? (start with 1,increment by 1) primary key, ^
More? name varchar(10))
DB20000I SQL 命令成功完成。
D:/>db2 select char(TABSCHEMA,20),char(TABNAME,30),char(COLNAME,20),GENERATED from syscat.columns where IDENTITY='Y'
1 2 3 GENERATED
-------------------- ------------------------------ -------------------- ---------
CWGLADM T1 ID D
CWGLADM T2 ID A
2 条记录已选择。
下面分别对这两种类型的标识列做试验，对于generated by default
D:/>
D:/>db2 insert into t1 (name) values('id1'),('id2') -- 可以自动生成标识列的值 注意此时标识列的下一个值为3
DB20000I SQL 命令成功完成。
D:/>db2 select * from t1
ID NAME
-------------------- ----------
1 id1
2 id2
2 条记录已选择。
D:/>db2 insert into t1 values(4,'id4'),(5,'id5') -- 也可以手工输入标识列的值
DB20000I SQL 命令成功完成。
D:/>db2 select * from t1
ID NAME
-------------------- ----------
1 id1
2 id2
4 id4
5 id5
4 条记录已选择。
D:/>db2 insert into t1 (name) values('id3') -- 再次使用标识列自动生成值(id=3)，此时id将由3自动增长为4
DB20000I SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('test') -- 再次使用标识列自动生成值时报错，因为主键已经有 id=4 的值了。
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0803N INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效，因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T1"的那些列限制为不能具有重复行。 SQLSTATE=23505
D:/>db2 insert into t1 (name) values('test') -- 再次使用标识列自动生成值时报错，因为主键已经有 id=5 的值了。
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0803N INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效，因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T1"的那些列限制为不能具有重复行。 SQLSTATE=23505
D:/>db2 insert into t1 (name) values('test') -- 再次使用标识列自动生成值时成功，说明即使插入语句失败序列也会自增。
DB20000I SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('ttt')
DB20000I SQL 命令成功完成。
D:/>db2 select * from t1
ID NAME
-------------------- ----------
1 id1
2 id2
4 id4
5 id5
3 id3
6 test
7 ttt
7 条记录已选择。
D:/>db2 insert into t1 values(9,'id9'),(10,'id10')
DB20000I SQL 命令成功完成。
D:/>db2 select * from t1
ID NAME
-------------------- ----------
1 id1
2 id2
4 id4
5 id5
3 id3
6 test
7 ttt
9 id9
10 id10
9 条记录已选择。
D:/>db2 insert into t1 (name) values('id8')
DB20000I SQL 命令成功完成。
D:/>db2 alter table t1 alter id restart with 11 --可以手工重置标识列的起始值，以免自增的值与已插入的主键(id=9,10)冲突
DB20000I SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('id11')
DB20000I SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('id12')
DB20000I SQL 命令成功完成。
D:/>db2 select * from t1
ID NAME
-------------------- ----------
1 id1
2 id2
4 id4
5 id5
3 id3
6 test
7 ttt
9 id9
10 id10
8 id8
11 id11
12 id12
12 条记录已选择。
D:/>
D:/>db2 update t1 set id=15 where id=12 -- 再次说明这种类型的标识列的值是可以修改的
DB20000I SQL 命令成功完成。
再来看看generated always
D:/>
D:/>db2 insert into t2 (name) values('id1'),('id2')
DB20000I SQL 命令成功完成。
D:/>db2 select * from t2
ID NAME
-------------------- ----------
1 id1
2 id2
2 条记录已选择。
D:/>db2 insert into t2 values(4,'id4'),(5,'id5') -- 这种类型的标识列不能指定值，只能由系统生成。
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0798N 不能对定义为 GENERATED ALWAYS 的列 "ID" 指定值。 SQLSTATE=428C9
D:/>db2 alter table t2 alter id restart with 5 -- 可以重置起始值
DB20000I SQL 命令成功完成。
D:/>db2 insert into t2(name) values('id5'),('id6')
DB20000I SQL 命令成功完成。
D:/>db2 select * from t2
ID NAME
-------------------- ----------
1 id1
2 id2
5 id5
6 id6
4 条记录已选择。
D:/>db2 alter table t2 alter id restart with 3
DB20000I SQL 命令成功完成。
D:/>db2 insert into t2(name) values('id3'),('id4')
DB20000I SQL 命令成功完成。
D:/>db2 insert into t2(name) values('id5') -- 自增值与已有主键值冲突
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0803N INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效，因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T2"的那些列限制为不能具有重复行。 SQLSTATE=23505
D:/>db2 select * from t2
ID NAME
-------------------- ----------
1 id1
2 id2
5 id5
6 id6
3 id3
4 id4
6 条记录已选择。
D:/>db2 insert into t2(name) values('test')
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0803N INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效，因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T2"的那些列限制为不能具有重复行。 SQLSTATE=23505
D:/>db2 insert into t2(name) values('test') -- 说明即使插入语句失败，标识列仍然自增
DB20000I SQL 命令成功完成。
D:/>db2 select * from t2
ID NAME
-------------------- ----------
1 id1
2 id2
5 id5
6 id6
3 id3
4 id4
7 test
7 条记录已选择。
D:/>db2 update t2 set id=9 where id=7 -- 再次确认这种类型标识列的值不可修改，只能有系统生成。
DB21034E 该命令被当作 SQL
语句来处理，因为它不是有效的“命令行处理器”命令。在 SQL 处理期间，它返回：
SQL0798N 不能对定义为 GENERATED ALWAYS 的列 "ID" 指定值。 SQLSTATE=428C9
D:/>
小结：
自增列有两种类型：generated by default、generated always
相同点：1、即使插入语句失败，标识列仍然自增
2、都可以重置起始值，语句：alter table [table_name] alter [col_name] restart with [x]
不同点：generated by default：可以修改,手工指定标识列的值。
generated always ：不可修改，只能由系统生成。
注意事项：
1、因上述标识列的特性在数据迁移时要特别小心处理！
要仔细分析导入数据与目标库表里的标识列的当前值，以免主键冲突；
如果改变导入数据的标识列，更要考虑导入表子表外键同步更新，所以要研究透彻再动手。
generated by default ... ,该约束性弱，允许重置起始值，在导入数据时注意重新设置目标表的自增列的起始值，
如自增列不连续，就麻烦了，要分段设置导数据，恶梦...
generated always ...约束性强，其值只允许系统根据其定义自动生成，在导入数据时，可以根据实际情况重置起始值后，
再指定"忽略"选项，即modified by IDENTITYIGNORE。
补充：
identityignore 选项还是由系统根据标识列的定义自动生成。应该用LOAD程序的identityoverride选项来导入文件里的内容，
而不是有系统生成这些值，主要是外键关系的影响。
2、在新项目的数据库设计过程，强烈建议用sequence代替标识列！
