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()
SET gStarttime = now();
CREATE TABLE foo AS (
vStarttime as t_change
) WITH DATA;
BTW If you are migrating ...
Db2 does not support the INTERVAL keyword on OLAP window-aggregation-group-clause https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html so you need to convert the TIMESTAMPs to MINIUTEs first. E.g
, SUM("P/L") OVER (PARTITION BY ID
ORDER BY (DAYS("TIME") * ...
You would use RANGE, not ROWS, after converting the timestamp to a numeric value:
SELECT ID, tm, P/L,
SUM(P/L) OVER (PARTITION BY ID
ORDER BY extract(epoch from tm)
range BETWEEN 600 PRECEDING AND CURRENT ROW) as ROLLING
This will work with Db2 for LUW 11.1 and later.
Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with ...