Isolation levels Unlike Repeatable Read, Read Stability does not completely isolate the application process from the effects of concurrent application processes. At the RS level, application processes that issue the same query more than once may see additional rows caused by other application processes appending new information to the database. These additional rows are called phantom rows For example, a phantom row can occur in the following situation 1. Application process Pl reads the set of rows n that satisfy some search 2. Application process P2 then inserts one or more rows that satisfy the arch condition and commits those new inserts 3. P1 reads the set of rows again with the same search condition and obtains both the original rows and the rows inserted by P2 In addition to any exclusive locks, an application process running at the RS isolation level acquires at least share locks on all the qualifying rows. Cursor Stability(CS) Like the Repeatable Read level, the Cursor Stability level ensures that any row that was changed by another application process cannot be read until it is committed by that application process Unlike Repeatable Read, Cursor Stability only ensures that the current row of every updatable cursor is not changed by other application processes Thus, the rows that were read during a unit of work can be changed by other application In addition to any exclusive locks, an application process running at the CS isolation level acquires at least a share lock on the current row of every Uncommitted Read (UR For SELECT INTO, FETCH with a read-only cursor, fullselect in an INSERT row fullselect in an UPDATE, or scalar fullselect(wherever it is used ), the Uncommitted Read level allows Any row read during a unit of work to be changed by other application processes. Any row changed by another application process to be read, even if the change has not been committed by that application process For other operations, rules associated with the Cs level apply Related reference Appendix H,"Comparison of isolation levels"on Page s7 e2 DECLARE CURSOR statement"in the SQL Reference, Volun
Unlike Repeatable Read, Read Stability does not completely isolate the application process from the effects of concurrent application processes. At the RS level, application processes that issue the same query more than once may see additional rows caused by other application processes appending new information to the database. These additional rows are called phantom rows. For example, a phantom row can occur in the following situation: 1. Application process P1 reads the set of rows n that satisfy some search condition. 2. Application process P2 then inserts one or more rows that satisfy the search condition and commits those new inserts. 3. P1 reads the set of rows again with the same search condition and obtains both the original rows and the rows inserted by P2. In addition to any exclusive locks, an application process running at the RS isolation level acquires at least share locks on all the qualifying rows. v Cursor Stability (CS) Like the Repeatable Read level, the Cursor Stability level ensures that any row that was changed by another application process cannot be read until it is committed by that application process. Unlike Repeatable Read, Cursor Stability only ensures that the current row of every updatable cursor is not changed by other application processes. Thus, the rows that were read during a unit of work can be changed by other application processes. In addition to any exclusive locks, an application process running at the CS isolation level acquires at least a share lock on the current row of every cursor. v Uncommitted Read (UR) For SELECT INTO, FETCH with a read-only cursor, fullselect in an INSERT, row fullselect in an UPDATE, or scalar fullselect (wherever it is used), the Uncommitted Read level allows: – Any row read during a unit of work to be changed by other application processes. – Any row changed by another application process to be read, even if the change has not been committed by that application process. For other operations, rules associated with the CS level apply. Related reference: v “DECLARE CURSOR statement” in the SQL Reference, Volume 2 v Appendix H, “Comparison of isolation levels” on page 827 Isolation levels Chapter 1. Concepts 15
Queries queries A query is a component of certain SQL statements; it specifies a(temporary) result table Related reference SQL queries"on page 553 Table expressions rary result table from a simple query Clauses further refine the result table. For example, you can use a table expression as a query to select all of the managers from several departments, specify that they must have over 15 years of working experience, and be located at the New York branch office A common table expression is like a temporary view within a complex query. It can be referenced in other places within the query, and can be used in place of a view. Each use of a specific common table expression within a complex query shares the same temporary view. Recursive use of a common table expression within a query can be used to support applications such as airline reservation systems, bill of materials (BOM) generators, and network planning Related reference: Appendix L, "Recursion example: bill of materials"on page 861 Application processes, concurrency, and recovery All SQL programs execute as part of an application process or agent. An application process involves the execution of one or more programs, and is he unit to which the database manager allocates resources and locks programs, or different executions of the same progan n of different Different application processes may involve the executio More than one application process may request access to the same data at the same time. Locking is the mechanism used to maintain data integrity under such conditions, preventing, for example, two application processes from updating the same row of data simultaneously. The database manager acquires locks to prevent uncommitted changes made by one application process from being accidentally perceived by any other The database manager releases all locks it has acquired and retained 16 SQL Reference, Volume 1
Queries A query is a component of certain SQL statements; it specifies a (temporary) result table. Related reference: v “SQL queries” on page 553 Table expressions A table expression creates a temporary result table from a simple query. Clauses further refine the result table. For example, you can use a table expression as a query to select all of the managers from several departments, specify that they must have over 15 years of working experience, and be located at the New York branch office. A common table expression is like a temporary view within a complex query. It can be referenced in other places within the query, and can be used in place of a view. Each use of a specific common table expression within a complex query shares the same temporary view. Recursive use of a common table expression within a query can be used to support applications such as airline reservation systems, bill of materials (BOM) generators, and network planning. Related reference: v Appendix L, “Recursion example: bill of materials” on page 861 Application processes, concurrency, and recovery All SQL programs execute as part of an application process or agent. An application process involves the execution of one or more programs, and is the unit to which the database manager allocates resources and locks. Different application processes may involve the execution of different programs, or different executions of the same program. More than one application process may request access to the same data at the same time. Locking is the mechanism used to maintain data integrity under such conditions, preventing, for example, two application processes from updating the same row of data simultaneously. The database manager acquires locks to prevent uncommitted changes made by one application process from being accidentally perceived by any other process. The database manager releases all locks it has acquired and retained Queries 16 SQL Reference, Volume 1
Application processes, concurrency, and recovery on behalf of an application process when that process ends. However, an application process can explicitly request that locks be released sooner. This is done using a commit operation, which releases locks acquired during the unit of work and also commits database changes made during the unit of work The database manager provides a means of backing out uncommitted changes made by an application process. This might be necessary in the event of a failure on the part of an application process, or in the case of a deadlock, or a lock time-out situation. An application process can explicitly request that its database changes be backed out. This is done us ation A unit of work is a recoverable sequence of operations within an application process. A unit of work is initiated when an application process is started,or when the previous unit of work is ended by something other than the termination of the application process. A unit of work is ended by a commit operation, a rollback operation, or the end of an application process. A commit or rollback operation affects only the database changes made within the unit of work it is ending As long as these changes remain uncommitted, other application processes are unable to perceive them, and they can be backed out. This is not true, however, when the isolation level is uncommitted read (UR). Once committed, these database changes are accessible by other application processes and can no longer be backed out through a rollback. Both DB2 call level interface(CLI) and embedded SQL allow for a connection mode called concurrent transactions, which supports multiple onnections, each of which is an independent transaction. An application can have multiple concurrent connections to the same database Locks acquired by the database manager on behalf of an application process are held until the end of a unit of work. This is not true, however when the isolation level is cursor stability( CS, in which the lock is released as the cursor moves from row to row) or uncommitted read(UR, in which locks are not obtained) because of its own locks. However, if an application uses concurren o An application process is never prevented from performing operations transactions, the locks from one transaction may affect the operation of a concurrent transaction The initiation and the termination of a unit of work define points of onsistency within an application process. For example, a banking transaction may involve the transfer of funds from one account to another. Such a transaction would require that these funds be subtracted from the first account, and then added to the second account. Following the subtraction
on behalf of an application process when that process ends. However, an application process can explicitly request that locks be released sooner. This is done using a commit operation, which releases locks acquired during the unit of work and also commits database changes made during the unit of work. The database manager provides a means of backing out uncommitted changes made by an application process. This might be necessary in the event of a failure on the part of an application process, or in the case of a deadlock, or a lock time-out situation. An application process can explicitly request that its database changes be backed out. This is done using a rollback operation. A unit of work is a recoverable sequence of operations within an application process. A unit of work is initiated when an application process is started, or when the previous unit of work is ended by something other than the termination of the application process. A unit of work is ended by a commit operation, a rollback operation, or the end of an application process. A commit or rollback operation affects only the database changes made within the unit of work it is ending. As long as these changes remain uncommitted, other application processes are unable to perceive them, and they can be backed out. This is not true, however, when the isolation level is uncommitted read (UR). Once committed, these database changes are accessible by other application processes and can no longer be backed out through a rollback. Both DB2® call level interface (CLI) and embedded SQL allow for a connection mode called concurrent transactions, which supports multiple connections, each of which is an independent transaction. An application can have multiple concurrent connections to the same database. Locks acquired by the database manager on behalf of an application process are held until the end of a unit of work. This is not true, however, when the isolation level is cursor stability (CS, in which the lock is released as the cursor moves from row to row) or uncommitted read (UR, in which locks are not obtained). An application process is never prevented from performing operations because of its own locks. However, if an application uses concurrent transactions, the locks from one transaction may affect the operation of a concurrent transaction. The initiation and the termination of a unit of work define points of consistency within an application process. For example, a banking transaction may involve the transfer of funds from one account to another. Such a transaction would require that these funds be subtracted from the first account, and then added to the second account. Following the subtraction Application processes, concurrency, and recovery Chapter 1. Concepts 17
Application processes, concurrency, and recovery step, the data is inconsistent. Only after the funds have been added to the second account is consistency reestablished. When both steps are complete, the unit of work ends, the database manager will roll back uncommitted g da the commit operation can be used to end the unit of work, thereby making the changes available to other application processes. If a failure occurs before changes to restore the data consistency that it assumes existed when the unit initiated Point of New point of one unit of work TIME LINE database updates Begin unit End unit of work Figure 2. Unit of Work with a COMMIT Statement Point of New point of consistency consistenc one unit of work database TIME LINE back out updates Begin unit Failure: Data is returned to End unit of work Figure 3 Unit of Work with a ROLLBACK Statement Related 18 SQL Reference, Volume 1
step, the data is inconsistent. Only after the funds have been added to the second account is consistency reestablished. When both steps are complete, the commit operation can be used to end the unit of work, thereby making the changes available to other application processes. If a failure occurs before the unit of work ends, the database manager will roll back uncommitted changes to restore the data consistency that it assumes existed when the unit of work was initiated. Related concepts: v “Isolation levels” on page 13 Point of consistency New point of consistency Begin unit of work Commit End unit of work one unit of work TIME LINE database updates Figure 2. Unit of Work with a COMMIT Statement Point of consistency New point of consistency Begin unit of work Failure; Begin rollback Data is returned to its initial state; End unit of work one unit of work database updates back out updates TIME LINE Figure 3. Unit of Work with a ROLLBACK Statement Application processes, concurrency, and recovery 18 SQL Reference, Volume 1
DB2 Call level interface(CLI)and open database connectivity (ODBC DB2 Call level interface(CLI) and open database connectivity(ODBc) The DB2 call level interface is an application programming interface that provides functions for processing dynamic SQL statements to application programs. CLI programs can also be connectivity Software Developer's Kit(available from Microsoft or other vendors), which enables access to OdBC data sources. Unlike embedded SQL, this interface requires no precompilation. Applications can be run against a variety of databases without having to be compiled against each of these databases. Applications use procedure calls at run time to connect to databases, issue SQL statements, and retrieve data and status information The DB2 CLI interface provides many features not available in embedded QL. For example CLI provides function calls that support a way of querying database catalogs that is consistent across the DB2 family. This reduces the need to write catalog queries that must be tailored to specific database servers CLI provides the ability to scroll through a cursor: Forward by one or more rows Backward by one or more rows war from the first row by one or more rows Backward from the last row by one or more rows From a previously stored location in the cursor. Stored procedures called from application programs that were written using CLI can return result sets to those programs Java database connectivity (JDBC)and embedded SQL for Java (SQLJ) programs DB2 Universal Database implements two standards-based Java Java( SQLJ). Both can be used to create Java applications and applets tharfor programming APIs: Java database connectivity (DBC)and embedded SQL for access DB2. JDBC calls are translated into DB2 CLI calls through Java native methods JDBC requests flow from the DB2 client through DB2 CLI to the DB2 server. JDBC cannot use static SQL. SQL] applications use JDBC as a foundation for such tasks as connecting to databases and handling SQL errors, but can also contain embedded static SQL statements in the SQLj source files. An SQLj source file must be translated by the SQL] translator before the resulting Java source code can compile Chapter 1. Concepts 19
DB2 Call level interface (CLI) and open database connectivity (ODBC) The DB2® call level interface is an application programming interface that provides functions for processing dynamic SQL statements to application programs. CLI programs can also be compiled using an open database connectivity Software Developer’s Kit (available from Microsoft® or other vendors), which enables access to ODBC data sources. Unlike embedded SQL, this interface requires no precompilation. Applications can be run against a variety of databases without having to be compiled against each of these databases. Applications use procedure calls at run time to connect to databases, issue SQL statements, and retrieve data and status information. The DB2 CLI interface provides many features not available in embedded SQL. For example: v CLI provides function calls that support a way of querying database catalogs that is consistent across the DB2 family. This reduces the need to write catalog queries that must be tailored to specific database servers. v CLI provides the ability to scroll through a cursor: – Forward by one or more rows – Backward by one or more rows – Forward from the first row by one or more rows – Backward from the last row by one or more rows – From a previously stored location in the cursor. v Stored procedures called from application programs that were written using CLI can return result sets to those programs. Java database connectivity (JDBC) and embedded SQL for Java (SQLJ) programs DB2® Universal Database implements two standards-based Java™ programming APIs: Java database connectivity (JDBC) and embedded SQL for Java (SQLJ). Both can be used to create Java applications and applets that access DB2: v JDBC calls are translated into DB2 CLI calls through Java native methods. JDBC requests flow from the DB2 client through DB2 CLI to the DB2 server. JDBC cannot use static SQL. v SQLJ applications use JDBC as a foundation for such tasks as connecting to databases and handling SQL errors, but can also contain embedded static SQL statements in the SQLJ source files. An SQLJ source file must be translated by the SQLJ translator before the resulting Java source code can be compiled. DB2 Call level interface (CLI) and open database connectivity (ODBC) Chapter 1. Concepts 19