IBM DB2 9.7 Advanced Application Developer Cookbook电子书

IBM DB2 9.7 Advanced Application Developer Cookbook

Table of Contents

IBM DB2 9.7 Advanced Application Developer Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Application Development Enhancements in DB2 9.7

Introduction

Changing column names online using the ALTER TABLE operation

Getting ready

How to do it...

How it works…

See also

Using the CREATE OR REPLACE clause while creating objects

Getting ready

How to do it...

How it works…

Using the ALTER TABLE operation in a single transaction

Getting ready

How to do it...

How it works…

Using the CREATE WITH ERROR support

How to do it...

How it works...

There's more...

Using the soft invalidation and automatic revalidation support

Getting ready

How to do it...

How it works...

There's more...

Using the ALTER COLUMN SET DATA TYPE extended support

Getting ready

How to do it...

How it works...

There's more...

Using the new TRUNCATE statement

Getting ready

How to do it...

How it works...

There's more...

Using AUTONOMOUS transactions

How to do it...

How it works…

Using implicit casting during application enablement

Getting ready…

How to do it...

How it works…

There's more…

Using the DEFAULT values and NAMED arguments in procedures

Getting ready

How to do it…

How it works…

2. DB2 Application Techniques

Introduction

Granting and revoking instance-level authorities

Getting ready

How to do it...

Granting instance-level authorities

Revoking authorities

How it works....

There's more...

What changed in DB2 9.7

Granting and revoking database-level authorities

Getting ready

How to do it...

Granting database-level authorities

Revoking authorities

How it works...

There's more…

Granting and revoking object privileges

Getting ready

How to do it...

Granting privileges

Revoking privileges

There's more…

Implementing static SQL in DB2

Getting ready

How to do it...

How it works…

There's more…

Implementing dynamic SQL in DB2

How to do it...

How it works...

There's more…

Statement concentrator for dynamic SQL

Choosing between static and dynamic SQL

Creating Declared Global Temporary Tables (DGTTs)

Getting ready

How to do it...

How it works...

There's more...

Referencing a declared global temporary table

Dropping a declared temporary table

Using declared temporary tables across transactions

Using Admin views to view temporary tables information

Using XML in a declared temporary table

How to do it...

How it works…

There's more…

Improving performance by creating indexes on a DGTT

How to do it...

How it works…

There's more…

Creating Created Global Temporary Tables (CGTT)

Getting ready

How to do it...

How it works...

There's more…

Referencing a CGTT

Dropping a CGTT

Using generated columns in tables

Getting ready

How to do it...

How it works...

There's more…

LOAD and IMPORT considerations for generated columns

Using a generated column as range partitioning key

Using generated columns as MDC dimensions

Using generated columns for performance

Creating a savepoint

How to do it...

How it works...

There's more…

Rolling back to a savepoint

How to do it...

Using savepoints in JDBC

Getting ready…

How to do it…

Using savepoints in SQLJ

Getting ready…

How to do it…

Creating a sequence object

Getting ready

How to do it...

How it works...

There's more…

Modifying a sequence object

Getting ready

How to do it...

How it works…

Referencing a sequence object

Getting ready

How to do it...

How it works…

There's more…

3. General Application Design

Introduction

Improving concurrency by using a connection concentrator

Getting ready

How to do it

How it works...

There's more...

Differences between connection pooling and the connection concentrator

Using trusted contexts for improved security

Getting ready

How to do it

How it works...

Acquiring role membership through a trusted context

Enhancing performance

Altering a trusted context

Using trusted connections in JDBC

Getting ready

How to do it

How it works...

Using trusted connections in PHP

Getting ready

How to do it...

Securing data by using DB2 encryption

Getting ready

How to do it...

How it works...

There's more...

Improving concurrency by using enhanced optimistic locking

How to do it...

How it works

There's more...

Working with user-defined types (UDT)

How to do it...

There's more…

Working with structured types

Getting ready

How it works...

Dropping typed tables

Defining constraints on object identifier columns

4. Procedures, Functions, Triggers, and Modules

Introduction

Creating a simple stored procedure

Getting ready

How to do it...

How it works...

There's more

Stored procedure execution

Calling a stored procedure

Using named parameters

Using dynamic SQL in stored procedures

How to do it...

How it works...

Working with result sets in stored procedures

How to do it...

How it works...

There's more…

Using ARRAY objects in stored procedures

How to do it...

There's more…

Using procedures with ARRAY parameters in Java

Handling errors in stored procedures

How to do it...

How it works...

