Optimizing Your WebSphere Applications with Optim and pureQuery

1. Optimizing Your WebSphere Applications with Optim and pureQuery Curt Cotner IBM Fellow, CTO for IBM Database Servers

2. Development Tools for Your Java Database Applications

3. 3. Generate test code 2. Name bean & select styles 5. Select template SQL CRUD 4. Map table to bean Java Data Access in 5 Simple Steps 1. Select table

4. What happened?Generate code from table • The code is generated for • Bean (aka Java Object) representing the table • Interface with associated SQL or Sample Application with Inline SQL • Implementation of Interface • Optionally a JUnit test case. 4

5. Generated Test Cases for Your SQL Jump start your migrations! 5

6. Developing with pureQueryUnleash SQL from Java SQL content assist SQL validation SQL execution SQL analysis 6

7. Developing with pureQueryUnleash SQL from Java SQL content assist SQL execution SQL analysis SQL validation 7

8. Developing with pureQueryUnleash 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 8

9. Developing with pureQueryUnleash SQL from Java SQL content assist SQL validation SQL execution SQL analysis • View explain plans for SQL inside you Java programs 9

10. pureQuery OutlineSpeed 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 Java source code for faster problem isolation Enhance impact analysis by identifying specific application code impacted by a 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 10

11. pureQuery Outline View’s 3 categorizations How do you look at the relationship between SQL and Java? pureQuery Outline 11

12. Optim Development Studio Identify and change the HQL • 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

13. Analyzing Performance

14. Filter to see use of sensitive data See queries accessing sensitive data Filter SQL by action View only SQL accessing sensitive data Analyze Use of Sensitive Data Optim Development Studio Icon identifies sensitive access

15. pureQuery ToolsSQL 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 Data Studio pureQuery For DBAs and Application Developers (v1.2) - Part 1 15 15

16. Java/SQL Development on Steroids Why the 10x Productivity Improvement 16 16

17. Optimizing Your WebSphere Applications

18. DB2 ConnectRecommended Deployment Options Several options: 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 Desktop PCs • 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 Application servers Co-locate DB2 Connect on the application server Web application servers

19. ADO .Net On-ramps to pureQuery .Net applications Data Studio pureQuery Data Web Services, Project Zero, sMash Hibernate, iBATIS, EclipseLink,... JPA for WebSphere, Apache OpenJPA Plain JDBC .Net Applications JDBC API pureQuery API JPA API Web API Open Source Persistence API Open Source Persistence Engine JPA Persistence Engine JDBC pureQuery JCC driver DB2, Informix, and Oracle now…more coming

20. Client OptimizationImprove Java data access performance– without changing 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

21. 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

22. 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

23. pureQuery -- More Visibility, Productivity, and Control of Application SQL • Capture SQL • Share, review, and optimize SQL • Revise/optimize SQL and validate equivalency without changing the application • Bind for static execution to lock in service level or run dynamically • Restrict SQL to eliminate SQL injection Capture Review Optimize Revise Restrict

24. Visualize application SQL Visualize execution metrics Replace SQL without changing the application Position in Database Explorer Execute, tune, share, trace, explore SQL

25. Have You Heard of SQL Injection?

26. 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

27. Table 1, operation 1 Table 1, operation 2 Table 1, operation 3 Table 2, operation 1 Table 2, operation 2 Table 3, operation 1 What Is Heterogeneous Batching? Heterogenous Batching – multiple operations across different tables all execute as one batch Data Server Table 1, operation 4

28. JDBC Batching vs pureQuery 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 • pureQuery heterogeneous batching plug-in • 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 * Preliminary findings based on validation with a test designed to demonstrate heterogeneous batching differences. This is not intended to be a formal benchmark.

29. OpenJPA and Hibernate --SQL Query Generation JPA Query Select emp_obj(), dept_obj() SQL Select * from EMP WHERE … Select * from DEPT WHERE … JPA query transform • Hibernate and OpenJPA often rewrite queries • No database statistics are used – entirely heuristic!!! • Can often result in poorly performing queries

