62531 - You cannot identify which campaign submits a query to the Oracle database from SAS® Customer Intelligence Studio

Problem Note 62531: You cannot identify which campaign submits a query to the Oracle database from SAS® Customer Intelligence Studio

After you submit queries from SAS Customer Intelligence to the Oracle database for execution, it is difficult to identify which campaigns are associated with which database query.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Note: After you apply the hot fix, follow the steps below to enable you to determine which campaigns own campaign-based queries. In the code below, the variable used is CAMPCODE.

Add the following command to the MarketingAutomation_autoexec_usermods.sas file, which is typically located in the SAS-configuration-directory\Lev1\Applications\SASCustomerIntelligence directory:
%let campcode=%str(); /* need a default */
Add the following new Java argument (introduced in the hot fix):
-Dsas.ci.infomap.libname.macrovars

This new argument enables you to identify which campaigns are associated with which database query in Oracle. Add the argument as instructed below for your operating environment.
- For Microsoft Windows operating environments (in which the servers are started as Windows services):
  Navigate to the SAS-configuration-directory\Lev1\Web\WebAppServer\SASServer6_1\conf directory and add the new argument to the wrapper.conf file, as shown below:
  
  wrapper.java.additional.XX=-Dsas.ci.infomap.libname.macrovars
  
  In this argument, XX represents the next number in the sequence that already exists in the file. Modify XX to be the next number in that sequence.
- For Linux or other UNIX operating environments:
  Navigate to the SAS-configuration-directory/Lev1/Web/WebAppServer/SASServer6_1/bin directory and add the option to setenv.sh file, as shown below:
  
  JAVA_OPTS="$JAVA_OPTS -Dsas.ci.infomap.libname.macrovars"
After you add the argument, restart the middle tier so that the setting can take effect.

For the Oracle database, you must add the variable to the LIBNAME statement, as follows:

In SAS^® Management Console, open the Data Library Manager.
Select your-library ► Properties ► Options ► Advanced Options ► Connection. In this case, your-library specifies the libraries (either your data-mart or Common Data Model (CDM) library) that are used for SAS Marketing Automation.
On the Connection tab, add the following setting to the User-defined connection initialization command field (Please note that these settings work for Oracle only):
EXEC DBMS_APPLICATION_INFO.SET_ACTION(ACTION_NAME=>'&CAMPCODE')

Note: Once you add this option, you can no longer use these modified libraries (data-mart or CDM) with SAS^® Enterprise Guide^®. Adding this option can also impact other applications that use these libraries.

Therefore, SAS Technical Support recommends that you clone these libraries by re-creating the LIBNAME statements with these options and re-register the tables in SAS Management Console. Then, reference these cloned libraries with the setting (shown earlier) in from within the information map that is used by SAS Marketing Automation. You can use the Resource Replacement feature in SAS^® Information Map Studio to seamlessly replace the reference. After you replace the reference, you must clear the metadata cache for the affected business context in SAS^® Customer Intelligence Studio.

For Oracle, you can submit the following statements (as the database administrator) to see active and historic queries. Using these commands enables you to identify which campaign submits which structured query language (SQL) to the database. Adjust the filter, as appropriate, for your environment.

For active queries, submit the following statement:

SELECT PADDR, STATUS, ACTION FROM V$SESSIOn WHERE ACTION LIKE 'CAMP%';

For historic queries, submit the following statement:

SELECT SQL_ID, ACTION FROM V$SQL WHERE ACTION LIKE 'CAMP%';

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
Product Family	Product	System	Reported	Fixed*	Reported	Fixed*
SAS System	SAS Customer Intelligence Core Mid-Tier	Microsoft® Windows® for x64	6.4		9.4 TS1M2
		64-bit Enabled AIX	6.4		9.4 TS1M2
		64-bit Enabled Solaris	6.4		9.4 TS1M2
		HP-UX IPF	6.4		9.4 TS1M2
		Linux for x64	6.4		9.4 TS1M2
		Solaris for x64	6.4		9.4 TS1M2

* 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

Date Modified:	2019-09-02 11:21:30
Date Created:	2018-06-27 10:38:57