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

New answers tagged

0

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


3

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") * ...


1

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.


1

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