150 lines
8.8 KiB
HTML
150 lines
8.8 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Savepoints" />
|
||
|
|
<meta name="abstract" content="A savepoint is a named entity that represents the state of data and schemas at a particular point in time within a unit of work. You can create savepoints within a transaction. If the transaction rolls back, changes are undone back to the specified savepoint, rather than to the beginning of the transaction." />
|
||
|
|
<meta name="description" content="A savepoint is a named entity that represents the state of data and schemas at a particular point in time within a unit of work. You can create savepoints within a transaction. If the transaction rolls back, changes are undone back to the specified savepoint, rather than to the beginning of the transaction." />
|
||
|
|
<meta name="DC.subject" content="SAVEPOINT statement, statements, SAVEPOINT, savepoint, definition, data integrity, SAVEPOINT statement, considerations for distributed databases, levels" />
|
||
|
|
<meta name="keywords" content="SAVEPOINT statement, statements, SAVEPOINT, savepoint, definition, data integrity, SAVEPOINT statement, considerations for distributed databases, levels" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafydataintex.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafysavepoint" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Savepoints</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafysavepoint"><a name="rbafysavepoint"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Savepoints</h1>
|
||
|
|
<div><p><span>A <dfn class="term">savepoint</dfn> is a
|
||
|
|
named entity that represents the state of data and schemas at a particular
|
||
|
|
point in time within a unit of work. You can create savepoints within a transaction.
|
||
|
|
If the transaction rolls back, changes are undone back to the specified savepoint,
|
||
|
|
rather than to the beginning of the transaction.</span></p>
|
||
|
|
<div class="section"><p>A savepoint is set by using the SAVEPOINT SQL statement. For example,
|
||
|
|
create a savepoint called STOP_HERE: </p>
|
||
|
|
<pre><strong>SAVEPOINT</strong> STOP_HERE
|
||
|
|
<strong>ON ROLLBACK RETAIN CURSORS</strong></pre>
|
||
|
|
<p> Program logic in the application
|
||
|
|
dictates whether the savepoint name is reused as the application progresses,
|
||
|
|
or if the savepoint name denotes a unique milestone in the application that
|
||
|
|
should not be reused.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>If the savepoint represents a unique milestone that should not
|
||
|
|
be moved with another SAVEPOINT statement, specify the UNIQUE keyword. This
|
||
|
|
prevents the accidental reuse of the name that can occur by invoking a stored
|
||
|
|
procedure that uses the identical savepoint name in a SAVEPOINT statement.
|
||
|
|
However, if the SAVEPOINT statement is used in a loop, then the UNIQUE keyword
|
||
|
|
should not be used. The following SQL statement sets a unique savepoint named
|
||
|
|
START_OVER. </p>
|
||
|
|
<pre><strong>SAVEPOINT</strong> START_OVER <strong>UNIQUE</strong>
|
||
|
|
<strong>ON ROLLBACK RETAIN CURSORS</strong></pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>To rollback to a savepoint, use the ROLLBACK statement with the
|
||
|
|
TO SAVEPOINT clause. The following example illustrates using the SAVEPOINT
|
||
|
|
and ROLLBACK TO SAVEPOINT statements:</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>This application logic books airline reservations on a preferred
|
||
|
|
date, then books hotel reservations. If the hotel is unavailable, it rolls
|
||
|
|
back the airline reservations and then repeats the process for another date.
|
||
|
|
Up to 3 dates are tried. </p>
|
||
|
|
<pre>got_reservations =0;
|
||
|
|
EXEC SQL <strong>SAVEPOINT</strong> START_OVER <strong>UNIQUE ON ROLLBACK RETAIN CURSORS</strong>;
|
||
|
|
|
||
|
|
if (SQLCODE != 0) return;
|
||
|
|
|
||
|
|
for (i=0; i<3 & got_reservations == 0; ++i)
|
||
|
|
{
|
||
|
|
Book_Air(dates(i), ok);
|
||
|
|
if (ok)
|
||
|
|
{
|
||
|
|
Book_Hotel(dates(i), ok);
|
||
|
|
if (ok) got_reservations = 1;
|
||
|
|
else
|
||
|
|
{
|
||
|
|
EXEC SQL <strong>ROLLBACK TO SAVEPOINT</strong> START_OVER;
|
||
|
|
if (SQLCODE != 0) return;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
EXEC SQL<strong> RELEASE SAVEPOINT</strong> START_OVER;</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Savepoints are released using the RELEASE SAVEPOINT statement.
|
||
|
|
If a RELEASE SAVEPOINT statement is not used to explicitly release a savepoint,
|
||
|
|
it is released at the end of the current savepoint level or at the end of
|
||
|
|
the transaction. The following statement releases savepoint START_OVER. </p>
|
||
|
|
<pre><strong>RELEASE SAVEPOINT</strong> START_OVER</pre>
|
||
|
|
<p>Savepoints
|
||
|
|
are released when the transaction is committed or rolled back. Once the savepoint
|
||
|
|
name is released, a rollback to the savepoint name is no longer possible.
|
||
|
|
The COMMIT or ROLLBACK statement releases all savepoint names established
|
||
|
|
within a transactions. Since all savepoint names are released within the transaction,
|
||
|
|
all savepoint names can be reused following a commit or rollback.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Savepoints are scoped to a single connection only. Once a savepoint
|
||
|
|
is established, it is not distributed to all remote databases that the application
|
||
|
|
connects to. The savepoint only applies to the current database that the application
|
||
|
|
is connected to when the savepoint is established.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>A single statement can implicitly or explicitly invoke a user-defined
|
||
|
|
function, trigger, or stored procedure. This is known as nesting. In some
|
||
|
|
cases when a new nesting level is initiated, a new savepoint level is also
|
||
|
|
initiated. A new savepoint level isolates the invoking application from any
|
||
|
|
savepoint activity by the lower level routine or trigger. </p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Savepoints can only be referenced within the same savepoint level
|
||
|
|
(or scope) in which they are defined. A ROLLBACK TO SAVEPOINT statement cannot
|
||
|
|
be used to rollback to a savepoint established outside the current savepoint
|
||
|
|
level. Likewise, a RELEASE SAVEPOINT statement cannot be used to release a
|
||
|
|
savepoint established outside the current savepoint level. The following table
|
||
|
|
summarizes when savepoint levels are initiated and terminated:</p>
|
||
|
|
|
||
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e116">A new savepoint level is initiated when:</th>
|
||
|
|
<th valign="top" id="d0e118">That savepoint level ends when:</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody><tr><td valign="top" headers="d0e116 ">A new unit of work is started</td>
|
||
|
|
<td valign="top" headers="d0e118 ">COMMIT or ROLLBACK is issued</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e116 ">A trigger is invoked</td>
|
||
|
|
<td valign="top" headers="d0e118 ">The trigger completes</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e116 ">A user-defined function is invoked</td>
|
||
|
|
<td valign="top" headers="d0e118 ">The user-defined function returns to the invoker</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e116 ">A stored procedure is invoked, and that stored procedure
|
||
|
|
was created with the NEW SAVEPOINT LEVEL clause</td>
|
||
|
|
<td valign="top" headers="d0e118 ">The stored procedure returns to the caller</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e116 ">There is a BEGIN for an ATOMIC compound SQL statement</td>
|
||
|
|
<td valign="top" headers="d0e118 ">There is an END for an ATOMIC compound statement</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</div>
|
||
|
|
<p>A savepoint that is established in a savepoint level is implicitly
|
||
|
|
released when that savepoint level is terminated.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafydataintex.htm" title="Data integrity protects data from being destroyed or changed by unauthorized persons, system operation or hardware failures (such as physical damage to a disk), programming errors, interruptions before a job is completed (such as a power failure), or interference from running applications at the same time (such as serialization problems).">Data integrity</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|