Heterogene Zugriffe leicht gemacht

Heterogene Zugriffe leicht gemacht
Oracle Database Gateways
Thomas Niewel
Leitender Systemberater STU
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for
information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied
upon in making purchasing decisions. The development, release, and timing of any
features or functionality described for Oracle’s products remains at the sole discretion
of Oracle.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Oracle
|
Confidential – Internal/Restricted/Highly Restricted 3
Agenda
1
Oracle Database Gateways
2
Anwendungsfälle Oracle Database Gateways
3
Funktionsweise von Database Gateways
4
Do‘s and Dont‘s
5
Oracle Database Provider for DRDA
6
SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
4
Oracle Database Gateways
Architektur
• Informationen
• http://www.oracle.com/technetwork/database/gateways/index.html
• Empfehlung: Version >= 11.2.04
Client
Database
Gateway
Installation auf dedizierten Server möglich
Oracle
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Non Oracle
Database
Oracle Database Gateways
• Database Gateway for ODBC
• Database Gateway for Sybase
• Database Gateway for SQL Server
• Database Gateway for Teradata
• Database Gateway for Informix
• Database Gateway for DRDA
• Database Gateway for APPC *
• Database Gateway for Websphere MQ *
* Procedural Gateway
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Database Gateway for ODBC vs. “Spezielle” Gateways
DG4ODBC
Distributed transactions
Stored procedures
Additional software (ODBC Driver)
Enhanced DD translation
Explain Plan
Use of FDS Optimizer Statistics
SQL Optimizations
“Spezielle”
No
No
Yes
No *
No
No *
No
Yes
Yes
No
Yes
Yes
Yes
Yes
* Abhängig vom ODBC Treiber
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle Database Gateways - Hochverfügbarkeit
Gateway
Oracle
System 1
DG
Instance 1
Listener 1
DB21 =
(DESCRIPTION =
FDS
Gateway
(address_LIST=
(LOAD_BANANCE=ON)
(FAILOVER=ON)
(ADDRESS = (PROTOCOL = TCP)(PORT=1860)(HOST=demo1.de.oracle.com))
(ADDRESS = (PROTOCOL = TCP)(PORT=1861)(HOST=demo2.de.oracle.com)))
(CONNECT_DATA =
(SID =GWI1)
)
System 2
DG
Instance 2
Listener 2
(HS=ok)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle Database Gateways in einer RAC Umgebung
DB21 =
(DESCRIPTION =
NODE 1
Gateway
Oracle
RAC
System 1
Instance 1
(address_LIST=
(LOAD_BANANCE=ON)
(FAILOVER=ON)
DG
Instance 1
Listener 1
NODE 2
Gateway
(ADDRESS = (PROTOCOL =
(ADDRESS = (PROTOCOL =
(CONNECT_DATA =
(SID =GWI1)
)
(HS=ok
Oracle
RAC
System 2
DG
Instance 2
Instance 2
Listener 2
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
FDS
Oracle Database Gateways in einer RAC Umgebung
DB21 =
(DESCRIPTION =
NODE 1
Gateway
Oracle
RAC
System 1
Instance 1
(address_LIST=
(LOAD_BANANCE=ON)
(FAILOVER=ON)
DG
Instance 1
Listener 1
NODE 2
Gateway
(ADDRESS = (PROTOCOL =
(ADDRESS = (PROTOCOL =
(CONNECT_DATA =
(SID =GWI1)
)
(HS=ok
Oracle
RAC
System 2
DG
Instance 2
Instance 2
Listener 2
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
FDS
Agenda
1
Oracle Database Gateways
2
Funktionsweise Oracle Database Gateways
3
Do‘s and Dont‘s
4
Anwendungsfälle Oracle Database Gateways
5
Oracle Database Provider for DRDA
6
SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
11
Oracle Database Gateways – Funktionalität
• Implementierung über Oracle Heterogenous Services
• Mapping der Datentypen
• FDS Result Set Support
• FDS Data Dictionary Emulation
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle Database Gateways – Funktionalität
• Zugriff auf Fremddatenbanken über Database Links
Select * from CREATOR.NAME@DB2
• Oracle SQL
Select substr(C1,-5,2) from CREATOR.NAME@DB2
• Verteilte Transaktionen
Update tab1@ORACLE set sal=sal+1;
Update tab2@DB2 set sal=sal+2;
Commit;
• Verteilte Joins
Select a.f1,b.f2 from tab1@oracle a, tab2@DB2 where a.key=b.key
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle Database Gateways – Funktionalität
• Locking Modell von Fremddatenbanken
• Oracle Multi Version Concurrency Modell vs. Locking Modell von
Fremddatenbanken
• Oracle CBO vs. Optimizer von Fremddatenbanken
• Oracle SQL Dialekt vs. SQL Dialekt von Fremddatenbanken
• Funktionen
• SQL Syntax Elemente
• Stored Procedures
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Agenda
1
Oracle Database Gateways
2
Funktionsweise Oracle Database Gateways
3
Do‘s and Dont‘s
4
Anwendungsfälle Oracle Database Gateways
5
Oracle Database Provider for DRDA
6
SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
15
Do‘s and Dont‘s
Compatible Functions
Oracle
DB2
Step 2
Step 1
select max(Salary) from
tab1@DB2 where
Name= ‘MAIER‘
select max(salary) from
tab1 where
Name= ‘MAIER‘
Step 3
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Translated Functions
Oracle
DB2
Step 1
select name,age from
tab1@DB2 where
nvl(age,18)= 18
Step 2
select name,age from
tab1 where
value(age,18)= 18
Step 3
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Compensated Functions
Oracle
DB2
Step 2
Step 1
select * from tab@DB2
where
substr(ename,-5,1) ='S'
Step 4
select * from tab
where
substr(ename,-5,1) ='S'
select * from tab
Step 3
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
• Native Semantics (DG4DRDA only)
– HS_FDS_CAPABILITY={FUNCTION/{ON|OFF}} [OFF=DEFAULT]
ON=Funktionsunterschiede werden nicht mehr kompensiert
– HS_FDS_CAPABILITY=SUBSTR/OFF
select * from tniewel.emp1@ds3l where substr(ename,-3,1)='A‘
– Funktion wird auf Oracle Seite ausgeführt
– HS_FDS_CAPABILITY=SUBSTR/ON
select * from tniewel.emp1@ds3l where substr(ename,-3,1)='A‘
– Funktion wird auf DB2 Seite ausgeführt und nicht übersetzt
– ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:[Oracle][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and
z/OS]SECOND OR THIRD ARGUMENT OF SUBSTR FUNCTION OUT OF RANGE.
{HY000,NativeErr =-138}
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
• Katalogstatistiken der Fremddatenbank
•
HS_FDS_SUPPORT_STATISTICS=TRUE
•
Table Cardinality
•
Key Column Cardinality
• Shared Read Locks der Fremddatenbank
• Freigabe von Locks: Häufige Commits notwendig
• Gateway Initialisierungsparameter: HS_IDLE_TIMEOUT=xx
– FDS Thread/session wird nach xx Minuten abgebrochen
• HS_FDS_TRANSCTION_ISOLATION=READ_UNCOMMITTED
• Dirty Reads
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Debugging / Tuning
• SQL_TRACE, tkprof
• Gateway Trace
• Initialisierung Parameter: HS_FDS_TRACE_LEVEL=on/debug
• Explain Plan
• Explain plan for select ....table@gateway
• SELECT * FROM Table (DBMS_XPLAN.DISPLAY('plan_table', null, 'ALL'));
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
SQLTRACE
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Explain Plan
explain plan select a.* from tniewel.emp@dsnt a, tniewel.xemp@dsnt b
where a.empno=b.empno
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Gateway Trace
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Do‘s and Dont‘s
Optimierung von Bulk Operationen
array fetch
HS_RPC_FETCH_SIZE
Oracle Server
HS_RPC_FETCH_ROWS
Database Gateway
Foreign data store
Empfehlung: Nutzung der default Settings
ODBC Treiber Abhängigkeiten(DG4ODBC)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Agenda
1
Oracle Database Gateways
2
Funktionsweise Oracle Database Gateways
3
Do‘s and Dont‘s
4
Anwendungsfälle Oracle Database Gateways
5
Oracle Database Provider for DRDA
6
SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
28
Anwendungsfälle Oracle Database Gateways
Anwendungen mit synchronem Datenzugriff auf Fremddatenbanken
• Alles ist möglich !
• OLTP
• DWH
• Vereinfachung von Load Prozessen
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Anwendungsfälle Oracle Database Gateways
Load von FDS Daten
• CTAS
• Insert
• sqlplus
• Copy statement
• Materialized Views
• Full Refresh
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Anwendungsfälle Oracle Database Gateways
Load von FDS Daten
• Parallelisierung mit der Oracle Datenbank >= 11g
• Nutzung von dbms_hs_parallel
• LOAD_TABLE
•
Parallel load der „remote tables“
• CREATE_TABLE_TEMPLATE
•
Erstellen „local Table“ auf Basis einer „remote Table“
• CREATE_OR_REPLACE_VIEW
Erstellen einer „read only View“ zum parallelisierten Lesen der „remote Daten“
• DROP_VIEW
•
Löschen von Objekten, die mit CREATE_OR_REPLACE_VIEW erstellt wurden
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Anwendungsfälle Oracle Database Gateways
Load von FDS Daten
Voraussetzung für eine Parallelisierung ist eine der folgenden
Eigenschaften
• Die „remote Table“ ist „range partitioned“
• Histogramm für eine numerische Spalte ist vorhanden
• Index oder Primärschlüssel auf einer numerischen Spalte ist
vorhanden
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Agenda
1
Oracle Database Gateways
2
Funktionsweise Oracle Database Gateways
3
Do‘s and Dont‘s
4
Anwendungsfälle Oracle Database Gateways
5
Oracle Database Provider for DRDA
6
SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
33
Oracle Database Provider for DRDA
Verfügbar für DB2 z/OS, System i, LUW
Klassische DB2 Architektur
Cobol
PLI
CICS/TS
IMS/TM
DB2
TSO
Batch
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle Database Provider for DRDA
Verfügbar für DB2 z/OS, System i, LUW
Modifizierte DB2 Architektur
Cobol
PLI
CICS/TS
IMS/TM
DB2
Database
Provider
for
DRDA
TSO
Batch
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle
Database Provider for DRDA - Architektur
Database
Provider for
DRDA
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Integration in DB2 for z/OS
Insert in die DB2 Communications Database
INSERT INTO SYSIBM.IPNAMES (LINKNAME,SECURITY_OUT,USERNAMES,IPADDR)
VALUES ('TNIEWEL','P','O','SCCLOUD026.DE.ORACLE.COM');
INSERT INTO SYSIBM.USERNAMES(TYPE,AUTHID,LINKNAME,NEWAUTHID,PASSWORD)
VALUES ('O',' ','TNIEWEL','SCOTT', 'tiger' );
INSERT INTO SYSIBM.LOCATIONS (LOCATION,LINKNAME,PORT)
VALUES ('DRDAAS_TNIEWEL','TNIEWEL','1899');
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Integration in DB2 for LUW
Create eines Catalog Node
catalog tcpip node drdaas remote sccloud026.de.oracle.com server 1898
CATALOG DATABASE drdaas AT NODE drdaas AUTHENTICATION SERVER
db2 CONNECT TO drdaas USER scott USING tiger;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Integration in DB2 for System i
Create eines RDB Node
ADDRDBDIRE
RDB(rdbmapname)
RMTLOCNAME(adc2100688.us.oracle.com *IP)
PORT(1446)
RMTAUTMTH(*USRIDPWD)
TEXT('some descriptive text here')
RMVRDBDIRE
RDB(rdbmapname)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Konfiguration
Initdrdas.ora - $ORACLE_HOME/drdaas/admin
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Konfiguration
Utility - drdactl
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Integration in DB2 for z/OS
• Wie können SQL Statements „umgeleitet“ werden ?
– Bind der Package(s) mit der DRDAAS Location (Remote Package)
– Bind des Plans mit der Package List, die das Remote Package enthalten
– Angabe der Location (Alias, Synonym, connect to , reset connection) in
den Static / dynamic SQL queries
BIND PACKAGE (DRDAAS_TNIEWEL.TNIEWEL) MEMBER(PGMDRDA2) ACTION(REPLACE) QUALIFIER(SCOTT) LIB('TNIEWEL.DBRMLIB')
BIND PACKAGE (TNIEWEL) MEMBER(PGMDRDA2) ACTION(REPLACE) LIB('TNIEWEL.DBRMLIB')
BIND PLAN(TNPLAN2) PKLIST(*.TNIEWEL.*,TNIEWEL.*)
-
ACTION(REPLACE) ISOLATION(CS)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Integration in DB2 for LUW
Wie können SQL Statements „umgeleitet“ werden ?
– Remote Bind des DB2 JDBC drivers
db2jdbcbind -url jdbc:db2://sccloud026.de.oracle.com:1899/drdaas -user SCOTT -password tiger
– Remote Bind desDB2 CLP Utilties
db2 bind $HOME/sqllib/bnd/db2clpcs.bnd action add
db2 bind $HOME/sqllib/bnd/db2clpnc.bnd action add
db2 bind $HOME/sqllib/bnd/db2clprr.bnd action add
db2 bind $HOME/sqllib/bnd/db2clprs.bnd action add
db2 bind $HOME/sqllib/bnd/db2clpur.bnd action add
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Konvertierung
• SQL – DB2 Sprachelemente (>= Oracle 11.2)
– DB2 SQL Statement Übersetzung
• Isolation overriding
– Select * from dsnsamp.employee with UR
• Special Registers
– SELECT CURRENT CLIENT_ACCTNG FROM SYSIBM.SYSDUMMY1;
Wird übersetzt in:
– SELECT SYS_CONTEXT('DRDAAS_CTX_NAME','CLIENT_ACCTNG') FROM SYSIBM.SYSDUMMY1;
• Modifikationen is Where Clauses
– Übersetzung von ‘‘ in is NULL
– Oracle 12c SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Konvertierung
Beispiel: Nutzung von Labeled Duration und Isolation Overriding
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Funktionalitäten
Beispiel: Verteilte Transaktion (DB2 LUW)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Agenda
1
Oracle Database Gateways
2
Anwendungsfälle Oracle Database Gateways
3
Funktionsweise von Database Gateways
4
Do‘s and Dont‘s
5
Oracle Database Provider for DRDA
6
SQL Translation Framework
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
47
SQL Translation Framework
• Definition von „SQL Translation rules“
• Import von „External SQL translation rules“
– SQL Developer
– 3rd Party translation engines
• Nicht Oracle-SQL wird während der Ausführung in Oracle-SQL
umgewandelt
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle 12c SQL Translation Framework
Q&A
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Translation Framework – Allgemeine Schritte
• Installation eines SQL Translators (Oracle, third-party)
• Create eines “SQL Translation Profile” und Register des SQL
Translators (Verknüpfung des Translators mit dem Profile)
• Zuordnung einer Anwendung zum Translation Profile
– Alter Session
– Logon Triggers
– Database Provider for DRDA
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Translation Framework - Monitoring
• Ist das Translation Profile geladen ?
– SELECT * FROM ALL_SQL_TRANSLATION_PROFILES;.
• Monitoring der SQL Translations
– Select profile_name,REGISTRATION_TIME, SQL_TEXT,
all_sql_translations
TRANSLATED_TEXT from
• Enable eines SQL Profile für Testzwecke
– alter session set sql_translation_profile =
SYSIBM.DRDAAS_SQL_FINGERPRINTER;
– alter session set events = '10601 trace name context forever, level
32';
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Translation Framework
Zuordnung eines DB2 Package zu einem Translation Profile
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Translation Framework
Zuordnung eines DB2 Package zu einem Translation Profile
connect scott/TIGER@pdb1
SET ROLE DRDAAS_ADMIN_ROLE;
exec DBMS_DRDAAS_ADMIN.SET_PROFILE('TNIEWEL','PGMDRDA9',
'SYSIBM.DRDAAS_SQL_FINGERPRINTER');
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Translation Framework
Links
• Certification Matrix
• www.oracle.com/technetwork/database/gateways/certmatrix-168347.pdf
• Best Practices Oracle Database Gateways
• Note 1497914.1
• OTN
• http://www.oracle.com/technetwork/database/gateways/index-100140.htm
• SQL Translation Framework OBE
• http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/12c_sqldev/sql_transla
tion_sql_dev/sql_translation_sql_dev.html
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved.
Oracle
|
Confidential – Internal/Restricted/Highly Restricted55