I have a large union.

If I break it into its individual parts they all run quick.  The
longest is the last select it takes 2 minutes to fetch all rows. When
I run the query below it does not come back for quite some time. 20
minutes. There are very few duplicates.  When I run the sql's
individually the first two give no warning but the third
does(SQL0437W  Performance of this complex query may be sub-optimal.
Reason code:"1".  SQLSTATE=01602).  If I run the first two by them
selves they work quick in 2 minutes. But if I run any of the first
SQL's with the last one it takes 20 minutes again.   I have changed
the STMTHEAP to 25600 pages(100MB) but it caused other SQL's to become
bad and take upwards of 5 minutes to explain( I did not think this was
a viable solution since the third SQL with the bad access path by
itself it ran in 2 minutes).  This one does as well.  If I take the
individual third SQL statement it takes 4 minutes to prepare and then
runs in 3 seconds(Finally with the right access path, in my eyes).

Is there something that the optimizer does to look for like parts of
the SQL when there is a union do those like parts once?

Any help would be appreciated. Thanks,

(
SELECT
 RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
 ( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
       'WT-Off'
        WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
       'WT-On'
        END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME ),
 ODS_WT_DEAL.DEAL_NR,
 SUM(ABS(CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM')
THEN ODS_WT_SCHDL_ENRGY_PRFL_BLK.GEN_MW_QT
WHEN ( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM')  THEN
    ODS_WT_SCHDL_ENRGY_PRFL_BLK.LOAD_MW_QT
END)),
 Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT,
 ODS_WT_DEAL.TRAN_TYP_CD,
 ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
 ODS_WT_DEAL.DEAL_ID,
 CHAR(ODS_WT_DEAL.DEAL_NR),
 ODS_WT_DEAL.TRD_DT,
 rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
 ODS_WT_DEAL.DEAL_TYP_TX,
 RTRIM(ODS_WT_DEAL.TERM_TX),
 Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_TS
FROM
 DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
 DB2INSTB.ODS_WT_SCHDL_DEAL ODS_WT_SCHDL_DEAL,
 DB2INSTB.ODS_AEE_BOOK_DESIGNATION  Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
 DB2INSTB.ODS_WT_SCHDL_ENRGY_PRFL_BLK ODS_WT_SCHDL_ENRGY_PRFL_BLK,
 DB2INSTB.ODS_EDW_PERIOD  Alias_EDW_PERIOD_MWH_WT,
 DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
 DB2INSTB.ODS_WT_SCHDL_DEAL_CPRTY ODS_WT_SCHDL_DEAL_CPRTY,
 DB2INSTB.ODS_AEE_BOOK  Alias_ODS_AEE_BOOK_WT_DEAL
WHERE

( ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_CPRTY.PHYS_DEAL_NR  )
 AND  ( ODS_WT_SCHDL_DEAL_CPRTY.DEAL_NR=ODS_WT_DEAL.DEAL_NR  )
 AND  ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DEAL.CPRTY_NM
AND ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT'  )
 AND
( Alias_EDW_PERIOD_MWH_WT.GMT_PERIOD_TS=ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_TS  )
 AND  ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME  )
 AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_ID  )
 AND
( ODS_WT_SCHDL_ENRGY_PRFL_BLK.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_CPRTY.PHYS_DEAL_NR  )
 AND  (
 ( ( Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT ) between '03/01/2007'
and  '03/31/2007'  AND (Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE )
AND
ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1
day and  ODS_WT_SCHDL_ENRGY_PRFL_BLK.HOUR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT  )
 AND  ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD )  NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%'  )
 AND  ( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM')
AND ( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
       'WT-Off'
        WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
       'WT-On'
        END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME )  IN  ('WT-
On - PE', 'WT-On - PW')
 AND  ODS_WT_DEAL.STATUS_CD  <>  'VOIDED'
 AND  ODS_WT_SCHDL_DEAL.STAT_CD  <>  'VOIDED'
 AND  ODS_WT_DEAL.DEAL_TYP_TX  <>  'Transmission'
 )
GROUP BY
 RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
 ( CASE WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) NOT IN ('CSWS','PJM') AND
( ODS_WT_SCHDL_DEAL.SINK_CD ) NOT IN ('CSWS','PJM') THEN
       'WT-Off'
        WHEN ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') OR
( ODS_WT_SCHDL_DEAL.SINK_CD ) IN ('CSWS','PJM') THEN
       'WT-On'
        END ) || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME ),
 ODS_WT_DEAL.DEAL_NR,
 Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_DT,
 ODS_WT_DEAL.TRAN_TYP_CD,
 ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
 ODS_WT_DEAL.DEAL_ID,
 CHAR(ODS_WT_DEAL.DEAL_NR),
 ODS_WT_DEAL.TRD_DT,
 rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
 ODS_WT_DEAL.DEAL_TYP_TX,
 RTRIM(ODS_WT_DEAL.TERM_TX),
 Alias_EDW_PERIOD_MWH_WT.CPT_PERIOD_TS
