Optimizing DB2 SAS query.

Imroze — Mon, 03 Mar 2014 15:57:58 GMT

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

Re: Optimizing DB2 SAS query.

Imroze — Tue, 04 Mar 2014 06:07:31 GMT

hi guys,

any suggestions please.

thank you

topic Re: Optimizing DB2 SAS query. in SAS Programming

Optimizing DB2 SAS query.

Re: Optimizing DB2 SAS query.