Hi All,
Please find the below query for optimization.
proc sql ;
connect to db2 (ssid=&ssid) ;
create table &work..xyz as
select * from connection to db2
(select a.x123
, a.x124
, a.x125
, a.x126
, a.x127
, a.x128
.........
, upper(d.x129) as x129
, upper(d.x130) as x130
, d.x131
, case when b.x131 is null
then c.x131
else b.x131 end as x131
.............................../*more variables******/
from J.ATABLE a
left join J.BTABLE b
on a.X132 = b.X133
left join J.CTABLE0 c
on a.X134 = c.X135
left join J.FTABLE F
on a.X136 = f.X136
and a.X137 = f.X137
and a.X138 = f.X138
and a.X139 = f.X139
left join J.DTABLE d
on a.X136 = d.X136
and a.X137 = d.X137
and a.X138 = d.X138
and a.X139 = d.X139
and d.X140 <= &XYZ
and d.X141 >= &XYZ
, (select z.X142, z.X143
from J.ZTABLE z
left join J.YTABLE y
on z.X142 = y.X142
where z.X143 in ('3','4')
and z.X144 = '1'
and z.X145 <= &XYZ
and y.X146='10' and y.X147 = '9'
) e
where a.X141 <= &XYZ
and a.X147 >= &XYZ
and a.X148 = e.X149
and a.X150 = e.X151
&MORE_WHERE_BASED_ON_CONDITIONS /* online testen 'FORE' */
) ;
%put RC =&sqlxrc ;
%put MSG =&sqlxmsg ;
%put N =&sqlobs ;
disconnect from db2 ;
quit ;
Till know I have added indexes to all the variable's, which is either in on condition or in where condition. apart from suggestions on indexing is there any other way.
The table will create the data with more than 4 billion records.
Thank you
