Preparing datasource for Unica Campaign using ODBC Connection

1. Go to <ORACLE_HOME>/network/admin
2. Remove “SQLNET.ALLOWED_LOGON_VERSION_SERVER” parameter if it is present in sqlnet.ora file

   e.g.

   #SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

1. Ask all users to logout from application
2. Stop all EMM applications. Stop Server
3. Go to <CAMPAIGN_HOME>/bin folder and stop the listener process started with rc.unica_ac or CmpServer.bat.
4. Kill all unica_acsvr process if any exist after listener stops.
5. Restart Oracle Database

   export ORACLE_SID=<SID>

   export ORACLE_HOME= <ORACLE_HOME>

   export PATH=$PATH:/<ORACLE_HOME>/bin

   cd <ORACLE_HOME>/bin

   Make sure you are logged in as root. Stop Oracle Listener service as a root user

   ./lsnrctl stop

   Change user to Oracle user and restart Oracle DB

   su oracle

   sqlplus "/ as sysdba"

   shutdown

   startup

   quit

   Now start Oracle Listener service as a root user

   ./lsnrctl start LISTENER

1. Reset DB user to same value as it was before.

   sqlplus "/ as sysdba"

   ALTER USER <username> IDENTIFIED BY <password>;

   ALTER USER <username> IDENTIFIED BY <password>;

   commit;

1. Go to <CAMPAIGN_HOME>/bin location
2. Add the path of odbc.ini in setenv file

   export ODBCINI=<Path_to_odbc.ini>

3. Start the Appserver.
4. Now test the ODBC connectivity by running cxntest utility.

   e.g.

   . ./setenv.sh

   ./cxntest

   Connection Library? libodb4dDD.so

   Registered Data Sources:

   ORCL

   Data Source? ORCL

   User ID? <username>

   Password? <password>

   Executing Unica Campaign DB Certification Utility at 06 10 2020 17.20...

   Connecting to DB with "libraryName=libodb4dDD.so serverName=ORCL userID=UserName

   DB Connection successful!

   >

1. Save the ‘Oracle_ODBC_Template’ from zip file at <CAMPAIGN_HOME>/conf location.
2. Use configtool to Import this template (Make sure Appserver is up and running).
3. Go to <PLATFORM_HOME>/tools/bin and execute following-

   ./configTool.sh -i -p "Affinium|Campaign|partitions|partition1|dataSources" -f “<CAMPAIGN_HOME>/conf /Oracle_ODBC_Template.xml”

4. Make sure ‘Oracle_ODBC_Template’ is available under Settings > Configurations > Campaign > partitions > partition1 > dataSources node.

1. Make sure to take the backup of System table DS i.e. UA_SYSTEM_TABLES and User table DS (if they already exist).

   Use the below command configTool.bat -x -p "Affinium|Campaign|partitions|partition1|dataSources" -f "<Platform_Home>\tools\bin\DataSourceBackup.xml"

2. Delete System table DS (UA_SYSTEM_TABLES) and User table DS.
3. Recreate both the datasources using Oracle_ODBC_Template.
4. Keep all the properties identical to NATIVE datasource except the below mentioned.
5. Make sure to set the properties as mentioned below:

   DateFormat	DELIM_Y_M_D
   DateOutputFormatString	%Y-%m-%d
   DateTimeFormat	DT_DELIM_Y_M_D
   DateTimeOutputFormatString	%Y-%m-%d %H:%M:%S

6. Specify DSN exactly same as in ‘odbc.ini’ and ‘tnsnames.ora’ file.
   Note: DSN is case sensitive
7. Specify following query in SQLOnConnect

   ALTER SESSION SET NLS_LANGUAGE='American' NLS_TERRITORY='America' NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF' NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

8. Keep below mentioned properties as default

   Type	Oracle ODBC
   BindDateToTimestamp	TRUE

Add following NLS variables in setenv file

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF'

For UNICODE environment below are the recommended settings:

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export NLS_LANG

export LC_ALL=en_US.UTF-8

export LANG=en_US.UTF-8

1. Stop the Application Server (WebLogic or WebSphere) for Platform and Campaign.
2. Clean the application server cache
3. Start the Web application server to bring up all the applications.
4. Start the listener process with "rc.unica_ac" or "CmpServer.bat".

1. IMPORTANT! Be sure to clean the browser cache for every user.
2. Access Campaign and make sure all other products are accessible.