Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   name VARCHAR(1) NOT NULL 
  ,date INTEGER  NOT NULL
  ,Flag INTEGER  NOT NULL
);

INSERT INTO mytable(name,date,Flag) VALUES ('x',2014,0);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2015,0);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2016,1);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2017,1);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2018,0);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2019,1);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2020,0);
INSERT INTO mytable(name,date,Flag) VALUES ('x',2021,1);
INSERT INTO mytable(name,date,Flag) VALUES ('y',2014,0);
INSERT INTO mytable(name,date,Flag) VALUES ('y',2016,1);
INSERT INTO mytable(name,date,Flag) VALUES ('y',2017,0);
INSERT INTO mytable(name,date,Flag) VALUES ('y',2018,1);
select
    name,
    date,
    flag,
    case when flag = 1 
        then sum(cycle_start) over(partition by name order by date) 
    end cycle 
from (
    select
        t.*,
        case
            when lag(flag) over(partition by name order by date) = 0 and flag = 1
            then 1
        end cycle_start
    from mytable t
) t
order by name, date


NAME
DATE
FLAG
CYCLE


x
2014
0
null

x
2015
0
null

x
2016
1
1

x
2017
1
1

x
2018
0
null

x
2019
1
2

x
2020
0
null

x
2021
1
3

y
2014
0
null

y
2016
1
1

y
2017
0
null

y
2018
1
2

NAME	DATE	FLAG	CYCLE
x	2014	0	null
x	2015	0	null
x	2016	1	1
x	2017	1	1
x	2018	0	null
x	2019	1	2
x	2020	0	null
x	2021	1	3
y	2014	0	null
y	2016	1	1
y	2017	0	null
y	2018	1	2