zOs/SQL/EXPVIV

SET CURRENT SQLID = 'A540769';
delete   from A540769.plan_table where queryno = 81
;
EXPLAIN PLAN SET QUERYNO = 81 FOR
  SELECT *
  FROM oa1t.VMF150A1V
  WHERE ID_MITTELFLUSS IN(
           SELECT AB.ID_MITTELFLUSS
           FROM TABLE
                 (SELECT ID_MITTELFLUSS,CIFNUMMER_KUNDE,BUCHUNGSDATUM,
                         ZU_ABFLUSS,POLICEN_NR,KURSDATUM,KURSWHGR,
                         NOMINAL_STUECK
                  FROM oa1t.VMF150A1V JOIN oa1t.TMF401A1
                    ON ID_MITTELFLUSS  = UUID
                  WHERE LIFE_CYCLE_STATUS  = 10
                    AND TRANSACTION_TYP    = 20
                    AND ERSTELLUNGSTIMSTMP BETWEEN
                                               CURRENT TIMESTAMP - 1 DAY
                                           AND CURRENT TIMESTAMP
                    AND BETRAG_FWHRG       < 0
                    AND VALOR_SEQUENZ      = 'NDF'
                   ) AS AB
               ,TABLE
                 (SELECT ID_MITTELFLUSS,CIFNUMMER_KUNDE,BUCHUNGSDATUM,
                         ZU_ABFLUSS,POLICEN_NR,KURSDATUM,KURSWHGR,
                         NOMINAL_STUECK
                  FROM oa1t.VMF150A1V JOIN oa1t.TMF401A1
                    ON ID_MITTELFLUSS  = UUID
                  WHERE LIFE_CYCLE_STATUS  = 10
                    AND TRANSACTION_TYP    = 20
                    AND ERSTELLUNGSTIMSTMP BETWEEN
                                               CURRENT TIMESTAMP - 1 DAY
                                           AND CURRENT TIMESTAMP
                    AND BETRAG_FWHRG       > 0
                    AND VALOR_SEQUENZ      = 'NDF'
                   ) AS ZU
           WHERE AB.CIFNUMMER_KUNDE = ZU.CIFNUMMER_KUNDE
           AND   AB.BUCHUNGSDATUM   = ZU.BUCHUNGSDATUM
           AND   AB.POLICEN_NR      = ZU.POLICEN_NR
           AND   AB.KURSDATUM       = ZU.KURSDATUM
           AND   AB.KURSWHGR        = ZU.KURSWHGR
           AND   AB.NOMINAL_STUECK  = ZU.NOMINAL_STUECK
           UNION ALL
           SELECT ZU.ID_MITTELFLUSS
           FROM TABLE
                 (SELECT ID_MITTELFLUSS,CIFNUMMER_KUNDE,BUCHUNGSDATUM,
                         ZU_ABFLUSS,POLICEN_NR,KURSDATUM,KURSWHGR,
                         NOMINAL_STUECK
                  FROM oa1t.VMF150A1V JOIN oa1t.TMF401A1
                    ON ID_MITTELFLUSS  = UUID
                  WHERE LIFE_CYCLE_STATUS  = 10
                    AND TRANSACTION_TYP    = 20
                    AND ERSTELLUNGSTIMSTMP BETWEEN
                                               CURRENT TIMESTAMP - 1 DAY
                                           AND CURRENT TIMESTAMP
                    AND BETRAG_FWHRG       < 0
                    AND VALOR_SEQUENZ      = 'NDF'
                   ) AS AB
               ,TABLE
                 (SELECT ID_MITTELFLUSS,CIFNUMMER_KUNDE,BUCHUNGSDATUM,
                         ZU_ABFLUSS,POLICEN_NR,KURSDATUM,KURSWHGR,
                         NOMINAL_STUECK
                  FROM oa1t.VMF150A1V JOIN oa1t.TMF401A1
                    ON ID_MITTELFLUSS  = UUID
                  WHERE LIFE_CYCLE_STATUS  = 10
                    AND TRANSACTION_TYP    = 20
                    AND ERSTELLUNGSTIMSTMP BETWEEN
                                               CURRENT TIMESTAMP - 1 DAY
                                           AND CURRENT TIMESTAMP
                    AND BETRAG_FWHRG       > 0
                    AND VALOR_SEQUENZ      = 'NDF'
                   ) AS ZU
           WHERE AB.CIFNUMMER_KUNDE = ZU.CIFNUMMER_KUNDE
           AND   AB.BUCHUNGSDATUM   = ZU.BUCHUNGSDATUM
           AND   AB.POLICEN_NR      = ZU.POLICEN_NR
           AND   AB.KURSDATUM       = ZU.KURSDATUM
           AND   AB.KURSWHGR        = ZU.KURSWHGR
           AND   AB.NOMINAL_STUECK  = ZU.NOMINAL_STUECK
          )
  WITH UR;
select * from plan_view5
    where queryno in (  81,  91)
    order by applname, progname, queryNo, qblockno, planno
;
commit
;