不允许设置db2主键问题的解决
设置db2主键问题出错的话,应该如何处理呢?下面就教您一个不允许设置db2主键问题的解决方法,希望对您学习设置db2主键方面有所帮助。
将empno设置为主键
db2 => alter table emp add primary key (empno)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0542N "EMPNO" cannot be a column of a primary key or unique key because it
can contain null values. SQLSTATE=42831
fenced 出现问题的原因是,因为empno列在创建的时候,默认支持值为空。而在DB2中,针对这种情况,是不允许创建主键的。这点也与oracle和mysql不同。
fenced 因此,我们要添加主键,需要先修改empno,添加上约束,让此列为非空:
db2 => alter table emp alter column empno set not null
将列修改了之后,尝试添加主键,再次出错:
db2 => alter table emp add primary key (empno)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0668N Operation not allowed for reason code "7" on table "DB2INST1.EMP".
SQLSTATE=57016
解决办法:
查看错误信息代码SQL0668N,去查看具体的code“7”的含义。
db2 => ? SQL0668N
SQL0668N Operation not allowed for reason code "<reason-code>" on table
"<table-name>".
Explanation:
Access to table "<table-name>" is restricted. The cause is based on the
following reason codes "<reason-code>":
1fenced
fenced The table is in the Set Integrity Pending No Access state.
。。。。。内容省略。。。。。。。。
7fencedThe table is in the reorg pending state. This can occur after
fenced an ALTER TABLE statement containing a REORG-recommended
fenced operation.
User response:
1fencedExecute the SET INTEGRITY statement with the IMMEDIATE CHECKED
fenced option on table "<table-name>" to bring the table out of the
。。。。。内容省略。。。。。。。。
7fencedReorganize the table using the REORG TABLE command.
fenced For a table in the reorg pending state, note that the following
fenced clauses are not allowed when reorganizing the table:
fenced
fenced * The INPLACE REORG TABLE clause
fenced * The ON DATA PARTITION clause for a partitioned table when
fenced table has nonpartitioned indexes defined on the table
通过查看上述描述中的红色部分的文字,emp表的empno列是添加了not null约束而导致表出于了“reorg pending state”这种状态。我们可以通过以下的方法来验证:
db2 => load query table emp
Tablestate:
Reorg Pending
解决办法,参照6.1中的“User response”描述,使用REORG TABLE命令:
db2 => reorg table emp
DB20000I The REORG command completed successfully.
修改完成后,再次添加主键:
db2 => alter table emp add primary key(empno)
DB20000I The SQL command completed successfully.
查看下表的状态,若为normal则能够正常得进行操作:
db2 => load query table emp
Tablestate:
Normal
总结:对表添加主键时出现的错误,是由于之前对表的列进行了修改,添加了非空约束,导致整个表出于“Reorg Pending”状态。
针对表出现这种状况时,使用reorg table命令,将表进行reorganize即可。
【编辑推荐】
相关文章
- 【技术种草】cdn+轻量服务器+hugo=让博客“云原生”一下
- CLB运维&运营最佳实践 ---访问日志大洞察
- vnc方式登陆服务器
- 轻松学排序算法:眼睛直观感受几种常用排序算法
- 十二个经典的大数据项目
- 为什么使用 CDN 内容分发网络?
- 大数据——大数据默认端口号列表
- Weld 1.1.5.Final,JSR-299 的框架
- JavaFX 2012:彻底开源
- 提升as3程序性能的十大要点
- 通过凸面几何学进行独立于边际的在线多类学习
- 利用行动影响的规律性和部分已知的模型进行离线强化学习
- ModelLight:基于模型的交通信号控制的元强化学习
- 浅谈Visual Source Safe项目分支
- 基于先验知识的递归卡尔曼滤波的代理人联合状态和输入估计
- 结合网络结构和非线性恢复来提高声誉评估的性能
- 最佳实践丨云开发CloudBase多环境管理实践
- TimeVAE:用于生成多变量时间序列的变异自动编码器
- 具有线性阈值激活的神经网络:结构和算法
- 内网渗透之横向移动 -- 从域外向域内进行密码喷洒攻击