Connecting to a relational MR database using RDB DSC 2
Location connection property
When connecting to a relational MR database using RDB DSC 2, you need to specify the Location connection property using an OLE DB connection string. This should specify these parameters:
Parameters
Provider
This must be set to MSOLEDBSQL.1.
Integrated Security
This should be set to SSPI.
Persist Security
This should be set to False.
Initial Catalog
The name of the database.
Data Source
The name of the server.
Example
Here is an example of an OLE DB connection string that connects to the Short Drinks sample database:
Provider=MSOLEDBSQL.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=short_drinks;
Data Source=LocalHost
MR Init Custom connection property
The MR Init Custom connection property can be used to specify whether the connection should favor memory use or speed.
To set this property in the Data Link Properties dialog, select the All tab, and then double-click the MR Init Custom property. Type the required setting into the Property Value text box and then click OK.
The valid settings are:
FavorMemory
Uses server-side cursors so that a single SQL Server connection can be re-used across multiple commands. This mode is slower, but uses less memory (and fewer SQL Server connections) as the number of concurrent commands increases. Use the CursorFetchRows custom property to set the number of rows to fetch. Increasing the number of rows might increase performance.
FavorSpeed
Uses default cursors, requiring a new connection for each command. This mode is considerably faster than the FavorMemory mode, but does use more memory. The FavorSpeed option also optimizes binding look-up by creating a look-up vector. This is the default setting.
MaxCommandsPerConnection
Specifies the maximum number of commands that will share a single case data connection. By default, all commands open for a project (one command or recordset is created for each active interview) share the same database connection. When interviewing, if there is a high number of concurrent interviews for an individual project, database case date write performance can be impacted due to contention on the database connection. To reduce contention on the database connection, MaxCommandsPerConnection can be set for a project in DPM under Server > Projects > [ProjectName] > mrInterview > MaxCommandsPerConnection. Based on load testing, the suggested property value is 30.
Complete example
The following is a complete connection string for the Short Drinks sample database. The Data Source property is set to mrRdbDsc2 and the MR Init Customproperty is set to favor memory:
(Each connection property is presented on a separate line for clarity. In practice, specify the connection string without line breaks.)
Provider=mrOleDB.Provider.2;
Data Source=mrRdbDsc2;
Location="Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=short_drinks;Data
Source=LocalHost";Initial Catalog=[INSTALL_FOLDER]\IBM\SPSS\DataCollection\7\DDL\Data\Mdd\short_drinks.mdd;
MR Init Project=short_drinks;
MR Init Custom=FavorMemory
See also