In some cases, especially when analysing performance issues, it might be helpful to trace SQL queries when publishing a campaign to the Common Data Model.
To enable tracing, follow these steps:
1. Add the following options statement to the MarketingAutomation_autoexec_usermods.sas file that resides in
\SAS-configuration-directory\Lev1\Applications\SASCustomerIntelligence:
options sastrace=',,,ds' sastraceloc=saslog nostsuffix sql_ip_trace=(note,source) msglevel=i fullstimer;
2. Restart the SAS Object Spawner.
3. Execute a campaign in SAS Customer Intelligence Studio and the following messages are shown in the SASCustIntelCore6.x and Stored Process Server logs:
MPRINT(MAUPLOAD): ;
OLEDB: AUTOCOMMIT turned ON for connection id 7
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 7
OLEDB: AUTOCOMMIT turned OFF for connection id 7
MPRINT(MAUPLOAD): libname dbtmplib OLEDB DIRECT_EXE=DELETE SQL_FUNCTIONS=ALL UTILCONN_TRANSIENT=YES PROPERTIES=("Initial Catalog" = CDM) DATASOURCE=SAS PROMPT=NO PROVIDER=SQLNCLI10 USER=sasdemo PASSWORD=********;
NOTE: Libref DBTMPLIB was successfully assigned as follows:
Engine: OLEDB
Physical Name: SQLNCLI10
MPRINT(MAUPLOAD): option nodbidirectexec;
OLEDB: AUTOCOMMIT turned ON for connection id 8
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 8
OLEDB: AUTOCOMMIT turned OFF for connection id 8
OLEDB: AUTOCOMMIT turned ON for connection id 8
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 8
Summary Statistics for OLEDB are:
Total SQL execution seconds were: 0.004799
Total seconds used by the OLEDB ACCESS engine were 0.005096
DEBUG: SQL Implicit Passthru stmt has been prepared successfully.
SQL_IP_TRACE: The SELECT statement was passed to the DBMS.
The example above demonstrates the message when SAS/Access to OLEDB is used, but this works for all other SAS/Access engines as well.
As you can see, it will show a Summary Statistic about the execution time and that the SQL statement is properly sent to the DBMS.
Please note: Because publishing to the Common Data Model is done by a Stored Process, you will see these messages in both log files.
