How to declare timestamp variable with default as current timestamp not working

Question

In my stored procedure, I want to set as default timestamp the current time. But I am running in a compilation error, but with additional set, it will work.

DECLARE VSTART TIMESTAMP DEFAULT CURRENT TIMESTAMP;

I got this exception:

  [Code: -104, SQL State: 42601]  An unexpected token "CURRENT TIMESTAMP" was found following "ME TIMESTAMP DEFAULT".  Expected tokens may include:  "<literal>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11

Answer 1

Please tag your questions with the correct Db2 platform tag (db2-zos , db2-400, db2-luw) - sometimes the answer depends on this.

If your Db2-server is Db2-LUW (Linux/Unix/Windows) then you need two separate statements, because the documentation specifies that the variable-declaration syntax allows defaults of constant values, or null. Hence, one statement to declare the variable, another to assign a varying value to that variable.

This is true for both forms of compound sql (compiled and inlined).

The syntax that you show ...TIMESTAMP with DEFAULT CURRENT TIMESTAMP is valid in DDL, but not in compound SQL.