Designing external stored procedures

Getting ready

How to do it...

How it works...

There's more…

Parameter styles in external stored procedures

Using PL/SQL exception handling in a procedure

How to do it...

How it works...

There's more…

Explicitly raising an exception

Working with the message buffer in stored procedures

Getting ready

How to do it…

How it works...

Planning and designing triggers

Getting ready

How to do it...

How it works...

There's more…

Modifying and dropping triggers

Using scalar user-defined functions

Getting ready

How to do it...

How it works...

There's more…

Restrictions on user-defined functions

Writing external user-defined functions

How to do it...

How it works...

There's more...

Using scratchpad in external functions

Designing external table functions

How to do it...

How it works...

Working with modules in DB2

Getting ready

How to do it

How it works

There's more…

Module privileges

Object name resolution

5. Designing Java Applications

Introduction

Creating connection objects in JDBC applications

Getting ready

How to do it...

How it works...

There's more…

Creating connection objects in SQLJ applications

Getting ready

How to do it...

How it works...

Java packages for SQLJ applications

Manipulating XML Data in Java applications

Getting ready

How to do it...

How it works...

There's more...

Batch processing in JDBC applications

Getting ready

How to do it...

Running SQL multiple times with different values for parameter markers

Running multiple SQL statements in a batch

How it works...

There's more...

Restrictions on executing statements in a batch

Batch processing in SQLJ applications

Getting ready

How to do it...

How it works...

Working with scrollable result sets

Getting ready

How to do it ...

How it works...

There's more...

Using scrollable iterators in SQLJ applications

Handling errors and warnings in JDBC applications

Getting ready

How to do it...

How it works...

Handling SQL warnings

Using SQLException subclasses

Developing Java applications for high availability

Getting ready

How to do it...

Configuring an automatic client reroute

Configuring client affinity

Workload balancing by using DB2 pureScale

How it works...

Automatic client rerouting

Workload balancing in a pureScale environment

Using SSL in Java applications

Getting ready

How to do it...

Step 1: Configuring JRE for SSL Support

Step 2: Setting up the server environment

Step 3: Setting up the client environment

Step 4: Configuring connections to use SSL

How it works...

6. DB2 9.7 Application Enablement

Introduction

First step towards enabling Oracle application to DB2 9.7

Getting ready

How to do it...

How it works…

There's more...

Using ROWNUM and DUAL in DB2 9.7

Getting ready

How to do it...

How it works…

There's more…

Using CUR_COMMIT concurrency control isolation level

Getting ready

How to do it...

How it works…

Implementing hierarchical queries for recursion support

Getting ready

How to do it...

How it works...

Using PL/SQL ANONYMOUS block statements

Getting ready

How to do it...

How it works...

There's more…

Handling RAISE_APPLICATION_ERROR in DB2 PL/SQL

Getting ready

How to do it...

How it works…

Migrating Oracle database objects on to DB2 9.7

Getting ready

How to do it...

How it works…

Porting multi-action triggers from Oracle to DB2 9.7

Getting ready

How to do it...

How it works…

7. Advanced DB2 Application Features and Practices

Introduction

Working with OLAP functions

Getting ready

How to do it...

How it works...

There's more...

Other ways of defining an OLAP window

Using optimizer profiles

Getting ready

How to do it...

How it works...

There's more...

Examples of optimization profiles

List of guidelines available

Using explain utilities

Getting ready

How to do it...

How it works...

There's more...

Capturing explain information for a routine

Explain tables

Using section explain information

Getting ready

How to do it...

How it works

There's more...

Interpreting db2exfmt output

How to do it...

How it works

There's more...

Understanding optimizer joins

Operators in an access plan

Application development in partitioned databases

How to do it

There's more

Database partitioning feature

Range partitioning

Multidimensional clustering

Putting everything together

8. Preparing and Monitoring Database Applications

Introduction

Preparing applications for execution

Getting ready

How to do it...

How it works...

Understanding the consistency token

There's more...

Advantages of deferred binding

Packages and SQL routines

Rebinding existing packages

Getting ready

How to do it...

How it works...

There's more...

Customizing application behavior using registry variables

Getting ready

How to do it...

How it works...

There's more...

Monitoring application performance

How to do it...

How it works...

9. Advanced Performance Tuning Tips

Introduction

Understanding predicates in SQL statements

Getting ready

How to do it...

How it works...

There's more...

Improving INSERT performance

Getting Ready

How to do it...

How it works...

Writing effective SQL statements

Getting Ready

How to do it...

Index