Table Of ContentG E T T I N G S T A R T E D W I T H
pureQuery
A book for the community by the community
VITOR RODRIGUES, ZEUS COURTOIS, HEATHER LAMB,
CHRISTINA SHEETS, KATHRYN ZEIDENSTEIN
F I R S T E D I T I O N
4 Getting Started with pureQuery
First Edition December 2010
© Copyright IBM Corporation 2010. All rights reserved.
Notices
This information was developed for products and services offered in the U.S.A.
IBM may not offer the products, services, or features discussed in this document in other countries.
Consult your local IBM representative for information on the products and services currently available
in your area. Any reference to an IBM product, program, or service is not intended to state or imply
that only that IBM product, program, or service may be used. Any functionally equivalent product,
program, or service that does not infringe any IBM intellectual property right may be used instead.
However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product,
program, or service.
IBM may have patents or pending patent applications covering subject matter described in this
document. The furnishing of this document does not grant you any license to these patents. You can
send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.
For license inquiries regarding double-byte character set (DBCS) information, contact the IBM
Intellectual Property Department in your country or send inquiries, in writing, to:
Intellectual Property Licensing
Legal and Intellectual Property Law
IBM Japan, Ltd.
3-2-12, Roppongi, Minato-ku, Tokyo 106-8711
The following paragraph does not apply to the United Kingdom or any other country where
such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES
CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in
certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are
periodically made to the information herein; these changes will be incorporated in new editions of the
publication. IBM may make improvements and/or changes in the product(s) and/or the program(s)
described in this publication at any time without notice.
Any references in this information to non-IBM Web sites are provided for convenience only and do
not in any manner serve as an endorsement of those Web sites. The materials at those Web sites
are not part of the materials for this IBM product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it believes appropriate without
incurring any obligation to you.
6 Getting Started with pureQuery
The licensed program described in this document and all licensed material available for it are
provided by IBM under terms of the IBM Customer Agreement, IBM International Program License
Agreement or any equivalent agreement between us.
Any performance data contained herein was determined in a controlled environment. Therefore, the
results obtained in other operating environments may vary significantly. Some measurements may
have been made on development-level systems and there is no guarantee that these measurements
will be the same on generally available systems. Furthermore, some measurements may have been
estimated through extrapolation. Actual results may vary. Users of this document should verify the
applicable data for their specific environment.
Information concerning non-IBM products was obtained from the suppliers of those products, their
published announcements or other publicly available sources. IBM has not tested those products and
cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM
products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of
those products.
All statements regarding IBM's future direction or intent are subject to change or withdrawal without
notice, and represent goals and objectives only.
This information contains examples of data and reports used in daily business operations. To
illustrate them as completely as possible, the examples include the names of individuals, companies,
brands, and products. All of these names are fictitious and any similarity to the names and addresses
used by an actual business enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information contains sample application programs in source language, which illustrate
programming techniques on various operating platforms. You may copy, modify, and distribute these
sample programs in any form without payment to IBM, for the purposes of developing, using,
marketing or distributing application programs conforming to the application programming interface
for the operating platform for which the sample programs are written. These examples have not been
thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability,
serviceability, or function of these programs. The sample programs are provided "AS IS", without
warranty of any kind. IBM shall not be liable for any damages arising out of your use of the sample
programs.
References in this publication to IBM products or services do not imply that IBM intends to make
them available in all countries in which IBM operates.
If you are viewing this information softcopy, the photographs and color illustrations may not
appear.
Trademarks
IBM, the IBM logo, DB2, z/OS, Optim, and ibm.com are trademarks or registered trademarks of
International Business Machines Corp., registered in many jurisdictions worldwide. Other product and
service names might be trademarks of IBM or other companies. A current list of IBM trademarks is
available on the Web at “Copyright and trademark information” at
www.ibm.com/legal/copytrade.shtml.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States,
other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries,
or both.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other company, product, or service names may be trademarks or service marks of others.
9
Table of Contents
Preface ............................................................................................................................. 17
Who should read this book? ........................................................................................ 17
How is this book structured? ........................................................................................ 17
A book for the community ............................................................................................ 18
Conventions ................................................................................................................. 19
What’s next? ................................................................................................................ 19
About the Authors ........................................................................................................... 21
Contributors .................................................................................................................... 23
Acknowledgements ........................................................................................................ 24
PART I – OVERVIEW AND SETUP ................................................................................. 25
Chapter 1 - Introduction to pureQuery.......................................................................... 27
1.1 The big picture: What is pureQuery? ..................................................................... 27
1.1.1 What’s in a name? .......................................................................................... 28
1.1.2 What do we mean by platform? ...................................................................... 29
1.2 pureQuery application programming interfaces ..................................................... 30
1.3 pureQuery runtime ................................................................................................. 31
1.4 pureQuery application development environment ................................................. 33
1.5 pureQuery monitoring services .............................................................................. 35
1.6 Career path ............................................................................................................ 38
1.7 The pureQuery community .................................................................................... 38
1.7.1 Popular community Web sites ........................................................................ 39
1.8 Competitive products ............................................................................................. 40
1.9 Exercises ............................................................................................................... 42
1.10 Review Questions ................................................................................................ 42
Chapter 2 - Installing pureQuery (Optim Development Studio) ................................. 45
2.1 Installation: The big picture .................................................................................... 45
2.2 Installation prerequisites ........................................................................................ 46
2.3 Installing Optim Development Studio ..................................................................... 47
2.3.1 Installing from the launchpad .......................................................................... 48
2.3.2 Applying Optim Development Studio Fixpack 1 ............................................. 54
2.4 Running Optim Development Studio ..................................................................... 55
2.4.1 Adding pureQuery support to projects ............................................................ 55
2.5 Summary ................................................................................................................ 58
2.6 Exercise ................................................................................................................. 58
2.7 Review Questions .................................................................................................. 59
PART II – TOOLING ......................................................................................................... 61
Chapter 3 - Code generation and customized Java Editor Integration ..................... 65
3.1 pureQuery Tooling: The big picture ....................................................................... 65
3.1.1 Generating pureQuery code ........................................................................... 66
3.2 Overview of the different types of wizards for code generation within the pureQuery
tooling........................................................................................................................... 70
10 Getting Started with pureQuery
3.2.1 Stored procedure code generation wizard ...................................................... 71
3.2.2 Generating or updating JUnit testcases for existing interfaces ...................... 71
3.3 SQL editor integration inside the Java editor ......................................................... 71
3.3.1 SQL validation within the Java editor ............................................................. 72
3.3.2 SQL semantic and syntactic content assist for SQL inside Java editor ......... 72
3.3.3 Test and run SQL within your Java program .................................................. 73
3.4 Summary ................................................................................................................ 73
3.5 Review Questions .................................................................................................. 73
Chapter 4 - pureQuery tooling Views ............................................................................ 75
4.1 pureQuery tooling: The big picture ........................................................................ 75
4.2 SQL Outline view ................................................................................................... 76
4.2.1 Gather the SQL statements issued by the application ................................... 76
4.2.2 Gain insight into where SQL statements are issued within your application .. 78
4.2.3 Pages of the SQL Outline view ....................................................................... 79
4.2.4 Enhanced impact analysis within database applications ................................ 82
4.3 Designing and binding packages for static SQL development .............................. 83
4.3.1 Designing database packages ....................................................................... 84
4.3.2 Binding database packages ........................................................................... 86
4.4 pureQuery Analysis view ....................................................................................... 87
4.5 Enforce data privacy from modeling through development ................................... 88
4.6 Summary ................................................................................................................ 90
4.7 Review Questions .................................................................................................. 91
Chapter 5 - Developing for performance ...................................................................... 93
5.1 Developing for performance: The big picture ........................................................ 93
5.2 Identify the time and frequency of execution and cost of a statement .................. 94
5.2.1 Gathering and viewing performance metrics .................................................. 94
5.2.2 Gathering and viewing explain data ............................................................... 97
5.3 Optimizing your application for performance ......................................................... 97
5.3.1 Optimize your applications by using Optim Query Tuner ............................... 98
5.4 Comparing performance results after optimizing your application ........................ 99
5.5 Summary .............................................................................................................. 100
5.6 Review Questions ................................................................................................ 101
PART III – SECURITY, PERFORMANCE, AND INSIGHT ............................................ 105
Chapter 6 - The Client Optimizer: pureQuery for Existing JDBC Applications ...... 107
6.1 Client Optimizer: The big picture ......................................................................... 107
6.2 Overview of how to use the Client Optimizer ....................................................... 108
6.3 JDBC driver requirements for using Client Optimizer functionality ...................... 112
6.4 pureQuery properties ........................................................................................... 112
6.5 Capturing SQL statements .................................................................................. 114
6.5.1 pureQuery properties during capture ............................................................ 115
6.5.2 In practice: Capture SQL statements in the Human Resources Manager
application .............................................................................................................. 115
