IBM DB2 9.7 Advanced Application Developer Cookbook by Kumar, Sanjay -- Read -- Imperial Library of Trantor

Index

IBM DB2 9.7 Advanced Application Developer Cookbook

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

← Prev
Back
Next →

← Prev
Back
Next →