© 2007 IBM Corporation
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
cotner@us.ibm.com
IBM Information Management
© 2007 IBM Corporation
2
© 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.
Disclaimer
IBM Information Management
© 2007 IBM Corporation
3
DB2 Connect
Recommended Deployment Options
Desktop PCs
Application servers
Web application servers
Several options:
Personal Edition
or
DB2 Connect Server
or
file server
Co
-
locate
DB2 Connect
on the application
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
reconnect, etc.)
•
fewer potential points of failure
•
less hardware cost
•
less system administration cost
•
fewer network hops (up to 40%
better elapsed time)
•
simplified failover strategy
•
less complex problem determination
and monitoring
IBM Information Management
© 2007 IBM Corporation
4
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.
IBM Information Management
© 2007 IBM Corporation
5
What’s so Great About DB2 Accounting for CICS Apps?
z/OS LPAR
CICS AOR1
Txn1
-
Pgm1
-
Pgm2
CICS AOR2
TxnA
-
PgmX
-
PgmY
DB2PROD
CICS AOR3
Txn1
-
Pgm1
-
Pgm2
App CPU PLAN
Txn1 2.1
TN1PLN
TxnA 8.3
TNAPLN
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)
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.
IBM Information Management
© 2007 IBM Corporation
6
JDBC Performance Reporting and Problem Determination
–
Before pureQuery
Application Server
DB2 or IDS
A1
A2
A5
A3
A6
A4
USER1
USER1
USER1
User CPU
PACKAGE
USER1 2.1 JDBC
USER1 8.3 JDBC
USER1 22.0 JDBC
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.
Data Access Logic
Persistence Layer
DB2 Java Driver
EJB Query Language
IBM Information Management
© 2007 IBM Corporation
7
What’s so Great About Optim pureQuery Accounting for
WebSphere Applications?
z/OS LPAR
CICS AOR2
TxnA (PLANA)
-
PgmX
-
PgmY
App CPU
TxnA 2.1
TxnB 8.3
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)
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.
Unix or Windows
WAS 21.22.3.4
TxnA (Set Client App=TxnA)
-
ClassX
-
ClassY
IBM Information Management
© 2007 IBM Corporation
8
Simplifying Problem Determination Scenario
Application Developer
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
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
Java
Profiling
pureQuery
DRDA
Extentions
IBM Information Management
© 2007 IBM Corporation
9
Simpler Development
Employee my_emp = db.queryFirst("
SELECT Name, HomeAddress, HomePhone
FROM Employee WHERE Name=?
", Employee.class, my_emp);
-
or
-
Employee my_emp = getEmployee(name);
#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);
SQLJ:
JDBC:
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();
pureQuery API’s:
XML file or Java annotation
SELECT * FROM EMPLOYEE
WHERE NAME=?1;
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;
…
}
IBM Information Management
© 2007 IBM Corporation
10
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
IBM Information Management
© 2007 IBM Corporation
11
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>
IBM Information Management
© 2007 IBM Corporation
12
1. Select table
Java Data Access in 5 Simple Steps
2. Name bean & select styles
3. Generate test code
4. Map table to bean
5. Select template SQL CRUD
IBM Information Management
© 2007 IBM Corporation
13
13
SQL analysis
SQL execution
SQL validation
Developing with pureQuery
Unleash SQL from Java
SQL content assist
IBM Information Management
© 2007 IBM Corporation
14
14
SQL content assist
SQL analysis
SQL execution
Developing with pureQuery
Unleash SQL from Java
SQL validation
IBM Information Management
© 2007 IBM Corporation
15
15
SQL validation
SQL content assist
SQL analysis
Developing with pureQuery
Unleash SQL from Java
SQL execution
Run SQL with parameters at
design time
in the Java program WITHOUT
writing a test application
IBM Information Management
© 2007 IBM Corporation
16
16
SQL execution
SQL validation
SQL content assist
Developing with pureQuery
Unleash SQL from Java
SQL analysis
View explain plans for SQL inside you Java programs
IBM Information Management
© 2007 IBM Corporation
17
Using pureQuery to Foster Collaboration and Produce Enterprise
-
ready Apps
Application Server
Catalog data for SQL
Application
Meta data
DB2 or IDS
Prod
A4
A1
A1
A6
A6
A2
A2
A3
A3
A4
A4
A5
A5
A1
A4
A5
Performance
Data
Warehouse
Application
Developer
Database
Administrator
A1
A6
A2
A3
A4
A5
Use pureQuery app metadata
as a way to communicate in terms
familiar to both DBA and developer
Application
Meta data
DB2 or IDS
Dev System
A1
A6
A2
A3
A4
A5
A1
A4
A5
IBM Information Management
© 2007 IBM Corporation
18
18
pureQuery Outline
pureQuery Outline View’s
3 categorizations
How do you look at the relationship between SQL and Java?
IBM Information Management
© 2007 IBM Corporation
19
IBM Data
Studio
pureQuery
For DBAs
and
Applicatio
n
Developer
s (v1.2)
-
Part 1
19
19
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
IBM Information Management
© 2007 IBM Corporation
20
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
IBM Information Management
© 2007 IBM Corporation
21
How Optim Development Studio Helps
IBM Information Management
© 2007 IBM Corporation
23
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 pre
-
execution, 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
IBM Information Management
© 2007 IBM Corporation
24
Optim pureQuery Runtime for z/OS
In
-
house testing shows double
-
digit reduction in CPU costs over dynamic
JDBC
IRWW
–
an OLTP workload,
Type 4 driver
Cache hit ratio between 70 and 85%
15%
-
25% reduction on CPU per txn
over dynamic JDBC
274
360
420
446
485
524
0
100
200
300
400
500
Normalized Throughput (ITR)
EJB 2
JPA
JDBC
pQ Method Dynamic
Client Optimizn Static
pQ Method Static
Normalized Throughput by API for JDBC Type 4 Driver
-35%
-14%
6%
15%
25%
-50%
% increase/reduction in CPU per
transn compared to JDBC
EJB 2
JPA
pQ Method Dynamic
Client Opt. Static
pQ Method Static
% increase/reduction in CPU per transaction compared
to JDBC using Type 4 driver
IBM Information Management
© 2007 IBM Corporation
25
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
IBM Information Management
© 2007 IBM Corporation
26
pureQuery Client
Optimization Static
pureQuery API Static
(Method Style)
97%
115%
0%
20%
40%
60%
80%
100%
120%
ITR: % difference from JDBC
90% Package Cache hit ratio:
DB2 LUW Database ITR comparison to JDBC
Increased throughput with Optim pureQuery Runtime v2.2
as compared to JDBC with 90% Package Cache hit ratio
In comparison with JDBC, pureQuery Static APIs
doubled
the
throughput (ITR*) of the DB2 LUW database server.
On identical hardware,
pureQuery API Static
(Method Style) more than
doubled the transaction
throughput.
* See speaker notes
IBM Information Management
© 2007 IBM Corporation
27
pureQuery Client
Optimization Static
pureQuery API Static
(Method Style)
60%
66%
0%
20%
40%
60%
80%
100%
ITR: % difference from JDBC
95% Package Cache hit ratio:
DB2 LUW Database ITR comparison with JDBC
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
Increased throughput with Optim pureQuery Runtime v2.2
as compared to JDBC with 95% Package Cache hit ratio
On identical hardware,
pureQuery Client
Optimization Static improved
transaction throughput by
60%
* See speaker notes
IBM Information Management
© 2007 IBM Corporation
28
Have You Heard of SQL Injection?
IBM Information Management
© 2007 IBM Corporation
29
More Visibility, Productivity, and Control of Application SQL
Capture
Capture performance and application
metadata
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
Capture
Review
Restrict
Configure
Bind
Execute
Monitor
Optimize
Java Persistence
Architecture
IBM Information Management
© 2007 IBM Corporation
30
Visualize execution
metrics
Execute, tune,
share, trace,
explore SQL
Replace SQL
without changing
the application
Position in
Database Explorer
Visualize application
SQL
IBM Information Management
© 2007 IBM Corporation
31
Optim Development Studio
Problem
determination and
isolation
with pureQuery
outline go to the
source of the
problematic SQL
Improve hibernate
data access calls
Re
-
write HQL
Use better
performing native
SQL
With performance
metrics Identify the
hot spots in your
hibernate application
Source code correlation
Database object correlation
Identify and change
the HQL
IBM Information Management
© 2007 IBM Corporation
32
Analyze Use of Sensitive Data in Applications
See queries accessing sensitive data
Optim Development Studio
Icon identifies
sensitive access
Filter to see use of sensitive data
View only SQL
accessing
sensitive data
Filter SQL by
action
IBM Information Management
© 2007 IBM Corporation
33
What Is Heterogeneous Batching?
Data Server
Table 2, operation 2
Table 1, operation 1
Table 1, operation 2
Table 1, operation 3
Table 2, operation 1
Table 3, operation 1
Heterogenous Batching
–
multiple operations across different tables all
execute as one batch
Table 1, operation 4
IBM Information Management
© 2007 IBM Corporation
34
JDBC Batching v/s pureQuery Heterogeneous Batching
JDBC batching used by Hibernate
Batcher is currently limited
Cannot batch entities that map to
multiple tables
•
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
The advantage of Heterogenous Batching
0
50
100
150
200
250
300
350
400
450
500
0
20
40
60
80
100
# of operations per transaction
Elapsed Time (mSec)
HeteroBatching
No Batching
JDBC Batching
*
Preliminary findings based on validation with a test designed to demonstrate
heterogeneous batching differences. This is not intended to be a formal
benchmark
.
IBM Information Management
© 2007 IBM Corporation
35
pureQuery
–
Stripping Literals from SQL
JDBC app
INSERT INTO T1
VALUES(‘ABC’,2,’DEF’)
INSERT INTO T1
VALUES(:h1,:h2,:h3)
pureQuery
Runtime
conversion
•
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
IBM Information Management
© 2007 IBM Corporation
36
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
IBM Information Management
© 2007 IBM Corporation
37
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
IBM Information Management
© 2007 IBM Corporation
38
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
IBM Information Management
© 2007 IBM Corporation
39
Toughest issue for Web applications
–
Problem diagnosis
and resolution
Web
Browser
Users
Web
Server
Application
Server
DB2
Server
Business Logic
Data Access Logic
Persistence Layer
DB2 Java Driver
JDBC
Package
EJB Query Language
IBM Information Management
© 2007 IBM Corporation
40
Customer Job Roles
–
A Barrier to a “Holistic View”
Application
Server
DB
Server
Data Access Logic
Persistence Layer
DB Java Driver
JDBC
Package
EJB Query Language
WebSphere
Connection
Pool
Business
Logic
1
3
5
4
2
Application
Developer
System
Programmer
DBA
Network
Admin
IBM Information Management
© 2007 IBM Corporation
41
How do we plan to help?
IBM Information Management
© 2007 IBM Corporation
42
Extended Insight (EI) Overview dashboard
IBM Information Management
© 2007 IBM Corporation
43
OPM and OPM EI
–
SQL Resolution Integration Points
Query Tuner
Integration
provides tuning
advice and tools
pureQuery
Integration provides
source code
identification
IBM Information Management
© 2007 IBM Corporation
44
IBM Data Studio
www.ibm.com/software/data/studio
•
FAQs / Tutorials
•
Downloads
•
Forum
/ Blogs
•
Join the IBM Data Studio user community
Data Studio Book
http://bit.ly/dstudiobook
NEW!!!
IBM Information Management
© 2007 IBM Corporation
45
- Αρχική
- Περιήγηση
- Όλες οι Κατηγορίες
- Internet και Εφαρμογές Web
- Αποθήκευση
- Ασφάλεια
- Βιοτεχνολογία
- Δίκτυα και Επικοινωνίες
- Διακομιστές
- Διαχείριση
- Διαχείριση Δεδομένων
- Επιχειρησιακές Εφαρμογές
- Ηλεκτρονική - Συσκευές
- Ημιαγωγοί
- Κινητά – Ασύρματες Τεχνολογίες
- Λογισμικό & κατασκευή λογ/κού
- Μηχανική
- Περιβαλλοντική Τεχνολογία
- Πετρελαϊκά και Εξόρυξη
- Πολεοδομικά Έργα
- Τεχνίτη Νοημοσύνη και Ρομποτική
- Τηλεπικοινωνίες
- Ψυχαγωγία
- Κοινότητα
- Μεταφόρτωση
Σχόλια 0
Συνδεθείτε για να κοινοποιήσετε σχόλιο