• Print |
• E-mail  |
• Bookmark

If you use the RENAME= data set option when referencing a DBMS table,
you may suffer some performance problems.  Two of the cases where you
may encounter this are:

  1) Using a WHERE statement or data set option which references one
     of the renamed columns.  For example:

       libname mydb2 db2;
       data new;
         set db2.delay(rename=(flight=flt));
         where flt='114';
       run;

  2) Using a MODIFY with a BY statement and your BY statement
     references one of the renamed columns.  For example:

       libname mydb2 db2 reread_exposure=yes;
       data mysas;
        input flt $ delay;
       cards;
       114 0
       999 -10
       ;
       data mydb2.modify_test;
         modify mydb2.modify_test(rename=(flight=flt)) mysas;
           by flt;
         select (_iorc_);
           when(%sysrc(_sok)) do; replace; end;
           when(%sysrc(_dsenmr)) do; _error_=0; output; end;
           otherwise do;
             put 'Unexpected value for _IORC_= ' _iorc_ / _all_;
             stop;
           end;
         end;
       run;

The problem is caused by SAS not passing the WHERE clause to the
DBMS.  This causes the query to run slower since SAS must retrieve all
the rows from the table and then subset them according to the WHERE
clause.

This problem has been fixed in Release 8.1 TS1M0.

Operating System and Release Information