1

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.

2
  • 1
    Do you expect there to be a noticeable lag between the execution of SET vStarttime = now() and the start of CREATE TABLE? I'd expect not, in which case you can simply reference current timestamp in your select statement; it will use the same value for all rows. – mustaccio Jan 21 '20 at 15:32
  • Not between those two statements. However, the create table might take quite some time. If current timestamp produces the same time for all rows regardless, that would be a perfect solution to that particular problem! However, i still feel like there should be a somewhat easy, intuitive way to access variables in a subquery. We might bump into the same problem in a different context later, after all – Alexander Meyer Jan 21 '20 at 15:36
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.