How to add a not null constraint in an existing column in DB2 iSeries?

Question

I have tried the following scripts to add a not null constraint to my column.

ALTER TABLE MYDB.RULES ALTER TYPEID SET NOT NULL;
ALTER TABLE MYDB.RULES ALTER COLUMN TYPEID SET NOT NULL;

I also get this error when I execute it:

 Reason code 10. [SQL State=57014, DB Errorcode=-952] 

Is it possible to do this in DB2 iSeries?

Answer 1

First you have to identify and handle the NULL values of the target table ( update, delete, etc. )

SELECT COUNT(1) FROM MYDB.RULES WHERE TYPEID IS NULL;

Than you can create the constraint on the target table.

ALTER TABLE MYDB.RULES ALTER COLUMN TYPEID SET NOT NULL;

Answer 2

ALTER TABLE MYDB.RULES ALTER TYPEID SET NOT NULL;

Is correct.

How are you issuing the statement?

The error code you report, SQL State= 57014 - Processing was canceled as requested.

Leads me to believe that however you're running the statement, you're not seeing the CPA32B2 - Change of file RULES may cause data to be lost. (C I) inquiry message; thus it's getting automatically answered with a C-Cancel.

Answer 3

try this, with type of column

ALTER TABLE yourlib/yourtable ALTER COLUMN yourcolumn SET DATA 
TYPE VARCHAR ( 100) NOT NULL                           

Answer 4

If you want a constraint you add a constraint. First you make sure all the rows in the table satisfy the constraint then add the constraint.

update mylib/myfile set myfield = ' ' where myfield is null;
ALTER TABLE mylib/myfile ADD CONSTRAINT myfieldisnull CHECK (myfield is not null );

Answer 5

May be you altered the table before and didn't run the Reorg command. Run this:

CALL SYSPROC.ADMIN_CMD('REORG TABLE MYDB.RULES');