Re:Hibernate problem: de MySQL para DB2

An identity column is a numeric column in a table that automatically generates a unique numeric value in sequence for each row inserted. A unique identifier is often used in applications to identify a specific row. Unlike sequence objects, which we discuss in section 7.16, Sequences, identity columns are bound to the table they are defined on. There can be only one identity column per table. DB2 can generate the identity column values in two ways.

Generated always: The values are always generated by DB2. Applications are not allowed to provide an explicit value.

Generated by default: The values can be explicitly provided by an application; if no value is given, DB2 generates one. In this case, however, DB2 cannot guarantee the uniqueness of the value generated.

To create an identity column, use the CREATE TABLE statement with the GENERATED clause and make sure it contains the IDENTITY keyword because GENERATED can also be used to generate other values automatically that are not identity columns. Here is an example.

CREATE TABLE product (
       productno   INTEGER GENERATED ALWAYS AS
                           IDENTITY (START WITH 200 INCREMENT BY 1),
       description VARCHAR(50) )



The column productno is an INTEGER defined as an identity column that is always generated. The value generated will start from 200, and it will be incremented by 1. Let's perform a few INSERT statements and see the results obtained.


INSERT INTO product VALUES (DEFAULT,'banana');        --->inserts 200,banana
INSERT INTO product (description) VALUES ('apple');   --->inserts 201,apple
INSERT INTO product VALUES (300,'pear');              --->error SQL0798N
COMMIT;

INSERT INTO product (description) VALUES ('orange');  --->inserts 202,orange
ROLLBACK;

INSERT INTO product (description) VALUES ('plum');    --->inserts 203,plum
COMMIT;


The following query shows the final result.

SELECT * FROM product;

PRODUCTNO   DESCRIPTION
----------- ------------
        200 banana
        201 apple
        203 plum