book

IBM DB2 9.7 Advanced Application Developer Cookbook

Name: IBM DB2 9.7 Advanced Application Developer Cookbook
ISBN: 9781849683968

by Mohankumar Saraswatipura, Sanjay Kumar

March 2012

Intermediate to advanced

442 pages

9h 31m

English

Packt Publishing

Read now

Unlock full access

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 moreWhy Subscribe?Free Access for Packt account holdersInstant 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 codeErrataPiracyQuestions
1. Application Development Enhancements in DB2 9.7
Introduction
Changing column names online using the ALTER TABLE operation
Getting readyHow to do it...How it works…See also
Using the CREATE OR REPLACE clause while creating objects
Getting readyHow to do it...How it works…
Using the ALTER TABLE operation in a single transaction
Getting readyHow 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 readyHow to do it...How it works...There's more...
Using the ALTER COLUMN SET DATA TYPE extended support
Getting readyHow to do it...How it works...There's more...
Using the new TRUNCATE statement
Getting readyHow 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 readyHow to do it…How it works…
2. DB2 Application Techniques
Introduction
Granting and revoking instance-level authorities
Getting readyHow to do it...Granting instance-level authoritiesRevoking authoritiesHow it works....There's more...What changed in DB2 9.7
Granting and revoking database-level authorities
Getting readyHow to do it...Granting database-level authoritiesRevoking authoritiesHow it works...There's more…
Granting and revoking object privileges
Getting readyHow to do it...Granting privilegesRevoking privilegesThere's more…
Implementing static SQL in DB2
Getting readyHow 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 SQLChoosing between static and dynamic SQL
Creating Declared Global Temporary Tables (DGTTs)
Getting readyHow to do it...How it works...There's more...Referencing a declared global temporary tableDropping a declared temporary tableUsing declared temporary tables across transactionsUsing 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 readyHow to do it...How it works...There's more…Referencing a CGTTDropping a CGTT
Using generated columns in tables
Getting readyHow to do it...How it works...There's more…LOAD and IMPORT considerations for generated columnsUsing a generated column as range partitioning keyUsing generated columns as MDC dimensionsUsing 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 readyHow to do it...How it works...There's more…
Modifying a sequence object
Getting readyHow to do it...How it works…
Referencing a sequence object
Getting readyHow to do it...How it works…There's more…
3. General Application Design
Introduction
Improving concurrency by using a connection concentrator
Getting readyHow to do itHow it works...There's more...Differences between connection pooling and the connection concentrator
Using trusted contexts for improved security
Getting readyHow to do itHow it works...Acquiring role membership through a trusted contextEnhancing performanceAltering a trusted context
Using trusted connections in JDBC
Getting readyHow to do itHow it works...
Using trusted connections in PHP
Getting readyHow to do it...
Securing data by using DB2 encryption
Getting readyHow to do it...How it works...There's more...
Improving concurrency by using enhanced optimistic locking
How to do it...How it worksThere's more...
Working with user-defined types (UDT)
How to do it...There's more…
Working with structured types
Getting readyHow it works...Dropping typed tablesDefining constraints on object identifier columns
4. Procedures, Functions, Triggers, and Modules
Introduction
Creating a simple stored procedure
Getting readyHow to do it...How it works...There's moreStored procedure executionCalling a stored procedureUsing 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 readyHow 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 readyHow to do it…How it works...
Planning and designing triggers
Getting readyHow to do it...How it works...There's more…Modifying and dropping triggers
Using scalar user-defined functions
Getting readyHow 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 readyHow to do itHow it worksThere's more…Module privilegesObject name resolution
5. Designing Java Applications
Introduction
Creating connection objects in JDBC applications
Getting readyHow to do it...How it works...There's more…
Creating connection objects in SQLJ applications
Getting readyHow to do it...How it works...Java packages for SQLJ applications
Manipulating XML Data in Java applications
Getting readyHow to do it...How it works...There's more...
Batch processing in JDBC applications
Getting readyHow to do it...Running SQL multiple times with different values for parameter markersRunning multiple SQL statements in a batchHow it works...There's more...Restrictions on executing statements in a batch
Batch processing in SQLJ applications
Getting readyHow to do it...How it works...
Working with scrollable result sets
Getting readyHow to do it ...How it works...There's more...Using scrollable iterators in SQLJ applications
Handling errors and warnings in JDBC applications
Getting readyHow to do it...How it works...Handling SQL warningsUsing SQLException subclasses
Developing Java applications for high availability
Getting readyHow to do it...Configuring an automatic client rerouteConfiguring client affinityWorkload balancing by using DB2 pureScaleHow it works...Automatic client reroutingWorkload balancing in a pureScale environment
Using SSL in Java applications
Getting readyHow to do it...Step 1: Configuring JRE for SSL SupportStep 2: Setting up the server environmentStep 3: Setting up the client environmentStep 4: Configuring connections to use SSLHow it works...
6. DB2 9.7 Application Enablement
Introduction
First step towards enabling Oracle application to DB2 9.7
Getting readyHow to do it...How it works…There's more...
Using ROWNUM and DUAL in DB2 9.7
Getting readyHow to do it...How it works…There's more…
Using CUR_COMMIT concurrency control isolation level
Getting readyHow to do it...How it works…
Implementing hierarchical queries for recursion support
Getting readyHow to do it...How it works...
Using PL/SQL ANONYMOUS block statements
Getting readyHow to do it...How it works...There's more…
Handling RAISE_APPLICATION_ERROR in DB2 PL/SQL
Getting readyHow to do it...How it works…
Migrating Oracle database objects on to DB2 9.7
Getting readyHow to do it...How it works…
Porting multi-action triggers from Oracle to DB2 9.7
Getting readyHow to do it...How it works…
7. Advanced DB2 Application Features and Practices
Introduction
Working with OLAP functions
Getting readyHow to do it...How it works...There's more...Other ways of defining an OLAP window
Using optimizer profiles
Getting readyHow to do it...How it works...There's more...Examples of optimization profilesList of guidelines available
Using explain utilities
Getting readyHow to do it...How it works...There's more...Capturing explain information for a routineExplain tables
Using section explain information
Getting readyHow to do it...How it worksThere's more...
Interpreting db2exfmt output
How to do it...How it worksThere's more...Understanding optimizer joinsOperators in an access plan
Application development in partitioned databases
How to do itThere's moreDatabase partitioning featureRange partitioningMultidimensional clusteringPutting everything together
8. Preparing and Monitoring Database Applications
Introduction
Preparing applications for execution
Getting readyHow to do it...How it works...Understanding the consistency tokenThere's more...Advantages of deferred bindingPackages and SQL routines
Rebinding existing packages
Getting readyHow to do it...How it works...There's more...
Customizing application behavior using registry variables
Getting readyHow 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 readyHow to do it...How it works...There's more...
Improving INSERT performance
Getting ReadyHow to do it...How it works...
Writing effective SQL statements
Getting ReadyHow to do it...
Index

Content preview from IBM DB2 9.7 Advanced Application Developer Cookbook

Using the new TRUNCATE statement

In the earlier version of DB2, in order to empty the tables, we used the DELETE statement. The DELETE statement logs everything, so it's not efficient when we are dealing with a large volume of data. An alternate solution is to load the table using a null file and replacing the table data with it. In DB2 9.7, the TRUNCATE command is introduced, which deletes the data from a table quickly and does not log the activity, resulting in very good performance.

Getting ready

We need one of the following privileges to execute the TRUNCATE command:

DELETE privilege
CONTROL privilege
DATAACCESS authority

How to do it...

TRUNCATE is just a simple command that can also be embedded in any host language.

Truncating a table with DROP ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781849683968Other

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design