Generated always with identity

Suja K · New User Joined: 03 Mar 2008 Posts: 16 Location: Hyderabad

Hi,

I have a doubt regarding the column specification GENERATED ALWAYS WITH IDENTITY.

As per my knowledge, we use GENERATED ALWAYS WITH IDENTITY column specification in CREATE TABLE sql to auto increment the values in the column by a specific number(useful in declaring it as primary key). My doubt is when we use this column specification, and if we delete the last inserted row, will it increment the last inserted field value or the largest value that is present in the table?

For example, in a table STAFF as given below where ID is an auto incremented field(using GENERATED ALWAYS WITH IDENTITY column specification)

ID Name
-- -------
12 John
13 Annie
14 Ralph

If I delete the last row, that is ID=14, and when i insert next time, what will be the value for the field ID in the newly inserted row?

Will it be 14 or 15? Could you please explain it to me?
-------------------
Thanks in advance
Suja

ruodeer · New User Joined: 06 Jul 2007 Posts: 58 Location: home

I think it is 15, did you do a test? and FYI another option for ID rows as of DB2 V8 is SEQUENCE OBJECT.

Suja K · New User Joined: 03 Mar 2008 Posts: 16 Location: Hyderabad

For my requirement the column specification will be better than the using a sequence. Will it store the last assigned value somewhere? Could you please make it much more clear?

--------------
Thanks in advance
Suja

sushanth bobby · Posted: Tue Nov 04, 2008 5:01 pm

Suja,

The answer will be 15.
For automatic numbers there are,
IDENTITY(which you are using by way),
SEQUENCE,
ROW ID(which is sort of RECORD NUMBER or ROW NUMBER).

When using identity columns to know the previous or the next values. There are sequence expressions available like PREVVAL & NEXTVAL. You can use them to know the values

To know more : http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/xf7c63.htm

Sushanth Bobby

sushanth bobby · Posted: Tue Nov 04, 2008 5:04 pm

Suja,

If you want 14. use might need BEFORE INSERT TRIGGER.
I hope iam right. Please correct me, if iam wrong.

Sushanth Bobby

Suja K · New User Joined: 03 Mar 2008 Posts: 16 Location: Hyderabad

Hi Sushanth,

Could you please explain where the last generated value of the column will be stored if it is 15. I want to specify that i am using GENERATED ALWAYS WITH IDENTITY column specification, not sequence. Also is there any method other than inserting a new row to know the last value inserted value in this case?

-----------------------
Thanks in advance
Suja

sushanth bobby · Posted: Wed Nov 05, 2008 12:08 pm

Hi Suja,

Could you please explain where the last generated value of the column will be stored if it is 15.
You can check the previous value or next values which will be stored in SYSDUMMY. Below query is to get the NEXTVALUE