Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying IBM DB2 9.7 Advanced Application Developer Cookbook
  • Table Of Contents Toc
IBM DB2 9.7 Advanced Application Developer Cookbook

IBM DB2 9.7 Advanced Application Developer Cookbook

By : Mohankumar Saraswatipura, Sanjay Kumar
4 (6)
Buy this Book
close
close
IBM DB2 9.7 Advanced Application Developer Cookbook

IBM DB2 9.7 Advanced Application Developer Cookbook

4 (6)
By: Mohankumar Saraswatipura, Sanjay Kumar
Buy this Book

Overview of this book

With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. DB2 pureXML optimizes Web 2.0 and SOA applications. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. This DB2 9.7 Advanced Application Developer Cookbook will provide an in-depth quick reference during any application's design and development. This practical cookbook focuses on advanced application development areas that include performance tips and the most useful DB2 features that help in designing high quality applications. This book dives deep into tips and tricks for optimized application performance. With this book you will learn how to use various DB2 features in database applications in an interactive way.
Table of Contents (15 chapters)
close
close
close
IBM DB2 9.7 Advanced Application Developer Cookbook
Icon
IBM DB2 9.7 Advanced Application Developer Cookbook
Credits
Icon
Credits
About the Authors
Icon
About the Authors
About the Reviewers
Icon
About the Reviewers
www.PacktPub.com
Icon
www.PacktPub.com
Preface
Icon
Preface
Lock Free Chapter
1
Application Development Enhancements in DB2 9.7
chevron up
Icon
Application Development Enhancements in DB2 9.7
Icon
Introduction
Icon
Changing column names online using the ALTER TABLE operation
Icon
Using the CREATE OR REPLACE clause while creating objects
Icon
Using the ALTER TABLE operation in a single transaction
Icon
Using the CREATE WITH ERROR support
Icon
Using the soft invalidation and automatic revalidation support
Icon
Using the ALTER COLUMN SET DATA TYPE extended support
Icon
Using the new TRUNCATE statement
Icon
Using AUTONOMOUS transactions
Icon
Using implicit casting during application enablement
Icon
Using the DEFAULT values and NAMED arguments in procedures
2
DB2 Application Techniques
Icon
DB2 Application Techniques
Icon
Introduction
Icon
Granting and revoking instance-level authorities
Icon
Granting and revoking database-level authorities
Icon
Granting and revoking object privileges
Icon
Implementing static SQL in DB2
Icon
Implementing dynamic SQL in DB2
Icon
Creating Declared Global Temporary Tables (DGTTs)
Icon
Using XML in a declared temporary table
Icon
Improving performance by creating indexes on a DGTT
Icon
Creating Created Global Temporary Tables (CGTT)
Icon
Using generated columns in tables
Icon
Creating a savepoint
Icon
Rolling back to a savepoint
Icon
Using savepoints in JDBC
Icon
Using savepoints in SQLJ
Icon
Creating a sequence object
Icon
Modifying a sequence object
Icon
Referencing a sequence object
3
General Application Design
Icon
General Application Design
Icon
Introduction
Icon
Improving concurrency by using a connection concentrator
Icon
Using trusted contexts for improved security
Icon
Using trusted connections in JDBC
Icon
Using trusted connections in PHP
Icon
Securing data by using DB2 encryption
Icon
Improving concurrency by using enhanced optimistic locking
Icon
Working with user-defined types (UDT)
Icon
Working with structured types
4
Procedures, Functions, Triggers, and Modules
Icon
Procedures, Functions, Triggers, and Modules
Icon
Introduction
Icon
Creating a simple stored procedure
Icon
Using dynamic SQL in stored procedures
Icon
Working with result sets in stored procedures
Icon
Using ARRAY objects in stored procedures
Icon
Handling errors in stored procedures
Icon
Designing external stored procedures
Icon
Using PL/SQL exception handling in a procedure
Icon
Working with the message buffer in stored procedures
Icon
Planning and designing triggers
Icon
Using scalar user-defined functions
Icon
Writing external user-defined functions
Icon
Designing external table functions
Icon
Working with modules in DB2
5
Designing Java Applications
Icon
Designing Java Applications
Icon
Introduction
Icon
Creating connection objects in JDBC applications
Icon
Creating connection objects in SQLJ applications
Icon
Manipulating XML Data in Java applications
Icon
Batch processing in JDBC applications
Icon
Batch processing in SQLJ applications
Icon
Working with scrollable result sets
Icon
Handling errors and warnings in JDBC applications
Icon
Developing Java applications for high availability
Icon
Using SSL in Java applications
6
DB2 9.7 Application Enablement
Icon
DB2 9.7 Application Enablement
Icon
Introduction
Icon
First step towards enabling Oracle application to DB2 9.7
Icon
Using ROWNUM and DUAL in DB2 9.7
Icon
Using CUR_COMMIT concurrency control isolation level
Icon
Implementing hierarchical queries for recursion support
Icon
Using PL/SQL ANONYMOUS block statements
Icon
Handling RAISE_APPLICATION_ERROR in DB2 PL/SQL
Icon
Migrating Oracle database objects on to DB2 9.7
Icon
Porting multi-action triggers from Oracle to DB2 9.7
7
Advanced DB2 Application Features and Practices
Icon
Advanced DB2 Application Features and Practices
Icon
Introduction
Icon
Working with OLAP functions
Icon
Using optimizer profiles
Icon
Using explain utilities
Icon
Using section explain information
Icon
Interpreting db2exfmt output
Icon
Application development in partitioned databases
8
Preparing and Monitoring Database Applications
Icon
Preparing and Monitoring Database Applications
Icon
Introduction
Icon
Preparing applications for execution
Icon
Rebinding existing packages
Icon
Customizing application behavior using registry variables
Icon
Monitoring application performance
9
Advanced Performance Tuning Tips
Icon
Advanced Performance Tuning Tips
Icon
Introduction
Icon
Understanding predicates in SQL statements
Icon
Improving INSERT performance
Icon
Writing effective SQL statements

Using the CREATE WITH ERROR support

The AUTO_REVAL database configuration parameter controls the revalidation and invalidation semantics in DB2 9.7. This configuration parameter can be altered online without taking the instance or the database down. By default, this is set to DEFERRED and can take any of the following values:

  • IMMEDIATE
  • DISABLED
  • DEFERRED
  • DEFERRED_FORCE

Now that we know all of the REVALIDATION options available in DB2 9.7, let's understand more about the CREATE WITH ERROR support. Certain database objects can now be created, even if the reference object does not exist. For example, one can create a view on a table which never existed. This eventually errors out during the compilation of the database object body, but still creates the object in the database keeping the object as INVAILD until we get the base reference object.

How to do it...

First, we will look at the ways in which we can change the AUTO_REVAL configuration parameter.

UPDATE DB CFG FOR <DBNAME> USING AUTO_REVAL [IMMEDIATE|DISABLED|DEFERRED|DEFERRED_FORCE]

CREATE WITH ERROR is supported only when we set AUTO_REVAL to DEFERRED_FORCE and the INVALID objects can be viewed from the SYSCAT.INVALIDOBJECTS system catalog table.

  1. Update the database configuration parameter AUTO_REVAL to DEFERRED_FORCE.
    UPDATE DB CFG FOR SAMPLE USING AUTO_REVAL DEFERRED_FORCE
  2. Try to create a view v_FMSALE, referring to the FMSALE base table. Since we do not have the base table currently present in the database, DB2 9.7 still creates the view, marking it as invalid until we create the base reference object. This wasn't possible in the earlier versions of DB2.
    CREATE VIEW c_FMSALE AS SELECT * FROM FMSALE
  3. How do you verify if the object is invalid? The following SQL query on the system catalog table, SYSCAT.INVALIDOBJECTS, shows why the database object is in an invalid state:
    SELECT OBJECTNAME, SQLCODE, SQLSTATE FROM SYSCAT.INVALIDOBJECTS
  4. Once you create the base reference object and access the invalid object, DB2 revalidates and marks it as valid.
  5. The following screenshot illustrates the sample output for the preceding statements:
    How to do it...

How it works...

When we create an object without a base reference object, DB2 still creates the object with a name resolution error such as the table does not exist (SQLCODE: SQL0204N SQLSTATE: 42704).

  1. DB2 creates an object even if the reference column does not exist with the error codes (SQLCODE: SQL0206N SQLSTATE: 42703).
  2. If the referenced function is not present, we get SQLCODE: SQL0440N SQLSTATE: 42884.
  3. When AUTO_REVAL is set to IMMEDIATE, all of the dependent objects will be revalidated as soon as they get invalidated. This is applicable to ALTER TABLE, ALTER COLUMN, and OR REPLACES SQL statements.
  4. When AUTO_REVAL is set to DEFERRED, all of the dependent objects will be revalidated only after they are accessed the very next time; until then, they are seen as INVALID objects in the database.
  5. When AUTO_REVAL is set to DEFERRED_FORCE, it is the same as DEFERRED plus the CREATE WITH ERORR feature is enabled.

There's more...

Let's have a quick look at the difference between AUTO_REVAL settings and behavior.

Case 1: AUTO_REVAL=DEFERRED

  1. When the table T1, on which the view V1 depends, is dropped, the drop would be successful, but V1 would be marked as invalid.
  2. After creating T1, V1 would still be marked as invalid until explicitly used.

Case 2: AUTO_REVAL=DEFERRED_FORCE

  1. One can create an object without having the base reference object present in the database; this only happens when we set AUTO_REVAL to DEFERRED_FORCE.
  2. Object revalidation happens when an object is being accessed.
There's more...
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
IBM DB2 9.7 Advanced Application Developer Cookbook
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon