CODEWITHSUNDEEP
sqldb2db2-400iseries-navigator
Loren
Loren

Reputation: 1310

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

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?

Upvotes: 2

Views: 25222

Answers (5)

Pekmezli Dürüm
Pekmezli Dürüm

Reputation: 183

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

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

Upvotes: 0

Zoli
Zoli

Reputation: 61

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;

Upvotes: 4

danny117
danny117

Reputation: 5651

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 );

Upvotes: 0

Charles
Charles

Reputation: 23793

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.

Upvotes: 1

Esperento57
Esperento57

Reputation: 17472

try this, with type of column

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

Upvotes: 0

Related Questions