http://knowledgebase.progress.com/articles/Article/P119452
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.
- 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
********* everyline of the properties file requires a CRLF <Enter> character at the end of the line or the auxiliary connection will fail. See 000061265, Unable to access Auxiliary Database tables from a Multi-database SQL connection
- 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]
- A connection must be made the primary database using the ODBC DataSource or JDBC connection string.
- After a connection is made to the primary database all other databases listed for that configuration should be connected.
- To check what databases are connected issue the following SQL statement if the client allows statements to be made:
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>;