TransWikia.com

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

Database Administrators Asked by Alexander Meyer on November 23, 2021

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 Answers

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.

Answered by mustaccio on November 23, 2021

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

Answered by Paul Vernon on November 23, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP