Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Generated always with identity

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Suja K

New User


Joined: 03 Mar 2008
Posts: 16
Location: Hyderabad

PostPosted: Tue Nov 04, 2008 2:01 pm    Post subject: Generated always with identity
Reply with quote

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
Back to top
View user's profile Send private message

ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Tue Nov 04, 2008 2:19 pm    Post subject:
Reply with quote

I think it is 15, did you do a test? and FYI another option for ID rows as of DB2 V8 is SEQUENCE OBJECT.
Back to top
View user's profile Send private message
Suja K

New User


Joined: 03 Mar 2008
Posts: 16
Location: Hyderabad

PostPosted: Tue Nov 04, 2008 3:01 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 04, 2008 5:01 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 04, 2008 5:04 pm    Post subject:
Reply with quote

Suja,

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

Sushanth Bobby
Back to top
View user's profile Send private message
Suja K

New User


Joined: 03 Mar 2008
Posts: 16
Location: Hyderabad

PostPosted: Wed Nov 05, 2008 11:14 am    Post subject: Reply to: Generated always with identity
Reply with quote

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
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Nov 05, 2008 12:08 pm    Post subject:
Reply with quote

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
Code:
Select nextval for from sysibm.sysdummy1
Likewise you can get the previous values. To get the CACHE values you can get from syscat.sequences.

Also is there any method other than inserting a new row to know the last value inserted value in this case?
Thing is you can't get the CURRENT VALUE. Because if the insertions are high, before you process the current value, lots of inserts would have been finished and the current value which you have got and the present CURRENT VALUE would be different changed.

For More reference & Examples CLICK ME

I hope it HELPS,
Sushanth Bobby
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts XML generated from cobol is displayin... vangeti COBOL Programming 2 Wed Aug 19, 2015 5:16 pm
No new posts Validation of reports generated in As... deb_parthas PL/I & Assembler 3 Wed Jul 30, 2014 3:18 pm
No new posts generated Identity-PK: BIGINT or DECI... Auryn DB2 1 Mon Jan 28, 2013 5:24 pm
No new posts Select IDENTITY column dbzTHEdinosauer DB2 6 Fri Aug 31, 2012 6:26 pm
No new posts SDF generated code- problem with cons... dick scherrer CICS 3 Thu Mar 10, 2011 11:44 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us