Integration

1
(No Transcript)
2
Transparent Gateway for DB2 a success
story Thomas NiewelOracle Deutschland
GmbHThomas.Niewel_at_Oracle.com
3
Agenda

• The Geomarketing Project
• TG4DB2 How it works
• Pre-Processing
• Post-Processing
• Gateway Parameters
• Things to think about in a dB2 world
• DB2 Locking Model
• Predicates
• Lessons we have learned

4
The project Geomarketing

• The service center of a bank needed to provide a
  control instrument for marketing activities
• The marketing actions should be controlled by
  spatial data and operational data
• The requirements for the platforms were
• Spatial Data should reside Windows
• Operational Data is stored on DB2 for z/OS
• There are existing DB2 for z/OS
  datawarehouseswhich should be used as source
• There are 3 different LPARs with DB2 data
• RACF should be used to autenticate users

5
The project Geomarketing
zSeries
Windows
DB2
TG4DB2
GIS-DB Server
LPAR1
NET
Oracle 8.1.7.4
DB2
TG4DB2
LPAR2
NET
Integraph
DB2
TG4DB2
LPAR3
NET
6
The project Geomarketing

• A sample request
• Show me all customers who live in the city of
  Fürstenfeldbruck which do not have any shares
• The result is a Map of the city Fürstenfeldbruck
  where you can see the areas of the city combined
  with the result set of the request

7
The project Geomarketing
8
The project Geomarketing

• Project plan
• Suppliers had to build a prototype
• The customer evaluated the prototypes
• Performance was a key success factor
• Performance also was the main challenge
• DB2 had to be accessed with only little ressource
  consumtion
• DB2 Utilities should not be interfered
• Sometimes a great number of rows is transferred
• Distributed Queries(Oracle/DB2) had to be used

9
The Project Geomarketing

• The way how Transparent Gateway for DB2(TG4DB2)
  works had to be understood
• Features which speed up Bulk Transfers with
  TG4DB2 had to be used
• Efficient queries to access DB2 had to be
  developed
• That means the DB2 Optimizer had to be known
• The DB2 Locking behaviour had to be understood

10
Transparent Gateway for DB2 How it works
11
Problem areas

• SQL Dialects
• DB2 Locking Model
• Query Performance
• Transparency

12
How does a Gateway work ?
Oracle DB2 for z/OS
Step 1
Step 2
Select Max(Salary) from tab1 Where Name MAIER
Select Max(Salary) from tab1_at_DB2 Where Name
MAIER
Datatransfer
Step 3
Step 4
13
How does a Gateway work ?
Oracle DB2 for z/OS
Step 1
Step 2
Select Name,age from tab1 Where Value(Age,18)
18
Select Name,age from tab1_at_DB2 Where
NVL(Age,18) 18
Datatransfer
Step 3
Step 4
14
How does a Gateway work ?
Oracle DB2 for z/OS
Step 2
Step 1
Select from tab1
Select from tab1_at_DB2 Where Soundex(Column1)
Scott
Datatransfer
Step 3
Select from temptab Where Soundex(Column1)
Scott
Step 4
15
Gateway Performance

• Compatible Functionalities
• Good performance
• Translated Functionalities
• Gateway Pre-Processing
• Good Performance
• Compensated Functionalities
• Gateway Post-Processing
• Performance implications

16
Gateway Performance

• Compatible functions
• AVG
• CONCAT
• COUNT () only
• COUNT(DISTINCT expression)
• MAX
• MIN
• SUM
• ....
• Translated functions
• NVL Value
• .....

17
How can Post-Processing be discovered

• Bad response time (SQL Trace)
• Gateway Trace
• Explain Plan
• UTLXPLAN
• UTLXPLS
• PLAN_TABLE

18
Postprocessing - sample
explain plan for select b.ename, b.empno from
tniewel.emp_at_epg1 a, scott.emp_at_epg1 b where
a.empnob.empno and soundex(a.ename)'MAIER' /
19
Postprocessing - sample
--------------------------------------------------
---------------------------------------------
Id Operation Name Rows
Bytes Cost TQ IN-OUT PQ Distrib
------------------------------------------------
-----------------------------------------------
0 SELECT STATEMENT 1
40 1
1 HASH JOIN 1
40 1 2
FILTER
3
REMOTE
E.,OM SERIAL 4
REMOTE 1 20
E.,OM SERIAL
------------------------------------------------
----------------------------------------------- P
redicate Information (identified by operation
id) ---------------------------------------------
------ 1 - access("A2"."EMPNO""A1"."EMPNO")
2 - filter(SOUNDEX("A2"."ENAME")'MAIER') Slave
SQL Information (identified by operation
id) ---------------------------------------------
------ 3 - SELECT "EMPNO", "ENAME" FROM
"TNIEWEL"."EMP" 4 - SELECT "EMPNO", "ENAME"
FROM "SCOTT"."EMP" Note cpu costing is off
20
Without Postprocessing
explain plan for select b.ename, b.empno from
tniewel.emp_at_epg1 a, scott.emp_at_epg1 b where
a.empnob.empno and a.ename'MAIER' /
21
Without Postprocessing
-------------------------------------------------
----------------------------------------------
Id Operation Name Rows
Bytes Cost TQ IN-OUT PQ Distrib
------------------------------------------------
-----------------------------------------------
0 SELECT STATEMENT

1 REMOTE
E.,OM SERIAL
------------------------------------------------
----------------------------------------------- S
lave SQL Information (identified by operation
id) ---------------------------------------------
------ 1 - SELECT A1."ENAME", A1."EMPNO" FROM
"TNIEWEL"."EMP" A2, "SCOTT"."EMP" A1 WHERE
A2."EMP NO"A1."EMPNO" AND
A2."ENAME"'MAIER' Note rule based optimization
22
Distributed Joins how can splitted Queries be
prevented
explain plan for select a.ename from tniewel.emp
a, tniewel.emp_at_epg1 b, scott.emp_at_epg1 c where
a.enameb.ename and b.enamec.ename
23
Distributed Joins how can splitted Queries be
prevented
-------------------------------------------------
-----------------------------------------------
Id Operation Name Rows
Bytes Cost TQ IN-OUT PQ Distrib
------------------------------------------------
------------------------------------------------
0 SELECT STATEMENT
1 21 3
1 HASH JOIN 1
21 3
2 MERGE JOIN CARTESIAN 1
14 2 3
REMOTE 1
7 E.,OM SERIAL 4
BUFFER SORT 82
574 2 5
TABLE ACCESS FULL EMP 82
574 2 6
REMOTE 1
7 E.,OM SERIAL
------------------------------------------------
------------------------------------------------
Predicate Information (identified by operation
id) ---------------------------------------------
------ 1 - access("A"."ENAME""B"."ENAME" AND
"B"."ENAME""C"."ENAME") Slave SQL Information
(identified by operation id) --------------------
------------------------------- 3 - SELECT
"ENAME" FROM "SCOTT"."EMP" 6 - SELECT "ENAME"
FROM "TNIEWEL"."EMP"
24
Distributed Joins how can splitted Queries be
prevented
explain plan for select a.ename from tniewel.emp
a where a.ename (select b.ename from
tniewel.emp_at_epg1 b, scott.emp_at_epg1 c where
b.enamec.ename)
25
Distributed Joins how can splitted Queries be
prevented
--------------------------------------------------
---------------------------------------------
Id Operation Name Rows
Bytes Cost TQ IN-OUT PQ Distrib
------------------------------------------------
-----------------------------------------------
0 SELECT STATEMENT 1
7 2
1 TABLE ACCESS FULL EMP 1
7 2 2
REMOTE
E.,OM SERIAL
------------------------------------------------
----------------------------------------------- P
redicate Information (identified by operation
id) ---------------------------------------------
------ 1 - filter("A"."ENAME" (SELECT
"A2"."ENAME" FROM "SCOTT"."EMP"_at_EPG1.DE.ORACLE.COM
"A1")) Slave SQL Information (identified by
operation id) -----------------------------------
---------------- 2 - SELECT A2."ENAME" FROM
"TNIEWEL"."EMP" A2, "SCOTT"."EMP" A1 WHERE
A2."ENAME"A1."ENAM E" Note cpu costing
is off 22 Zeilen ausgewõhlt.

26
Distributed Joins how can splitted Queries be
prevented

• Create views in DB2 Be careful with View
  Materialization
• View Merge vs. View Materialization
• View Materialization is performed when a view
  is complex e.g. when sorts need to be performed
  
• Explain of the View
• Use Filters

27
Traces - Gateway Trace

• Gateway Parameters
• Tracelevel4
• Dedicated Trace Gateway makes live easier

28
Traces Gateway Trace
Gateway Trace
//////////////////////////////////////////////////
////////////////////////// Oracle for OS/390
Diagnostic Trace -- 2003/01/15 071907.357
// System MVS08 Subsystem EPO3 Service EPG1
Session 16 PID 00020010/////////////////////
//////////////////////////////////////////////////
///// hoapars SQL SELECT "NAME" FROM
"SYSIBM"."SYSTABLES"fdsapiSQL statement
follows. Length39SELECT "NAME" FROM
"SYSIBM"."SYSTABLES" hoapars SQL SELECT
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE",
"SAL", "COMM", "DEPTNO" FROM "TNIEWEL"."EMP"fdsa
piSQL statement follows. Length95SELECT
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE",
"SAL", "COMM", "DEPTNO" FROM "TNIEWEL"."EMP"
29
SQL Trace
Prerequisite timed_statisticstrue user action
- Connect Select sid, serial, osuser, username
from Vsession 1. SQLgt select sid, serial,
osuser, username from vsession   SID
SERIAL OSUSER
USERNAME --------- --------- ---------------------
----- ---------------------- .... 7 38
SYSTEM 9
70
TNIEWEL2   exec sys.dbms_system.set_sql_trace_in_s
ession(9, 70,TRUE) user action Work with
Application exec sys.dbms_system.set_sql_trace_in
_session(9, 70,FALSE) Format Trace Files with
TKPROF
30
Gateway Parameters

• HS_RPC_FETCH_SIZE40000
• DB2_STATSYES
• IDLE_TIMEOUT

31
Important Gateway Parameters
32
Parameter DB2STATSYES

• DB2 catalog Statistics are used influence the
  Execution plan
• Table Cardinality
• Key Column Cardinality

• What are the results of that setting
• Join Sequence may change
• Small remote-DB2-Tables are completely
  transferred

33
Parameter DB2STATSYES

• Sample Plan_table output
• Sysibm.Systables, Sysibm.Syscolumns (Only Key
  Columns)
• Column Cardf
• Select from tniewel.emp_at_epg1

--------------------------------------------------
---------------------------------------------
Id Operation Name Rows
Bytes Cost TQ IN-OUT PQ Distrib
------------------------------------------------
-----------------------------------------------
0 SELECT STATEMENT 10000
566K
1 REMOTE 10000
566K E.,OM SERIAL
------------------------------------------------
----------------------------------------------- S
lave SQL Information (identified by operation
id) ---------------------------------------------
------ 1 - SELECT "EMPNO", "ENAME", "JOB",
"MGR", "HIREDATE", "SAL", "COMM", "DEPTNO" FROM
"TNIE WEL"."EMP"
34
DB2 Locking

• IRLM (Internal Ressource Lock Manager)
• Row Locks possible - Page Locks were used because
  of performance reasons
• Lock Escalation

35
DB2 Locking
DB2 Locking is behaviour is determined by the
Isolation Level of the Gateway Plan CS
Cursor Stability RR Repeatable Read RS Read
StabilityUR Uncommitted Read
36
DB2 Locking
Nothing Qualifying Row
All Rows Row
accessed Read
37
DB2 Locking - Problems

• Problem at the customer site
• The Gateway holds Locks ! Our Batches Abend with
  SQLCODE 911

• Is this really a gateway problem ?

