See below for Connection String Examples.
A source or destination can be configured using either an ODBC data source or a DSN-less connection string.
To configure a a DSN-less connection string:
Select the ODBC Driver radio button. The source or destination screen will now look like this:
Select an ODBC driver from the list.
The ODBC driver name will appear in the Source or Destination Name field above the DSN list. Be sure to modify this name to clearly identify the database; the name cannot be changed after it is saved.
Enter a valid connection string (maximus 254 characters); see below for examples.
Enter the User ID and Password of an account that has permission to obtain data from the selected server. If you are configuring a connection to a SQL Server that uses Windows authentication you do not need to enter the user ID and password; select the Use Integrated Security radio button to disable the user and password fields, and add Trusted_Connection=Yes to the connection string.
Click the Advanced tab if you want to modify the connection limits, configure AS/400 multi-member access, or configure a Source for incremental replication operations (SQDR Plus is required for incremental replication.)
Click OK to save the properties and return to the Replicator Manager window.
The following examples typically show the minimum required keywords to establish a connection; refer to the documentation for the ODBC driver of interest for full information.
In cases where multiple drivers are available for a DBMS type, the recommended driver is typically listed first. However, there are scenarios where an alternate driver may be recommended. Contact Stelo support and refer to the technical document SQDR: Recommended ODBC Drivers for advice.
SingleStore (formerly MemSQL)
HostName=myhostip;Server=MYRDB
HostName=myhostip;Port=446;Server=MYRDB;IsolationLevel=2;PkgColID=SQDR
HostName=myhostip;Netlib=SQSSL.DLL;Port=448;Server=MYRDB;IsolationLevel=2;PkgColID=SQDR
See the StarQuest Technical Document Using a DSN-less Connection with StarSQL for additional information.
It is very important to specify IsolationLevel=2 when a Db2 for i source is being used for incremental replication.
Note: for Microsoft 18 Driver, the default for SSL encryption (parameter Encrypt) is now Yes. You must either install a Certificate Authority certificate (if needed), set TrustServerCertificate=Yes, or set Encrypt=No or Optional.
Server=(local) or Server=. or Server=127.0.0.1 (for a local default instance)
Server=.\SQLEXPRESS (for a local named instance)
Server=MYHOST (for a default instance)
Server=MYHOST\SQLEXPRESS (for a named instance)
Server=MYHOST,<port> (for a default instance listening on a port other than 1433)
Server=MYHOST,<port>\SQLEXPRESS (for a named instance if SQL Browser service is not being used)
Server=MYSERVER;Trusted_Connection=Yes;Database=MyDatabase;Encrypt=Yes;
Use Trusted_Connection=Yes to use integrated Windows security.
Use Encrypt=Yes to specify an SSL connection; use Encrypt=No or Optional to specify a non-SSL connection.
Use Encrypt=Yes;TrustServerCertificate=yes to specify an SSL connection without verifying the server certificate. This is useful if the server certificate was issued by a Certificate Authority (CA) unknown to the client machine or the connection specifies an IP address or hostname other than the Common Name defined in the certificate.
Use Database to specify a default database; this can be overridden when creating subscriptions.
If you get a connection failure ("invalid connect string param"), it may be necessary to add a trailing semicolon.
Specify MultiSubnetFailover=Yes when connecting to a SQL Server availability group listener or Failover Cluster Instance.
HostName=localhost\SQLEXPRESS; EnableQuotedIdentifiers=1
During creation of a subscription using this driver as a destination, select Insert using ODBC rather than the default of Use native-loader function in the Replication Options of the Destination panel.
Azure SQL Database
In the Azure Portal, select the Overview panel for the SQL database.
Select Show database connection strings - this will display the connection string to use with the Microsoft ODBC 17 driver.
Here is an example; specifying the database here is important.
Server=tcp:myserver.database.windows.net,1433;Database=mydatabase;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
To use the SQDR bundled ODBC driver, the connection string will look something like this:
HostName=myserver.database.windows.net; EnableQuotedIdentifiers=1; Database=mydatabase
Go to the Advanced Panel of the source or destination and change Target Schema from the user name (e.g. myID@myaccount) to dbo or the desired schema name.
HostName=myhostip;PortNumber=1521;ServiceName=myservice.mydomain.com
HostName=myhostip;PortNumber=1521;SID=MYSID
If you are using a multitenant container database (CDB), use the ServiceName property to specify a connection to the PDB of interest. The ServiceName is a fully qualified name consisting of the PDB name followed by a domain name; use the command lsnrctl status on the Oracle server to list the service name expected by the Listener.
See Oracle SSL Connections for connecting with SSL (Secure Sockets Layer). This is required for Oracle Autonomous databases.
DBQ=MYSERVER (where MYSERVER is one of the configured TNS names)
or DBQ=//hostname:port/SID (for connecting without tnsnames.ora)
You can also use an Oracle Internet Directory (OID) entry in place of tnsnames.ora.
If you are using a multitenant container database (CDB), specify a connection to the pluggable database (PDB) of interest with:
DBQ=//hostname:port/ServiceName
Where ServiceName refers to the PDB of interest; see the previous section for a details about ServiceName.
SERVER=myhostip;PORT=3306;DATABASE=mydb;FOUND_ROWS=1;NO_SSPS=1
This example shows connecting via TLS/SSL to AWS RDS for MySQL, validating the certificate downloaded from AWS:
SERVER=mysql2.xxxx.us-east-1.rds.amazonaws.com;PORT=3306;DATABASE=mysql2;FOUND_ROWS=1;NO_SSPS=1;SSLMODE=VERIFY_CA;SSLCA=C:\aws\global-bundle.pem
If fetching large amounts of data from a MySQL source results in memory
issues for the SQDR service, add NO_CACHE=1
to the connection string
MySQL (SQDR bundled ODBC driver - MySQL Enterprise only)
HostName=myhostip;PortNumber=3306;Database=mydb
MariaDB/MySQL (MariaDB ODBC Driver)
SERVER=myhostip;PORT=3306;DATABASE=mydb;OPTIONS=2;CHARSET=utf8
SERVER=mysql2.xxxx.use-east1.rds.amazonaws.com;PORT=3306;DATABASE=mysql2;OPTIONS=2;CHARSET=utf8;FORCETLS=1;SSLCA=C:\bin\aws\global-bundle.pem
OPTIONS=2 is the equivalent of MySQL FOUND_ROWS=1 - Return matched rows
instead of affected rows. This is only needed for destination.
HostName=myhost;PortNumber=9088;Database=mydb
If you are running SQDR on Linux, append Client_Locale=en_US.utf8 to the connection string:
HostName=myhost;PortNumber=9088;Database=mydb;Client_Locale=en_US.utf8
Server=myconn;Host=myhost;Service=9088;Protocol=olsoctcp;Database=mydb;DELIMIDENT=Y
The use of DELIMIDENT is not required if the server has been configured
with DELIMIDENT (e.g. in an environment variable), or Quote
SQL Identifiers is disabled in the Subscription Wizard panel of
SQDR Service properties
If you are running SQDR on Linux, append Client_Locale=en_US.utf8 to the
connection string:
Server=myconn;Host=myhost;Service=9088;Protocol=olsoctcp;Database=mydb;DELIMIDENT=Y;Client_Locale=en_US.utf8
OR (using Setnet32):
Server=myconn;DATABASE=mydb
where myconn is the name of a connection configured with Informix Setnet32:
Hostname=myhost
Protocolname=olsoctcp
Service Name=9088
Informix (IBM Db2 ODBC driver)
HOSTNAME=myhost;PORT=9089;DATABASE=mydb
PostgreSQL (SQDR bundled ODBC driver)
HostName=myhost;Database=mydatabase;PortNumber=5432
This example shows connecting via TLS/SSL
to Azure Database for Postgres without validating the certificate:
HostName=mypostgres.postgres.database.azure.com;Database=postgres;PortNumber=5432;EncryptionMethod=1;ValidateServerCertificate=0,
This example shows connecting via TLS/SSL to AWS RDS for Postgres, validating the certificate downloaded from AWS, and requiring TLS 1.2:
HostName=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;PortNumber=5432;EM=1;VSC=1;TrustStore=C:\aws\global-bundle.pem;CPV=TLSV1.2
The following properties are automatically added by SQDR when using a connection string:
FetchTSWTZasTimestamp=1;MaxCharSize=255;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;BatchMechanism=2
PostgreSQL (psqlODBC driver)
Servername=myhost;Database=mydatabase;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364
Servername=mypostgres.postgres.database.azure.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=require
Servername=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=verify-ca;pqopt={sslrootcert=C:\\aws\\global-bundle.pem}
SERVER=houmsqlfin23.eogresources.com;PORT=3306;DATABASE=db2_mirror;OPTIONS=70254610;CHARSET=utf8
Note: The OPTIONS parameter decompiles to the following individually configurable settings, where the (n) is the power of 2 value used to construct "Options" bit mask):
MULTIPLE_STATEMENTS(26)
FORWARD_ONLY (21)
NO_CACHE(20)
NO_PROMPT(4)
FOUND_ROWS(1)
Apache Spark/Databricks (Simba driver, available from Databricks)
Host=adb-1234567276565967.7.azuredatabricks.net;Port=443;SSL=1;AuthMech=3;ThriftTransport=2;HTTPPath=sql/protocolv1/o/1239854276565967/0307-232555-cxx91kvv
The userID should be specified as token, and the password is a token generated in the Databricks web GUI - select Settings/User Settings/Access Tokens.
Limitations: Does not support truncate OR delete, so only option is to append or recreate on each run of a snapshot. Does not support creation of an index over the destination.
EXAHOST=demodb.exasol.com
Servername=myhost;Port=5480;Database=MYDATABASE
DBQ=MYDATABASE;ServerName=myhost.1583;TCPPort=1583
Amazon Redshift (SQDR bundled ODBC driver)
HostName=myhostip;PortNumber=5439;Database=mydb
Salesforce (SQDR bundled ODBC driver)
HostName=login.salesforce.com;TransactionMode=1;SQLEngineMode=2
Note: when connecting to Salesforce, the password field should contain
an authentication token appended to the password - PasswordAuthenticationToken.
The token is a case-sensitive alphanumeric code that’s tied to your password.
Whenever your password is reset, your security token is also reset.
To reset the authentication token: In the Salesforce web interface, go
to your personal settings. Enter
Reset in the Quick
Find box, then select Reset My
Security Token. The new security token is sent to the email address
in your Salesforce personal settings.
To specify the location of the schema map files created and maintained by the driver, specify SchemaMap or SMP. Note that older documentation (prior to March 2021) incorrectly shows the short name as SM rather than SMP.
HostName=login.salesforce.com;TM=1;SEM=2;SMP=C:\mapfiles\mySFserver.conf
Here is an example using a proxy server listening on port 3128:
HostName=login.salesforce.com;TM=1;SEM=2;PXHN=myproxyserver;PXPT=3128
SERVER=hostname.snowflakecomputing.com;DATABASE=MYDB;WAREHOUSE=MYWH
Catalog=potent-veld-386317;Email=sqdr-access@potent-veld-386317.iam.gserviceaccount.com;KeyFilePath=C:\ProgramData\StarQuest\sqdr\potent-veld-386317-9e83f0afa52a.json;OAuthMechanism=0;EnableSession=1
Note: EnableSession=1 is a required parameter for SQDR. This parameter permits the use of Transactions.
Database=C:\mydest;AllowUpdateAndDelete=1
or
Database=C:\mydest
or
Database=L:\Transfer;AllowUpdateAndDelete=1;TableType=Character;Delimiter=|;DataFileExtension=CSV;FirstLineNames=1
For details, see Using the SQDR Text Driver.
Server=myhost;Port=8629;Database=mydb
System=mysystem; CommitMode=1; DefaultLibraries={QGPL,SQDR}
where mysystem is the name of a system that has been configured with System i Navigator.
It is important to specify CommitMode=1 (*CS - read committed) when a Db2 for i source is being used for incremental replication.
Using DefaultLibraries is optional, but simplifies configuration when using SQDR Plus.
System=mysystem;CommitMode=1;DefaultLibraries={QGPL,SQDR};Database=IASP1
where IASP1 is the name of a database name (RDB entry) associated with an Independent Auxiliary Storage Pool (IASP).
Hostname=myhostip;port=446;Database=MYRDB;Protocol=TCPIP;LongDataCompat=1;Authentication=SERVER
Hostname=myhostip;port=50000;Database=MYRDB;Protocol=TCPIP;LongDataCompat=1;Authentication=ENCRYPT
LOB considerations:
LongDataCompat=1 is required for correct replication of LOB data types. When replicating larger LOB columns, set LOBMaxColumnSize and the SQDR service property Inderterminant Column Size to the maximum value to be used.
SSL:
Hostname=myhostip;port=50448;Database=MYRDB;Protocol=TCPIP;;LongDataCompat=1;Authentication=ENCRYPT;
SECURITY=SSL;
Ssl_client_keystoredb=C:\mySSLdir\mykeystore.kdb;
Ssl_client_keystash=C:\mySSLdir\mykeystore.sth
Make sure that the stash file (mykeystore.sth) is readable by the user SYSTEM (or whatever user ID the SQDR service is running as).