If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Autoincrement fields

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 21
Autoincrement fields

Hi all!!
Is there another way of making a field autoincrement, instead of "genearted always as identity...." in db2 v7.2????
Because when using jdbc and i try to get the isAutoIncrement from the resultset metadata it doesn't work....

Thank you very much!!!

Cheers
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Rio de Janeiro - Brazil
Posts: 78
Re: Autoincrement fields

Hey Garcia,

If you want to use an identity field, try "Generated by Default" instead of "Generated Always".

Take a look at the Application Development Guide for more details on Generated Columns.

HTH
Fernando

Quote:
Originally posted by rgarcia
Hi all!!
Is there another way of making a field autoincrement, instead of "genearted always as identity...." in db2 v7.2????
Because when using jdbc and i try to get the isAutoIncrement from the resultset metadata it doesn't work....

Thank you very much!!!

Cheers
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 21
Hi Fernando!
So how would be the whole syntax??
instead of:
"CODE INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"

it should be:
"BOARDTYPECODE INTEGER NOT NULL GENERATED DEFAULT (START WITH 1, INCREMENT BY 1)"

Thank you again!!!
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Location: Rio de Janeiro - Brazil
Posts: 78
Garcia,

The whole syntax should be:
"BOARDTYPECODE INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)"

But note: the main diference between "GENERATED ALWAYS" and "GENERATED BY DEFAULT" is the behaviour when the value for the identity field is provided.
When you use the 'ALWAYS' type, you canīt provide the value for the Identity field, for it is generated ALWAYS using the auto-increment.
When using the 'BY DEFAULT' the auto-increment will only be used when you donīt provide the value for the identity field. In case you specify the value for the field, the auto-increment value is not used and thus not incremented. It works exactly like a "DEFAULT" value for a column. So you have to be very careful when using this kind of column generation, IMO.

Hope this cleared out your doubts.

Fernando

Quote:
Originally posted by rgarcia
Hi Fernando!
So how would be the whole syntax??
instead of:
"CODE INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"

it should be:
"BOARDTYPECODE INTEGER NOT NULL GENERATED DEFAULT (START WITH 1, INCREMENT BY 1)"

Thank you again!!!
Reply With Quote
  #5 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
You will need to be a bit cautious when using 'GENERATED BY DEFAULT'

When you INSERT explicitly the identity column, the autoincrement field is not incremented(as Fernando has said) ie

Table T1 : I INT GENERATED By DEFAULT AS IDENTITY, J INT

INSERT INTO T1(j) VALUES(5)
INSERT INTO T1(J) VALUES(10)
SELECT * FROM T1

I J
-------
1 5
2 10

INSERT INTO T1 values(3,15)

SELECT * FROM T1

I J
-------
1 5
2 10
3 15

INSERT INTO T1(J) VALUES(20)


SELECT * FROM T1

I J
-------
1 5
2 10
3 15
3 20

Note the last two lines ... As I's value in the third row was inserted by the user, DB2 did not consider it when auto-incrementing ...

You might face problems when using this type of column ...

Cheers

Sathyaram



Quote:
Originally posted by F.OHANA
Garcia,

The whole syntax should be:
"BOARDTYPECODE INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)"

But note: the main diference between "GENERATED ALWAYS" and "GENERATED BY DEFAULT" is the behaviour when the value for the identity field is provided.
When you use the 'ALWAYS' type, you canīt provide the value for the Identity field, for it is generated ALWAYS using the auto-increment.
When using the 'BY DEFAULT' the auto-increment will only be used when you donīt provide the value for the identity field. In case you specify the value for the field, the auto-increment value is not used and thus not incremented. It works exactly like a "DEFAULT" value for a column. So you have to be very careful when using this kind of column generation, IMO.

Hope this cleared out your doubts.

Fernando
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 1
Re: Autoincrement fields

Hi did you figure out the solution for this problem. I am stuck with the same problem.

Thanks in advance.

Quote:
Originally posted by rgarcia
Hi all!!
Is there another way of making a field autoincrement, instead of "genearted always as identity...." in db2 v7.2????
Because when using jdbc and i try to get the isAutoIncrement from the resultset metadata it doesn't work....

Thank you very much!!!

Cheers
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On