DB2 CRAMSESSION

fengming

DB2 Products and Components
DB2 Editions
Here are the DB2 editions (or “distributions”), from smallest to most comprehensive:
Everyplace        For mobile computing: PDAs, handhelds, Windows CE, embedded devices. DB2 Everyplace Sync Server synchronizes Everyplace data with main database
Satellite Edition        For single users that sometimes operate disconnected from the main database (e.g., laptops). Windows only
Personal Edition        Full-function for single users (Windows, Linux, OS/2), includes client components
Workgroup Edition (WE)        Full-featured Server database. For departmental LANs & applications that do not need access to remote databases on “hosts” (OS/390, VM, or OS/400). Includes Extenders and Net.Data
Enterprise Edition (EE)        Like WE, plus includes all connectivity options to “hosts.” Supports more concurrent users than WE
Extended Enterprise Edition (EEE)        Adds partitioning: splitting of a single DB2 database across multiple nodes (machines) for massive parallelism, yet still appears as a single DB2 database to the user
Everyplace, Satellite, and Personal Editions do not accept remote requests; Workgroup, EE, and EEE do and are for Servers.
DB2 Clients
Code is required on workstations that access a DB2 database. Protocols include: TCP/IP, NetBIOS, APPC, IPX/SPX, and Named Pipes. There are four “Options” (or versions):
Runtime Client        Run SQL statements, ODBC, OLE DB, Java JDBC, and SQLJ apps
Administrative Client        Runtime Client, plus GUI to remotely administer DB2 servers
Application Development Client         Administrative Client, plus all Developer tools
Aka the Software Development Kit (or SDK)
Thin Client        Smallest footprint for remote database access only
The Client Configuration Assistant (CCA) “Wizard” sets up DB2 Clients quickly.
Connectivity Components
DB2 Connect: Supports Unix and Intel application access to DB2 under OS/390, OS/400, and VM/VSE. Not required to access Unix or Intel-based DB2. Supports TCP/IP and IBM’s DRDA protocols. Two Versions: Personal Edition (single user) and Enterprise Edition (multiple user for 3-tier environment).
DB2 DataPropagator: For Replication. Integrated into DB2 for Unix & Intel.
DB2 Net.Data: Enables web browser to access DB2 data, other relational and non-relational data (including native access to Oracle & Sybase). Faster than CGI. Client & Server side features for Java, Rexx, Perl, C++, JavaScript, XML, and a macro language.
DB2 DataJointer: Enables clients to read, join, and update DB2 data with data from Sybase, Informix, and Microsoft SQL Server.
DB2 Relational Connect: Clients can read & join (but not update) DB2 data with Oracle databases.
Development & Administrative Tools
DB2 Developer’s Edition: Supports program development with: embedded SQL, Call Level Interface (CLI), DB2’s Application Programming Interface (APIs), Java with JDBC and SQLJ, and for web clients.
Personal Developer’s Edition (PDE): For one developer under Windows, OS/2 or Linux. Includes: DB2 Personal Edition, DB2 Connect Personal Edition, DB2 Extenders, DB2 Clients, etc.
Universal Developer’s Edition (UDE): For all platforms. Includes all of PDE, plus: DB2 EE, DB2 Connect EE, DB2 Workgroup Edition, WebSphere, and Net.Data.
DB2 Relational Extenders: Database “plug-ins” to support new kinds of data: Text, Audio, Image, and Video (TAIV). Also supports Spatial and XML. Net.Search Extender supports in-memory text searching for heavy Internet usage.
DB2 OLAP Server Starter Kit: Integrated Online Analytical Processing (OLAP) for DB2. Cubes can be stored in DB2 or multidimensional databases. DB2 OLAP Server supports heavier processing.
DB2 Data Warehouse Center: GUI tool integrated with DB2 for ETL (Extract, Transformation, Load) of data to build warehouses. Includes star schema builder and process modeler.
DB2 Data Warehouse Manager: The Warehouse Center plus: more transformations, Information Catalog (GUI tool for Warehouse users), QMF for Windows (GUI for user queries), and Query Patroller (controls database resource usage by queries).
DB2 Data Links Manager: Enables DB2 applications to use SQL to access data stored outside of DB2.
Websphere Application Server: Application Server ships with DB2 supports: Java, Java Server Pages (JSPs), XML, and Enterprise Java Beans (EJB) for dynamic web content.
The Control Center
The Control Center is the GUI used to administer DB2. Tools include:
Command Center: Run DB2 Statements and Commands. View DB2’s access plan (EXPLAIN) info on how it reads/updates data.
Script Center: Create and manage Scripts that run SQL Statements, DB2 Commands, and OS commands.
Alert Center: Enable, manage, and view Alerts (performance indicators that have reached predefined values or thresholds).
Journal Center: Manage, schedule, and view Script outputs. Also view DB2 Recovery History.
License Center: Manage product licensing.
Command Line Processor (CLP): Prompt to run textual DB2 Statements and Commands.
Visual Explain: GUI display of the access plan (I/O strategy) DB2 uses for data access.
SQL Assist: GUI assistance to visually create and run queries.
Stored Procedure Builder: GUI tool for developing Stored Procedures in SQL or Java. DB2 Stored Procedures are server-resident code.
Tools Settings: Set options for tools and the Control Center.
Satellite Center: Synchronize and manage DB2 Satellite Edition
Information Center: DB2 product information (online and offline).
Instances, & Server and Client Installation
Instance
A configurable DB2 environment. Each contains one or more Databases. Each Database has its own System Catalog. Programs ATTACH to Instances but CONNECT to Databases.
Installing DB2 creates two Instances (on Unix, Linux, Windows, OS/2):
•        DB2 Administration Server (DAS): Instance used by Control Center to locally or remotely administer DB2.
•        DB2: Initial Instance in which to create/manage database(s).
Configure DB2 in 4 places:
•        Database Manager Configuration (DBM) File: Configures an Instance. Changing these parameters requires a stop & restart of the Instance!
•        Database Configuration (DB) File: Configures a Database.
•        DB2 Profile Registry: Configure via the db2set command.
•        Environmental Variables: Set in the System Properties panel (Windows) or via scripts (Unix).
Commands to manage the DB2 Administration Server (DAS) Instance:
DB2 Command:        Purpose:
DB2ADMIN START        Starts DAS Instance
DB2ADMIN STOP        Stops DAS Instance
DB2ADMIN CREATE        Creates DAS Instance (Windows, OS/2)
DB2ADMIN DROP        Drops DAS Instance (Windows, OS/2)
Dasicrt        Creates DAS Instance (Unix, Linux)
Dasidrop        Drops DAS Instance (Unix, Linux)
DB2 GET ADMIN CFG        Shows DBM parameters of the DAS
DB2 UPDATE ADMIN CFG        Updates DBM parameters of the DAS
DB2 RESET ADMIN CFG        Sets DBM parameters of the DAS to defaults
DB2ADMIN SETID        Modify DAS user account
Installation
Done under Windows like any other Windows product. Under Unix, perform the following:
1.        Log in as root
2.        Mount the CD-ROM, change to that directory
3.        ./db2setup
4.        Select Install
Response Files allow you to do remote/unattended Installs. The Response File Generator utility creates Response Files automatically.
To Setup a DB2 Client
1.        Install/configure/enable the client communication protocol (TCP/IP, NetBIOS, Named Pipes, IPX/SPX, or APPC)
2.        Catalog the remote Node (database server)
3.        Catalog the remote Database
DB2 Discovery feature automatically returns:
•        Remote Instances
•        Remote Databases on those Instances
Discovery can be enable/disabled for each Instance and/or Database. The Client Configuration Assistant (CCA) GUI tool automates client setup. The DB2COMM registry variable enables communications protocols.
DB2 the uses 3 Directory Files for database access:
Directory File:        For:        Display via:
System Database        Each Instance        LIST DB DIRECTORY
Local Database        In every path/drive containing a Database        LIST DB DIRECTORY ON location
Node        On each client, entries for all Instances accessible        LIST NODE DIRECTORY
You can manually add (CATALOG) or remove (UNCATALOG) entries from these Directory Files; or use the Control Center GUI for this task.
Security
Authentication
This is the first step in user access to DB2. Verification is done on the user’s identity, usually by User ID and Password. DB2 relies upon an external facility like the operating system or a separate product for authentication.
The AUTHENTICATION Database Manager Configuration parameter on the Server drives how this occurs:
Parameter:        Authentication:
SERVER        Authentication occurs on Server by OS (Operating System). User Id and Password. Default
SERVER_ENCRYPT        Same as SERVER but password is encrypted
CLIENT        Authentication occurs at client, by one of several methods
DCS        If server access is via DRDA protocol, authentication occurs in DRDA/APPC level. Otherwise, same as SERVER
DCS_ENCRYPT        Like DCS but password is encrypted
DCE        Authentication on Server by Distributed Computing Environment (DCE) Security Services
DCE_SERVER_ENCRYPT        Like DCE but password is encrypted
KERBEROS        Uses Kerberos security protocol (Windows 2000 only)
KRB_SERVER_ENCRYPT        Server uses KERBEROS or SERVER_ENCRYPT, depending on the Client Authentication setting
Authorities
DB2 contains 5 sets of administrative rights called Authorities:
Called:        Authority:        Rights:
SYSADM        System Administrator        All administrator and object rights
SYSCTRL        System Control        All administrator rights but no object (data) rights: can create/drop/restore Database or Tablespace, force applications
SYSMAINT        System Maintenance        All maintenance rights but no object (data) rights: can backup/restore Databases and Tablespaces; stop/start DB2, traces, monitor; run reorg/runstats
LOAD        Load Table        Run/manage database LOAD utility
DBADM        Database Administrator        All administrative and object rights for a specific database
Authorities SYSADM, SYSCTRL, and SYSMAINT are NOT given by the GRANT statement. Set them by updating the Database Manager Configuration file, then stopping/starting the Instance. This example gives those users an existing operating system group db2cntrl for SYSCNTRL authority:
UPDATE DATABASE MANAGER CONFIGURATION USING SYSCTRL_GROUP
db2cntrl
db2stop
db2start
Privileges
Gives power to create/alter/drop database objects. Privileges are stored in the Database System Catalog and are of two kinds: Database Privileges and Object Privileges. Database Privileges:
Database Privilege:        Rights Conferred:
CONNECT        Connect to the Database
CREATETAB        Create new Tables
BINDADD        Create Packages
CREATE_NOT_FENCED        Create User Defined Functions (UDFs) to run in DB2’s process or address space
IMPLICIT_SCHEMA        Create new Schemas
LOAD        Load data into existing tables
Object Privileges
•        Ownership (or Control) Privileges: Full control over an object, gained either by creating the object or by grant of the CONTROL privilege.
•        Individual Privileges: A right to perform a specific action on a database object.
•        Implicit Privileges: Rights gained automatically when a higher-level right is granted. Implicit privileges are NOT automatically revoked when the higher-level privilege is revoked!
•        Indirect Privileges: Right(s) gained thru ability to execute a Package (a compiled and bound program).
Here’s all the Object Privileges:

