Default Clause

Description

Specifies a default 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

CREATE TABLE T1
(
	COL1 INT,
	COL2 CHAR(8) WITH DEFAULT CURRENT MEMBER,
	COL3 VARCHAR(128) WITH DEFAULT CURRENT SCHEMA,
	COL4 INT DEFAULT NULL,
	COL5 CLOB(1) WITH DEFAULT EMPTY_CLOB(),
	COL6 CLOB(1) WITH DEFAULT EMPTY_DBCLOB(),
	COL7 BLOB WITH DEFAULT EMPTY_BLOB()
);

Snowflake

Code

CREATE TABLE T1
(
	COL1 INT,
	COL2 CHAR(8) DEFAULT /*** MSC-WARNING - MSCEWI5001 - FUNCTIONALITY FOR CURRENT_ROLE MIGHT BE DIFFERENT DEPENDING ON THE DB2 DATABASE. ***/ CURRENT_ROLE(),
	COL3 VARCHAR(128) DEFAULT CURRENT_SCHEMA(),
	COL4 INT DEFAULT NULL,
	COL5 VARCHAR /*** MSC-WARNING - MSCEWI1036 - CLOB DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '',
	COL6 VARCHAR /*** MSC-WARNING - MSCEWI1036 - CLOB DATA TYPE CONVERTED TO VARCHAR ***/ DEFAULT '',
	COL7 BINARY /*** MSC-WARNING - MSCEWI1036 - BLOB DATA TYPE CONVERTED TO BINARY ***/
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--	                                                                                    WITH DEFAULT EMPTY_BLOB()
);

When comparing the result of the transformation, the following points must be noted.

CURRENT MEMBER is transformed to CURRENT_ROLE( ). While this may work for z/Os, in other versions of Db2 it may not work, hence it is transformed and a warning is added.

The DEFAULT EMPTY_BLOB( ) is removed, since the data type BLOB is transformed to BINARY, a BINARY column can not have a default value in it's definition.

Known issues

WITH DEFAULT EMPTY_BLOB( ) is not supported in Snowflake.

Related EWIs

1. MSCEWI1021: THE NODE IS NOT SUPPORTED

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