CODEWITHSUNDEEP
db2
Dev
Dev

Reputation: 13753

How to create ROWID column in DB2?

I tried:

  CREATE TABLE rowid_type
  (
  col1 ROWID NOT NULL GENERATED ALWAYS,
  col2 int
  );

Error:

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=GENERATED ALWAYS;col1 ROWID NOT NULL;, DRIVER=4.14.111

SQLState: 42601

ErrorCode: -104

EDIT

I referred this link- https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/com.ibm.db2z10.doc.apsg/src/tpc/db2z_specifydirectrowaccess.html?view=embed

It shows:

CREATE TABLE EMPLOYEE
  (EMP_ROWID   ROWID NOT NULL GENERATED ALWAYS,
   EMPNO       SMALLINT,
   NAME        CHAR(30),
   SALARY      DECIMAL(7,2),
   WORKDEPT    SMALLINT);

EDIT2

Upvotes: 1

Views: 4990

Answers (1)

data_henrik
data_henrik

Reputation: 17118

[My answer applies to DB2 LUW, the question has been updated and is pointing to DB2 for z/OS now]

Try something like this:

CREATE TABLE rowid_type
(
  col1 int NOT NULL GENERATED ALWAYS AS IDENTITY,
  col2 int
);

There are a couple more options available like setting start and stop values. See the DB2 docs on identity columns for details.

Upvotes: 1

Related Questions