Why innovation matters for IBM

streakconvertingSoftware and s/w Development

Dec 13, 2013 (3 years and 10 months ago)

84 views

© 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