How can I INSERT data into two tables simultaneously with only one sql script db2?

Question

How would I insert into multiple tables with one sql script in db2

For example, insert a row into T1 DOCK_DOOR and then insert into T2 DOCK_DOOR_LANE multiple times based on the dock_door_sysid from the first table.

My first approach was the following. I was attempting to use a with with three inserts. on the other hand, doing to inserts on the second table is not and option if this can be automated with one insert. thanks for any feedback

sql example

WITH ins AS (
  INSERT INTO DBF1.DOCK_DOOR    (DOCK_DOOR_SYSID,DOOR_NUMBER,DOOR_NAME,DOCK_SYSID,DOOR_SEQ,ENCRYPTION_CODE,RFID_ENBLD_FLAG,LANES_COUNT,CMNT_TEXT,CREATE_TS,CREATE_USERID,UPDATE_TS,UPDATE_USERID,VER_NUMBER,ACTIVE_FLAG,STATUS_SYSID,DOOR_TYPE_SYSID) 
VALUES (nextval for DBF1.DOCK_DOOR_SEQ,'026','DOOR025',61,25,NULL,'N','2',NULL,current timestamp,'SQL_INSERT',current timestamp,'SQL_INSERT',0,NULL,1723,1142)
  RETURNING door_number,dock_door_sysid),
ins2 AS (
INSERT INTO SIT.DOCK_DOOR_lane (DOCK_DOOR_LANE_SYSID,DOOR_LANE_ID,DOCK_DOOR_SYSID,LANE_ID,CREATE_TS,CREATE_USERID,UPDATE_TS,UPDATE_USERID,VER_NUMBER) 
VALUES (nextval for DBF1.DOCK_DOOR_LANE_seq,door_number||''||'A',dock_door_sysid,'A',current timestamp},'SQL_INSERT',current timestamp,'SQL_INSERT',0)
  SELECT door_number,dock_door_sysid FROM DBF1.DOCK_DOOR
  RETURNING door_number,dock_door_sysid)
INSERT INTO DBF1.DOCK_DOOR_lane (DOCK_DOOR_LANE_SYSID,DOOR_LANE_ID,DOCK_DOOR_SYSID,LANE_ID,CREATE_TS,CREATE_USERID,UPDATE_TS,UPDATE_USERID,VER_NUMBER) 
VALUES (nextval for DBF1.DOCK_DOOR_LANE_seq,door_number||''||'B',dock_door_sysid,'B',current timestamp},'SQL_INSERT',current timestamp,'SQL_INSERT',0)
SELECT door_number,dock_door_sysid FROM DBF1.DOCK_DOOR;

Table 1 = dock_door

Table 2 = Dock_door_lane

Answer 1

You could do it with a trigger on the dock_door table.

However, if you're on a recent, version on IBM i. You might be able to make use of data change table reference

Your statement would look something like this

insert into dock_door_lane
  select <....>
    from final table (insert into dock_door <...>)

I'm not sure it will work, as this article indicates that at least at a couple of years ago DB2 for i didn't support the secondary insert required.

This old SO question also seems to confirm that at least at v7.1, the double insert isn't supported.

If I get a chance, I'll run a test on a 7.2 system Monday.