If your browser doesn't support inline frames click HERE to view the full-sized graphic.

Auditing
Audit Facility is associated with an Instance. How to use it:
1.        Configure auditing: DB2AUDIT CONFIGURE
2.        Start the audit facility: DB2AUDIT START
3.        Before extracting the audit log for review, flush buffers: DB2AUDIT FLUSH
4.        Extract audit log to file for review: DB2AUDIT EXTRACT
5.        Stop auditing: DB2AUDIT STOP
To check current auditing configuration: DB2AUDIT DESCRIBE
To prune (delete) old records from the log: DB2AUDIT PRUNE
Creating & Managing Database Objects
Database Objects
These include: Database, Schema, Table, View, Index, Table Space, Alias, Buffer Pool, Stored Procedure, Trigger, User-Defined Function (UDF), and User-Defined Data Type (UDT). These are created/altered/dropped through the Control Center GUI, or by SQL Data Definition Language (DDL) statements:
•        CREATE
•        ALTER
•        DROP

Database
Create a database prior to creating other database objects (they reside within a database). By default, CREATE DATABASE creates 3 Table spaces:
•        SYSCATSPACE (for System Catalog tables)
•        USERSPACE1 (for user objects and data)
•        TEMPSPACE1 (for temporary tables)
Creating a Database creates: database physical directories, the 3 Table spaces, System Catalog tables and views, database configuration file, binds utility programs, grants CONNECT, CREATETAB, and BINDADD to PUBLIC, and grants DBADM to the database creator.

