Problem Note 64744: SAS/ACCESS® Interface to JDBC does not pass down some functions to Google BigQuery
 |  |  |  |  |
SAS/ACCESS Interface to JDBC is not passing down some database-specific functions. The issue occurs with the following functions:
- SUBSTR
- YEAR
You can use the SASTRACE= system option in an OPTIONS statement to determine whether the function was passed down:
options sastrace=',,,d' sastraceloc=saslog nostsuffix sql_ip_trace=(note,source) msglevel=i;
If the function is not passed down, you see a message in the SAS® log similar to the following:
SQL_IP_TRACE: pushdown attempt # 1
SQL_IP_TRACE: passed down query: select distinct SUBSTR ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.
SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.
SQL_IP_TRACE: passed down query: select distinct SUBSTR ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.
SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.
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 | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to JDBC | Windows 7 Enterprise 32 bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 |
| Microsoft Windows Server 2019 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 Std | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2016 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 R2 Std | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows Server 2012 Datacenter | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Pro x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 10 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Pro x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Enterprise x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Pro 32-bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft Windows 8 Enterprise 32-bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Microsoft® Windows® for x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Enterprise x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Home Premium 32 bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Home Premium x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Professional 32 bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Professional x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Ultimate 32 bit | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Windows 7 Ultimate x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| 64-bit Enabled AIX | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| 64-bit Enabled Solaris | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| HP-UX IPF | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Linux for x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
| Solaris for x64 | 9.4 | 9.4 | 9.4 TS1M6 | 9.4 TS1M6 | ||
A fix for this issue for SAS/ACCESS Interface to Hadoop 9.46 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/E4Y.html#64744| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2019-09-18 08:32:42 |
| Date Created: | 2019-09-09 04:55:36 |