BN.com Gift Guide

DB2 Universal Database SQL Developer's Guide

Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (9) from $1.99   
  • New (1) from $146.95   
  • Used (8) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$146.95
Seller since 2008

Feedback rating:

(196)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New

Ships from: Chicago, IL

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Product Details

  • ISBN-13: 9780071353892
  • Publisher: McGraw-Hill School Education Group
  • Publication date: 10/1/1999
  • Series: Data Management Ser.
  • Edition description: BOOK & CD
  • Edition number: 1
  • Pages: 834
  • Product dimensions: 7.38 (w) x 9.17 (h) x 2.27 (d)

Read an Excerpt

Chapter 2: Database Consistency Mechanisms

Once you understand the underlying architecture of DB2 Universal Database, you should become familiar with the mechanisms DB2 uses to provide and maintain data consistency. This chapter is designed to introduce you to the concepts of data consistency and to the three mechanisms DB2 uses to enforce consistency: transactions, locking, and transaction logging. The first part of this chapter defines database consistency and examines some of the requirements a database management system must meet to provide and maintain consistency. This part is followed by a close look at the heart of all data manipulation: the transaction. Next, DB2's locking mechanism is described and how that mechanism is used by multiple transactions working concurrently to maintain data integrity is discussed. Finally, this chapter concludes with a discussion of transaction logging and the data recovery process used by DB2 to restore data consistency if application or system failure occurs.

What Is Data Consistency?

The best way to define data consistency is by example. Suppose your company owns a chain of restaurants, and you have a database designed to keep track of supplies stored in each of those restaurants. To facilitate the supplies purchasing process, your database contains an inventory table for each restaurant in the chain. Whenever supplies are received or used by a restaurant, the inventory table for that restaurant is updated. Now, suppose some bottles of ketchup are physically moved from one restaurant to another. The ketchup bottle count value in the donating restaurant's inventory table needs to be lowered, and the ketchup bottlecount value in the receiving restaurant's inventory table needs to be raised to accurately represent this inventory move. If a user lowers the ketchup bottle count from the donating restaurant's inventory table but fails to raise the ketchup bottle count in the receiving restaurant's inventory table, the data has become inconsistent. Now, the total ketchup bottle, count for the entire chain of restaurants is incorrect.

Data can become inconsistent if a user fails to make all necessary changes (as in the previous example), if the system crashes while the user is in the middle of making changes, or if an application accessing data stops prematurely for some reason. Inconsistency can also occur when several users are accessing the same data at the same time. For example, one user might read another user's changes before the data has been properly updated and take some inappropriate action-or make an incorrect change based on the premature data values read.

To properly maintain data consistency, solutions must be provided for the following questions:

  • How can you maintain generic consistency of data if you do not know what each individual data owner or user wants?
  • How can you keep a single application from accidentally destroying data consistency?
  • How can you ensure that multiple applications accessing the same data at the same time will not destroy data consistency?
  • If the system fails while a database is in use, how can the database be returned to a consistent state?

DB2 provides solutions to these questions with its transaction support, locking, an logging mechanisms.

Transactions

A transaction, or a unit of work, is a recoverable sequence of one or more SQL operations grouped together as a single unit within an application process. The initiation and termination of a transaction define the points of data consistency within an application process. Either all SQL operations within a transaction are applied the data source, or the effects of all SQL operations within a transaction are completely "undone."

Transactions and commitment control are relational database concepts that have been around for quite some time. They provide the capability to commit or recover from pending changes made to a database in order to enforce data consistency and integrity. With embedded SQL applications, transactions are automatically initiated when the application process is started. With Open Database Connectivity (ODBC) and Call-Level Interface (CLI), transactions are implicitly started whenever the application begins working with a data source.

Regardless of how transactions are initiated, they are terminated when they are either committed or rolled back. When a transaction is committed, all changes made to the data source since the transaction was initiated are made permanent. When a transaction is rolled back, all changes made to the data source since the transaction was initiated are removed, and the data in the data source is returned to its previous state (before the transaction began). In either case, the data source is guaranteed to be in a consistent state at the completion of each transaction.

A commit or roll back operation only affects the data changes made within the transaction they end. As long as data changes remain uncommitted, other application processes are usually unable to see them, and they can be removed with the roll back operation. However, once data changes are committed, they become accessible to other application processes and can no longer be removed by a roll back operation.

A database application program can do all of its work in a single transaction or spread its work over several sequential transactions. Data used within a transaction is protected from being changed or seen by other transactions through various isolation levels.

Transactions provide generic database consistency by ensuring that changes become permanent only when you issue a COMMIT SQL statement or via API calls defined within a Transaction Manager. Your responsibility, however, is to ensure that the sequence of SQL operations in each transaction results in a consistent database. DB2 then ensures that each transaction is either completed (committed) or removed (rolled back) as a single unit of work. If a failure occurs before the transaction is complete, DB2 will back out all uncommitted changes to restore the database consistency that DB2 assumes existed when the transaction was initiated. Figure 2-1 shows the effects of both a successful transaction and a transaction that failed.

Concurrency and Transaction Isolation Levels

So far, we have only looked at transactions from a single-user data source point-of-view. With single-user data sources, each transaction occurs serially and does not have to contend with interference from other transactions. With multi-user data sources, however, transactions can occur simultaneously, and each transaction has the potential to interfere with another transaction. Transactions that have the potential of interfering with one another are said to be interleaved, or parallel, transactions. Transactions that run isolated from each other are said to be serializable, which means that the results of running them simultaneously are the same as the results of running them one right after another (serially). Ideally, all transactions should be serializable.

So why should transactions be serializable? Consider the following problem. Suppose a salesman is entering orders on a database system at the same time a clerk is sending out bills. Now, suppose the salesman enters an order from Company X but does not commit the order (the salesman is still talking to the representative from Company X), While the salesman is on the phone, the clerk queries the database for a list of all outstanding orders, sees the order for Company X, and sends Company X a bill. Now, suppose the representative from Company X decides to cancel the order. The salesman rolls back the transaction, because the representative changed his mind and the order information was never committed. A week later, Company X receives a bill for a part it never ordered. If the salesman's transaction and the clerk's transaction had been isolated from each other (serialized), this problem would never have occurred. Either the salesman's transaction would have finished before the clerk's transaction started, or the clerk's transaction would have finished before the salesman's transaction started. In either case, Company X would not have received a bill.

When transactions are not isolated from each other in multi-user environments, the following three types of events (or phenomena) can occur as a result:

  • Dirty reads-This event occurs when a transaction reads data that has not yet been committed. For example: Transaction 1 changes a row of data, and Transaction 2 reads the changed row before Transaction 1 commits the change. If Transaction 1 rolls back the change, then Transaction 2 will have read data that is considered never to have existed.
  • Nonrepeatable reads—This event occurs when a transaction reads the same row of data twice but receives different data values each time. For example: Transaction 1 reads a row of data, and Transaction 2 changes or deletes that row and commits the change. If Transaction 1 attempts to reread the row, Transaction 1 retrieves different data values (if the row was updated) or discovers that the row no longer exists (if the row was deleted).
  • Phantoms-This event occurs when a row of data matches a search criteria but initially is not seen. For example: Transaction 1 reads a set of rows that satisfy some search criteria, and Transaction 2 inserts a new row matching Transaction 1's search criteria. If Transaction 1 re-executes the query statement that produced the original set of rows, a different set of rows will be retrieved.

Maintaining database consistency and data integrity while enabling more than one application to access the same data at the same time is known as concurrency. DB2 enforces concurrency by using four different transaction isolation levels. An isolation level determines how data is locked or isolated from other processes while the data is being accessed. DB2 supports the following isolation levels:

  • Repeatable read
  • Read stability
  • Cursor stability
  • Uncommitted read

Repeatable Read

The repeatable read isolation level locks all the rows an application retrieves within a single transaction. If you use the repeatable read isolation level, SELECT SQL statements issued multiple times within the same transaction will yield the same result. A transaction running under the repeatable read isolation level can retrieve and operate on the same rows as many times as needed until the transaction completes. However, no other transactions can update, delete, or insert a row (which would affect the result table being accessed) until the isolating transaction terminates. Transactions running under the repeatable read isolation level cannot see uncommitted changes of other transactions. The repeatable read isolation level does not allow phantom rows to be seen....

Read More Show Less

Table of Contents

Introduction
Part I: Basic Database Concepts
Chapter 1: DB2 Database Architecture
Chapter 2: Database Consistency Mechanisms
Part II: Application Development Fundamentals
Chapter 3: Getting Started with DB2 Application Development
Chapter 4: Writing Embedded SQL Applications
Chapter 5: Programming in Complex Environments
Chapter 6: Improving Application Performance
Part III: SQL Statements
Chapter 7: Application Programming Language Construct Statements
Chapter 8: Database Connection and Transaction
Chapter 9: Data Access and Modification Control
Chapter 10: Data Definition Language(DDL) Statements
Chapter 11: Modifying Data Object Definitions
Chapter 12: Data Manipulation Language(DML) Statements
Chapter 13: User-Defined Data Types, User-Defined Functions and Stored Procedures
Chapter 14: Triggers
Chapter 15: Database Event Monitors
Chapter 16: Setting DB2 Special Registers
Appendix A: SQL Data Structures
Appendix B: SQL Functions
Appendix C: Federated Systems
Appendix D: The DB2 Data Links Manager
Appendix E: Developing JDBC Applications and Applets
Appendix F: How The Example Programs Were Developed
Bibliography
Index
SQL Statements Index
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)