By default, the runtime environment writes contact and response history data from session data into staging tables. On a very active production system, however, the amount of memory required to cache all the data before runtime can write it to the staging tables may be prohibitive. You can configure runtime to use a database load utility to improve performance.When you enable a database load utility, instead of holding all contact and response history in memory before writing to the staging tables, runtime writes the data to a staging file.You define the location of the directory containing the staging files with the externalLoaderStagingDirectory property.This directory contains several subdirectories. The first subdirectory is the runtime instance directory, which contains the contactHist and respHist directories.The contactHist and respHist directories contain uniquely named sub directories in the format of audienceLevelName.uniqueID.currentState, which contain the staging files.Here are the detailed steps for configuring Db loader with Oracle, Db2 and Sql Server.
1. Create a Data Source under asm_admin as “systemTablesDataSource”. Specify the login details of the Interact Runtime specified in the “InteractRTDS” JNDI2. Need to update below configuration
Update the parameters under –
"Affinium|Interact|general|systemTablesDataSource|loaderProperties" as follows:databaseName: ujanidatabaseName must be correctly specified as SID.loaderCommandForAppend: /data/unica/qa/interact/RG_ACI753_RT_8100/Affinium/Interact/loaderService/oraload.sh <CONTROLFILE> <TABLE> <USER> <PASSWORD> <DATABASE>oraload.sh is located under <Interact_Home>/ loaderService directory update above configuration with path to it along with the mentioned arguments.loaderControlFileTemplateForAppend: /data/unica/qa/interact/RG_ACI753_RT_8100/Affinium/Interact/loaderService/oraload_control_template.txtFor above configuration update path till Oracle Control template file located under <Interact_Home>/ loaderService directory at your installation directory.loaderDelimiterForAppend: |loaderDelimiterAtEndForAppend: FalseloaderUserLocaleDP: False
3. Update the parameters under “Affinium|Interact|services” as follows:
externalLoaderStagingDirectory: /data/unica/qa/interact/RG_ACI753_RT_8100/Affinium/Interact/loaderService/staging
Mention path till <Interact_Home>/loaderService/staging directory at your installation directory.
4. Update the parameters under “Affinium|Interact|services|contactHist” as follows:
cacheType: External Loader File
5. Update the parameters under “Affinium|Interact|services|ResponseHist” as follows:
cacheType: External Loader File
6. Update below fileCache configuration as per requirement, for detail check Interact Administration Guide.
Interact|services|contactHist|fileCache
Affinium|interact|services|responseHist|fileCache
7. Update ORACLE_HOME variable in oraload.sh file located under installation directory at <Interact_Home>/loaderService
1. Create a Data Source under asm_admin as “systemTablesDataSource”. Specify the login details of the Interact Runtime specified in the “InteractRTDS” JNDI2. Need to update below configuration
Update the parameters under
"Affinium|Interact|general|systemTablesDataSource|loaderProperties" as follows:databaseName: InteractdatabaseName must be correctly specified as SID.loaderCommandForAppend: /data/unica/qa/interact/RG_ACI753_RT_8100/Affinium/Interact/loaderService/db2load.sh <CONTROLFILE> <TABLE> <USER> <PASSWORD> <DATABASE>oraload.sh is located under <Interact_Home>/ loaderService directory update above configuration with path to it along with mentioned arguments.loaderControlFileTemplateForAppend: /data/unica/qa/interact/RG_ACI753_RT_8100/Affinium/Interact/loaderService/db2load_control_template.txtFor above configuration update path till Db2 Control template file located under- <Interact_Home>/ loaderService directory at your installation directory.loaderDelimiterForAppend: |loaderDelimiterAtEndForAppend: FalseloaderUserLocaleDP: False
3. Update the parameters under “Affinium|Interact|services” as follows:
externalLoaderStagingDirectory:
/data/unica/qa/interact/RG_ACI753_RT_8100/Affinium/Interact/loaderService/staging
Mention path till <Interact_Home>/loaderService/staging directory at your installation directory.
4. Update the parameters under “Affinium|Interact|services|contactHist” as follows:
cacheType: External Loader File
5. Update the parameters under “Affinium|Interact|services|ResponseHist” as follows:
cacheType: External Loader File
6. Update below fileCache configuration as per requirement, for detail check Interact Administration Guide.
Interact|services|contactHist|fileCache
Affinium|interact|services|responseHist|fileCache
7. Update DB2_HOME variable in db2load.sh file located under installation directory at <Interact_Home>/loaderService
8. Update DB2DIR variable with Db2 installation directory on same machine and db2profile file path location in setenv.sh located under <Campaign_Home>/bin
Db Loader configuration for Sql Server:
Pre-requisite:
Check %MSSQLDIR%\Binn has bcp.exe utility installed already. bcp.exe should be installed on the server where loader is to run.
Important: If bcp.exe is not present under folder %MSSQLDIR% Loader cannot be tested on that setup.
1. Create a Data Source under asm_admin as “systemTablesDataSource”. Specify the login details of the Interact Runtime specified in the “InteractRTDS” JNDI e.g sa
2. Need to update below configuration
Update the parameters under
"Affinium|Interact|general|systemTablesDataSource|loaderProperties" as follows:databaseName: e63rt11.dbofor SQL Server it should be database.dbo (e.g. atrt11.dbo)loaderCommandForAppend: C:\Installs\Interact11WLNew\Interact\loaderService\/bcpload.bat <DATABASE> <TABLE> <DATAFILE> <USER> <PASSWORD> <CONTROLFILE>bcpload.bat is located under <Interact_Home>/ loaderService directory, update above configuration with path to it along with mentioned arguments.loaderControlFileTemplateForAppend: C:\Installs\Interact11WLNew\Interact\loaderService\bcpload_control_template.txt For above configuration update path till Sql Svr Control template file located under <Interact_Home>/ loaderService directory at your installation directory.loaderDelimiterForAppend: |loaderDelimiterAtEndForAppend: FalseloaderUserLocaleDP: False
3. Update the parameters under “Affinium|Interact|services” as follows:
externalLoaderStagingDirectory:
C:\Installs\Interact11WLNew\Interact\loaderService\staging
Mention path till <Interact_Home>/loaderService/staging directory at your installation directory.
4. Update the parameters under “Affinium|Interact|services|contactHist” as follows:
cacheType: External Loader File
5. Update the parameters under “Affinium|Interact|services|ResponseHist” as follows:
cacheType: External Loader File
6. Update below fileCache configuration as per requirement, for detail check Interact Administration Guide.
Interact|services|contactHist|fileCache
<Interact_Home>/loaderService with the below details:
c. Update path for LOGFILEDIRSample bcpload.bat
#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--light {padding: 20px 0px;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--light .header {padding: 10px 20px;font-weight: bold;border-left: 1px solid #C9CDCF;border-right: 1px solid #C9CDCF;border-top: 1px solid #C9CDCF;background-color: #F8F8F8;color: #363B3E;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--light .header .paragraph {margin: 0;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--light .body-code {margin: 0;border: 1px solid #C9CDCF;background-color: #FFFFFF;color: #666C70;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--dark {padding: 20px 0px;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--dark .ace-tomorrow-night-eighties {background-color: #363B3E;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--dark .header {padding: 10px 20px;font-weight: bold;border-left: 1px solid #E0E1E2;border-right: 1px solid #E0E1E2;border-top: 1px solid #E0E1E2;background-color: #666C70;color: #FFFFFF;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--dark .header .paragraph {margin: 0;}#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3 .code-editor--dark .body-code {margin: 0;border: 1px solid #E0E1E2;background-color: #363B3E;color: #F8F8F8;}function setupElement346754986560125028() {var requireFunc = window.platformElementRequire || window.require;// Relies on a global require, specific to platform elementsrequireFunc(['w-global','underscore','jquery','backbone','util/platform/elements/PlatformElement','util/platform/elements/PlatformElementSettings'], function(_W,_,$,Backbone,PlatformElement,PlatformElementSettings) {var dependencies = ["\/\/marketplace.editmysite.com\/elements\/270170748587580171-1.3.3\/assets\/ace\/ace.js"] || [];var platform_element_id = "270170748587580171-1.3.3";if (typeof _W.loadedPlatformDependencies === 'undefined') {_W.loadedPlatformDependencies = [];}if (typeof _W.platformElements === 'undefined') {_W.platformElements = [];}if (typeof _W.platformElements[platform_element_id] === 'undefined') {_W.platformElements[platform_element_id] = {};_W.platformElements[platform_element_id].deferredObject = new $.Deferred();_W.platformElements[platform_element_id].deferredPromise = _W.platformElements[platform_element_id].deferredObject.promise();}if(_.intersection(_W.loadedPlatformDependencies, dependencies).length !== dependencies.length){_.reduce(dependencies, function(promise, nextScript){_W.loadedPlatformDependencies.push(nextScript);return promise.then(function(){return $.getScript(nextScript);});}, $().promise()).then(function(){_W.platformElements[platform_element_id].deferredObject.resolve();});}if (dependencies.length === 0){_W.platformElements[platform_element_id].deferredObject.resolve();}_W.platformElements[platform_element_id].deferredPromise.done(function(){var _ElementDefinition = /*** This is required for element rendering to be possible* @type {PlatformElement}*/(function() {var CodeBlock = PlatformElement.extend({/*** PlatformElement initialization. Code to setup the* element should go here.*/initialize: function() {this.theme = this.settings.get('style') == 'light' ? 'tomorrow' : 'tomorrow_night_eighties';/*** The script are not defined in the manifest* so that 6mb of scripts are not loaded. This* allows us to load only scripts needed for* each language and theme.*/$.when($.getScript(this.assets_path + 'ace/mode-' + this.settings.get('syntax') + '.js'),$.getScript(this.assets_path + 'ace/theme-' + this.theme + '.js')).done(function() {/*** After the scripts are loaded, we can* then make the call to setup the editor.*/this.setUpEditor();}.bind(this));this.checkIfHeader();this.modifyStyle();},/*** Function to setup the editor*/setUpEditor: function() {this.editor = ace.edit(this.$el.find('.editor')[0]);this.editor.setValue(this.settings.get('code'), -1);this.editor.setTheme('ace/theme/' + this.theme);this.editor.getSession().setMode('ace/mode/' + this.settings.get('syntax'));this.editor.container.style.lineHeight = '26px';this.editor.container.style.fontSize = '14px';this.editor.renderer.setScrollMargin(20, 20);this.editor.renderer.setPadding(20);this.editor.setOptions({'highlightActiveLine': this.settings.get('highlight_active_line'),'readOnly': true,'showPrintMargin': false});this.editor.renderer.setOptions({'showGutter': this.settings.get('show_gutter'),'maxLines': this.settings.get('max_lines'),'minLines': this.settings.get('min_lines'),'displayIndentGuides': this.settings.get('display_indent_guides')});this.editor.session.setOptions({'wrap': this.settings.get('line_wrap'),'useSoftTabs': this.settings.get('use_soft_tabs'),'useWorker': false // workers rebind window.require so we need to disable them});},/*** Checks settings for whether or not the header* should be displayed or not.*/checkIfHeader: function() {if(this.settings.get('display_header') == "0") {this.$el.find('.header').remove();}},/*** Minor style tweaks done on some elements*/modifyStyle: function() {this.$el.find('.ace_content').css({'margin-left': '20px'});}});return CodeBlock;})();;if (typeof _ElementDefinition == 'undefined' || typeof _ElementDefinition == 'null') {var _ElementDefinition = PlatformElement.extend({});}var _Element = _ElementDefinition.extend({initialize: function() {// we still want to call the initialize function defined by the developer// however, we don't want to call it until placeholders have been replacedthis.placeholderInterval = setInterval(function() {// so use setInterval to check for placeholders.if (this.$('.platform-element-child-placeholder').length == 0) {clearInterval(this.placeholderInterval);this.constructor.__super__.initialize.apply(this);}}.bind(this), 100);}});_Element.prototype.settings = new PlatformElementSettings({"max_lines_each":[{"max_lines_index":0},{"max_lines_index":1},{"max_lines_index":2},{"max_lines_index":3},{"max_lines_index":4},{"max_lines_index":5},{"max_lines_index":6},{"max_lines_index":7},{"max_lines_index":8},{"max_lines_index":9},{"max_lines_index":10},{"max_lines_index":11},{"max_lines_index":12},{"max_lines_index":13},{"max_lines_index":14},{"max_lines_index":15},{"max_lines_index":16},{"max_lines_index":17},{"max_lines_index":18},{"max_lines_index":19},{"max_lines_index":20},{"max_lines_index":21},{"max_lines_index":22},{"max_lines_index":23},{"max_lines_index":24},{"max_lines_index":25},{"max_lines_index":26},{"max_lines_index":27},{"max_lines_index":28},{"max_lines_index":29}],"min_lines_each":[{"min_lines_index":0}],"style":"light","display_header":false,"syntax":"javascript","code":"@echo off\r\n\r\n@REM set the root directory of SQL Server tools.\r\nset MSSQLDIR=\"C:\\PROGRA~1\\MICROS~1\\CLIENT~1\\ODBC\\110\\Tools\"\r\n\r\n@REM set the server name.\r\nset DBHOSTNAME=zebra125.nonprod.hclpnp.com\r\n@REM set the path for log files\r\nset LOGFILEDIR=\"C:\\Installs\\Interact11WLNew\\Interact\\loaderService\\staging\"\r\n\r\n\r\nREM\r\nREM the following bcp command assumes these tokens passed into the command in this order:\r\nREM\r\nREM as specified in interact configuration property \"loaderCommandForAppend\"\r\n\r\n\"%MSSQLDIR%\\Binn\\bcp\" \"%1.%2\" in \"%3\" -e \"%LOGFILEDIR%\\bcpload_errorfile.txt\" -o \"%LOGFILEDIR%\\bcpload.log\" -S \"%DBHOSTNAME%\" -U \"%4\" -P \"%5\" -w -t\"|\"\r\n","highlight_active_line":false,"show_gutter":false,"display_indent_guides":false,"line_wrap":false,"use_soft_tabs":true,"max_lines":30,"min_lines":1});_Element.prototype.settings.page_element_id = "346754986560125028";_Element.prototype.element_id = "2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3";_Element.prototype.user_id = "102707030";_Element.prototype.site_id = "216674772855878839";_Element.prototype.assets_path = "//marketplace.editmysite.com/elements/270170748587580171-1.3.3/assets/";new _Element({el: '#element-2ba2fc8d-eb3c-4289-b1f4-bf9f63ba89c3'});});});}if (typeof document.documentElement.appReady == 'undefined') {document.documentElement.appReady = 0;}if (document.documentElement.appReady || (window.inEditor && window.inEditor())) {setupElement346754986560125028();} else if (document.createEvent && document.addEventListener) {document.addEventListener('appReady', setupElement346754986560125028, false);} else {document.documentElement.attachEvent('onpropertychange', function(event){if (event.propertyName == 'appReady') {setupElement346754986560125028();}});}
Senior Test Engineer



Hello Sir
I want to use same database(SQL server) with two users in single partition. Let’s say my database is ABC, and users are A & B. So accordingly I have created DSN ABC_A and ABC_B.
If I removes loader command…then unica system works absolutely fine as expected.
But whenever I am putting loader command….I am unable to write output to file/table with loader command.
So Sir, will you please suggest, what necessary changes should be done in following loader command.
<DATABASE> <TABLE> <DATAFILE> <USER> <PASSWORD> <CONTROLFILE>
Thanks Sir
This article really helped me !
With the help of it, I can configure same data source, with multiple users, in single partition now.