Schema
This is a logical grouping of Tables, Indexes, Views, etc. If you create an object without specifying its Schema, DB2 uses an Implicit Schema based on the Authorization ID. For Static SQL, this defaults to the authorization id of the binder. For Dynamic SQL, this defaults to the issuer’s authorization id.

Alias
This is an alternate name for a Table or View.

Index
An index is a data structure defined upon a Table that (1) ensures uniqueness of data values for column(s) and/or (2) increases query performance. You cannot define Indexes on Views or other Indexes.

View
A view looks like a table to a user, but it is actually a “logical table” that is defined upon a Base Table.

Buffer Pool
During Database creation, DB2 creates one default Buffer Pool IBMDEFAULTBP. Buffer Pools reduce I/O time by caching database pages in memory.

Trigger
SQL statement(s) automatically executed BEFORE or AFTER executing a INSERT, UPDATE, or DELETE on a particular Table.

Package
Packages contain executable-format SQL statements.
Table
A table is an unordered set of rows. Here are the types of tables:
•        Permanent (aka Base) Tables: User-created to store data
•        Declared Temporary Tables: User-created for duration of a program via: DECLARE GLOBAL TEMPORARY TABLE
•        Temporary (Derived) Tables: Used internally by DB2
•        Summary Tables: Usually contains precomputed results based on another table’s data
•        Typed Tables: Created based on a user-defined structured type. Gives DB2 object-oriented storage capabilities.
Declare table columns with one of these DB2 Data Types:
Numeric:                        Range:
        Integer:        SMALLINT        -32768 to +32767
                INTEGER        -2,147,483,648 to +2,147,483,647
                BIGINT        64-bit integer
        Decimal:        DECIMAL or NUMERIC        (Precision,Scale) default is (5,0)
        Floating Point:        REAL        32-bit real number
                DOUBLE        64-bit real number
