Problem Note 7857: Performance problem when using a libref pointing to a remote location
that includes the option preserve_names=yes
If you define a libref pointing to a remote location and also have
preserve_names=yes (or preserve_tab_names=yes) and use that libref to
join 2 (or more) tables, the wrong syntax is generated and passed to
DB2. DB2 will return a -104 error. SAS sees that as DB2 not being able
to process the query so it does the query itself. This requires SAS
pulling in both entire tables and performing the join. This can cause a
significant performance hit. Everything works fine when the query
references just 1 remote table. It also works if you use
preserve_names=yes without location or location without preserve_names.
It's the two in combination with each other that causes the issue.
The incorrect syntax is caused by the way the table name is built:
"location"."authid".""."table".
There is an additional quoted period between the authorization id and
the table name.
The only workaround is to switch to the SQL passthru facility or to
remove the preserve_names= option and code the query differently.
A Technical Support hot fix for Release 8.2 TSLEVEL TS2M0 for this
issue is available at:
http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#007857
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to DB2 | z/OS | 8 TS M0 | 9.1 TS1M0 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | high |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> DB2
SAS Reference ==> LIBNAME Engines
|
| Date Modified: | 2003-11-26 08:23:45 |
| Date Created: | 2002-06-25 14:51:11 |
