Status: Verified
GOAL:
Quick Guide to setting up Multi Database ODBC / JDBC connectivity
GOAL:
How to make an automatic multi-database connection with ODBC or JDBC?
GOAL:
How to make a multi-database connection with ODBC or JDBC?
GOAL:
How to connect to multiple databases using ODBC or JDBC driver
GOAL:
How to connect to multiple databases from OESQL
GOAL:
How to connect to multiple databases from SQL-92
GOAL:
Is it possible to connect to multiple databases using one DSN with ODBC?
GOAL:
Quick Guide to setting up Multi-Database ODBC / JDBC connectivity.
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1B
OpenEdge 10.1C
OpenEdge 10.2x
FIX:
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 served with separate TCP/IP ports.
The original datase(database1) is not included in the database1.oesql.properties file.
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
2) 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]
3) A connection must be made the primary database using the ODBC DataSource or JDBC connection string.
4) After a connection is made to the primary database all other databases listed for that configuration should be connected.
5) To check what databases are connected issue the following SQL statement if the client allows statements to be made:
show catalogs all
NOTE 1: Only the primary database may have changes made to it. All databases connected after the primary are read-only connections.
NOTE 2: Make sure you specify the full path for the databases when the databases are started via the AdminServer / Progress Explorer.
NOTE 3: All databases need to be of the same codepage for this configuration to function.
NOTE 4: 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.
NOTE 5: 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:
NOTE 6: 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.
[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.
and in 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)
.