Detail example with explaination for ALTER command

Bharani · New User Joined: 02 Jul 2004 Posts: 4

Hai Friends,

I am having doubt in db2 alter command.

can anyone give detail example with explaination for alter command.

that is alter table, alter column, alter constraints, drop constaints, drop column

priya · Posted: Sat Jul 03, 2004 12:21 am

ALTER command is used to alter the parameters of STORAGE GROUP or TABLE, index and tablespace.

krbabu · New User Joined: 20 Feb 2004 Posts: 57

Hi barani,
Alter command is used to alter the structure of the table, length of the colums and change the constraint.

Bharani · New User Joined: 02 Jul 2004 Posts: 4

Hai Friends

I need example query. please explain with example query for alter

meetsrk · New User Joined: 13 Jun 2004 Posts: 85

hi bharani,

while using alter table in DB2 always keep these things in mind,

u cannot do the following,

#change not null --> null
#reduce the size of the field i.e char(20) to Char(10)
#drop a praticular column

a syntax to add a new column for size 10 fields.

Bharani · New User Joined: 02 Jul 2004 Posts: 4

[quote] Thank U Friend if u r giving more examples means i will be comfortable. then can we create sequence in db2 if we can means what is the syntax to create the sequence.

satya123 · New User Joined: 18 Aug 2006 Posts: 57

u can increase the column size for varchar fields but not decrease

mahsug · Posted: Mon Aug 21, 2006 11:59 am

Hi Bharani ,

Yes, you can create Sequences in Db2 , Below are the informations regarding the same .

To Create a Sequence

You need to have any one of the following :

| The CREATEIN privilege on the schema

| SYSADM or SYSCTRL authority

Command :

CREATE SEQUENCE ORDER_SEQ
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
CACHE 20;

Note :
1) You need to provide the starting value , and incrementing value , say here it is 1 , so every time you say next value , it will be incremented by 1
2) MAXVALUE : MaxVALUE is the maximum value for your sequence , if you say 25 , your sequence will range from 1 - 25
3) CYCLE : When your maxvalue is reached , this is to say whether you want to reuse the same sequence numbers , say if you say MAXVALUE is 10 and CYCLE YES , when your sequence reaches 10 and you call for the next value , the sequence will be resetted to the first value , ie 1 .

Using Sequence values with your DML Operations

INSERT INTO ORDERS (ORDERNO, CUSTNO)
VALUES (NEXT VALUE FOR ORDER_SEQ, 123456);

UPDATE ORDERS
SET ORDERNO = NEXT VALUE FOR ORDER_SEQ
WHERE ORDERNO = 123456;

Hope , I have cleared your doubt .

Bye ,
Mahesh