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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP