DB-1: Multi-database query for ODBC and JDBC Brian Werne

DB-1: Multi-database query for ODBC and JDBC
Brian Werne
OpenEdge® SQL em um Ambiente 10.1B
multi-database para ODBC e JDBC
Claudemiro Pacheco
Technical Support Americas
Objetivos
Respostas para as seguintes perguntas:
ƒ O que é uma query multi-database?
ƒ O que acontece internamente?
ƒ Como deve ser configurado?
2
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Agenda
ƒ Visão Geral da configuração
ƒ Conexões e como identificar-las
ƒ Acesso à Dados – single e multi-database
ƒ Gerenciamento e Planejamento
3
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
1
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Multi-Database Query - client
Permite realizar um JOIN entre tabelas de múltiplos bancos de dados ( cross
database join)
Join: customers from DB, Orders from SecondDB, Inventory from
ThirdDB
SQL
Client
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
Customers
Inventory
Orders
DB
4
Third
DB
Second
DB
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
10.1B Multi-Database Query - server
Cross database join
SQL
Client
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
Host
Shmem
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
DB
5
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Terminologia
6
ƒ
Fields/Columns ( CustNum, CustName)
ƒ
Table
( Customer)
- collection of columns
ƒ
Schema ( PUB, bwerne)
- collection of tables
ƒ
Catalog (AuxCat1, mysports)
- Alias for database
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
2
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Multi-Database Query – Scope
Mysports
PUB schema
Customer
7
AuxCat1
Roltman
PUB
Inventory
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
3 part naming – single database connection
ƒ Sintaxe de 3 níveis (Já existente)
schema.table.column-name
ƒ Exemplo
SELECT Pub.Customer.CustNum,
Pub.Customer.Name,
Pub.Order.OrderNum …
ƒ Se aplica à: schema, tables, columns, stored
procedures
8
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
4 part naming – multi-database query
ƒ Sintaxe de 4 níveis
catalog.schema.table.column-name
ƒ Exemplo
SELECT Pub.Customer.CustNum,
SportsPrimary.Pub.Customer.Name,
SportsAux1.Pub.Order.OrderNum …
ƒ Se aplica à: schema, tables, columns, stored
procedures
9
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
3
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Crystal Reports – cross database join
Catalog
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"
10
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Single Database
simple query
SQL
Client
DBNavigator
- JDBC URL
Crystal Reports – ODBC DSN
Host
Shmem
OpenEdge SQL
Server
DB
11
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Suporte Multi-Database
Define bancos de dados primários e auxiliares
SQL
Client
DBNavigator - JDBC URL
Crystal Reports – ODBC DSN
Host
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
DB
Primary
12
Shmem
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Read
Only
Auxiliaries
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
4
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Agenda
ƒ Visão Geral da configuração
ƒ Conexões e como identificar-las
ƒ Acesso à Dados – single e multi-database
ƒ Gerenciamento e Planejamento
13
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Conexões Multi-database
2 métodos:
ƒ Conexões explícitas aos bancos de dados
auxiliares com o comando sql CONNECT
ƒ Modelo de conexão automática (“Automatic”)
14
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Conectando-se à um Banco de Dados Auxiliar
ƒ Catalog -
um alias para o banco de dados
ƒ Conectando-se à um banco de dados auxiliar
CONNECT ‘/usr/wrk/sports2000’ AS CATALOG mysports;
• Deve estar no mesmo servidor onde está o
banco de dados primário
ƒ Desconectando um catalog
DISCONNECT CATALOG mysports;
15
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
5
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Detalhes do Banco de Dados Auxiliar
Syntaxe
ƒ Default catalog é o Banco de Dados Primário
SET CATALOG mysports;
ƒ Listagem dos catálogos conectados
SHOW CATALOGS
ALL | { PRO_NAME | PRO_TYPE | PRO_STATUS }
16
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
O que fazer ?!?!
ƒ Algumas aplicações, como por exemplo
Crystal Reports, somente permitem que
comandos SELECT sejam executados
ƒ Não podemos utilizar o comando CONNECT:
O que fazer agora?
ƒ Utilize conexões automáticas
17
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Multi-Database : Conexões Automáticas
SQL
Client
DBNavigator
- JDBC URL
Crystal Reports – ODBC DSN
dbname[-mdbq:config]
Host
Shmem
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
Properties
DB
[config]
Primary
18
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Read
Only
Auxiliaries
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
6
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Arquivo de Propriedades - Exemplo
sports2000.oesql.properties
configuration
name
[sql-configuration]
configuration-names-list=config1
[configuration.config1]
database-id-list=Aux1, Aux2
[database.Aux1]
Full path to database
Name=SportsAux1
Catalog=AuxCat1
Location=c:\openedge\WRK\db\SportsAux1
[database.Aux2]
Name=SportsAux2
Catalog=AuxCat2
Location=c:\openedge\WRK\db\SportsAux2
19
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Multi-Database : Conexões Automáticas
SQL
Client
DBNavigator
- JDBC URL
Crystal Reports – ODBC DSN
dbname[-mdbq:config1]
Host
Shmem
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
Properties
DB
[config1]
Primary
20
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Read
Only
Auxiliaries
© 2007 Progress Software Corporation
Single connection : JDBC -DBNavigator
jdbc:datadirect:openedge://localhost:6748;databaseNa
me=sports2000
21
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
7
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Configuração Multi-DB:JDBC -DBNavigator
jdbc:datadirect:openedge://localhost:6748;databaseNa
me=sports2000 [-mdbq:config1]
22
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
DBNavigator – Configuração MDBQ
Auxiliary
databases
Primary
database
23
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
ODBC – single connection
24
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
8
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
ODBC – Configuração Multi-DB
25
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Exemplo do MDBQ no Crystal
Auxiliary databases
Primary database
26
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Todo bom projeto de engenharia tem os seus
acrônimos
Conexões primárias ou single database
ƒ SQSV
(OpenEdge SQL Server )
• É o processo servidor principal do OpenEdge
SQL
ƒ REMC
(Remote Client)
• É a thread cliente do OpenEdge SQL para
cada conexão ao banco primário
27
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
9
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Acrônimos
Conexões auxiliares
ƒ SQFA (SQL Federated Agent)
• Conexão principal do SQL ao banco auxiliar
ƒ SQFC
(SQL Federated Client)
• Client thread para cada conexão que é feita
ao banco auxiliar
28
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Tipos de Conexões
OpenEdge SQL
Server
connection
29
OpenEdge SQL
Client connection
Primary
SQSV
REMC
Auxiliary
SQFA
SQFC
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Conexões – usando MDBQ
OpenEdge SQL Server
Main
Thread
SQSV
SQFA
Primary
DB
REMC
Client 1
Thread
SQFC
REMC
Client 2
Thread
30
Auxiliary
DB
SQFC
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
10
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Promon – Banco Primário
Conexões ao Banco Primário
31
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Promon – Banco Auxiliar
Conexões aos Bancos Auxiliares
32
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
OpenEdge Management User Activity View
– Banco Primário
Conexões ao Banco Primário
33
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
11
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
OpenEdge Management User Activity View
– Bancos Auxiliares
Conexões aos Bancos Auxiliares
34
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
MDBQ Restrições, Dicas e Detalhes
ƒ
Restrição
• Os bancos de dados primário e auxiliares devem estar
no mesmo servidor
ƒ
Autenticação da conexão
• Usuário deve ser o mesmo para o banco primário e
para cada um dos auxiliares
ƒ
Código de página do banco de dados
• O Código de páginas dos bancos de dados deve ser o
mesmo
35
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
MDBQ Restrições, Dicas e Detalhes
ƒ
Conexões auxiliares são READ ONLY
• Nenhuma operação de update, seja ela à nível de
esquema ou registro, pode ser realizada nos bancos
auxiliares. Somente o banco primário aceitará
comandos de update.
ƒ
Permissões de Granting / revoking - autorização
• Deve ser realizada no banco auxiliar
independentemente
ƒ
Considerações de performance
• Update Statistics deve ser feito em cada um dos
bancos auxiliares independentemente
36
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
12
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Suporte ao Multi-Database Query
Read
Only
SQL
Client
dbname[-mdbq:config1]
Host
Shmem
Shmem
Second
DB
Shmem
Third
DB
OpenEdge SQL
Server
Properties
DB
[config1]
Primary
37
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
Auxiliaries
© 2007 Progress Software Corporation
Agenda
ƒ Visão Geral da configuração
ƒ Conexões e como identificar-las
ƒ Acesso à Dados – single e multi-database
ƒ Gerenciamento e Planejamento
38
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Planejamento – startup
ƒ
ƒ
39
Efeito do MDBQ no banco primário: startup parameters
• Efetivamente nenhuma mudança , faz uso do –Mi para
controlar o número de conexões/threads por server. Ainda
temos o SQSV (server slot (-Mi,Ma)) e o REMC ( user slot (n))
• OpenEdge SQL tem uma melhor performance com threads
(mais conexões por server)
Efeito do MDBQ no banco auxiliar: startup parameters
• SQFA é self-service (adicione 1 ao –n)
• Cada SQFC ocupa um user slot (Cada um deve ser
adicionado ao –n)
• SQFA, para cada SQSV, será servidor de múltiplos
contextos SQFC
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
13
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Shutdown do banco auxiliar
OpenEdge SQL Server
Main
Thread
SQSV
SQFA
Primary
DB
REMC
Client 1
Thread
SQFC
REMC
Client 2
Thread
40
Auxiliary
DB
SQFC
O broker do banco primário
não é afetado
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Shutdown do banco auxiliar
OpenEdge SQL Server
Main
Thread
SQSV
Primary
DB
Auxiliary
DB
41
O broker primário não é
afetado
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Shutdown do banco primário
OpenEdge SQL Server
Main
Thread
SQSV
SQFA
Primary
DB
REMC
Client 1
Thread
SQFC
REMC
Client 2
Thread
Auxiliary
DB
SQFC
O banco auxiliar não é afetado
42
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
14
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Shutdown of Primary database
Primary
DB
Auxiliary
DB
O banco auxiliar não é afetado
43
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Promon – Desconexão do SQFA
OpenEdge SQL Server
Main
Thread
SQSV
SQFA
Primary
DB
REMC
Client 1
Thread
SQFC
REMC
Client 2
Thread
Auxiliary
DB
SQFC
Os brokers do banco primário e auxiliar não são afetados
44
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Promon – Desconexão of SQFA
OpenEdge SQL Server
Main
Thread
SQSV
Primary
DB
Auxiliary
DB
Os brokers do banco primário e auxiliar não são afetados
45
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
15
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Promon – Desconexão do REMC ou SQFC
OpenEdge SQL Server
Main
Thread
SQSV
SQFA
Primary
DB
REMC
Client 1
Thread
SQFC
REMC
Client 2
Thread
Auxiliary
DB
SQFC
Os brokers do banco primário e auxiliar não são afetados
46
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Promon – Desconexão do REMC ou SQFC
OpenEdge SQL Server
Main
Thread
SQSV
SQFA
Primary
DB
REMC
Client 1
Thread
SQFC
Auxiliary
DB
Os brokers do banco primário e auxiliar não são afetados
47
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Sumário
ƒ Multi-database query
ƒ Configuração e conexões
ƒ Detalhes Internos
48
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
16
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
Para mais informações
ƒ
PSDN
ƒ
Progress eLearning Community
• Using OpenEdge SQL
ƒ
Documentation – 10.1B
• OpenEdge Data Management:
SQL Development
• OpenEdge Data Management:
SQL Reference
49
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Perguntas?
50
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Obrigado pela
atenção
51
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
17
DB-1: Multi-database query for ODBC and JDBC
Brian Werne
52
DB-1: OpenEdge SQL multi-database environment for ODBC and JDBC
© 2007 Progress Software Corporation
Progress Exchange 2007
10-13 June, Phoenix, AZ, USA
18