30. Monitoring of Java Applications

31. TOP 3 currently running SQL Statements  Statement information X In-flight analysis Heat Chart Alerts Dashboard SLAs  Database: Accounting DS Proc TOP by DS elapsed DS CPU time Physical I/O Sort time Stmt text Analyze SELECT TIME FROM UNIVERSE - + - + Time distribution sorting DS sorting Unacc wait USER CPU SYSTEM CPU - + Stop SQL Force application

32. Toughest issue for Web applications – Problem diagnosis and resolution DB2 Server Application Server Web Browser Users Web Server Business Logic EJB Query Language Data Access Logic Persistence Layer DB2 Java Driver JDBC Package

33. What’s so Great About DB2 Accounting for CICS Apps? z/OS LPAR CICS AOR1 Txn1 - Pgm1 - Pgm2 DB2PROD CICS AOR2 TxnA - PgmX - PgmY 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.

34. JDBC Performance Reporting and Problem Determination – Before pureQuery Application Server DB2 or IDS EJB Query Language Data Access Logic Persistence Layer DB2 Java Driver USER1 A1 A4 USER1 A3 A2 USER1 A5 A6 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. User CPU PACKAGE USER1 2.1 JDBC USER1 8.3 JDBC USER1 22.0 JDBC

35. What’s so Great About Optim pureQuery Accounting for WebSphere Applications? Unix or Windows z/OS LPAR WAS 21.22.3.4 TxnA (Set Client App=TxnA) - ClassX - ClassY 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)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. App CPU TxnA 2.1 TxnB 8.3

36. 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 Java Profiling 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 pureQuery DRDA Extentions

37. Using pureQuery to Foster Collaboration and Produce Enterprise-ready Apps Application Server DB2 or IDS Prod DB2 or IDS Dev System A1 A1 A4 A4 A3 A2 Application Meta data A4 A6 Application Meta data A5 A5 A4 A1 A2 A3 A5 A1 A2 A3 A4 A5 A6 A4 A5 A6 Catalog data for SQL Performance Data Warehouse A1 A1 A2 A3 A4 A5 A6 Quickly compare unit test perf results to production Use pureQuery app metadata as a way to communicate in terms familiar to both DBA and developer Application Developer Database Administrator

38. Customer Job Roles – A Barrier to a “Holistic View” Application Server DB Server Application Developer System Programmer DBA Network Admin EJB Query Language Data Access Logic Persistence Layer DB Java Driver Business Logic WebSphere Connection Pool 1 JDBC Package 3 2 4 5

39. Enables early and rapid problem detection to prevent impact to production systems Identify resource shortages across CPU, memory, and file systems, locking conflicts and deadlocks, and data skew Increase ability to meet service level agreements Provides optimization and tuning recommendations Supports trend analysis and growth planning Maintains and analyzes performance warehouse Supports a variety of database workloads Includes online transaction processing, data warehouse, and enterprise content management Monitors Workload Management environment Visualizes the end-to-end response time See where Java database workloads, transactions, and SQL requests are spending their time across the database client, application server, and network DB2 Performance Expert and Extended Insight Provides in-depth database monitoring, problem isolation, and trend analysis for DB2 for Linux, UNIX, and Windows databases. Add Extended Insight Feature for visibility into where Java database workloads, transactions, and SQL requests are spending their time

40. Scenario It seems that the first application server has a problem. Double-click to drill-down. In this situation, all applications are equally affected, and the problem seems not to be in the data server.

41. Scenario - continued Most of the time is spent for „WAS connection pool wait“ time. Double-click to drill-down and display detail information.

42. Scenario – continued 5 second wait time indicates that the maximum number of allowed connections is not sufficient… … which becomes also evident when comparing the parameters and metrics of this client with other clients.

43. Where to get IBM Data Studio ? • IBM Data Studio • www.ibm.com/software/data/studio • FAQs / Tutorials • Downloads • Forum / Blogs • Join the IBM Data Studio user community