38
DB2 Locking
Duration of Page and Row Locks If a page or
row is locked, DB2 acquires the lock only when it
is needed. When the lock is released depends on
many factors, but it is rarely held beyond the
next commit point.
..... Commit Work as Soon as Is Practical To
avoid unnecessary lock contentions, issue a
COMMIT statement as soon as possible after
reaching a point of consistency, even in
read-only applications. To prevent unsuccessful
SQL statements (such as PREPARE) from holding
locks, issue a ROLLBACK statement after a
failure.
Quelle DB2 Version 7 Administration Guide
39
DB2 - SQL Tuning Hints

• Use Functions on columns in where condition
  which are also available in DB2
• Predicates in DB2
• Indexable
• Index can be used
• Stage 1
• Predicates, which are not of Type Stage 1 are
  evaluated by the RDS(Relational Data System)
  which is more expensive

40
Predicates Source DB2 Administration Guide
41
Predicates Source DB2 Administration Guide
42
Predicates Source DB2 Administration Guide
43
Predicates Source DB2 Administration Guide
44
Lessons we have learned
45
Lessons we have learned

• DB2 Locking
• Commits also after Read operations
• If Consistency doesnt matter
• Bind TG4DB2 Plan with Isolation Level UR (TG4DRDA
  (DRDA_ISOLATION_LEVELNC)
• Hanging Sessions
• Alter Session Close Database Link xxx
• Should be executed when a logical application
  unit is finished.

46
Lessons we have learned

• Try to make bulk transfers small
• use as much filters as possible on remote DB
• The split of distributed joins (Oracle / DB2)
  sometimes is more efficient

47
Lessons we have learned

• Oracle functions in SQL can produce a lot of
  overhead
• Sample
• Select a.,b. from testtn1_at_epg1 a,
  testtn2_at_epg1 b where
• a.c3 between to_char(sysdate-1,'YYYY-M
  M-DD')'-00.00.00.000000'
• and to_char(sysdate,'YYYY-MM-DD')'-00.00.0
  0.000000' and
• a.c2b.c2
• The use of Bind variables improves the
  performance

48
More...

• Gateway Passthrough
• Allows input of native DB2 Syntax without
  Gateway Pre- and Postprocessing
• Call of DB2 Stored Procedures
• Allows the use of static SQL
• DB2 Stored Procedure result sets are not
  supported

49
Passthrough

• DECLARE
• CRS binary_integer RET binary_integer VAL
  VARCHAR2(10)AGE Number
• BEGIN
• CRSDBMS_HS_PASSTHROUGH.OPEN_CURSOR_at_gtwlink
• DBMS_HS_PASSTHROUGH.PARSE_at_gtwlink(CRS,SELECT
  NAME, AGE FROM PT_TABLE)
• BEGIN
• RET0
• WHILE (TRUE)
• LOOP
• RETDBMS_HS_PASSTHROUGH.FETCH_ROW_at_gtwlink
  (CRS,FALSE)
• DBMS_HS_PASSTHROUGH.GET_VALUES_at_gtwlink
  (CRS,1,VAL)
• DBMS_HS_PASSTHROUGH.GET_VALUES_at_gtwlink
  (CRS,2,AGE)
• INSERT INTO PT_TABLE_LOCAL VALUES (VAL)
• END LOOP
• EXCEPTION
• WHEN NO_DATA_FOUND THEN
• BEGIN
• DBMS_OUTPUT.PUT_LINE(END OF FETCH)
• DBMS_HS_PASSTHROUGH.CLOSE_CURSOR_at_gtwlink(CRS)
  

50
DB2 Stored Procedures

• Can be called from PL/SQL
• DECLARE
• INPUT VARCHAR2(15)
• RESULT NUMBER(8,2)
• BEGIN
• INPUT JOHN SMYTHE
• SYSPROC.REVISE_SALARY_at_DB2(INPUT, RESULT)
• UPDATE EMP SET SAL RESULT WHERE ENAME INPUT
• END
• ....

51
The results...

• Geomarketing is in Production
• The response time is o.k.
• TG4DB2 V9.2 (compared to version 8.1.7)
  generated improved distributed query
  execution plans

52
?