<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="COMMIT_ROLLBACK.html" title="COMMIT WORK, ROLLBACK WORK Statement" />
<link rel="next" href="spasviewsandtables.html" title="Stored Procedures as Views & Derived Tables" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>8. SQL Reference</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="CHECKPOINT" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="COMMIT_ROLLBACK.html" title="COMMIT WORK, ROLLBACK WORK Statement">Prev</a> | <a class="link" href="spasviewsandtables.html" title="Stored Procedures as Views & Derived Tables">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div class="selected">
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
<div>
<a href="#checkpointparams" title="Checkpoint & Page Remapping">Checkpoint & Page Remapping</a>
</div>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="CHECKPOINT" />
<h2>8.21. CHECKPOINT, SHUTDOWN Statement</h2>
<div>
<pre class="programlisting">
admin_statement
: SHUTDOWN opt_log
| CHECKPOINT opt_log
| BACKUP opt_log
;
</pre>
</div>
<p>
The checkpoint is a point in the history of a database where all
the state is written on disk as a single, consistent image that contains all
the state committed so far and no uncommitted state. A transaction log starts after a checkpoint
and contains the information to allow the recreation of the effect of transactions
committed since the checkpoint. The checkpoint state and the transaction
log together allow recovering the database up to the last committed transaction.
</p>
<p>
The CHECKPOINT statement forces a checkpoint to be made. Making the checkpoint
allows starting a new transaction log.
If no new log name is specified the old log is truncated to length 0 and reused
for logging transactions. If the CheckpointAuditTrail option is enabled in
virtuoso.ini a new log will be started even if no new log is specified in the checkpoint
or shutdown statement.
</p>
<p>
The SHUTDOWN statement performs a CHECKPOINT, and terminates the server upon
completion.
</p>
<p>
BACKUP is an alternate notation for backup().
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples:</div>
<div>
<pre class="programlisting">
checkpoint 'new.log';
backup 'bak.log';
shutdown 'new2.log';
</pre>
</div>
</div>
<p>
The above sequence of commands makes a checkpoint and starts logging subsequent
transactions into new.log. The backup statement makes bak.log, which represents the state
prior to starting new.log. The shutdown statement makes a new checkpoint and marks new2.log as the log
file to be used for logging transactions after the database restarts. The database server exits at the completion of the
SHUTDOWN statement.
</p>
<div>
<pre class="programlisting">
replay ('bak.log');
replay ('new.log');
</pre>
</div>
<p>
These statements executed on an empty database will recreate the state in effect after the last transaction
to commit before the SHUTDOWN statement of the previous example.
</p>
<div class="tip">
<div class="tiptitle">See Also</div>
<p>The <a href="databaseadmsrv.html#backup">Backup section</a> for more backup and
recovery information.</p>
</div>
<a name="" />
<div class="example">
<div class="exampletitle">Example for control the transaction logging:</div>
<div>
<pre class="programlisting">
create procedure log_test ()
{
-- disable the transaction logging
log_enable (0);
-- action code, for ex.:
delete from TAG_REL_INX;
insert into TAG_REL_INX (TR_T1, TR_T2, TR_COUNT) select TR_T1, TR_T2, TR_COUNT from TAG_REL;
exec('checkpoint');
-- enable the transaction logging
log_enable (1);
};
</pre>
</div>
</div>
<a name="checkpointparams" />
<h3>8.21.1. Checkpoint & Page Remapping</h3>
<p>
In concept, making a checkpoint consists of writing the last committed
state of a page on the read-only pre-checkpoint state. This is in case
the page existed before the previous checkpoint. If the page was
created after the last checkpoint, making the checkpoint consists of
just writing it to disk. Now if the number of pages modified between
checkpoints largely exceeds the amount of RAM, the checkpoint will be a
disk-to-disk copy process which takes a time proportional to the number
of modified pages. This can take a long time. Virtuoso offers a
mechanism called checkpoint remap. This allows making a committed
state persistent, i.e. safe, without copying all the delta
collected since the last checkpoint over the pre-checkpoint state.
</p>
<p>
The checkpoint remap mechanism means that a page, say number 12 get
remapped to 15 when updated. Suppose now that the page were written
to disk. Now we have the pre-checkpoint page on 12 and the committed
post checkpoint state on 15. If a checkpoint now takes place, instead
of copying 15 over 12, which may involve reading the page, we just write
that 12 is actually on 15. This speeds up the checkpoint but causes one
page to take the space of 2. Now suppose 12 were modified again, now we
would see that the pre checkpoint page is 15 and that the original 12
is free. The page now gets mapped to 12. The next checkpoint now will
mark that 12 is 12, which was the original condition and 15 is free.
</p>
<p>
The mechanism is actually more complex but this is the basic idea. By
allowing a page to occupy two pages in some situations we dramatically
cut down on atomic checkpoint time and improve availability.
</p>
<p>
Now we may argue that this destroys locality since pages that were
created contiguous are no longer that after an update. This is why
there is also a mechanism called unremap. and there is an upper limit
to checkpoint remaps. If every page were updated between checkpoints,
they would alternately be in their original place or in a remote place
from which the next update-checkpoint combination would return them to
the original. Now this is not always the case. Also there is not always
enough space to keep up to three copies of each page. Therefore there
is an unremap mechanism and an upper limit to checkpoint remaps.
</p>
<p>
The <span class="computeroutput">MaxCheckpointRemap</span> parameter in the
<a href="databaseadmsrv.html#VIRTINI">virtuoso.ini</a> file controls how many pages may
be stored on a page other than their logical page. The
<span class="computeroutput">UnremapQuota</span> parameter in the
<a href="databaseadmsrv.html#VIRTINI">virtuoso.ini</a> file controls how many previously
remapped pages are unremapped each time a checkpoint is made.
</p>
<p>
Having an unlimited MaxCheckpointRemap and zero UnremapQuota will cause
a checkpoint to consist exclusively of a flush of dirty buffers.
The downside is that a page can take the space of two for an unlimited
period of time and that original locality is not maintained. Setting
the maximum checkpoint remap to zero in such a situation causes the next
checkpoint to unremap all checkpoint remapped pages, restoring physical
sequence and freeing disk space.
</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>The <a href="tpcc.html">TPC C Benchmark</a> chapter for examples
of checkpoint remapping in use on test environments.</p>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="COMMIT_ROLLBACK.html" title="COMMIT WORK, ROLLBACK WORK Statement">Previous</a>
<br />COMMIT WORK, ROLLBACK WORK Statement</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="spasviewsandtables.html" title="Stored Procedures as Views & Derived Tables">Next</a>
<br />Stored Procedures as Views & Derived Tables</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
23
