51,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Veröffentlichungsjahr: 2012
Ähnliche
Table of Contents
IBM DB2 9.7 Advanced Application Developer Cookbook
IBM DB2 9.7 Advanced Application Developer Cookbook
Copyright © 2012 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: March 2012
Production Reference: 1050312
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-84968-396-8
www.packtpub.com
Cover Image by Artie Ng (<[email protected]> )
Credits
Authors
Sanjay Kumar
Mohankumar Saraswatipura
Reviewers
Nadir Doctor
Saurabh Jain
Eldho Mathew
Acquisition Editor
Rukhsana Khambatta
Lead Technical Editor
Dayan Hyames
Technical Editors
Manasi Poonthottam
Lubna Shaikh
Mohammed Sahil
Copy Editors
Leonard D’silva
Neha Shetty
Aaron Rosario
Project Coordinator
Michelle Quadros
Proofreader
Sandra Hopper
Indexer
Monica Ajmera
Graphics
Valentina D’souza
Manu Joseph
Production Coordinator
Alwin Roy
Cover Work
Alwin Roy
About the Authors
Sanjay Kumar has been working as a database developer and architect for seven years. He has a lot of expertise in data modeling and performance tuning for business intelligence systems. He is an IBM certified advanced database administrator, application developer, and solution developer. He also has a ‘PUBLISH’ rated disclosure on data compression, and is an active member of the DB2 community.
He started his career with IBM India Software Labs as part of the DB2 development team. After working with IBM for five years, he moved to work for an investment bank as a data architect.
First of all, I would like to thank my lovely wife for supporting me throughout several months; without your support, I wouldn’t have been able to accomplish this.
I would also like to thank the Packt Publishing staff for their help and support in getting this book published. I am especially grateful to Rukhsana who helped me understand the structure of cookbooks and professional writing. I would also like to thank Zainab, Dayan, and Michelle for keeping track of the schedule and reviews. Special thanks to Lubna and Manasi for making my book look professional.
I would also like to thank the technical reviewers, Saurabh Jain and Eldho Mathew for providing excellent suggestions. Last, but not least, I would like to thank Packt Publishing for getting my first book published. It was a pleasure working with Packt Publishing.
Mohankumar Saraswatipura started his career in DB2 8.1. He has worked as a database application developer and a performance consultant in High Performance On Demand Solutions (HiPODS) for IBM Software Labs, India. He is currently working as a Lead DB2 database administrator, helping application developers on Balanced Warehouse D5100, Siebel 8, and SAP ECC implementations. He is experienced in DB2 application performance tuning and database design. He is an IBM certified application developer, InfoSphere solution designer, XML solution developer, and Advanced DB2 database administrator.
He is an active member of IDUG India and an IBM Information Champion. Mohan completed his Master’s of Technology in computer science in 2004, and Executive MBA from IIMC in 2007.
Mohan has also worked on the following articles:
I work for an incredible organization, with brilliant people, where I’ve had opportunities to learn and grow. Thanks to Nelson Coish, and the higher management for helping me all the way. Thanks also to my wife, Dr. Nirmala Kammar, for being so kind when things were needed. Thanks, of course, to all at Packt Publishing group, especially Rukhsana, Zainab, Dayan, Michelle, Lubna, and Manasi. It was a great experience working with you all.
About the Reviewers
Saurabh Jain is part of the IBM tech sales team, covering the DM portfolio from IBM with a significant focus on DB2. Earlier, he was a part of the DB2 engineering lab, where he worked on the DB2 product. He is a DBA and field expert, having a significant amount of practical knowledge about DB2. He has done extensive work on database architecture, design, and development, with an emphasis on implementation best practices and evangelizing the product. He has co-authored some DB2 best practices documents published in IBM's developerWorks, and also has a couple of patents.
Eldho Mathew is a DB2 LUW, Linux, and AIX certified administrator, with eight years of proven expertise in various aspects of building, administering, and supporting highly complex 24x7 operational and warehouse database servers. He has handled highly complex and critical systems for many top branded customers in the UK.
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at<[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
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:
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:
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
Any command-line input or output is written as follows:
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.
Tip
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<[email protected]>, 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<[email protected]>.
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<[email protected]> 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<[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Application Development Enhancements in DB2 9.7
In this chapter, we will focus on the following recipes related to application development enhancements in DB2 9.7 that help the application developer community to use DB2 features, instead of application logic:
Introduction
DB2 9.7 provides many enhanced application features that make an application developer's life easier. In this chapter, we will focus on most of the new application features and their usage along with examples. This helps developers to understand the new features with respect to improving the application portability. The chapter is divided into various recipes and each recipe is followed by an example that helps in understanding the concept better.
Changing column names online using the ALTER TABLE operation
To rename a column in earlier versions of DB2, we used to recreate the table with a new column name and then insert the data from the earlier table on to a newly created table. The catch here is that while renaming the table, the source table should not have any references such as views, indexes, MQTs, functions, triggers, and constraints. This makes an application developer depend on a database administrator while changing the database object, based on the business requirement. In DB2 9.7, renaming a column is made extremely easy with just a single command inside the application code.
Getting ready
You need to have the ALTER privilege on the table that needs to be altered.
How to do it...
You can rename an existing column in the table to a new name without losing the data, privileges, and LBAC policies.
The DB2 command syntax to rename the column is as follows:
For example:
After renaming the column, the application can start accessing the table without a table REORG requirement.
How it works…
When an ALTER TABLE RENAME COLUMN command runs on the system, DB2 will rename the column in the table and invalidate the dependent objects (if any) such as views, functions, procedures, materialized query tables (MQT), and so on. Invalidated objects would get validated when the dependent objects are being accessed within the application or outside the application by a user. This automatic revalidation of invalid database objects depends on the value of the database configuration parameter, auto_reval.
See also
Refer to the Using the CREATE WITH ERROR support recipe for more details on automatic revalidation of invalid database objects, discussed in this chapter.
Using the CREATE OR REPLACE clause while creating objects
In DB2 9.7, we can create new database objects, such as aliases, procedures, functions, sequences, triggers, views, nicknames, and variables, with a CREATE OR REPLACE clause. These clauses would replace an object if it's already present; otherwise, they create a new object.
The privileges are preserved while replacing an object. In the case of modules, all of the objects within the module are dropped and the replaced version contains no objects.
The main benefit of using this feature is that DB2 doesn't have to wait for a lock on the database object being replaced. Without this feature, we cannot drop an object that is being used. Now DB2 is very intelligent and capable of making a judgment and recreating the object, even if it's been locked.
Getting ready
For the existing database objects, we need the CONTROL privilege, as the objects will be dropped and recreated.
How to do it...
When we use CREATE OR REPLACE, it replaces the earlier object, if it already exists; otherwise, it creates the object. This feature helps application developers not to worry about existing objects, but the production support team should be very cautious while using this.
Tip
Downloading the example code
You can download the example code fles 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 fles e-mailed directly to you.
How it works…
The CREATE OR REPLACE command will create the object specified, if it doesn't exist, or drop and recreate the object, if it's already present. During this process of recreation, it invalidates any dependent objects. Based on the AUTO_REVAL parameter settings, DB2 will automatically revalidate the dependent objects after recreating the objects with the new definition.
Using the ALTER TABLE operation in a single transaction
When we perform the ALTER TABLE operations, such as dropping a column, in earlier versions of DB2, modifying the data types would force the database administrator to perform REORG on the table before any application would work on the table. Now in DB2 9.7, one can perform an unlimited number of table alterations within a single unit of work. This will allow a data modeler to manage the alteration of the column easily.
Getting ready
By default, autocommit is ON in DB2. That means each SQL statement we execute is a unit of work or a transaction. There are many ways to execute the unit of work; the simplest one is to turn off autocommit, or use db2 + c at the beginning, or use atomic procedures.
The different options available for the DB2 command can be listed using the following command:
How to do it...
In this section, we'll see how multiple ALTER TABLE operations are allowed in three different units of work. This can be implemented in any language, but the user must have privileges to alter a table.
For demonstration purposes, we have created the EMPLOYEEE table, which is a replica of EMPLOYEE of the sample database without the dependent objects. If there are dependent objects, we may have to drop them and perform the ALTER TABLE statement.
The following screenshot shows the sample output for the preceding statements:
How it works…
When a table is altered with operations, such as dropping a column, altering a column data type, or altering the nullability feature of a column, the table may be placed in a REORG PENDING state. While the table is in the REORG state, no queries can be run until the table is brought online from the REORG PENDING state by executing the REORG command. Starting with DB2 9.7, one can perform an unlimited number of ALTER TABLE statements in a single transaction with a maximum of three transactions in a row before the need for table reorganization. This reduces the maintenance window requirement, in the case of a huge data warehouse environment.
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:
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.
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.
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).
There's more...
Let's have a quick look at the difference between AUTO_REVAL settings and behavior.
Case 1: AUTO_REVAL=DEFERRED
Case 2: AUTO_REVAL=DEFERRED_FORCE
Using the soft invalidation and automatic revalidation support
In the earlier versions of DB2, whenever an object was altered or dropped, an exclusive lock was applied to ensure that no user accessed the object. This locking resulted in lock-waits or the rolling back of the transaction because of the deadlocks.
Getting ready
We need the SYSADM authority to modify the values for DB2 registry variables.
How to do it...
To enable or disable soft invalidation at the instance level, use the DB2 registry variable, DB2_DDL_SOFT_INVAL.
How it works...
In DB2 9.7, we have the soft invalidation feature to avoid these lock-waits or deadlocks. Upon activating soft invalidation using the registry variable DB2_DDL_SOFT_INVAL=ON in any transaction, the DDL operations, such as DROP TABLE, ALTER TABLE, and DETACH partitions on database objects will not be stuck because of a lock-wait (SQL0911N Reason Code 68) or a deadlock (SQL0911N Reason Code 2) while the modifying objects are being accessed by other transactions. This is because the current transaction will continue to access the original object definition while the new transaction will make use of the changed object definition of ALTER, DROP, or DETACH if the object being accessed is altered. During the DROP statement, the current transaction would still see the object until the completion of the execution of the transaction and all new transactions would fail to find the dropped object. This way, DB2 9.7 improves the application concurrency for DDL statements.
The following is the list of DDL statements for which soft invalidation is supported in DB2 9.7:
There's more...
As discussed in the earlier recipe, DB2 9.7 supports automatic object revalidation, based on the database configuration parameter's AUTO_REVAL setting.
Normally, the object would get revalidated whenever the application or the user accesses the invalid object, if AUTO_REVAL is set to DEFERRED. If we set AUTO_REVAL to IMMEDIATE, the objects get revalidated immediately after they become invalid.
Using the ALTER COLUMN SET DATA TYPE extended support
ALTER COLUMN SET DATA TYPE was present in the earlier versions of DB2 as well, supporting SMALLINT to INTEGER, INTEGER to BIG, REAL to DOUBLE, and BLOB(n) to BLOB(n+m) conversions; data types could not be cast to smaller data types. In DB2 9.7, the ALTER TABLE statement is extended to support all compatible types, from casting to small data types.
In some cases, data may be truncated upon altering the column data type such as DECIMAL to INTEGER. To avoid the data loss issues, DB2 9.7 scans the column data before the change and writes the error messages, such as overflow errors and truncation errors, into the notification log.
The column data type is set to a new data type only if there is no error reported during the column data scan phase.
Getting ready
To perform the ALTER COLUMN SET DATA TYPE action, the user needs to have one of the following authorizations on the object:
How to do it...
We can do it using ALTER COLUMN SET DATA TYPE as follows:
The preceding SQL statements try to change the data type from one to another.
The reason for the failure in the case of second ALTER COLUMN statement is because an MQT "ADEFUSR" is referring to the base table EMPLOYEE.
A column cannot be dropped or have its length, data type, security, or nullability altered on a table that is a base table for a materialized query table.
We can change the COLUMN type in the base table as follows:
How it works...
The ALTER COLUMN SET DATA TYPE statement downcasts the data type INT to SMALLINT, which invalidates the views V1 and V2. Since we have AUTO_REVAL set to DEFERRED, the dependent objects become invalid until used.
As soon as we access the dependent objects after altering the column data type, objects become valid and you won't see them in the SYSCAT.INVALIDOBJECTS system catalog table.
There's more...
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:
How to do it...
TRUNCATE is just a simple command that can also be embedded in any host language.
The following screenshot illustrates the sample output for the TRUNCATE command:
How it works...
The TRUNCATE statement cannot be rolled back, as with the DELETE statement. This is very useful if you have tons of records to be deleted, saving archive log space and time.
The sample table used in this recipe had 0.2 million rows. TRUNCATE deleted all rows in a second, where the same set of records DELETE would take 10 seconds or more on an average-performing system, and sometimes we may hit the condition when the transaction log is full and may need to change the LOGSECOND/LOGFILSZ parameter.
One can use TRUNCATE on a table, which is present on the current server. The TRUNCATE statement cannot be used against the following database objects:
If the table that we are truncating is a root table in the hierarchy, then all tables in the hierarchy are truncated.
The DROP STORAGE or REUSE STORAGE clause specifies whether to drop or reuse the existing allocated storage space for the table.
The IMMEDIATE clause is mandatory, where it specifies if the TRUNCATE operation is processed immediately and cannot be undone. Always and always, the TRUNCATE statement should be the first statement in the transaction. If we have many statements inside the transaction, other operations can be undone, except the TRUNCATE operation.
IGNORE DELETE TRIGGERS or RESTRICT WHEN DELETE TRIGGERS specifies if any delete triggers, which are defined on the table, would not be activated by the TRUNCATE operation and is the default behavior. Otherwise, an error is returned in the case of RESTRICT WHEN DELETE TRIGGER.
There's more...
There are different ways to delete the data without logging the activity in the transaction logs other than TRUNCATE. They are explained as follows:
Now let's see how we can delete the table data without logging:
Now that we know we have many ways to delete data, we should be using the right method in the right situation. For example, when one uses the ACTIVATE NOT LOGGED INITIALLY option and the unit of work fails, the table has to be rebuilt and the data is lost. In any DB2 High Availability and Disaster Recovery (HADR) setup, be very sure that only tables (the data of which can be easily reproducible) can be marked as NOT LOGGED INITIALLY if required, else we may end up losing the data upon a DR switch. Also, be very cautious while working in a huge data warehouse environment in LOAD with the REPLACE clause. When the data is distributed across multiple partitions, one can expect the APPLHEAPSZ error and the table may go inaccessible. There is also the issue of running out of the utility's heap space UTIL_HEAP_SZ, if you have many data range partitions.
