IBM Information Management
Revolutionizing the Data Abstraction
Layer and Solution Delivery for
Data-Driven Applications
Curt Cotner
IBM Fellow
Vice President and CTO for IBM Database Servers
[email protected]
© 2007 IBM Corporation
IBM Information Management
Disclaimer
© Copyright IBM Corporation 2009. All rights reserved.
U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule
Contract with IBM Corp.
THE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES
ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE
INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS” WITHOUT WARRANTY OF
ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT
PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE. IBM
SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE
RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS
PRESENTATION IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR
REPRESENTATIONS FROM IBM (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND
CONDITIONS OF ANY AGREEMENT OR LICENSE GOVERNING THE USE OF IBM PRODUCTS AND/OR
SOFTWARE.
IBM, the IBM logo, ibm.com, and DB2 are trademarks or registered trademarks of International Business Machines
Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their
first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law
trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common
law trademarks in other countries. A current list of IBM trademarks is available on the Web at “Copyright and trademark
information” at www.ibm.com/legal/copytrade.shtml
Other company, product, or service names may be trademarks or service marks of others.
2
© 2007 IBM Corporation
IBM Information Management
DB2 Connect
Recommended Deployment Options
Several options:
Desktop PCs
Application servers
Web application servers
3
Personal Edition
or
DB2 Connect Server
or
file server
Recommendation:
• Personal Edition is best for small
numbers of end users
• DB2 Connect server or file server
deployment is best for lots of desktops
Rationale:
• drivers now include the key DB2
Connect gateway features (sysplex
workload balancing, connection
concentrator, XA support, automatic
Co-locate
reconnect, etc.)
DB2 Connect
• fewer potential points of failure
on the application • less hardware cost
• less system administration cost
server
• fewer network hops (up to 40%
better elapsed time)
• simplified failover strategy
• less complex problem determination
and monitoring
© 2007 IBM Corporation
IBM Information Management
pureQuery Runtime – every Java application benefits!
 JDBC – acceleration for any JDBC application
Convert dynamic SQL to static SQL
Replace problem queries without changing the source
Remove literals from SQL to get better statement cache hit ratios
 Hibernate/OpenJPA/iBatis – acceleration for persistence layers
Improved SQL “batch” peformance
Auto-tuning of Hibernate and OpenJPA peristence options
 SQL-friendly APIs for OO access to relational
Object to relational mapping
APIs that can be tailored to return XML, JSON, arrays, etc.
 Improved management, monitoring, problem determination
Tracks SQL back to the Java class file and line number
Enables performance monitors to report by application name
 Provides the foundation for improved developer tooling
Syntax assist, code generation, performance reporting, etc.
4
© 2007 IBM Corporation
IBM Information Management
What’s so Great About DB2 Accounting for CICS Apps?
CICS AOR1
Txn1
- Pgm1
- Pgm2
z/OS LPAR
CICS AOR2
TxnA
- PgmX
- PgmY
DB2PROD
CICS AOR3
Txn1
- Pgm1
- Pgm2
DB2 Accounting for CICS apps allows you to study
performance data from many perspectives:
• By transaction (PLAN name)
• By program (package level accounting)
• By address space (AOR name)
• By end user ID (CICS thread reuse)
App
CPU
Txn1
2.1
TN1PLN
TxnA
8.3
TNAPLN
PLAN
This flexibility makes it very easy to isolate performance
problems, perform capacity planning exercises, analyze
program changes for performance regression, compare
one user’s resource usage to another’s, etc.
5
© 2007 IBM Corporation
IBM Information Management
JDBC Performance Reporting and Problem Determination
– Before pureQuery
Application Server
A6
A5
What is visible to the DBA?
- IP address of WAS app server
- Connection pooling userid for WAS
- app is running JDBC or CLI
What is not known by the DBA?
- which app is running?
- which developer wrote the app?
- what other SQL does this app issue?
- when was the app last changed?
- how has CPU changed over time?
- etc.
6
DB2 Java Driver
A2
Persistence Layer
A3
Data Access Logic
A4
EJB Query Language
A1
DB2 or IDS
User
CPU
PACKAGE
USER1
2.1
USER1
8.3
USER1 22.0
USER1
USER1
USER1
JDBC
JDBC
JDBC
© 2007 IBM Corporation
IBM Information Management
What’s so Great About Optim pureQuery Accounting for
WebSphere Applications?
z/OS LPAR
CICS AOR2
TxnA (PLANA)
- PgmX
- PgmY
Data Studio and pureQuery provide the same granularity
for reporting WebSphere’s DB2 resources that we have
with CICS:
• By transaction (Set Client Application name )
• By class name (program - package level accounting)
• By address space (IP address)
• By end user ID (DB2 trusted context and DB2 Roles)
Unix or Windows
WAS 21.22.3.4
TxnA (Set Client App=TxnA)
- ClassX
- ClassY
App
TxnA
TxnB
CPU
2.1
8.3
This flexibility makes it very easy to isolate performance
problems, perform capacity planning exercises, analyze
program changes for performance regression, compare
one user’s resource usage to another’s, etc.
7
© 2007 IBM Corporation
IBM Information Management
Simplifying Problem Determination Scenario
Application Developer
Java
Profiling
 Available for each db access
SQL text generated
Access path
Cost estimates
Estimated response time
Elapsed & CPU time
Data transfer (getpages)
Tuning advice
Database Administrator
 Available for each SQL
pureQuery
Application name
Java class name
Java method name
Java object name
Source code line number
Source code context
J-LinQ transaction name
Last compile timestamp
DRDA
Extentions
8
© 2007 IBM Corporation
IBM Information Management
pureQuery API’s:
Simpler Development
Employee my_emp = db.queryFirst("SELECT Name, HomeAddress, HomePhone
FROM Employee WHERE Name=?", Employee.class, my_emp);
-orEmployee my_emp = getEmployee(name);
XML file or Java annotation
SELECT * FROM EMPLOYEE
WHERE NAME=?1;
SQLJ:
#sql [con] { SELECT NAME, ADDRESS, PHONE_NUM INTO :name, :addr, :phone FROM EMP
WHERE NAME=:name };
new Employee my_emp;
my_emp.setName(name);
my_emp.setHomeAddress(addr);
my_emp.setHomePhone(phone);
JDBC:
9
java.sql.PreparedStatement ps = con.prepareStatement(
"SELECT NAME, ADDRESS, PHONE_NUM FROM EMP
WHERE NAME=?");
ps.setString(1, name);
java.sql.ResultSet names = ps.executeQuery();
names.next();
new Employee my_emp;
my_emp.setName(names.getString(1));
my_emp.setHomeAddress(names.getString(2));
my_emp.setHomePhone(names.getString(3));
names.close();
Table
Column
Type
EMP
NAME
CHAR(64)
EMP
ADDRESS
CHAR(128)
EMP
PHONE_NUM
CHAR(10)
class Employee
{ public String Name;
public String HomeAddress;
public String HomePhone;
…
}
© 2007 IBM Corporation
IBM Information Management
Data API: query Beans
 The class of the return Bean type is passed in as a parameter
Person person = data.queryFirst("SELECT * FROM person
WHERE person.name=?", Person.class, "Brian");
List<Person> people = data.queryList("SELECT * FROM person",
Person.class);
Person[] people = data.queryArray("SELECT * FROM person",
Person.class);
Iterator<Person> people = data.queryIterator("SELECT * FROM person",
Person.class);
 Beans, Maps, Arrays, Collections, Iterators, or your own data
10
© 2007 IBM Corporation
IBM Information Management
Example – create XML from a query
 Create XML from a query:
String xml = d.query("select * from Department", new
XMLResultHandler());
 XMLResultHandler has one method:
String handle(ResultSet rs)
sb.append("\t<"+ m.getTableName(x) +">");
for (int x=1; x<=cols; x++) {
sb.append("<"+ m.getColumnName(x) +">");
sb.append(rs.getString(x));
sb.append("</"+ m.getColumnName(x) +">");
}
sb.append("\t</"+ m.getTableName(x) +">");
 Output:
<result>
<DEPARTMENT><DEPTNO>A00</DEPTNO><DEPTNAME>SPIFFY
COMPUTER SERVICE DIV.</DEPTNAME> <MGRNO>000010</MGRNO> ...
</DEPARTMENT>
</result>
11
© 2007 IBM Corporation
IBM Information Management
Java Data Access in 5 Simple Steps
1. Select table
2. Name bean & select styles
4. Map table to bean
12
3. Generate test code
5. Select template SQL CRUD
© 2007 IBM Corporation
IBM Information Management
Developing with pureQuery
Unleash SQL from Java
SQL content assist
13
SQL validation
SQL execution
SQL analysis
© 2007 IBM Corporation
13
IBM Information Management
Developing with pureQuery
Unleash SQL from Java
SQL content assist
14
SQL validation
SQL execution
SQL analysis
© 2007 IBM Corporation
14
IBM Information Management
Developing with pureQuery
Unleash SQL from Java
SQL content assist
SQL validation
SQL execution
SQL analysis
 Run SQL with parameters at design time in the Java program WITHOUT
writing a test application
15
© 2007 IBM Corporation
15
IBM Information Management
Developing with pureQuery
Unleash SQL from Java
SQL content assist
SQL validation
SQL execution
SQL analysis
View explain plans for SQL inside you Java programs
16
© 2007 IBM Corporation
16
IBM Information Management
Using pureQuery to Foster Collaboration and Produce Enterprise-ready Apps
Application Server
DB2 or IDS
Dev System
A4
A5
Application
Meta data
DB2 or IDS
A1 Prod
A1
A4
A2
A4
A3
A4
A6
A5
A5
A1 A2 A3
Application
Meta data
A1 A2 A3
A4 A5 A6
A4 A5 A6
Catalog data for SQL
A1
Performance
Data
Warehouse
Application
Developer
17
Use pureQuery app metadata
as a way to communicate in terms
familiar to both DBA and developer
A1 A2 A3 A4 A5 A6
Database
Administrator
© 2007 IBM Corporation
IBM Information Management
pureQuery Outline View’s
3 categorizations
pureQuery Outline
 How do you look at the relationship between SQL and Java?
18
© 2007 IBM Corporation
18
IBM Information Management
pureQuery Tools
SQL templates and customizations

Create your own SQL templates

Use templates to write SQL that is
frequently reused

Use tabs to change the variable
names after inserting SQL statement
from the template through SQL
context assist
Use newly created merge template in your
java code using SQL assist
19
IBM Data
© 2007 IBM Corporation19
19Studio
IBM Information Management
Optim Development Studio -- pureQuery Outline
Speed up problem isolation for developers – even when using frameworks
 Capture application-SQL-data object
correlation (with or without the source
code)
 Trace SQL statements to using code
for faster problem isolation
 Enhance impact analysis identifying
application code impacted due to
database changes
 Answer “Where used” questions like
“Where is this column used within the
application?”
 Use with modern Java frameworks
e.g. Hibernate, Spring, iBatis,
OpenJPA
20
© 2007 IBM Corporation
IBM Information Management
How Optim Development Studio Helps
21
© 2007 IBM Corporation
IBM Information Management
Client Optimization
Improve Java data access performance for DB2 – without changing a line of code
 Captures SQL for Java applications
Custom-developed, framework-based, or packaged applications
 Bind the SQL for static execution without changing a line of code
New bind tooling included
 Delivers static SQL execution value to existing DB2 applications
Making response time predictable and stable by locking in the SQL access path preexecution, rather than re-computing at access time
Limiting user access to tables by granting execute privileges on the query packages
rather than access privileges on the table
Aiding forecasting accuracy and capacity planning by capturing additional workload
information based on package statistics
Drive down CPU cycles to increase overall capability
 Choose between dynamic or static execution at deployment time,
rather than development time
23
© 2007 IBM Corporation
IBM Information Management
Optim pureQuery Runtime for z/OS
 In-house testing shows double-digit reduction in CPU costs over dynamic
JDBC
300
200
524
485
446
420
360
274
100
25%
15%
6%
-14%
-35%
ta t
ic
ic
Me
tho
dS
Sta
t
pQ
Op
t.
Cli
en
t
yn
am
ic
pQ
Me
tho
dD
ta t
ic
dS
Me
tho
pQ
Op
t
Me
tho
dD
im
iz n
St
at
ic
yn
am
ic
C
JD
B
JP
A
0
JP
A
EJ
B2
-50%
2

400
EJ
B

Normalized Throughput (ITR)
500
% increase/reduction in CPU per
transn compared to JDBC
Normalized Throughput by API for JDBC Type 4 Driver
% increase/reduction in CPU per transaction compared
to JDBC using Type 4 driver
24
Cl
ien
t
pQ
 IRWW – an OLTP workload, Type 4 driver
 Cache hit ratio between 70 and 85%
 15% - 25% reduction on CPU per txn over dynamic JDBC
© 2007 IBM Corporation
IBM Information Management
Throughput Increase with .NET
 Same IRWW OLTP application used for the Java tests but in .NET
 Application access DB2 for z/OS via Windows Application Server (IIS)
 Throughput during static execution increased by 159% over dynamic SQL execution assuming a
79% statement cache hit ratio
25
© 2007 IBM Corporation
IBM Information Management
Increased throughput with Optim pureQuery Runtime v2.2
as compared to JDBC with 90% Package Cache hit ratio
90% Package Cache hit ratio:
DB2 LUW Database ITR comparison to JDBC
On identical hardware,
pureQuery API Static
(Method Style) more than
doubled the transaction
throughput.
ITR: % difference from JDBC
120%
100%
80%
115%
60%
97%
40%
20%
0%
pureQuery Client
Optimization Static
pureQuery API Static
(Method Style)
In comparison with JDBC, pureQuery Static APIs doubled the
throughput (ITR*) of the DB2 LUW database server.
26
* See speaker notes
© 2007 IBM Corporation
IBM Information Management
Increased throughput with Optim pureQuery Runtime v2.2
as compared to JDBC with 95% Package Cache hit ratio
95% Package Cache hit ratio:
DB2 LUW Database ITR comparison with JDBC
ITR: % difference from JDBC
100%
80%
60%
40%
On identical hardware,
pureQuery Client
Optimization Static improved
transaction throughput by
60%
66%
60%
20%
0%
pureQuery Client
Optimization Static
pureQuery API Static
(Method Style)
Compared to JDBC, pureQuery Client Optimization Static and pureQuery API
Static (Method Style) increased the throughput (ITR*) of the DB2 LUW
database server by 60 and 66% respectively
27
* See speaker notes
© 2007 IBM Corporation
IBM Information Management
Have You Heard of SQL Injection?
28
© 2007 IBM Corporation
IBM Information Management
More Visibility, Productivity, and Control of Application SQL
 Capture
Capture performance and application
metadata
Java Persistence
Architecture
 Optimize
Review and share SQL
• Visualize hotspots
• Analyze impacts from schema
changes
• Trace SQL to originating source
Optimize and replace SQL
Create approved SQL list
 Deploy
Configure execution properties
Optionally bind for static execution
29
Monitor
Capture
Review
Configure
Optimize
Restrict
Bind
Execute
© 2007 IBM Corporation
IBM Information Management
Visualize application
SQL
Replace SQL
without changing
the application
Position in
Database Explorer
30
Visualize execution
metrics
Execute, tune,
share, trace,
explore SQL
© 2007 IBM Corporation
IBM Information Management
Optim Development Studio
 Problem
determination and
isolation
with pureQuery
outline go to the
source of the
problematic SQL
 Improve hibernate
data access calls
Identify and change
the HQL
Source code correlation
Re-write HQL
Use better
performing native
SQL
 With performance
metrics Identify the
hot spots in your
hibernate application
31
Database object correlation
© 2007 IBM Corporation
IBM Information Management
Analyze Use of Sensitive Data in Applications
Optim Development Studio
See queries accessing sensitive data
Filter to see use of sensitive data
Icon identifies
sensitive access
32
View only SQL
accessing
sensitive data
Filter SQL by
action
© 2007 IBM Corporation
IBM Information Management
What Is Heterogeneous Batching?
Heterogenous Batching – multiple operations across different tables all
execute as one batch
Table 1, operation 1
Table 1, operation 2
Data Server
Table 1, operation 3
Table 2, operation 1
Table 2, operation 2
Table 3, operation 1
Table 1, operation 4
33
© 2007 IBM Corporation
IBM Information Management
JDBC Batching v/s pureQuery Heterogeneous Batching
 JDBC batching used by Hibernate
Batcher is currently limited
The advantage of Heterogenous Batching
•
•
Primary and Secondary tables.
Inheritance Join and Table per class
strategies
Cannot batch different operations
against same table
•
•
Field level updates
Insert, update
Cannot batch different entities
Each batch is a message to the
database
 pureQuery heterogeneous batching
plug-in for Hibernate on the other
hand
Can batch entities that map to multiple
tables
Can batch different operations against
the same table
Can batch different entities into a
single batch
Combines insert, deletes, updates into
single batch
34
Elapsed Time (mSec)
Cannot batch entities that map to
multiple tables
500
450
400
350
300
250
200
150
100
50
0
HeteroBatching
No Batching
JDBC Batching
0
20
40
60
80
100
# of operations per transaction
* Preliminary findings based on validation with a test designed to demonstrate
heterogeneous batching differences. This is not intended to be a formal
benchmark.
© 2007 IBM Corporation
IBM Information Management
pureQuery – Stripping Literals from SQL
JDBC app
INSERT INTO T1
VALUES(‘ABC’,2,’DEF’)
pureQuery
Runtime
conversion
INSERT INTO T1
VALUES(:h1,:h2,:h3)
• pureQuery can identify statements that use no
parameter markers, and strip the literals out at runtime
• significant performance gains:
• less CPU cost at PREPARE
• better use of dynamic statement cache
35
© 2007 IBM Corporation
IBM Information Management
Optim Performance Manager - Overview
dashboard
Enlarge ( Shows average and max
values in the selected timeframe )
Open details ( shows timeseries
in the selected timeframe )
• Focus on specific database to see key
performance indicators
36
© 2007 IBM Corporation
IBM Information Management
Optim Performance Manager - Active SQL dashboard
Show top running statements at a point in time according to selected metric, e.g. Elapsed time.
“Tune” launches Query Tuner
“Identify Workloads” refers to defined workloads in Extended Insight
37
© 2007 IBM Corporation
IBM Information Management
Optim Performance Manager – Reporting
Reporting
... gives you the flexibility you need to
adapt OPM on your own environment
 coming with sample reports for
- disk space usage
- system configuration
- top resource using SQL statements
- DB connections
 easy to modify and adapt to your
environment because report definition
stored in XML document
 can be saved and shared as PDF with
your colleagues
 a graphical report builder will also
follow soon
38
© 2007 IBM Corporation
IBM Information Management
Toughest issue for Web applications – Problem diagnosis
and resolution
Web
Browser
Users
Web
Server
DB2 Java Driver
Persistence Layer
Data Access Logic
EJB Query Language
Business Logic
39
DB2
Server
Application
Server
JDBC
Package
© 2007 IBM Corporation
IBM Information Management
Customer Job Roles – A Barrier to a “Holistic View”
Application
Developer
40
DB Java Driver
5
Persistence Layer
4
Network
Admin
Data Access Logic
2
DB
Server
DBA
System
Programmer
EJB Query Language
3
WebSphere
Connection
Pool
Business
Logic
1
Application
Server
JDBC
Package
© 2007 IBM Corporation
IBM Information Management
How do we plan to help?
41
© 2007 IBM Corporation
IBM Information Management
Extended Insight (EI) Overview dashboard
42
© 2007 IBM Corporation
IBM Information Management
OPM and OPM EI – SQL Resolution Integration Points
Query Tuner
Integration
provides tuning
advice and tools
pureQuery
Integration provides
source code
identification
43
© 2007 IBM Corporation
IBM Information Management
IBM Data Studio
www.ibm.com/software/data/studio
•
•
•
•
FAQs / Tutorials
Downloads
Forum / Blogs
Join the IBM Data Studio user community
 Data Studio Book
NEW!!!
http://bit.ly/dstudiobook
44
© 2007 IBM Corporation
IBM Information Management
45
© 2007 IBM Corporation
Descargar

Why innovation matters for IBM