UNION
(
SELECT
 RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
 'WT' || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME ),
 ODS_WT_DEAL.DEAL_NR,
 ABS(ODS_WT_DEAL_ENGRY_PRFL_BLK.EP_MW_QT),
 Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT,
 ODS_WT_DEAL.TRAN_TYP_CD,
 ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
 ODS_WT_DEAL.DEAL_ID,
 CHAR(ODS_WT_DEAL.DEAL_NR),
 ODS_WT_DEAL.TRD_DT,
 rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
 ODS_WT_DEAL.DEAL_TYP_TX,
 RTRIM(ODS_WT_DEAL.TERM_TX),
 Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_TS
FROM
 DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
 DB2INSTB.ODS_AEE_BOOK_DESIGNATION  Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
 DB2INSTB.ODS_WT_DEAL_ENGRY_PRFL_BLK ODS_WT_DEAL_ENGRY_PRFL_BLK,
 DB2INSTB.ODS_EDW_PERIOD  Alias_EDW_PERIOD_Price_WT,
 DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
 DB2INSTB.ODS_AEE_BOOK  Alias_ODS_AEE_BOOK_WT_DEAL
WHERE
 ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DEAL.CPRTY_NM AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT'  )
 AND
( Alias_EDW_PERIOD_Price_WT.GMT_PERIOD_TS=ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_TS  )
 AND  ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME  )
 AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_ID  )
 AND  ( ODS_WT_DEAL_ENGRY_PRFL_BLK.DEAL_NR=ODS_WT_DEAL.DEAL_NR  )
 AND  (
 ( ( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) between '03/01/2007'
and  '03/31/2007'  AND (( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE )
AND
ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1
day  and  ODS_WT_DEAL_ENGRY_PRFL_BLK.HOUR_ENDING_DT <=
date('03/31/2007') + 1 day
and ( Alias_EDW_PERIOD_Price_WT.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT  )
 AND  ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD )  NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%'  )
 AND  'WT' || ' - ' ||
( Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME )  IN  ('WT -
PE', 'WT - PW')
 AND  ODS_WT_DEAL.DEAL_TYP_TX  <>  'Transmission'
 AND  ODS_WT_DEAL.STATUS_CD  <>  'VOIDED'
 )
UNION
SELECT
 RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
 CASE  WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME =
'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
 ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR,
 SUM(ODS_WT_SCHDL_LPRFL_BLK.BLK_MW*-1),
 ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT,
 ODS_WT_DEAL.TRAN_TYP_CD,
 ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
 ODS_WT_DEAL.DEAL_ID,
 CHAR(ODS_WT_DEAL.DEAL_NR),
 ODS_WT_DEAL.TRD_DT,
 rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
 ODS_WT_DEAL.DEAL_TYP_TX,
 RTRIM(ODS_WT_DEAL.TERM_TX),
 ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_TS
FROM
 DB2INSTB.ODS_WT_DEAL ODS_WT_DEAL,
 DB2INSTB.ODS_AEE_BOOK_DESIGNATION  Alias_ODS_AEE_BOOK_DESIG_WT_DEAL,
 DB2INSTB.ODS_WT_SCHDL_DEAL ODS_WT_SCHDL_DEAL,
 DB2INSTB.ODS_WT_SCHDL_LPRFL_BLK_VW ODS_WT_SCHDL_LPRFL_BLK,
 DB2INSTB.ODS_STLMT_CNTR_PRTY_XREF ODS_STLMT_CNTR_PRTY_XREF,
 DB2INSTB.ODS_WT_SCHDL_TPRFL_RSRVN ODS_WT_SCHDL_TPRFL_RSRVN,
 DB2INSTB.ODS_AEE_BOOK  Alias_ODS_AEE_BOOK_WT_DEAL,
 DB2INSTB.ODS_WT_SCHDL_DEAL_CPRTY ODS_WT_SCHDL_DEAL_CPRTY,
 DB2INSTB.ODS_WT_SCHDL_DEAL_LPRFL ODS_WT_SCHDL_DEAL_LPRFL,
 DB2INSTB.ODS_WT_SCHDL_DEAL_TPRFL ODS_WT_SCHDL_DEAL_TPRFL
WHERE

( ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_CPRTY.PHYS_DEAL_NR  )
 AND  ( ODS_WT_SCHDL_DEAL_CPRTY.DEAL_NR=ODS_WT_DEAL.DEAL_NR  )
 AND  ( ODS_STLMT_CNTR_PRTY_XREF.SOURCE_CPRTY_CD=ODS_WT_DEAL.CPRTY_NM
AND ODS_STLMT_CNTR_PRTY_XREF.SOURCE_SYS_CD = 'WT'  )
 AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR  )
 AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_LPRFL_BLK.PHYS_DEAL_NR
and
ODS_WT_SCHDL_DEAL_LPRFL.LPRFL_ID=ODS_WT_SCHDL_LPRFL_BLK.LPRFL_ID  )
 AND
