DB2 - Using stored procedure variable in a "CREATE TABLE as (..)" Statement

Question

migrating from Netezza to DB2 we face the following problem:

CREATE OR REPLACE PROCEDURE DoStuff()  
BEGIN  
  DECLARE vStarttime TIMESTAMP;  
  SET vStarttime = now();

CREATE TABLE foo AS (  
    SELECT   
    x,  
    y,  
    vStarttime as t_change  
    FROM  
    bar  
  );  
END

With the basic idea being that t_change should hold the time at which the procedure was called. However, this fails because apparently vStarttime "is not valid in that context". We opted to first create the table, and then call an update to fill in the timestamp.. while this works, however, it feels very hacky, and we would like to avoid the additional update statement for performance reasons. Is there a better solution?

Note: foo is actually a quite complex temporary table. I omitted most of the details to keep it as simple as possible.

mustaccio · Answer

The use of an intermediary variable is not necessary here. Db2 regsitry variables, including CURRENT TIMESTAMP or its standard-compliant variant CURRENT_TIMESTAMP, are set once within the scope of a statement referencing them. This means that in a statement like yours:
SELECT   
    x,  
    y,  
    CURRENT_TIMESTAMP as t_change  
FROM  
    bar

all rows will have the same value of t_change as of approximately the beginning of the statement execution.

Paul Vernon · Answer

I guess you could create a global variable outside of the SP.  E.g. this would work

CREATE VARIABLE gStarttime TIMESTAMP;

CREATE OR REPLACE PROCEDURE DoStuff()  
BEGIN

SET gStarttime = now();

CREATE TABLE foo AS (  
    SELECT   
    x,  
    y,  
    vStarttime as t_change  
    FROM  
    bar  
  ) WITH DATA;

END

BTW If you are migrating existing Netezza stored procedures I trust you have looked at the Database Conversion Workbench

DB2 - Using stored procedure variable in a "CREATE TABLE as (..)" Statement

2 Answers

Add your own answers!

Ask a Question