I found following descriptions on "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 2".
Quote:
default-clause
Specifies a default value for the column.
.....
.....
datetime-special-register
Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP)
at the time of INSERT, UPDATE, or LOAD as the default for the column.
The data type of the column must be the data type that corresponds to the special register specified
(for example, data type must be DATE when CURRENT DATE is specified).
.....
|
and
Quote:
The generation-expression cannot contain any of the following (SQLSTATE 42621):
v Subqueries
v XMLQUERY or XMLEXISTS expressions
v Column functions
v Dereference operations or DEREF functions
v User-defined or built-in functions that are non-deterministic
v User-defined functions using the EXTERNAL ACTION option
v User-defined functions that are not defined with NO SQL
v Host variables or parameter markers
v Special registers and built-in functions that depend on the value of a special register
v Global variables
v References to columns defined later in the column list
v References to other generated columns
v References to columns of type XMLThe
|
So, the following examples are valid:
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE IBM_TABLE (
BATCH varchar(20) ,
EXPIRY timestamp ,
TIMESTAMP varchar(30) ,
PI_AGAINST_ALLITEMS integer ,
V_FLAG integer default 0
,TSID timestamp DEFAULT sysdate
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
or
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE IBM_TABLE (
BATCH varchar(20) ,
EXPIRY timestamp ,
TIMESTAMP varchar(30) ,
PI_AGAINST_ALLITEMS integer ,
V_FLAG integer default 0
,gen_current_ts timestamp DEFAULT sysdate
,TSID bigint GENERATED ALWAYS AS ( BIGINT(gen_current_ts) )
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
But, the following examples are not valid:
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE IBM_TABLE2 (
BATCH varchar(20) ,
EXPIRY timestamp ,
TIMESTAMP varchar(30) ,
PI_AGAINST_ALLITEMS integer ,
V_FLAG integer default 0
,TSID bigint DEFAULT BIGINT(sysdate)
);
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0574N DEFAULT value or IDENTITY attribute value is not valid for column
"TSID" in table "DB2ADMIN.IBM_TABLE2". Reason code: "7". SQLSTATE=42894
or
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE IBM_TABLE (
BATCH varchar(20) ,
EXPIRY timestamp ,
TIMESTAMP varchar(30) ,
PI_AGAINST_ALLITEMS integer ,
V_FLAG integer default 0
,TSID bigint GENERATED ALWAYS AS ( BIGINT(sysdate) )
);
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0548N A check constraint or generated column that is defined with
"sysdate" is invalid. SQLSTATE=42621
I couldn't find any rational reason.
dBforums
>
How to use Conversion and cast functions while creating table in DB2 9.7
07-28-09, 04:14
Linear Mode
