A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

New answers tagged


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 ...


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 ID , "TIME" , "P/L" , 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 FROM tbl 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 ...

Top 50 recent answers are included