Problem Note 41748: Using the LIBNAME option MULTI_DATASRC_OPT=IN_CLAUSE can result in an error
 |  |  |  |  |
When joining 2 tables from different librefs and one of table's libref is defined with the LIBNAME option MULTI_DATASRC_OPT=IN_CLAUSE, it is possible to receive an error. The error occurs when the IN list that is built by the access engine is longer than 31,000 characters. The error received varies depending on the operating system that SAS is running on.
In UNIX operating environments, the error can look similar to the following:
Segmentation Violation In Task [ SQL ] Fault Occurred at [/opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sasxdbi:sqlfre+0x4] Task Traceback /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sasxdbi:sqlfre+0x4 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sasxwher:yownewf+0x21c /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxsrc+0x3d5c /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxsrc+0x9bc /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxjhsh+0x10e8 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxsort+0xa7c /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxcrta+0xa90 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxcrta+0x9a8 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxcrta+0x210 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqxexec+0x554 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqlloop+0xe70 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassqx:sqlloop+0x16c /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sassql:sassql+0x2e0 /opt/sas/sas91ts1m3sp4/server/hotfix/sasexe/sas.e9bd12:vvtentr+0x120
On Windows, the error could look something like the following:
ERROR: Read Access Violation In Task [ SQL ) Exception occurred at (00000000) Task Traceback Address Frame (DBGHELP API Version 4.0 rev 5) 6796FA11 0664DBF0 sasxkern:mcn_main+0x1EA11 6796FC4C 0664DC24 sasxkern:mcn_main+0x1EC4C 65991663 0664DC5C sasxdbi:mcn_main+0x20663 65988CF0 0664DC88 sasxdbi:mcn_main+0x17CF0 65985891 0664DF40 sasxdbi:mcn_main+0x14891 65985636 0664DF7C sasxdbi:mcn_main+0x14636 65985393 0664E200 sasxdbi:mcn_main+0x14393 6711E354 0664E268 sasxwher:mcn_main+0xD354 6711E1DC 0664E284 sasxwher:mcn_main+0xD1DC 066646D5 0664E2D0 sassqx:mcn_main+0x36D5 066624C5 0664E3B4 sassqx:mcn_main+0x14C5 0666A0C5 0664E43C sassqx:mcn_main+0x90C5 0667A820 0664E480 sassqx:mcn_main+0x19820 06671F99 0664E4F0 sassqx:mcn_main+0x10F99 06671F11 0664E5EC sassqx:mcn_main+0x10F11 066716CF 0664E628 sassqx:mcn_main+0x106CF 066872B1 0664E654 sassqx:mcn_main+0x262B1 066839EE 0664FF54 sassqx:mcn_main+0x229EE 600D118A 0664FF88 sassql:mcn_main+0x18A 013C2B02 0664FFA0 sashost:Main+0xBE72 013C6C20 0664FFB4 sashost:Main+0xFF90 7C80B713 0664FFEC kernel32:GetModuleFileNameA+0x1B4
In the z/OS operating environment, the error can look similar to the following:
ERROR: System abend 0C4 occurred in module UNKNOWN function YMDALLC at
offset EDA864.
ERROR: Traceback will be attempted...
NOTE: Traceback called
NOTE: Module Function Offset Line
NOTE: -------- -------- ------ ----
NOTE: SASXKERN YMREALC 000080 0
NOTE: SASXDBI SQLADD 0000B8 0
NOTE: SASXDBI ADDWHR 000086 0
NOTE: SASXDBI BLDWHR 0001EE 0
NOTE: SASXDBI BLDWHR1 0001E4 0
NOTE: SASXDBI YOEWSET 000A18 0
NOTE: SASXWHER YOWNEW 0001A2 0
NOTE: SASXWHER YOWNEWF 000088 0
NOTE: SASSQX STICK_WH 000450 0
NOTE: SASSQX @@271913 0003F0 0
NOTE: SASSQX @@461078 000118 0
NOTE: SASSQX @@719831 000238 0
NOTE: SASSQX @@669285 0000C6 0
NOTE: SASSQX @@669271 000912 0
NOTE: SASSQX @@669257 0001D2 0
NOTE: SASSQX SQXEXEC 00012A 0
NOTE: SASSQX SQLLOOP 000C30 0
NOTE: SASSQL SASSQL 000312 0
NOTE: SASHOST UNKNOWN 002D10 0
ERROR: Delete current task.
The only workaround is to not use the MULTI_DATASRC_OPT=IN_CLAUSE option.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.1 TS1M3 SP4 | 9.3 TS1M0 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Server | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Professional | 9.1 TS1M3 SP4 | |||
| Microsoft Windows NT Workstation | 9.1 TS1M3 SP4 | |||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Microsoft Windows XP Professional | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Windows Vista | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Windows Vista for x64 | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| 64-bit Enabled AIX | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| 64-bit Enabled Solaris | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| HP-UX IPF | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Linux | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Linux on Itanium | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| OpenVMS Alpha | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Solaris for x64 | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
| Tru64 UNIX | 9.1 TS1M3 SP4 | 9.3 TS1M0 | ||
A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:
http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#041748A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at:
http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#041748A fix for this issue for Base SAS 9.21_M3 is available at:
http://ftp.sas.com/techsup/download/hotfix/HF2/B25.html#41748| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2010-12-28 10:59:52 |
| Date Created: | 2010-12-03 12:57:22 |