Problem Note 64681: SAS® Customer Intelligence queries to the Oracle database take a long time to execute
 |  |  |  |  |
After you submit queries for execution from SAS Customer Intelligence to the Oracle database, some queries take longer to execute. This problem happens after you enable arguments in the SAS® middle tier (per SAS Note 62531) and apply Hot Fix U53018 or later.
The problem occurs because the campcode macro value is used inconsistently in the SAS® Marketing Automation information-map libraries. The inconsistencies cause the query to execute in Base SAS® software instead of in the Oracle database, which takes more time.
In the SASCustIntelCore log file, you can see the inconsistent campcode values, as shown in the following examples:
Example 1
LIBNAME CMD ORACLE DBCONINIT="EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'&CAMPCODE')" SQL_FUNCTIONS=ALL PATH=QA SCHEMA=QACMD USER=QAUSER PASSWORD=***;
libname dbtmplib ORACLE DBCONINIT="EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'')" SQL_FUNCTIONS=ALL PATH=QA USER=QAUSER PASSWORD=*** schema=qatemp;
Example 2
LIBNAME CMD ORACLE DBCONINIT="EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'&CAMPCODE')" SQL_FUNCTIONS=ALL PATH=QA SCHEMA=QACMD USER=QAUSER PASSWORD=*** ;
libname dbtmplib ORACLE DBCONINIT="EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'CAMP6070')" SQL_FUNCTIONS=ALL PATH=QA USER=QAUSER PASSWORD=*** schema=qatemp;
Example 3
LIBNAME CMDMMCY ORACLE DBCONINIT="EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'&CAMPCODE')" SQL_FUNCTIONS=ALL PATH=QA SCHEMA=QACMD USER=QAUSER PASSWORD=*** ;
libname dbtmplib ORACLE DBCONINIT="EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'CAMP8068')" SQL_FUNCTIONS=ALL PATH=QA USER=QAUSER PASSWORD=*** schema=qatemp;
To work around this problem, do not use the following line in the information-map libraries:
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 Marketing Automation Server | Microsoft® Windows® for x64 | 6.4 | 6.6 | 9.4 TS1M2 | 9.4 TS1M6 |
| 64-bit Enabled AIX | 6.4 | 6.6 | 9.4 TS1M2 | 9.4 TS1M6 | ||
| 64-bit Enabled Solaris | 6.4 | 6.6 | 9.4 TS1M2 | 9.4 TS1M6 | ||
| HP-UX IPF | 6.4 | 6.6 | 9.4 TS1M2 | 9.4 TS1M6 | ||
| Linux for x64 | 6.4 | 6.6 | 9.4 TS1M2 | 9.4 TS1M6 | ||
| Solaris for x64 | 6.4 | 6.6 | 9.4 TS1M2 | 9.4 TS1M6 | ||