MainframeKit

+162

SQLSTATE 01514POSITIVE SQLCODE (WARNING)

An ALTER TABLE added a constraint and Db2 put the table space into CHECK-pending status until the existing data is checked.

MOST LIKELY

ALTER TABLE ... ADD FOREIGN KEY on a table that already has rows.

FIRST CHECK

Run the CHECK DATA utility on the table space named in the message.

TRY IT ▸

Block size and space calculator

What it means

When you add a referential constraint, or a check constraint while CURRENT RULES is 'DB2', Db2 does not check the rows that are already there. Instead it marks the table space CHECK-pending.

The table space cannot be used normally until the pending state is cleared, so applications may start failing until someone runs the check.

Typical causes

  • ALTER TABLE ... ADD FOREIGN KEY on a table that already has rows.
  • ALTER TABLE ... ADD CHECK with CURRENT RULES = 'DB2'.
  • A schema change applied without a follow-up utility step.

How to fix it

  1. Run the CHECK DATA utility on the table space named in the message.
  2. Fix or remove any rows that CHECK DATA reports as violating the constraint.
  3. Use DISPLAY DATABASE to confirm the CHECK-pending status has gone.
  4. Add the CHECK DATA step to your change procedure for future constraint changes.

Example

Illustrative only; names and values are invented.

ALTER TABLE MYSCHEMA.EMP ADD FOREIGN KEY (DEPTNO) REFERENCES MYSCHEMA.DEPT
SQLCODE = +162: TABLESPACE MYDB.MYTS HAS BEEN PLACED IN CHECK PENDING