You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by sc...@apache.org on 2007/03/23 19:58:49 UTC
svn commit: r521868 - /db/derby/docs/trunk/src/ref/rrefsyscsdiagtables.dita
Author: scotsmatrix
Date: Fri Mar 23 11:58:48 2007
New Revision: 521868
URL: http://svn.apache.org/viewvc?view=rev&rev=521868
Log:
DERBY-1520: Updated the file with technical comments from Army. Patch was contributed by me.
Modified:
db/derby/docs/trunk/src/ref/rrefsyscsdiagtables.dita
Modified: db/derby/docs/trunk/src/ref/rrefsyscsdiagtables.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsyscsdiagtables.dita?view=diff&rev=521868&r1=521867&r2=521868
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsyscsdiagtables.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsyscsdiagtables.dita Fri Mar 23 11:58:48 2007
@@ -1,5 +1,5 @@
<?xml version="1.0" encoding="utf-8"?>
-
+
<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
"../dtd/reference.dtd">
<!--
@@ -38,7 +38,7 @@
<indexterm>SYSCS_DIAG.TRANSACTION_TABLE diagnostic table</indexterm></keywords>
</metadata></prolog>
<refbody>
-<section> <p>There are two types of table expressions in <ph conref="refconrefs.dita#prod/productshortname"></ph>:<dl>
+<section> <p>There are two types of diagnostic table expressions in <ph conref="refconrefs.dita#prod/productshortname"></ph>:<dl>
<dlentry>
<dt>Diagnostic tables</dt>
<dd>Tables that are like any other table in <ph conref="refconrefs.dita#prod/productshortname"></ph>.
@@ -47,35 +47,48 @@
</dlentry><dlentry>
<dt>Diagnostic table functions</dt>
<dd>Functions that are like any other function in <ph conref="refconrefs.dita#prod/productshortname"></ph>.
-The diagnostic table functions require one or more arguments. You must use
-the SQL-defined table function syntax to access these functions.</dd>
-</dlentry></dl> </p><p>The following table shows the types and names of the <ph
-conref="refconrefs.dita#prod/productshortname"></ph> table expressions. </p><table>
-<title>System table expressions provided by <ph conref="refconrefs.dita#prod/productshortname"></ph></title>
+Diagnostic table functions can accept zero or more arguments, depending on
+the table function that you use. You must use the SQL-defined table function
+syntax to access these functions.</dd>
+</dlentry></dl> </p><p>The following table shows the types and names of the
+diagnostic table expressions in <ph conref="refconrefs.dita#prod/productshortname"></ph>. </p><table>
+<title>System diagnostic table expressions provided by <ph conref="refconrefs.dita#prod/productshortname"></ph></title>
<tgroup cols="2"><colspec colname="col1" colwidth="50*"/><colspec colname="col2"
colwidth="49*"/>
<thead>
<row valign="bottom">
-<entry colname="col1">Diagnostic tables</entry>
-<entry colname="col2">Diagnostic table functions</entry>
+<entry colname="col1">Diagnostic table expression</entry>
+<entry colname="col2">Type of expression</entry>
</row>
</thead>
<tbody>
<row>
+<entry colname="col1">SYSCS_DIAG.ERROR_LOG_READER</entry>
+<entry colname="col2">Table function</entry>
+</row>
+<row>
<entry colname="col1">SYSCS_DIAG.ERROR_MESSAGES</entry>
-<entry colname="col2">SYSCS_DIAG.ERROR_LOG_READER</entry>
+<entry colname="col2">Table</entry>
</row>
<row>
<entry colname="col1">SYSCS_DIAG.LOCK_TABLE</entry>
-<entry colname="col2">SYSCS_DIAG.SPACE_TABLE</entry>
+<entry colname="col2">Table</entry>
+</row>
+<row>
+<entry colname="col1">SYSCS_DIAG.SPACE_TABLE</entry>
+<entry colname="col2">Table function</entry>
</row>
<row>
<entry colname="col1">SYSCS_DIAG.STATEMENT_CACHE</entry>
-<entry colname="col2">SYSCS_DIAG.STATEMENT_DURATION</entry>
+<entry colname="col2">Table</entry>
+</row>
+<row>
+<entry colname="col1">SYSCS_DIAG.STATEMENT_DURATION</entry>
+<entry colname="col2">Table function</entry>
</row>
<row>
<entry colname="col1">SYSCS_DIAG.TRANSACTION_TABLE </entry>
-<entry colname="col2"> </entry>
+<entry colname="col2">Table</entry>
</row>
</tbody>
</tgroup>
@@ -89,14 +102,14 @@
to determine the active transactions and the SQL statements in those transactions
at a given point in time. For example, if a deadlock or lock timeout occurred
you can find the timestamp (timestampConstant) in the error log.</p><p>To
-use SYSCS_DIAG.ERROR_LOG_READER diagnostic table function, you specify the
-name of the function in the table function syntax.</p><p>For example:<codeblock>SELECT *
+access the SYSCS_DIAG.ERROR_LOG_READER diagnostic table function, you must
+use the SQL table function syntax.</p><p>For example:<codeblock>SELECT *
FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER())
AS T1</codeblock>where T1 is a user-specified table name that is any valid
-identifier.</p><p>To specify a log file name, the file name must be an expression
-whose data type maps to a Java string. Use single quotation marks when you
-specify the log file name to ensure that the argument is read as string, otherwise
-a syntax error is thrown.</p><p>For example:<codeblock>SELECT *
+identifier.</p><p>You can specify a log file name as an optional argument
+to the SYSCS_DIAG.ERROR_LOG_READER diagnostic table function. When you specify
+a log file name, the file name must be an expression whose data type maps
+to a Java string.</p><p>For example:<codeblock>SELECT *
FROM TABLE (SYSCS_DIAG.ERROR_LOG_READER('myderbyerrors.log'))
AS T1</codeblock></p><p><note type="tip"> By default <ph conref="refconrefs.dita#prod/productshortname"></ph> log
files contain only boot, shutdown, and error messages. In the <cite><ph conref="refconrefs.dita#pub/cittuning"></ph> guide</cite>,
@@ -121,20 +134,23 @@
It is possible that some locks will be in a transition state when the snap
shot is taken. </p></section>
<section><title>SYSCS_DIAG.SPACE_TABLE diagnostic table function</title><p>The
-SYSCS_DIAG.SPACE_TABLE diagnostics table function shows the space usage of
-a particular table and its indexes. You can use the SYSCS_DIAG.SPACE_TABLE
-diagnostics table function to determine if space might be saved by compressing
-the table and indexes.</p><p>To use SYSCS_DIAG.SPACE_TABLE diagnostic table
-function, you specify the name of the function in the table function syntax.</p><p>For
-example: <codeblock>SELECT *
- FROM TABLE (SYSCS_DIAG.SPACE_TABLE(schemaName, tableName))
- AS T2</codeblock>where T2 is a user-specified table name that is any valid
-identifier. </p><p>If you do not specify the <parmname>schemaName</parmname>,
-the current schema is used.</p><p>Both the <parmname>schemaName</parmname> and
-the <parmname>tableName</parmname> parameters must be expressions whose data
-types map to Java strings. Use single quotation marks when you specify these
-parameters to ensure that the arguments are read as strings, otherwise a syntax
-error is thrown. If the <parmname>schemaName</parmname> and the <parmname>tableName</parmname> are
+SYSCS_DIAG.SPACE_TABLE diagnostic table function shows the space usage of
+a particular table and its indexes. You can use this diagnostic table function
+to determine if space might be saved by compressing the table and indexes.</p><p>To
+access the SYSCS_DIAG.SPACE_TABLE diagnostic table function, you must use
+the SQL table function syntax. This diagnostic table function takes two arguments,
+the <parmname>schemaName</parmname> and the <parmname>tableName</parmname>.
+The <parmname>tableName</parmname> argument is required. If you do not specify
+the <parmname>schemaName</parmname>, the current schema is used. </p><p>For
+example, use the following query to return the space usage for all of the
+user tables and indexes in the database: <codeblock>SELECT T2.*
+ FROM
+ SYS.SYSTABLES systabs,
+ TABLE (SYSCS_DIAG.SPACE_TABLE(systabs.tablename)) AS T2
+ WHERE systabs.tabletype = 'T'</codeblock>where T2 is a user-specified
+table name that is any valid identifier. </p><p>Both the <parmname>schemaName</parmname> and
+the <parmname>tableName</parmname> arguments must be expressions whose data
+types map to Java strings. If the <parmname>schemaName</parmname> and the <parmname>tableName</parmname> are
non-delimited identifiers, you must specify the names in upper case.</p><p>For
example: <codeblock>SELECT *
FROM TABLE (SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE'))
@@ -146,20 +162,19 @@
<section><title>SYSCS_DIAG.STATEMENT_DURATION diagnostic table function</title><p>You
can use the SYSCS_DIAG.STATEMENT_DURATION diagnostic table function to analyze
the <term>execution duration</term> of the useful SQL statements in the <filepath>derby.log</filepath> file
-or a log file that you specify. </p><p>You can use the SYSCS_DIAG.STATEMENT_DURATION
-diagnostic table function to get a indication of where the bottlenecks are
-in the JDBC code for an application.</p><p>To use SYSCS_DIAG.STATEMENT_DURATION
-diagnostic table function, you specify the name of the function in the table
-function syntax.</p><p>For example:<codeblock>SELECT *
+or a log file that you specify. </p><p>You can also use this diagnostic table
+function to get an indication of where the bottlenecks are in the JDBC code
+for an application.</p><p>To access the SYSCS_DIAG.STATEMENT_DURATION diagnostic
+table function, you must use the SQL table function syntax.</p><p>For example:<codeblock>SELECT *
FROM TABLE (SYSCS_DIAG.STATEMENT_DURATION())
AS T1</codeblock>where T1 is a user-specified table name that is any valid
identifier.</p><note type="restriction">For each transaction ID, a row is
not returned for the last statement with that transaction id. Transaction
IDs change within a connection after a commit or rollback, if the transaction
-that just ended modified data.</note><p>To specify a log file name, the file
-name must be an expression whose data type maps to a Java string. Use single
-quotation marks when you specify the log file name to ensure that the argument
-is read as string, otherwise a syntax error is thrown.</p><p>For example:<codeblock>SELECT *
+that just ended modified data.</note><p>You can specify a log file name as
+an optional argument to the SYSCS_DIAG.STATEMENT_DURATION diagnostic table
+function. When you specify a log file name, the file name must be an expression
+whose data type maps to a Java string.</p><p>For example:<codeblock>SELECT *
FROM TABLE (SYSCS_DIAG.STATEMENT_DURATION('somederby.log'))
AS T1</codeblock></p><p><note type="tip"> By default <ph conref="refconrefs.dita#prod/productshortname"></ph> log
files contain only boot, shutdown, and error messages. In the <cite><ph conref="refconrefs.dita#pub/cittuning"></ph> guide</cite>,