Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE nodes
( node_id int not null primary key
-- , other attributes of a node
);

CREATE TABLE parent_nodes
( node_id int not null primary key
      references nodes (node_id)
, parent_id int not null 
      references nodes (node_id)
);

CREATE TABLE ancestor_nodes
( node_id int not null
     references nodes (node_id)
, ancestor_id int not null
     references nodes (node_id) 
);
create trigger add_closure
after insert on parent_nodes
referencing new as n
for each row
begin
    insert into ancestor_nodes (node_id, ancestor_id)
    values (n.node_id, n.parent_id)
    union all
    select n.node_id, ancestor_id
    from ancestor_nodes
    where node_id = n.parent_id
    union all
    select node_id, n.parent_id
    from ancestor_nodes
    where ancestor_id = n.node_id
    union all
    select a.node_id, b.ancestor_id
    from ancestor_nodes a
    cross join ancestor_nodes b
    where a.ancestor_id = n.node_id
      and b.node_id = n.parent_id; --
end;
insert into nodes (node_id) values (1),(2),(3),(4),(5);
insert into parent_nodes (node_id, parent_id) values (5,4),(4,3),(3,1),(2,1);
select * from ancestor_nodes;


NODE_ID
ANCESTOR_ID


5
4

5
3

4
3

5
1

4
1

3
1

2
1