( ODS_WT_SCHDL_DEAL_LPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_DEAL_TPRFL.PHYS_DEAL_NR  )
 AND
( ODS_WT_SCHDL_DEAL_LPRFL.TRNMT_PRVDR_NM=ODS_WT_SCHDL_DEAL_TPRFL.TRNMT_PRVDR_NM  )
 AND
( ODS_WT_SCHDL_DEAL_TPRFL.PHYS_DEAL_NR=ODS_WT_SCHDL_TPRFL_RSRVN.PHYS_DEAL_NR  )
 AND
( ODS_WT_SCHDL_DEAL_TPRFL.SCHDL_DEAL_PRFL_ID=ODS_WT_SCHDL_TPRFL_RSRVN.SCHDL_DEAL_PRFL_ID  )
 AND
( ODS_WT_SCHDL_DEAL_TPRFL.TRANS_PRFL_ID=ODS_WT_SCHDL_TPRFL_RSRVN.TRANS_PRFL_ID  )
 AND  ( RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,
4)))=Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_NAME  )
 AND
( Alias_ODS_AEE_BOOK_WT_DEAL.BOOK_ID=Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_ID  )
 AND  (
 ( ( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD )  NOT IN
('AEP','AEPGEN','AEPLOAD','AEPTCC','AEPTNC','PJM
OPRES','ERCOT','QFOXYCHEM','',' ') AND
( ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD ) NOT IN '%'  )
 AND  ( ( ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT ) between '03/01/2007'
and  '03/31/2007' AND (ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT >=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.START_DATE AND
ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT <=
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.STOP_DATE) and
ODS_WT_SCHDL_LPRFL_BLK.HOUR_ENDING_DT >= date('03/01/2007') -1 day
and  ODS_WT_SCHDL_LPRFL_BLK.HOUR_ENDING_DT<= date('03/31/2007') + 1
day
and ( Alias_EDW_PERIOD_WT_LPrfl.CPT_PERIOD_DT ) BETWEEN
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_START_DT AND
ODS_STLMT_CNTR_PRTY_XREF.SOURCE_END_DT  )
 AND  ODS_WT_SCHDL_DEAL.STAT_CD  <>  'VOIDED'
 AND  CASE  WHEN
Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME = 'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END  IN  ('WT-On - PE', 'WT-On - PW')
 AND  ODS_WT_DEAL.DEAL_TYP_TX  <>  'Transmission'
 AND  ODS_WT_DEAL.STATUS_CD  <>  'VOIDED'
 AND  ODS_WT_SCHDL_TPRFL_RSRVN.OWNER_NM  IN  ('AEPMEA', 'CSWMEO')
 )
GROUP BY
 RTRIM(CHAR(RIGHT(ODS_WT_DEAL.BOOK_NM,4))),
 CASE  WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME =
'PW'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PW'
WHEN Alias_ODS_AEE_BOOK_DESIG_WT_DEAL.BOOK_DESIGNATION_NAME = 'PE'
AND ( ODS_WT_SCHDL_DEAL.SRC_CD ) IN ('CSWS','PJM') THEN 'WT-On - PE'
END,
 ODS_WT_SCHDL_DEAL.PHYS_DEAL_NR,
 ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_DT,
 ODS_WT_DEAL.TRAN_TYP_CD,
 ODS_STLMT_CNTR_PRTY_XREF.CPRTY_CD,
 ODS_WT_DEAL.DEAL_ID,
 CHAR(ODS_WT_DEAL.DEAL_NR),
 ODS_WT_DEAL.TRD_DT,
 rtrim(ltrim(ODS_WT_DEAL.MKT1_NM)),
 ODS_WT_DEAL.DEAL_TYP_TX,
 RTRIM(ODS_WT_DEAL.TERM_TX),
 ODS_WT_SCHDL_LPRFL_BLK.CPT_PERIOD_TS
)
);