Did this article resolve your question/issue?



Quick Guide to setting up Multi Database ODBC / JDBC connectivity

« Go Back


TitleQuick Guide to setting up Multi Database ODBC / JDBC connectivity
URL NameP119452
Article Number000144791
EnvironmentProduct: OpenEdge
Version: 10.1B, 10.1C, 10.2x, 11.x
OS: All Supported Operating Systems
Other: SQL-92
Question/Problem Description
Quick Guide to setting up Multi Database ODBC / JDBC connectivity
How to make an automatic multi-database connection with ODBC or JDBC?
How to make a multi-database connection with ODBC or JDBC?
How to connect to multiple databases using ODBC or JDBC driver
How to connect to multiple databases from OESQL
How to connect to multiple databases from SQL-92
Is it possible to connect to multiple databases using one DSN with ODBC?
Quick Guide to setting up Multi-Database ODBC / JDBC connectivity.
How to configure auxiliar SQL databases as catalog databases. 
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number

For the purposes of this solution examples will be made referring to database1, database2 and database3 which are all copies of the sports2000 database.

All databases must be started with separate TCP/IP ports.

The primary database (database1) should not be included within the file mentioned below.

  1. In the directory where database1 resides create a file named <primarydatabasename>


Example of the file:
     configuration-names-list=northeastSales, MLBSales

       database-id-list=MA, NH

#NOTE:  database2 in the Location definition line is an example of an actual database name, it is not a directory.


      database-id-list=Ny, Ma1

       Name= NewYork
#NOTE:  database2 in the Location definition line is an example of an actual database name, it is not a directory.
       Name= Massachusetts
#NOTE:  database3 in the Location definition line is an example of an actual database name, it is not a directory.

********* everyline of the properties file requires a CRLF <Enter> character at the end of the line or the auxiliary connection will fail. See  Unable to access Auxiliary Database tables from a Multi-database SQL connection

  1. A modification to the ODBC datasource or JDBC connect string is necessary.  In the ODBC DataSource the following must be added to the database reference for the DatabaseName:  [-mdbq:<configuration-name>]




If using JDBC the connection string syntax is the following:



  1. A connection must be made the primary database using the ODBC DataSource or JDBC connection string. Progress/OpenEdge SQLEXP utility can be used to make the connection, by opening up the proenv and typing sqlexp <primarydbname>[-mdbq:<config name>] -S <portnumer> -user sysprogress -password sysprogress -H <host ip>
  2. After a connection is made to the primary database all other databases listed for that configuration should be connected.
  3. To check what databases are connected issue the following SQL statement if the client allows statements to be made:
show catalogs all


  • There is a limit of 16 databases in the MDBQ Catalog
  • Only the primary database may have changes made to it.  All databases connected after the primary are read-only connections.
  • Casing for <config name> in the connection string "<primary db>[-mdbq:<config name>]" and in the property value "configuration-names-list=<config name>" for the <primary db> file must match.
  • Make sure that the full path for the databases are specified when the databases are started via the AdminServer / Progress Explorer.
  • All databases need to be of the same codepage for this configuration to function.
  • All databases need to use the same collation for this configuration to function.
  • It is a requirement that the user name and the password used for the primary database must be the same for all database connections in a multi-db configuration.
  • A client connection is made to the secondary databases by the _sqlsrv2 process of the primary database. Note the entry in a secondary databases log file:

[2008/08/22@13:54.:01.321-0400] P-1620       T-2944  I Usr     6: (-----) Login usernum 6, federated SQL client
[2008/08/22@13:54:01.321-0400] P-1620       T-2944  I Usr     6: (7129)  Usr 6 set name to dave.

Snap shot of promon for a secondary database: promon dbname,1,1

User Control:
Usr    Name     Type   Wait  Table               Dbkey     Trans      PID   Sem      Srv     Login  Time
  0     dave       BROK    --      0                    0              0          1552     0         0      08/22/08 13:51
  5     dave      SQFA     --      0                    0              0           1620    0         0      08/22/08 13:54
  6     dave      SQFC     --      0                    0             1483      1620    0         5      08/22/08 13:54
  7     dave       MON      --      0                    0             0            2924   0         0      08/22/08 14:55

SQL Federated Agent (SQFA)
SQL Federated Client  (SQFC)

  • Read/write cannot be done to all databases from one DSN (data source). This really means wanting one server to update multiple databases, which requires some sort of 2 phase commit to work with database integrity.  OE SQL supports 2 phase commit only when JTA is used in a Java client-server setup, but this is done using multiple DSN's from a JDBC Java client.
Alternatively, use the following SQL statement to add catalog or auxiliary database connection.  The connection to the catalog database will be done using the same user profile as the main database.  The rules described above still apply.

CONNECT '<database_path/database_name>' AS CATALOG <catalog_name>; 
References to other documentation:

If using secondary login broker for SQL client, specify the SQL broker port in


       Name= NewYork
Last Modified Date11/20/2020 7:16 AM
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.