INCREMENTABLE KEY 2

[Guest_imported]

comment rendre ma clé auto incrementable
lors de la creation de ma table sous DB2?

j'ai essayé:
create table planning.themes (numtheme smallint not null auto_increment,

sans suite !

How to make an incrementable key in DB2 SYSTEM ?

i make this
create table planning.themes (numtheme smallint not null auto_increment,
but i have not succes

 

[dbmsguy]

one way would be to put a (select max(key)+1 from tab) in you insert sql for that value. i do believe I have done it this way.

this is not a great way but will work.
1. you would have to single thread the table.
2. overhead might be a problem
3. would have to be a singleton insert Paul

http://www.dbmsguy.com

 

[gregsimpson]

You could get DB2 to do it for you by using an IDENTITY column.

CREATE TABLE CUSTOMER (
CUSTNO INT GENERATED ALWAYS AS IDENTITY,
NAME CHAR(30));

INSERT INTO CUSTOMER(NAME) VALUES 'CUSTOMER1','CUSTOMER2';

SELECT * FROM CUSTOMER;

CUSTNO NAME
------- ---------------------
1 CUSTOMER1
2 CUSTOMER2

If you wish you can change the columns start value and incremental values


CREATE TABLE CUSTOMER (
CUSTNO INT GENERATED ALWAYS AS IDENTITY
(START WITH 200, INCREMENT BY 20),
NAME CHAR(30));

INSERT INTO CUSTOMER(NAME) VALUES 'CUSTOMER1','CUSTOMER2';

SELECT * FROM CUSTOMER;

CUSTNO NAME
------- ---------------------
200 CUSTOMER1
220 CUSTOMER2


Hope this helps
Greg

 

[stressman]

your solution gregsimpson is wright but if you have to insert the value that is auto increment by the way of a storage procedure it seems not to work in all case more over if the base is partitionned it wont work too to my mind the best way is to use a sequence
A problem with the sequence the right to use it i've got a problem to give the right on my sequence to other users that why i ask the question in a thread but nobody answer

 

[gregsimpson]

Not quite sure what your getting at but have added some further information below.

If you wish to provide the value yourself from a procedure you need to use GENERATED BY DEFAULT instead of GENERATED ALWAYS.

With GENERATED BY DEFAULT DB2 the "user" has the option of supplying a value for the field. If no value is supplied(using the DEFAULT keyword), DB2 will generate the next number in sequence.

Hope this helps

Greg

 

[MarcLodge]

Great answer Greg. Never heard of that function before, and you've clearly and comprehensively laid out your reply. Please accept my start with much thanks.
Marc Lodge

 

[MarcLodge]

Great answer Greg. Never heard of that function before, and you've clearly and comprehensively laid out your reply. Please accept my star with much thanks.
Marc Lodge