IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 4 replies
  • Latest Post - ‏2013-06-13T06:48:54Z by androcious
SystemAdmin
SystemAdmin
3105 Posts

Pinned topic Use of DEFAULT CURRENT TIMESTAMP in Column Definition

‏2007-05-02T02:03:26Z |
I am trying to create a table under DB2 8 on z/OS. The table definition
is as follows:

CREATE TABLE. sqlid.MYTABLE (

COL1 CHAR(5) NOT NULL,
COL2 INTEGER NOT NULL,
COL3 VARCHAR(50) ,
COL4 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP )
IN dbname.sqlid CCSID EBCDIC;

When I run this thru SPUFI, I receive the following error message:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "DEFAULT". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: CYCLE, NOCYCLE, ORDER, NOCACHE, NOORDER,
NOMINVALUE, NOMAXVALUE
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5S SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 300 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'0000012C' X'00000000' SQL DIAGNOSTIC INFORMATION

I have checked the manuals and cannot find anything wrong with this SQL.
I even tried using an ALTER TABLE tbname ALTER COLUMN after the table
was created, but DB2 did not like the DEFAULT statement in that
statement either.

Any ideas?

- Alan
Updated on 2007-05-03T13:13:21Z at 2007-05-03T13:13:21Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

    ‏2007-05-02T18:18:58Z  
    Unfortunately, DB2 for z/OS doesn't support DEFAULT datetime-special-register.
    It supports only constant | USER | CURRENT SQLID | NULL for DEFAULT.
    DB2 for LUW supports datetime-special-register for DEFAULT.
    What manual did you referenced?
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

    ‏2007-05-03T13:06:36Z  
    Try this:

    CREATE TABLE. sqlid.MYTABLE (

    COL1 CHAR(5) NOT NULL,
    COL2 INTEGER NOT NULL,
    COL3 VARCHAR(50) ,
    COL4 TIMESTAMP NOT NULL WITH DEFAULT )
    IN dbname.sqlid CCSID EBCDIC;

    /Helena
  • SystemAdmin
    SystemAdmin
    3105 Posts

    Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

    ‏2007-05-03T13:13:21Z  
    Try this:

    CREATE TABLE. sqlid.MYTABLE (

    COL1 CHAR(5) NOT NULL,
    COL2 INTEGER NOT NULL,
    COL3 VARCHAR(50) ,
    COL4 TIMESTAMP NOT NULL WITH DEFAULT )
    IN dbname.sqlid CCSID EBCDIC;

    /Helena
    helena.ljusberg@wmdata.com wrote:
    > Try this:
    >
    > CREATE TABLE. sqlid.MYTABLE (
    >
    > COL1 CHAR(5) NOT NULL,
    > COL2 INTEGER NOT NULL,
    > COL3 VARCHAR(50) ,
    > COL4 TIMESTAMP NOT NULL WITH DEFAULT )
    > IN dbname.sqlid CCSID EBCDIC;
    >
    > /Helena

    Thanks Helena. That did the trick.

    - Alan
  • androcious
    androcious
    1 Post

    Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

    ‏2013-06-13T06:48:54Z  
    helena.ljusberg@wmdata.com wrote:
    > Try this:
    >
    > CREATE TABLE. sqlid.MYTABLE (
    >
    > COL1 CHAR(5) NOT NULL,
    > COL2 INTEGER NOT NULL,
    > COL3 VARCHAR(50) ,
    > COL4 TIMESTAMP NOT NULL WITH DEFAULT )
    > IN dbname.sqlid CCSID EBCDIC;
    >
    > /Helena

    Thanks Helena. That did the trick.

    - Alan

    Thanks Helena, that did work perfect!