IBM Mainframe Forum

[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 ???

[NicC]

From the SQL Language Reference, which, of course, you have looked up:

Select all

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);

[kk12345]

Thanks NicC,
I got it.