It is not allowed to set the db2 primary key to solve the problem.

What should I do if an error occurs when setting the db2 primary key? The following describes how to solve the problem of not allowing you to set the db2 primary key. We hope this will help you learn how to set the db2 primary key.

Set empno as the primary key

Db2 => alter table emp add primary key (empno)

DB21034E The command was processed as an SQL statement because it was not

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

The reason for the fenced error is that when the empno column is created, the default supported value is null. In DB2, primary keys cannot be created in this case. This is also different from oracle and mysql.

Therefore, to add a primary key, modify empno and add the upper constraint so that this column is not empty:

Db2 => alter table emp alter column empno set not null

After modifying the column, try to add the primary key. An error occurs again:

Db2 => alter table emp add primary key (empno)

DB21034E The command was processed as an SQL statement because it was not

Valid Command Line Processor command. During SQL processing it returned:

SQL0668N Operation not allowed for reason code "7" on table "DB2INST1. EMP ".

SQLSTATE = 57016

Solution:

Check the error code SQL0668N to view the meaning of the specific 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

Following reason codes "<reason-code> ":

1 fenced

Fenced The table is in the Set Integrity Pending No Access state.

..... Content omitted ........

7 fencedThe table is in the reorg pending state. This can occur after

Fenced an alter table statement containing a REORG-recommended

Fenced operation.

User response:

1 fencedExecute the set integrity statement with the IMMEDIATE CHECKED

Fenced option on table "<table-name>" to bring the table out of

..... Content omitted ........

7 fencedReorganize the table using the reorg table command.

Fenced For a table in the reorg pending state, note that the following

Fenced clses 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

By checking the red text in the preceding description, the empno column of the emp table adds the not null constraint, resulting in the table being in the "reorg pending state" state. We can verify it using the following methods:

Db2 => load query table emp

Tablestate:

Reorg Pending

Solution: Use the reorg table command by referring to the "User response" Description in 6.1:

Db2 => reorg table emp

DB20000I The REORG command completed successfully.

After the modification, add the primary key again:

Db2 => alter table emp add primary key (empno)

DB20000I The SQL command completed successfully.

Check the status of the following table. If it is normal, the operation is normal:

Db2 => load query table emp

Tablestate:

Normal

Summary: an error occurs when a primary key is added to a table because the table columns have been modified and non-empty constraints are added. As a result, the entire table is in the "Reorg Pending" state.

In this case, run the reorg table command to reorganize the table.
 

DB2 temporary table definition method

Install DB2 in linux

Top 10 considerations for DB2 Performance Optimization

Connect SQL SERVER to the DB2 database

DB2 index creation principles