| View previous topic :: View next topic |
| Author |
Message |
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
 Posted: Mon Dec 15, 2008 2:19 pm Post subject: ALTER Table ADD Column set to NULL in DB2V8 |
 |
|
Hi all,
Is there any way to alter the table column to make it as NULL/NOT NULL.
I am using DB2 8.1 v
I have tried the following and got an error.
| Code: |
---------+---------+---------+---------+---------+---------+---------+-----
SET CURRENT SQLID = 'T0J0AJ7';
---------+---------+---------+---------+---------+---------+---------+-----
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+-----
ALTER TABLE TEMP5 ALTER COLUMN EAGE
SET NOT NULL;
---------+---------+---------+---------+---------+---------+---------+-----
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "NOT". SOME SYMBOLS THAT MI
BE LEGAL ARE: CYCLE, NOCYCLE, ORDER, NOCACHE, NOORDER, NOMINVALUE,
NOMAXVALUE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5S SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 150 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'00000096' X'00000000' SQL DIAGNOSTIC INFORMATION |
Thanks in advance.
_________________
Satya |
|
| Back to top |
|
 |
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Mon Dec 15, 2008 3:50 pm Post subject: |
|
|
Hi all,
I found one solution, as I am using DB2 V 8.1, it does not support alter columns to set to NULL or NOT NULL.
so I have added a CHECK constraint manually on that column.
Herein is the solution:
| Code: |
ALTER THE TABLE FROM NULL TO NOT NULL
|
AS IN DB2 8.1 WE CANNOT SET A COLUMN FROM NULL TO NOT NULL AND VICE VERSA,
ADD A CHECK CONSTRAINT ON THE TABLE MANUALLY.
| Code: |
ALTER TABLE TEMP5 ADD CONSTRAINT CONST1 CHECK (EAGE IS NOT NULL);
COMMIT;;
|
THEN TRY TO INSERT A ROW INTO THE TABLE WITH THE NULL VALUE:
HEREIN THE TABLE COLUMN EAGE IS DEFINED AS SMALLINT NULL
AND CHANGED TO NOT NULL USING ALTER COLUMN BY DEFINING A CONSTRAINT ON THE COLUMN
---------+---------+---------+---------+---------+---------+---------+---------
--SET CURRENT SQLID = 'T0J0AJ7';
| Code: |
INSERT INTO T0J0AJ7.TEMP5(ENAME)
VALUES('RAJU');
---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -545, ERROR: THE REQUESTED OPERATION IS NOT ALLOWED
BECAUSE A ROW DOES NOT SATISFY THE CHECK CONSTRAINT CONST1
DSNT418I SQLSTATE = 23513 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRINS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -110 13172815 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF92' X'00C9004F' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------
|
WHEN WE TRY TO INSERT THE ROW WITH NULL VALUE OF EAGE IT THROWS THE
ABOVE ERROR.
Please correct me if I am wrong.
Thanks in advance to all.
_________________
Satya |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Mon Dec 15, 2008 4:07 pm Post subject: |
|
|
satyenderd,
DB2 V8 does NOT support altering of null column to a not null column , so you would have to drop and re-create the table. You can do in DB2 V9 on wards.
Kolusu |
|
| Back to top |
|
|
satyenderd
Beginner
Joined: 26 Aug 2005
Posts: 144
Topics: 73
|
| Posted: Tue Dec 23, 2008 11:49 pm Post subject: |
|
|
Thanks Kolusu.
_________________
Satya |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