String:                       
        Character:        CHAR        1 to 254 characters
                VARCHAR        Up to 32,672 bytes
                LONG VARCHAR        Up to 32,700 (Must fit on 1 Data Page)
                CLOB        2 Gig Max.
        Double-Byte Character:        GRAPHIC        127 chars. Max.
                VARGRAPHIC        16336 chars. Max.
                LONG VARGRAPHIC        16350 chars Max.
                DBCLOB        Max depends on column definition
        Binary String:        BLOB        2 Gig Max.
Datetime:                       
                DATE        MM-DD-YYYY
                TIME        HH-MM-SS
                TIMESTAMP        YYYY-MM-DD-HH-MM-SS-NNNNNN
Datalink:                DATALINK        Reference to an external file via a URL
User-Defined Data Type (UDT):                        Defined by user
DB2 enforces 3 kinds of rules or Constraints:
•        Uniqueness – requires every column value to be unique
•        Referential Integrity – requires all Foreign Keys (values that point from one table to another) have valid values (enforced during update operations)
•        Check – requires data to pass a value check
This CREATE TABLE example shows: NOT NULL and DEFAULT value columns; a PRIMARY KEY clause and a Uniqueness Constraint; and a CHECK Constraint. Notice how the FOREIGN KEY clause renders a column in the 2nd table dependent on values in a column in the 1st table:
CREATE TABLE dept_tab
(deptnum SMALLINT NOT NULL, -- column allows no NULL values
deptname CHAR(20), -- may contain NULL values
PRIMARY KEY(deptnum) ) ; -- defines Primary Key on NOT NULL column
CREATE TABLE emp_tab
(emp_id SMALLINT NOT NULL,
dept SMALLINT,
gender CHAR(1) CHECK (gender IN (‘M’,’F’)), -- Check Constraint
hired DATE WITH DEFAULT CURRENT DATE, -- Uses a DEFAULT
CONSTRAINT UNIQUEID PRIMARY KEY(emp_id), -- Uniqueness Constraint
FOREIGN KEY(dept) REFERENCES dept_tab(deptnum) ON DELETE RESTRICT)
IN personnel_ts -- data Table space
INDEX IN personnel_ix_ts -- index in different Table space
NOT LOGGED INITIALLY; -- turn off logging for initial data load
Tables are created in Table spaces, logical entities that map tables onto Containers. Containers define physical storage for the data and are either:
•        System Managed Space (SMS) – a directory on the file system. Space is managed by the operating system’s file system. Default.
•        Database Managed Space (DMS) – a file of fixed preallocated size, or a physical raw device (disk). DB2 directly controls this space.
Typed Tables
DB2 has object-oriented capabilities.
To create a User-Defined Distinct Type (UDT):
CREATE DISTINCT TYPE ounces AS INTEGER WITH COMPARISONS;
Create a Table with the new type: CREATE TABLE sodas (botsize OUNCES);
You can not compare the new type ounces to a regular integer, you MUST use a CAST function: SELECT botsize FROM sodas WHERE botsize >; OUNCES(12);
Create a Structured Type:
CREATE TYPE pers_t AS (name CHAR(20), dob INTEGER)
REF USING INTEGER MODE DB2SQL;
Then create a Typed Table based on it:
CREATE TABLE pers OF pers_t (REF IS oid USER GENERATED);
Notice how SQL DML changes for Typed Tables:
INSERT INTO pers (oid, name, bod) VALUES (pers_t(1), ‘kate’, 1971);
The OID is the required Object Identifier. You can create hierarchies of Typed Tables. Keyword CREATE TYPE identifies a Structured Type, OF tells you it’s a Typed Table.
SQL
Structured Query Language (SQL) includes:
•        Data Definition Language (DDL): CREATE/ALTER/DROP objects
•        Data Control Language (DCL): GRANT/REVOKE privileges (rights)
•        Data Manipulation Language (DML): SELECT/INSERT/UPDATE/DELETE data
SELECT
Select Keywords include:
SELECT DISTINCT – no duplicate rows
FROM – where to retrieve data from
WHERE – limits results by search condition:
•        BETWEEN – inclusive range
•        LIKE – compares column values to a search string; e.g., LIKE ‘S%’
•        IN – in a list of values; e.g., IN (‘ABC’, ‘DEF’)
•        EXISTS – checks whether a row exists in a table
•        IS NULL – checks whether a column contains a value (use IS not =)
•        ALL – for subqueries: TRUE if true for all returned rows (or no rows)
•        SOME or ANY – for subqueries: TRUE if true for at least 1 row
GROUP BY – groups result set rows by the value(s) of a specified column
GROUP BY GROUPING SETS – specifies multiple aggregation groups
GROUP BY ROLLUP – aggregations (data groupings), useful for totals for each column group
GROUP BY CUBE – generates all combinations of groups (constructs a cube)
HAVING – use after GROUP BY instead of a WHERE clause for selectivity
ORDER BY – sort data by specified column(s) (ASCENDING or DESCENDING). Without this clause, data is ALWAYS considered unordered. This clause MUST appear last in the query.
Case Expressions: CASE WHEN condition_1 THEN value_1 WHEN condition_2 THEN value_2 ELSE value_3 END
Common Table Expressions: Uses WITH keyword; creates a temporary table for later use within the SQL statement.
Set Operators apply to the results of 2 (or more) SELECT statements:
UNION        Combine result sets. Requires same type, number, order of columns (but not the same names)
INTERSECT        Returns rows common to both result sets
EXCEPT        The “difference” – rows in the 1st result set but not the 2nd
Duplicates are excluded from results – use keyword ALL to keep duplicates:
•        UNION ALL
•        INTERSECT ALL
•        EXCEPT ALL
Projection: Retrieve only certain columns from the table
Permutation: Retrieve columns in a different order than defined in the table
Cartesian Product: Merges all rows from one table with all from another (a Join without a WHERE clause to restrict its output)
Join: Combines rows from 2 (or more) tables into one result set
Inner Join: Retrieves only rows present in both the joined tables (the “typical” join, based on matching values in specified columns across the two tables).
Outer Join: A join that includes rows present in one table but not the other
Left Outer Join: Result has rows present in both tables, plus rows present ONLY in the left-joined table (specified in the left part of the LEFT OUTER JOIN clause)
Right Outer Join: Result has rows present in both tables, plus rows present ONLY in the right-joined table (the table listed last in the join specification)
Full Outer Join: Result includes matching values from both tables (the inner join result) plus all values present only in either of the two tables
Recursive SQL: Uses WITH keyword, to solve routing or bill of materials queries.
Correlated Subquery: A subquery that processes each row identified in the main query.
Functions can be used in SQL statements:
Function Type:        How It Operates:
Scalar        A result per row
(Examples: ABS, HEX, LENGTH, YEAR, MONTH, DAY, LCASE, and UCASE)
Column        A result per group of rows
(Examples: SUM, AVG, MIN, MAX, COUNT)
Built-in Functions        Provided by DB2
User-Defined Functions (UDFs)        Functions you create via CREATE FUNCTION statement
INSERT
Syntax:
INSERT INTO table_name (column_list) VALUES (value_list);
column_list is ONLY required if you’re not inserting a value for each column.
value_list order MUST match column_list order.
To insert multiple rows, just list each row after VALUES:
INSERT INTO table_name (col_1, col_2) VALUES (‘A’, ‘B’), (‘C’, ‘D’) ;
Use NULL for a missing value, DEFAULT for a system-defined default:
INSERT INTO table_name (col_1, col_2) VALUES (NULL, DEFAULT);
You can use a sub-SELECT as part of an INSERT, UPDATE, or DELETE. Example:
INSERT INTO employee_new (emp_name, emp_cat)
(SELECT emp_name, emp_cat FROM employee_old);
UPDATE
Syntax:
UPDATE table_name SET column_name = value WHERE condition;
Example: UPDATE stock SET (stat, qn, pr) = (NULL, 0, 0) WHERE type <>; ‘S’;
Omitting the WHERE clause updates ALL rows.
DELETE
Syntax:
DELETE FROM table_name WHERE condition ;
Example: DELETE FROM candidate WHERE home_phone IS NULL;
Omitting the WHERE clause means ALL rows are deleted!
Locking and Concurrency
DB2 uses locks to ensure data integrity or data consistency (the accuracy of data). Most locking is automatic within DB2 (implicit) but you can manually dictate certain locks (explicit). Explicit locking statements are:
LOCK TABLE table_name in [ SHARE | EXCLUSIVE ] MODE
SHARE allows others to read table data; EXCLUSIVE does not allow other readers or writers.
ALTER TABLE table_name LOCKSIZE [ TABLE | ROW ] – use this to change DB2’s default row locking to table-level locking for the table.
The concept of a transaction (or Unit of Work or UOW) helps ensure data integrity. A transaction groups one or more SQL statements together as an atomic unit, so the statement(s) in the transaction either succeed or fail as a group. A transaction ends successfully when a COMMIT statement is issued, or it ends in failure when a ROLLBACK statement is issued. COMMITs and ROLLBACKs can be either explicitly issued or implicit. Both release locks held during the transaction.
Lock attributes (or characteristics) include: the type of lock or mode (IN, IS, S, IX, SIX, U, X, Z, NS, NX, NW, or WE), the lock’s duration (how long it’s applied), and the object of the lock (what is locked; e.g., a row or table). Granularity refers to the amount of resource locked. A row lock has a greater granularity than a table lock because it locks less data. Locks may be converted by DB2: changed from a less to more restrictive mode. DB2 may also escalate locks: lock a larger object (thereby reducing the granularity of the lock).
How long a query waits for a lock is called the Lock Wait. Set database configuration parameter LOCKTIMEOUT to tell DB2 how long a query should wait for a resource it needs that is locked (the default is –1; i.e., “wait as long as necessary”). If a program’s Lock Wait exceeds LOCKTIMEOUT, its transaction is rolled back and receives an error code.
Deadlock occurs when two queries each wait for a resource the other has already locked and refuses to give up. DB2’s deadlock detector process periodically awakes at a time interval specified by DLCHKTIME to detect deadlocks. If it finds one, it rolls back the query that has done the least work. This application receives a DB2 error code signifying the rollback, and locks for that query are released (ending the deadlock).
There are four major data integrity problems:
Lost Update        Two programs apply an update, but the 2nd update effectively overwrites the 1st (making it a “lost update”)
Uncommitted Read        A program reads data not yet committed to the database by an updating program
Nonrepeatable Read        A program reads the same row twice, getting different data values each time
Phantom Read        Same SELECT issued twice returns different sets of rows
Isolation Levels (DB2’s predefined locking strategies) allow you to control how DB2 handles implicit locking and address the above data integrity scenarios. Set the Isolation Level via the ISOLATION option in the PREP or BIND commands; via CHANGE ISOLATION LEVEL for the command line processor; or via the CLI configuration file DB2CLI.INI or via CLI statements for the DB2 Call Level Interface.
From least strict (most concurrency) to the most strict (least concurrency but fewest data integrity issues), DB2’s Isolation Levels are:
Isolation Level        Locks:        Uncommitted Read:        Nonrepeatable Reads:        Phantom Reads:
Uncommitted Read        No row lock        Yes        Yes        Yes
Cursor Stability
(DB2’s default)        Row to which cursor points        No        Yes        Yes
Read Stability        Rows that are part of result set        No        No        Yes
Repeatable Read        All rows processed to build result set        No        No        No