Preface

Welcome to the DB2 9.7 Advanced Application Developer Cookbook. DB2 9.7 is a truly incredible database product from IBM, which offers an excellent mix of application performance, reliability, and ease of development enablement.

In this cookbook, you can find both: hands-on and real world practical application development examples, as well as key references that provide a context for what you are learning.

As a reader, you will find several examples on new features in DB2 9.7 edition, including the following:

• A quick summary on new application development enhancements

• A step-by-step approach to port an Oracle 11g database to DB2 9.7

• Very generic application development techniques

• More detail on application designs

• A complete discussion on advanced OLAP functions

• Systematic approach in building stored procedures, functions, triggers,and modules

• Helping application developers to monitor the database performance and tuning for the better

What this book covers

Chapter 1, Application Development Enhancements in DB2 9.7, introduces the application development enhancements in DB2 9.7, which help most of the developer community to use the existing DB2 features, instead of building the application logic.

Chapter 2, DB2 Application Techniques, introduces all the basic techniques and aspects of application development that are very generic, and can be used with any programming language.

Chapter 3, General Application Design, introduces some advanced techniques that can be used to design efficient applications, including security aspects in a three-tier architecture, various methods of data encryption, extending the data type support, and so on.

Chapter 4, Procedures, Functions, Triggers, and Modules, introduces the different types of functional database objects that can be used to encapsulate business logic at the database server, including stored procedures, user defined functions, triggers, and modules.

Chapter 5, Designing Java Applications, introduces Java as a programming language, focusing on advanced topics, such as different ways of connecting to data sources, designing enhanced security in Java applications, handling XML data, and so on.

Chapter 6, DB2 9.7 Application Enablement, provides DB2 9.7 application enablement recipes, along with step-by-step instructions to port Oracle 11g database objects to DB2 9.7 using the IBM DataMovementTool (DMT). It also illustrates, with examples, how to use new functionalities, such as ROWNUM, DUAL, CUR_COMMIT isolation level, and ANONYMOUSPL/SQL blocks.

Chapter 7, Advanced DB2 Application Features and Practices, introduces some advanced DB2 features that are useful for analysing and troubleshooting application performance. This chapter also discusses some OLAP functions, which are very powerful for computing complex use cases in a very simple manner.

Chapter 8, Preparing and Monitoring Database Applications, discusses various tips and techniques that can be used to prepare an application environment. This chapter also focuses on the latest monitoring techniques introduced in DB2 9.7.

Chapter 9, Advanced Performance Tuning Tips, focuses on various tips and techniques that can be used to enhance application performance. It also discusses some best practices that should be followed while writing SQL statements for better performance.

What you need for this book

Ensure that the system meets the necessary operating system, memory, and disk requirements for a DB2 9.7 installation. The most recent requirements for DB2 9.7 data server installations are available on the Web at http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.qb.server.doc/doc/r0025127.html.

Along with DB2 9.7 code base, ensure that the system also meets the requirements for the IBM Data Movement Tool installation. The most recent update is available at: http://www.ibm.com/services/forms/preLogin.do?lang=en_US&source=idmt.

Who this book is for

If you are an IBM DB2 application developer who would like to exploit the advanced features provided by DB2 to design and implement high quality applications, then this book is for you.

This book assumes you have a basic understanding of DB2 application development.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "We can include other contexts through the use of the include directive."

A block of code is set as follows:

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
<!-- Global optimization guidelines section. -->
<OPTGUIDELINES>............</OPTGUIDELINES>
<!-- Statement profile section. -->
<STMTPROFILE ID="Guidelines for Q1">
<STMTKEY SCHEMA="TEST">
<![CDATA[SQL Query]]>
</STMTKEY>
<OPTGUIDELINES>...........</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

<OPTGUIDELINES>
<MQTENFORCE>

<NAME='MQT1'/>
<TYPE='REPLICATED'/>

</MQTENFORCE>
<OPTGUIDELINES>

Any command-line input or output is written as follows:

CREATE TABLE REPLACE1 (c1 INT, c2 INT)
CREATE TABLE REPLACE2 (c1 INT, c2 INT)
CREATE VIEW v1 AS SELECT * FROM REPLACE1
CREATE VIEW v2 as SELECT * FROM v1
CREATE FUNCTION fun1()
LANGUAGE SQL
RETURNS INT
RETURN SELECT c1 FROM v2
CREATE OR REPLACE VIEW v1 AS SELECT * FROM REPLACE2

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "In the previous section, we can observe following information for Operator#2:".

Note

Warnings or important notes appear in a box like this.

Note

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to<[e-mail]>, and mention the book title via the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail<[e-mail]>.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books maybe a mistake in the text or the code we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the erratasubmissionform link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at<[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at<[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.