Article

Quick Guide to setting up Multi Database ODBC / JDBC connectivity

« Go Back

Information

 
EnvironmentProduct: OpenEdge
Version: 10.1B, 10.1C, 10.2x, 11.x
OS: All Supported Operating Systems
Other: SQL92
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.
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution

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 database1.oesql.properties file mentioned below.

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

Example:

database1.oesql.properties

Example of the database1.oesql.properties file:
[sql-configuration]
     configuration-names-list=northeastSales, MLBSales

[configuration.northeastSales]
       database-id-list=MA, NH

[database.MA]
       Name=Massachusetts
       Catalog=Mass
       Location=/usr1/database2

[database.NH]
       Name=NewHampshire
       Catalog=NH
       Location=/usr1/database3

[configuration.MLBSales]
      database-id-list=Ny, Ma1

[database.Ny]
       Name= NewYork
      Catalog=Yankees
      Location=/usr1/database2
     
[database.Ma1]
       Name= Massachusetts
       Catalog=RedSox
       Location=/usr1/database3

 

  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>]

 

Example:
database1[-mdbq:northeastSales]

 

If using JDBC the connection string syntax is the following:
jdbc:datadirect:openedge://<hostname>:<portnumber>;databaseName=<databasename>[-mdbq:<configuration-name>]

Example:
jdbc:datadirect:openedge://localhost:6790;databaseName=database1[-mdbq:northeastSales]

 

  1. A connection must be made the primary database using the ODBC DataSource or JDBC connection string.
  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

Note:

  • Only the primary database may have changes made to it.  All databases connected after the primary are read-only connections.
  • Make sure you specify the full path for the databases when the databases are started via the AdminServer / Progress Explorer.
  • All databases need to be of the same codepage 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, you can 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>; 
Workaround
Notes
References to other documentation:
OpenEdge Data Management: SQL Development, Chapter "Performing Multi-database Queries"

Is using secondary login broker for SQL client, specify the SQL broker port in database1.oesql.properties

example:

[database.Ny]
       Name= NewYork
       Catalog=Yankees
       Location=/usr1/database2
       port=5555
Attachment 
Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful.



Characters Remaining: 255

 



Copyright © 1993-2014. Progress Software Corporation. All Rights Reserved. See the next generation of application development and data connectivity software, try now!