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 Option in DB2

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

New User


Joined: 14 Mar 2006
Posts: 2
Location: Hyderabad

PostPosted: Fri May 30, 2008 10:57 am    Post subject: GENERATED Option in DB2
Reply with quote

Can any body explain the difference between 'GENERATED BY DEFAULT AS IDENTITY' and 'GENERATED ALWAYS AS IDENTITY' ?

Let suppose i've a table declaration like

CREATE TABLE t1
(id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
c1 CHAR(10) NOT NULL,
c2 CHAR(10)

INSERT INTO t1 VALUES (1, 'abc', NULL);

Will the insert operation pass, if not why?

Best regards,
Mohan Eluri.
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri May 30, 2008 3:11 pm    Post subject:
Reply with quote

The GENERATED ALWAYS AS IDENTITY clause indicates that DB2 will always generate a unique value for the identity column for each row inserted into the table ....
DB2 will issue an error if a value is specified n an insert operation for the identity column defined using this clause ......

GENERATED BY DEFAULT AS IDENTITY clause will cause DB2 to generate unique values for the identity column during insert operations if no value is specified for the identity column. However, if a value is specified for the identity column, DB2 will use it in the insert operation ....

Now i hope you know what you can expect out of your insert stmt ....
Back to top
View user's profile Send private message
feng hao

New User


Joined: 26 Mar 2008
Posts: 44
Location: China

PostPosted: Wed May 13, 2009 3:33 pm    Post subject:
Reply with quote

Hi, ashimer, I also found the description in IBM references,
Quote:
The GENERATED BY DEFAULT clause is meant for use for data propagation where the intent is to copy the contents of an existing table; or, for the unload and reloading of a table.

I don't know what it means, could you explain it?

The address is here:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0004990.htm
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed May 13, 2009 7:51 pm    Post subject:
Reply with quote

Hello,

GENERATED BY DEFAULT clause is meant for use for data propagation
where:
the intent is to copy the contents of an existing table
or
for the unload and reloading of a table.

If a value is in the data to load - use it, otherwise db2 will generate a value.
Back to top
View user's profile Send private message
feng hao

New User


Joined: 26 Mar 2008
Posts: 44
Location: China

PostPosted: Thu May 21, 2009 6:15 pm    Post subject:
Reply with quote

Thanks very much, Dick!

But there is one thing which is still troubling me.

"generated by default" allows users to assign a number for the column by themselves, meanwhile it only remembers what it has generated rather than the newest number in the column.

So I wonder what is the purpose when we writed "generated by default as identity". Actually what we want is "generated always as identity".
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 Where is output from PARM ABEXIT (SNA... packerm CA Products 3 Thu Sep 28, 2017 4:06 pm
No new posts MQ command in option 6 of ISPF! Vignesh Sid All Other Mainframe Topics 3 Tue Sep 26, 2017 6:01 pm
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm

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