Generated Clause

Description

Specifies a generated value for the column.

Click here to navigate to the IBM DB2 docs page for this syntax.

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

CREATE TABLE TableName
(
	Col1 INT,
	Col2 INT GENERATED AS IDENTITY
);

CREATE TABLE TableName2
(
	Col1 INT,
	Col2 INT GENERATED ALWAYS AS IDENTITY 
	(
		START WITH 1,
		INCREMENT BY 1,
		MINVALUE 1,
		MAXVALUE 100,
		CYCLE,
		NO CACHE,
		ORDER
	)
);

Code 2

CREATE TABLE TableName3
(
	Col1 INT,
	Col2 INT GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);

CREATE TABLE TableName4
(
	Col1 INT,
	Col2 INT GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);

CREATE TABLE TableName5
(
	Col1 INT,
	Col2 INT GENERATED ALWAYS AS (1)
);

CREATE TABLE TableName6
(
	Col1 INT,
	Col2 INT GENERATED AS ROW BEGIN
);

CREATE TABLE TableName7
(
	Col1 INT,
	Col2 INT GENERATED AS TRANSACTION START ID
);

Snowflake

Code 1

CREATE OR REPLACE SEQUENCE TableName_Col2
COMMENT = 'FOR TABLE-COLUMN TableName.Col2';

CREATE OR REPLACE TABLE TableName (
Col1 INT,
Col2 INT DEFAULT TableName_Col2.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   ***/
);

CREATE OR REPLACE SEQUENCE TableName2_Col2
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN TableName2.Col2';

CREATE OR REPLACE TABLE TableName2 (
Col1 INT,
Col2 INT DEFAULT TableName2_Col2.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS    START WITH 1,  INCREMENT BY 1,  MINVALUE 1,  MAXVALUE 100,  CYCLE,  NO CACHE,  ORDER ***/
);

Code 2

CREATE TABLE TableName3
(
	Col1 INT,
	Col2 INT
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--	         GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);

CREATE TABLE TableName4
(
	Col1 INT,
	Col2 INT
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--	         GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);

CREATE TABLE TableName5
(
	Col1 INT,
	Col2 INT
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--	         GENERATED ALWAYS AS (1)
);

CREATE TABLE TableName6
(
	Col1 INT,
	Col2 INT
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--	         GENERATED AS ROW BEGIN
);

CREATE TABLE TableName7
(
	Col1 INT,
	Col2 INT
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--	         GENERATED AS TRANSACTION START ID
);

Known issues

As row change timestamp clause, As generated expression clause, As row transaction timestamp clause, and As row transaction start id clause are not supported in Snowflake.

Related EWIs

1. MSCEWI1048: Sequence Warning

2. MSCEWI5002: FUNCTIONALITY MIGHT BE DIFFERENT DEPENDING ON THE DB2 DATABASE.