db-1: openedge ® sql in a 10.1b multi- database environment for odbc and jdbc brian werne sr....
TRANSCRIPT
![Page 1: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/1.jpg)
DB-1: OpenEdge® SQL in a 10.1B multi-database environment for ODBC and JDBC
Brian WerneSr. Engineering Manager,
OpenEdge SQL and OpenEdge Management
![Page 2: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/2.jpg)
© 2007 Progress Software Corporation2 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Objectives
Answers for the following:
What is multi-database query?
What’s going on under the covers?
How do I manage the configuration?
![Page 3: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/3.jpg)
© 2007 Progress Software Corporation3 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Agenda
Overview of multi-database configurations
Connections and how they show up
Data access – single and multi-database
Management and planning
![Page 4: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/4.jpg)
© 2007 Progress Software Corporation4 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-Database Query - client
DB
SQL Client
Second DB
ThirdDB
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
Allow the ability to join tables in multiple databases ( cross database join)
CustomersOrders Inventory
Join: customers from DB, Orders from SecondDB, Inventory from ThirdDB
![Page 5: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/5.jpg)
© 2007 Progress Software Corporation5 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
10.1B Multi-Database Query - server
OpenEdge SQLServer
DB
SQL Client
Host
Shmem
Second DB
Third DB
Shmem
DBNavigator - JDBC URL
Shmem
Crystal Reports – ODBC DSN
Cross database join
![Page 6: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/6.jpg)
© 2007 Progress Software Corporation6 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Fields/Columns ( CustNum, CustName)
Table ( Customer)
- collection of columns
Schema ( PUB, bwerne)
- collection of tables
Catalog (AuxCat1, mysports)
- Alias for database
Terminology
![Page 7: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/7.jpg)
© 2007 Progress Software Corporation7 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-Database Query – Scope
PUB schema PUB
Customer Inventory
bwerne
Mysports AuxCat1
![Page 8: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/8.jpg)
© 2007 Progress Software Corporation8 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Three level naming convention
Example
Applies to schema, tables, columns, stored procedures
3 part naming – single database connection
Three level naming (already have this)
schema.table.column-name
SELECT Pub.Customer.CustNum,
Pub.Customer.Name,
Pub.Order.OrderNum …
![Page 9: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/9.jpg)
© 2007 Progress Software Corporation9 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Four level naming convention
Example
Applies to schema, tables, columns, stored procedures
4 part naming – multi-database query
Four level naming
catalog.schema.table.column-name
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
![Page 10: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/10.jpg)
© 2007 Progress Software Corporation10 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
SELECT "Customer1"."CustNum", "Customer1"."Name", "Order1"."OrderDate", "Order1"."Ordernum", "OrderLine1"."Itemnum", "OrderLine1"."Qty", "OrderLine1"."OrderLineStatus"
FROM ("AuxCat2"."PUB"."OrderLine" "OrderLine1" INNER JOIN "AuxCat1"."PUB"."Order" "Order1" ON "OrderLine1"."Ordernum"="Order1"."Ordernum") INNER JOIN "SPORTS2000"."PUB"."Customer" "Customer1" ON "Order1"."CustNum"="Customer1"."CustNum"
ORDER BY "Customer1"."CustNum"
Crystal Reports – cross database join
Catalog
![Page 11: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/11.jpg)
© 2007 Progress Software Corporation11 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Single Database
simple query
OpenEdge SQLServer
DB
SQL Client
Host
Shmem
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
![Page 12: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/12.jpg)
© 2007 Progress Software Corporation12 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-Database Support
OpenEdge SQLServer
DB
Primary
SQL Client
Host
Shmem
Auxiliaries
Second DB
Third DB
Read Only
Shmem
DBNavigator - JDBC URL
Shmem
Crystal Reports – ODBC DSN
Define primary and auxiliary database
![Page 13: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/13.jpg)
© 2007 Progress Software Corporation13 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Agenda
Overview of multi-database configurations
Connections and how they show up
Data access – single and multi-database
Management and planning
![Page 14: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/14.jpg)
© 2007 Progress Software Corporation14 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-database connections
Two methods:
Explicit connections to auxiliary databases with CONNECT sql statement
“Automatic” connection model
![Page 15: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/15.jpg)
© 2007 Progress Software Corporation15 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Connecting to an Auxiliary Database
Catalog - an alias for a database
Connecting to an auxiliary database
• Must be on same host as Primary database
Disconnecting a catalog
CONNECT ‘/usr/wrk/sports2000’ AS CATALOG mysports;
DISCONNECT CATALOG mysports;
![Page 16: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/16.jpg)
© 2007 Progress Software Corporation16 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Auxiliary Database Details
Default catalog is the Primary database
Listing available, currently connected catalogs
Syntax
SET CATALOG mysports;
SHOW CATALOGS
ALL | { PRO_NAME | PRO_TYPE | PRO_STATUS }
![Page 17: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/17.jpg)
© 2007 Progress Software Corporation17 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Say What ?!?!
Some applications, like Crystal Reports, only allow you to issue SELECT statements
Can’t use the CONNECT statement:
Now what?
Use automatic connections
![Page 18: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/18.jpg)
© 2007 Progress Software Corporation18 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-Database : automatic connections
Properties[config]
OpenEdge SQLServer
dbname[-mdbq:config]
DB
Primary
SQL Client
Host
Shmem
Auxiliaries
Second DB
Third DB
Read Only
Shmem
Shmem
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
![Page 19: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/19.jpg)
© 2007 Progress Software Corporation19 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Automatic connections
How does solution this work? What is a property file? What does the property file contain?
![Page 20: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/20.jpg)
© 2007 Progress Software Corporation20 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Property file example
[sql-configuration] configuration-names-list=config1[configuration.config1] database-id-list=Aux1, Aux2[database.Aux1] Name=SportsAux1 Catalog=AuxCat1 Location=c:\openedge\WRK\db\SportsAux1[database.Aux2] Name=SportsAux2 Catalog=AuxCat2 Location=c:\openedge\WRK\db\SportsAux2
sports2000.oesql.properties
Full path to database
configuration name
![Page 21: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/21.jpg)
© 2007 Progress Software Corporation21 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-Database : automatic connections
Properties[config1]
OpenEdge SQLServer
dbname[-mdbq:config1]
DB
Primary
SQL Client
Host
Shmem
Auxiliaries
Second DB
Third DB
Read Only
Shmem
Shmem
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
![Page 22: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/22.jpg)
© 2007 Progress Software Corporation22 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
jdbc:datadirect:openedge://localhost:6748;databaseName=sports2000
Single connection : JDBC -DBNavigator
![Page 23: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/23.jpg)
© 2007 Progress Software Corporation23 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
jdbc:datadirect:openedge://localhost:6748;databaseName=sports2000
Multi-DB configuration:JDBC -DBNavigator
[-mdbq:config1]
![Page 24: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/24.jpg)
© 2007 Progress Software Corporation24 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
DBNavigator – MDBQ configuration
Primary database
Auxiliary databases
![Page 25: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/25.jpg)
© 2007 Progress Software Corporation25 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
ODBC – single connection
![Page 26: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/26.jpg)
© 2007 Progress Software Corporation26 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
ODBC – Multi-DB configuration
![Page 27: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/27.jpg)
© 2007 Progress Software Corporation27 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Crystal – looks like this with MDBQ
Auxiliary databases
Primary database
![Page 28: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/28.jpg)
© 2007 Progress Software Corporation28 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Every good engineering project has acronyms (EGEPHA)
Primary or single database connections SQSV (OpenEdge SQL Server )
• Main OpenEdge SQL server process connection to storage engine
REMC (Remote Client) • OpenEdge SQL client thread for each
connection to the primary database
![Page 29: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/29.jpg)
© 2007 Progress Software Corporation29 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Under the covers - acronyms
Auxiliary connections SQFA (SQL Federated Agent)
• Main OpenEdge SQL connection to the Auxiliary database
SQFC (SQL Federated Client)• OpenEdge SQL client thread for each
connection to the auxiliary database
![Page 30: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/30.jpg)
© 2007 Progress Software Corporation30 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Connection types
OpenEdge SQL Server connection
OpenEdge SQL Client connection
Primary SQSV REMC
Auxiliary SQFA SQFC
![Page 31: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/31.jpg)
© 2007 Progress Software Corporation31 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Connections – using MDBQ
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
Client 1 Thread
Client 2 Thread
SQSV
SQFA
REMC
REMC
SQFC
SQFC
![Page 32: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/32.jpg)
© 2007 Progress Software Corporation32 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Promon – Primary Database
Primary database connections
![Page 33: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/33.jpg)
© 2007 Progress Software Corporation33 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Promon – Auxiliary Database
Auxiliary database connections
![Page 34: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/34.jpg)
© 2007 Progress Software Corporation34 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
OpenEdge Management User Activity View – Primary DatabasePrimary database connections
![Page 35: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/35.jpg)
© 2007 Progress Software Corporation35 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
OpenEdge Management User Activity View – Auxiliary DatabaseAuxiliary database connections
![Page 36: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/36.jpg)
© 2007 Progress Software Corporation36 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
MDBQ Tips/Restrictions/Gotchas!!
Restriction • Primary and auxiliary databases must reside
on same host
Connection authentication• User credentials must be same on Primary
and each Auxiliary
Database codepage• Codepage of databases must be the same
![Page 37: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/37.jpg)
© 2007 Progress Software Corporation37 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
MDBQ Tips/Restrictions/Gotchas!!
Auxiliary connections are READ ONLY• No update operations, schema or record based, can be
performed in the auxiliary databases. Only the primary database will accept update statements.
Granting / revoking permissions - authorization• Must be done on auxiliary database independently
Performance considerations• Update Statistics must be done on each auxiliary
database independently
![Page 38: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/38.jpg)
© 2007 Progress Software Corporation38 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Multi-Database Query Support
Properties[config1]
OpenEdge SQLServer
dbname[-mdbq:config1]
DB
Primary
SQL Client
Host
Shmem
Auxiliaries
Second DB
Third DB
Shmem
Shmem
Read Only
![Page 39: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/39.jpg)
© 2007 Progress Software Corporation39 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Overview of multi-database configurations
Connections and how they show up
Data access – single and multi-database
Management and planning
Agenda
![Page 40: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/40.jpg)
© 2007 Progress Software Corporation40 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Planning – startup
Effect of MDBQ: Primary DB startup parameters• Effectively no change from single connection,
make use of –Mi to control number of connections/threads per server. Still have SQSV (server slot (-Mi,Ma)) and REMC ( user slot (-n))
• Reminder: OpenEdge SQL does better with threads ( more connections per server)
Effect of MDBQ: Auxiliary DB startup parameters• SQFA is self-service (add one to –n) • Each SQFC takes up a user slot (each add to –n)• SQFA, for each SQSV, will host multiple SQFC
contexts
![Page 41: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/41.jpg)
© 2007 Progress Software Corporation41 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Shutdown of Auxiliary database
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
SQFA
Client 1 Thread
REMC
SQFC
Client 2 Thread
REMC
SQFC
Primary DB broker unaffected
![Page 42: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/42.jpg)
© 2007 Progress Software Corporation42 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Shutdown of Auxiliary database
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
Primary DB broker unaffected
![Page 43: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/43.jpg)
© 2007 Progress Software Corporation43 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Shutdown of Primary database
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
SQFA
Client 1 Thread
REMC
SQFC
Client 2 Thread
REMC
SQFC
Auxiliary DB broker unaffected
![Page 44: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/44.jpg)
© 2007 Progress Software Corporation44 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Shutdown of Primary database
Primary DB
Auxiliary DB
Auxiliary DB broker unaffected
![Page 45: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/45.jpg)
© 2007 Progress Software Corporation45 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Promon – Disconnect of SQFA
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
SQFA
Client 1 Thread
REMC
SQFC
Client 2 Thread
REMC
SQFC
Primary and Auxiliary DB broker unaffected
![Page 46: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/46.jpg)
© 2007 Progress Software Corporation46 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Promon – Disconnect of SQFA
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
Primary and Auxiliary DB broker unaffected
![Page 47: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/47.jpg)
© 2007 Progress Software Corporation47 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Promon – Disconnect of REMC or SQFC
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
SQFA
Client 1 Thread
REMC
SQFC
Client 2 Thread
REMC
SQFC
Primary and Auxiliary DB broker unaffected
![Page 48: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/48.jpg)
© 2007 Progress Software Corporation48 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Promon – Disconnect of REMC or SQFC
OpenEdge SQL Server
Primary DB
Auxiliary DB
Main Thread
SQSV
SQFA
Client 1 Thread
REMC
SQFC
Primary and Auxiliary DB broker unaffected
![Page 49: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/49.jpg)
© 2007 Progress Software Corporation49 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
In Summary
Multi-database query
Manage the configuration
Under the covers
![Page 50: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/50.jpg)
© 2007 Progress Software Corporation50 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
For More Information, go to…
PSDN
Progress eLearning Community• Using OpenEdge SQL
Documentation – 10.1B• OpenEdge Data Management:
SQL Development• OpenEdge Data Management:
SQL Reference
![Page 51: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/51.jpg)
© 2007 Progress Software Corporation51 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Other Exchange Sessions
DB-10: What’s New in OpenEdge 10.1B RDBMS
DB-11: Moving to OpenEdge COMP-10: OpenEdge Management and
Replication DB-21: Data Management Roadmap and
Info Exchange
![Page 52: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/52.jpg)
© 2007 Progress Software Corporation52 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Questions?
![Page 53: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/53.jpg)
© 2007 Progress Software Corporation53 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Thank you foryour time
![Page 54: DB-1: OpenEdge ® SQL in a 10.1B multi- database environment for ODBC and JDBC Brian Werne Sr. Engineering Manager, OpenEdge SQL and OpenEdge Management](https://reader033.vdocuments.us/reader033/viewer/2022061516/56649eef5503460f94bff7f6/html5/thumbnails/54.jpg)
© 2007 Progress Software Corporation54 DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC