Before you start
Thinking about obtaining the IBM Certified Database Associate — DB2 10.1 Fundamentals certification? If so, you've come to the right place. This DB2 10.1 Fundamentals certification preparation series is designed to cover all the topics you need to know before you sit down to take the DB2 10.1 Fundamentals certification exam (Exam 610). Even if you're not planning to seek certification right away, the information presented in this series can help you learn about many of the features and functionality available in DB2 10 for z/OS® and DB2 10.1 for Linux®, UNIX®, and Windows®.
You don't see the tutorial you're looking for yet? You can review the DB2 9 tutorials in the DB2 9 Fundamentals certification 730 prep series.
Thirteen percent of the DB2 10.1 Fundamentals certification exam (Exam 610) is designed to test your knowledge of the mechanisms DB2 uses to allow multiple users and applications to interact with a database without adversely affecting data consistency. The questions that make up this portion of the exam are designed to evaluate:
- Your ability to identify the appropriate isolation level to use for a given situation.
- Your ability to identify the characteristics of DB2 locks.
- Your ability to list objects for which locks can be acquired.
- Your ability to identify factors that can influence locking.
This tutorial is designed to introduce the concept of data consistency and to the two important mechanisms DB2 uses to maintain data consistency in single- and multi-user database environments: isolation levels and locks. This tutorial is the sixth in a series of six tutorials you can use to prepare for the DB2 10.1 Fundamentals certification exam (Exam 610).
The material in this tutorial covers the objectives in Section 6 of the DB2 10.1
Fundamentals certification exam (Exam 610). (You can view these objectives at http://www-03.ibm.com/certify/tests/obj610.shtml.)
After completing this tutorial, you should be able to:
- Identify factors that influence locking.
- List objects on which locks can be obtained.
- Use the
LOCK TABLEstatement appropriately.
- Identify characteristics of DB2 locks (common locks shared across all platforms).
- Identify isolation levels that should be used for a given situation.
- Know how and when to use currently committed (CC) semantics.
In order to understand some of the material presented here, you should be familiar with the following terms:
- Structured Query Language (SQL) — A standardized language used to define objects and manipulate data in a relational database.
- Object — Anything in a database that can be created or manipulated with SQL (e.g., tables, views, indices, packages, etc.).
- Table — A logical structure used to present data as a collection of unordered rows with a fixed number of columns. Each column contains a set of values, each value of the same data type. The definitions of the columns make up the table structure, and the rows contain the actual data.
- Record — The storage representation of a row in a table.
- Field — The storage representation of a column in a table.
- Value — A specific data item that can be found at each intersection of a row and column in a table.
- DB2 optimizer — A component of the SQL precompiler that chooses an access plan for a Data Manipulation Language (DML) SQL statement by modeling the execution cost of several alternative access plans and choosing the one with the minimal estimated cost.
You do not need a copy of DB2 to complete this tutorial, but if you have access to a DB2 database server, you will be able to test some of the commands and concepts presented.
You can download a complimentary copy of DB2 Express-C from IBM.