Topic
4 replies Latest Post - ‏2013-06-13T06:48:54Z by androcious
SystemAdmin
SystemAdmin
3105 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

    ‏2007-05-02T18:18:58Z  in response to SystemAdmin
    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
    ACCEPTED ANSWER

    Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

    ‏2007-05-03T13:06:36Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

      ‏2007-05-03T13:13:21Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Use of DEFAULT CURRENT TIMESTAMP in Column Definition

        ‏2013-06-13T06:48:54Z  in response to SystemAdmin

        Thanks Helena, that did work perfect!