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.
SET vStarttime = now()and the start of
CREATE TABLE? I'd expect not, in which case you can simply reference
current timestampin your select statement; it will use the same value for all rows.