Page 1 of 1

GENERATED ALWAYS AS IDENTITY

PostPosted: Sun Dec 26, 2010 6:40 pm
by kk12345
Dear Experts,

I have created a table EMP_IDENTITY as follows:

CREATE TABLE EMP_IDENTITY
(EMPID INTEGER GENERATED ALWAYS AS IDENTITY, NAME CHAR(20), DEPT INTEGER)
DB20000I The SQL command completed successfully.

DB2 INSERT INTO EMP_IDENTITY VALUES (1, 'KK', 20)
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0798N A value cannot be specified for column "EID" which is defined as GENERATED ALWAYS. SQLSTATE=428C9

DB2 INSERT INTO EMP_IDENTITY VALUES (1, 'KKK', 30)
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0117N The number of values assigned is not the same as the number of specified or implied columns or variables. SQLSTATE=42802

How to write the insert statement when GENERATED ALWAYS AS IDENTITY is used in the CRATE statement ???

Re: GENERATED ALWAYS AS IDENTITY

PostPosted: Sun Dec 26, 2010 9:46 pm
by NicC
From the SQL Language Reference, which, of course, you have looked up:

CREATE TABLE EMPLOYEE
          (EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
           NAME CHAR(30),
           SALARY DECIMAL(5,2),
           DEPTNO SMALLINT);

 INSERT INTO EMPLOYEE
          (NAME, SALARY, DEPTNO)
           VALUES (’Rupert’, 989.99, 50);

Re: GENERATED ALWAYS AS IDENTITY

PostPosted: Sun Dec 26, 2010 10:01 pm
by kk12345
Thanks NicC,
I got it.