2001 lines
82 KiB
HTML
2001 lines
82 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 xmlns="http://www.w3.org/1999/xhtml" lang="en-US" xml:lang="en-us">
|
|
<head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
|
<meta name="dc.language" scheme="rfc1766" 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. -->
|
|
<meta name="dc.date" scheme="iso8601" content="2005-09-19" />
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
|
<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="keywords" content="GET DIAGNOSTICS statement, GET DIAGNOSTICS,
|
|
SQL statements, in GET DIAGNOSTICS, CURRENT, STACKED, COMMAND_FUNCTION,
|
|
COMMAND_FUNCTION_CODE, DB2_DIAGNOSTIC_ CONVERSION_ERROR,
|
|
DB2_GET_DIAGNOSTICS _DIAGNOSTICS, DB2_LAST_ROW, DB2_NUMBER_CONNECTIONS,
|
|
DB2_NUMBER_PARAMETER _MARKERS, DB2_NUMBER_RESULT_SETS, DB2_NUMBER_ROWS,
|
|
DB2_NUMBER_SUCCESSFUL_, SUBSTMTS, DB2_RELATIVE_COST_ ESTIMATE, DB2_RETURN_STATUS,
|
|
RETURN_STATUS, DB2_ROW_COUNT_SECONDARY, DB2_ROW_LENGTH, DB2_SQL_ATTR_CONCURRENCY,
|
|
DB2_SQL_ATTR_CURSOR _CAPABILITY, DB2_SQL_ATTR_CURSOR _HOLD,
|
|
DB2_SQL_ATTR_CURSOR _ROWSET, DB2_SQL_ATTR_CURSOR _SCROLLABLE,
|
|
DB2_SQL_ATTR_CURSOR _SENSITIVITY, DB2_SQL_ATTR_CURSOR_TYPE, DYNAMIC_FUNCTION,
|
|
DYNAMIC_FUNCTION_CODE, MORE, NUMBER, ROW_COUNT, TRANSACTION_ACTIVE,
|
|
TRANSACTIONS_COMMITTED, TRANSACTIONS_ROLLED_BACK, CONNECTION_NAME,
|
|
DB2_AUTHENTICATION_TYPE, DB2_AUTHORIZATION_ID, DB2_CONNECTION_METHOD,
|
|
DB2_CONNECTION_NUMBER, DB2_CONNECTION_STATE, DB2_CONNECTION_STATUS,
|
|
DB2_CONNECTION_TYPE, DB2_DYN_QUERY_MGMT, DB2_ENCRYPTION_TYPE, DB2_PRODUCT_ID,
|
|
DB2_SERVER_CLASS_NAME, DB2_SERVER_NAME, CATALOG_NAME, CLASS_ORIGIN, COLUMN_NAME,
|
|
CONDITION_IDENTIFIER, CONDITION_NUMBER, CONSTRAINT_CATALOG, CONSTRAINT_NAME,
|
|
CONSTRAINT_SCHEMA, CURSOR_NAME, DB2_ERROR_CODE1, DB2_ERROR_CODE2,
|
|
DB2_ERROR_CODE3, DB2_ERROR_CODE4, DB2_INTERNAL_ERROR _POINTER, DB2_LINE_NUMBER,
|
|
DB2_MESSAGE_ID, DB2_MESSAGE_ID1, DB2_MESSAGE_ID2, DB2_MESSAGE_KEY,
|
|
DB2_MODULE_DETECTING _ERROR, DB2_NUMBER_FAILING_ STATEMENTS, DB2_OFFSET,
|
|
DB2_ORDINAL_TOKEN_n, DB2_PARTITION_NUMBER, DB2_REASON_CODE, DB2_RETURNED_SQLCODE,
|
|
DB2_ROW_NUMBER, DB2_SQLERRD_SET, DB2_SQLERRD1, DB2_SQLERRD2, DB2_SQLERRD3,
|
|
DB2_SQLERRD4, DB2_SQLERRD5, DB2_SQLERRD6, DB2_TOKEN_COUNT, DB2_TOKEN_STRING,
|
|
MESSAGE_LENGTH, MESSAGE_OCTET_LENGTH, MESSAGE_TEXT, PARAMETER_MODE,
|
|
PARAMETER_NAME, PARAMETER_ORDINAL_POSITION, RETURNED_SQLSTATE, ROUTINE_CATALOG,
|
|
ROUTINE_NAME, ROUTINE_SCHEMA, SCHEMA_NAME, SERVER_NAME, SPECIFIC_NAME,
|
|
SUBCLASS_ORIGIN, TABLE_NAME, TRIGGER_CATALOG, TRIGGER_NAME, TRIGGER_SCHEMA,
|
|
SQL (Structured Query Language), description" />
|
|
<title>GET DIAGNOSTICS</title>
|
|
<link rel="stylesheet" type="text/css" href="ibmidwb.css" />
|
|
<link rel="stylesheet" type="text/css" href="ic.css" />
|
|
</head>
|
|
<body>
|
|
<a id="Top_Of_Page" name="Top_Of_Page"></a><!-- Java sync-link -->
|
|
<script language = "Javascript" src = "../rzahg/synch.js" type="text/javascript"></script>
|
|
|
|
|
|
<a name="getdiag"></a>
|
|
<h2 id="getdiag"><a href="rbafzmst02.htm#ToC_1095">GET DIAGNOSTICS</a></h2><a id="idx2668" name="idx2668"></a><a id="idx2669" name="idx2669"></a>
|
|
<a name="getdiagp"></a>
|
|
<p id="getdiagp">The GET DIAGNOSTICS statement obtains information about the
|
|
previous SQL statement that was executed.</p>
|
|
<a name="wq1528"></a>
|
|
<h3 id="wq1528"><a href="rbafzmst02.htm#ToC_1096">Invocation</a></h3>
|
|
<p>This statement can only be embedded in an application program,
|
|
SQL function, SQL procedure, or trigger. It cannot be issued interactively.
|
|
It is an executable statement that cannot be dynamically prepared. It must
|
|
not be specified in REXX.</p>
|
|
<a name="wq1529"></a>
|
|
<h3 id="wq1529"><a href="rbafzmst02.htm#ToC_1097">Authorization</a></h3>
|
|
<p>None required.</p>
|
|
<a name="wq1530"></a>
|
|
<h3 id="wq1530"><a href="rbafzmst02.htm#ToC_1098">Syntax</a></h3>
|
|
<a href="rbafzmstgetdiag.htm#syncdiagnostics"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
|
<a name="wq1531"></a>
|
|
<div class="fignone" id="wq1531">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn344.htm"
|
|
border="0" /></span><a href="#skipsyn-343"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a> .-CURRENT-.
|
|
>>-GET--+---------+--DIAGNOSTICS--+-<span class="italic">statement-information</span>-+----><
|
|
'-STACKED-' +-<span class="italic">condition-information</span>-+
|
|
'-<span class="italic">combined-information</span>--'
|
|
|
|
statement-information:
|
|
|
|
.-,-------------------------------------------.
|
|
V |
|
|
|--+---<span><span class="italic">variable-1</span></span>-- = --<span class="italic">statement-information-item</span>-+--+---------|
|
|
'-<span><span class="italic">variable-1</span></span>-- = --DB2_GET_DIAGNOSTICS_DIAGNOSTICS-'
|
|
|
|
condition-information:
|
|
|
|
|--CONDITION--+-<span><span class="italic">variable-2</span></span>-+------------------------------------>
|
|
'-<span class="italic">integer</span>----'
|
|
|
|
.-,------------------------------------------------.
|
|
V |
|
|
>----<span><span class="italic">variable-3</span></span>-- = --+-<span class="italic">connection-information-item</span>-+-+---------|
|
|
'-<span class="italic">condition-information-item</span>--'
|
|
|
|
combined-information:
|
|
|
|
|--<span><span class="italic">variable-4</span></span>-- = ---------------------------------------------->
|
|
|
|
(1)
|
|
>--ALL--+----------------------------------------+--------------|
|
|
| .-,----------------------------------. |
|
|
| V | |
|
|
'---+-STATEMENT----------------------+-+-'
|
|
'-+-CONDITION--+--+------------+-'
|
|
'-CONNECTION-' +-<span><span class="italic">variable-5</span></span>-+
|
|
'-<span class="italic">integer</span>----'
|
|
|
|
</pre>
|
|
<a name="skipsyn-343" id="skipsyn-343"></a>
|
|
<a name="wq1532"></a>
|
|
<div class="notelisttitle" id="wq1532">Notes:</div>
|
|
<ol type="1">
|
|
<li>STATEMENT can only be specified once. If <span class="italic">variable-5</span> or <span class="italic">integer</span> is not specified, CONDITION
|
|
and CONNECTION can only be specified once.</li>
|
|
</ol></div>
|
|
<a name="wq1534"></a>
|
|
<div class="fignone" id="wq1534">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn345.htm"
|
|
border="0" /></span><a href="#skipsyn-344"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>statement-information-item:
|
|
|
|
|--+-COMMAND_FUNCTION----------------+--------------------------|
|
|
+-COMMAND_FUNCTION_CODE-----------+
|
|
+-DB2_DIAGNOSTIC_CONVERSION_ERROR-+
|
|
+-DB2_LAST_ROW--------------------+
|
|
+-DB2_NUMBER_CONNECTIONS----------+
|
|
+-DB2_NUMBER_PARAMETER_MARKERS----+
|
|
+-DB2_NUMBER_RESULT_SETS----------+
|
|
+-DB2_NUMBER_ROWS-----------------+
|
|
+-DB2_NUMBER_SUCCESSFUL_SUBSTMTS--+
|
|
+-DB2_RELATIVE_COST_ESTIMATE------+
|
|
+-DB2_RETURN_STATUS---------------+
|
|
+-DB2_ROW_COUNT_SECONDARY---------+
|
|
+-DB2_ROW_LENGTH------------------+
|
|
+-DB2_SQL_ATTR_CONCURRENCY--------+
|
|
+-DB2_SQL_ATTR_CURSOR_CAPABILITY--+
|
|
+-DB2_SQL_ATTR_CURSOR_HOLD--------+
|
|
+-DB2_SQL_ATTR_CURSOR_ROWSET------+
|
|
+-DB2_SQL_ATTR_CURSOR_SCROLLABLE--+
|
|
+-DB2_SQL_ATTR_CURSOR_SENSITIVITY-+
|
|
+-DB2_SQL_ATTR_CURSOR_TYPE--------+
|
|
+-DYNAMIC_FUNCTION----------------+
|
|
+-DYNAMIC_FUNCTION_CODE-----------+
|
|
+-MORE----------------------------+
|
|
+-NUMBER--------------------------+
|
|
+-ROW_COUNT-----------------------+
|
|
+-TRANSACTION_ACTIVE--------------+
|
|
+-TRANSACTIONS_COMMITTED----------+
|
|
'-TRANSACTIONS_ROLLED_BACK--------'
|
|
|
|
connection-information-item:
|
|
|
|
|--+-CONNECTION_NAME---------+----------------------------------|
|
|
+-DB2_AUTHENTICATION_TYPE-+
|
|
+-DB2_AUTHORIZATION_ID----+
|
|
+-DB2_CONNECTION_METHOD---+
|
|
+-DB2_CONNECTION_NUMBER---+
|
|
+-DB2_CONNECTION_STATE----+
|
|
+-DB2_CONNECTION_STATUS---+
|
|
+-DB2_CONNECTION_TYPE-----+
|
|
+-DB2_DYN_QUERY_MGMT------+
|
|
+-DB2_ENCRYPTION_TYPE-----+
|
|
+-DB2_PRODUCT_ID----------+
|
|
+-DB2_SERVER_CLASS_NAME---+
|
|
'-DB2_SERVER_NAME---------'
|
|
|
|
</pre>
|
|
<a name="skipsyn-344" id="skipsyn-344"></a></div>
|
|
<a name="wq1535"></a>
|
|
<div class="fignone" id="wq1535">
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn346.htm"
|
|
border="0" /></span><a href="#skipsyn-345"><img src="c.gif" alt="Skip visual syntax diagram"
|
|
border="0" /></a>condition-information-item:
|
|
|
|
|--+-CATALOG_NAME------------------+----------------------------|
|
|
+-CLASS_ORIGIN------------------+
|
|
+-COLUMN_NAME-------------------+
|
|
+-CONDITION_IDENTIFIER----------+
|
|
+-CONDITION_NUMBER--------------+
|
|
+-CONSTRAINT_CATALOG------------+
|
|
+-CONSTRAINT_NAME---------------+
|
|
+-CONSTRAINT_SCHEMA-------------+
|
|
+-CURSOR_NAME-------------------+
|
|
+-DB2_ERROR_CODE1---------------+
|
|
+-DB2_ERROR_CODE2---------------+
|
|
+-DB2_ERROR_CODE3---------------+
|
|
+-DB2_ERROR_CODE4---------------+
|
|
+-DB2_INTERNAL_ERROR_POINTER----+
|
|
+-DB2_LINE_NUMBER---------------+
|
|
+-DB2_MESSAGE_ID----------------+
|
|
+-DB2_MESSAGE_ID1---------------+
|
|
+-DB2_MESSAGE_ID2---------------+
|
|
+-DB2_MESSAGE_KEY---------------+
|
|
+-DB2_MODULE_DETECTING_ERROR----+
|
|
+-DB2_NUMBER_FAILING_STATEMENTS-+
|
|
+-DB2_OFFSET--------------------+
|
|
+-DB2_ORDINAL_TOKEN_n-----------+
|
|
+-DB2_PARTITION_NUMBER----------+
|
|
+-DB2_REASON_CODE---------------+
|
|
+-DB2_RETURNED_SQLCODE----------+
|
|
+-DB2_ROW_NUMBER----------------+
|
|
+-DB2_SQLERRD_SET---------------+
|
|
+-DB2_SQLERRD1------------------+
|
|
+-DB2_SQLERRD2------------------+
|
|
+-DB2_SQLERRD3------------------+
|
|
+-DB2_SQLERRD4------------------+
|
|
+-DB2_SQLERRD5------------------+
|
|
+-DB2_SQLERRD6------------------+
|
|
+-DB2_TOKEN_COUNT---------------+
|
|
+-DB2_TOKEN_STRING--------------+
|
|
+-MESSAGE_LENGTH----------------+
|
|
+-MESSAGE_OCTET_LENGTH----------+
|
|
+-MESSAGE_TEXT------------------+
|
|
+-PARAMETER_MODE----------------+
|
|
+-PARAMETER_NAME----------------+
|
|
+-PARAMETER_ORDINAL_POSITION----+
|
|
+-RETURNED_SQLSTATE-------------+
|
|
+-ROUTINE_CATALOG---------------+
|
|
+-ROUTINE_NAME------------------+
|
|
+-ROUTINE_SCHEMA----------------+
|
|
+-SCHEMA_NAME-------------------+
|
|
+-SERVER_NAME-------------------+
|
|
+-SPECIFIC_NAME-----------------+
|
|
+-SUBCLASS_ORIGIN---------------+
|
|
+-TABLE_NAME--------------------+
|
|
+-TRIGGER_CATALOG---------------+
|
|
+-TRIGGER_NAME------------------+
|
|
'-TRIGGER_SCHEMA----------------'
|
|
|
|
</pre>
|
|
<a name="skipsyn-345" id="skipsyn-345"></a></div>
|
|
<a name="syncdiagnostics"></a>
|
|
<h3 id="syncdiagnostics"><a href="rbafzmst02.htm#ToC_1099">Description</a></h3>
|
|
<dl class="parml">
|
|
<dt class="bold">CURRENT <span class="base">or</span> STACKED</dt>
|
|
<dd>Specifies which diagnostics area to access.
|
|
<dl class="parml"><a id="idx2670" name="idx2670"></a>
|
|
<dt class="bold">CURRENT</dt>
|
|
<dd>Specifies to access the first diagnostics area. It corresponds to the
|
|
previous SQL statement that was executed and that was not a GET DIAGNOSTICS
|
|
statement. This is the default.
|
|
</dd><a id="idx2671" name="idx2671"></a>
|
|
<dt class="bold">STACKED</dt>
|
|
<dd>Specifies to access the second diagnostics area. The second diagnostics
|
|
area is only available within a handler. It corresponds to the previous SQL
|
|
statement that was executed before the handler was entered and that was not
|
|
a GET DIAGNOSTICS statement. If the GET DIAGNOSTICS statement is the first
|
|
statement within a handler, then the first diagnostics area and the second
|
|
diagnostics area contain the same diagnostics information.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">statement-information</var></dt>
|
|
<dd>Returns information about the last SQL statement executed.
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">variable–1</var></dt>
|
|
<dd>Identifies a variable declared in the program in accordance with the
|
|
rules for declaring variables. The data type of the variable must be compatible
|
|
with the data type as specified in <a href="rbafzmstgetdiag.htm#getditable">Table 58</a> for the specified
|
|
condition information item. The variable is assigned the value of the specified
|
|
statement information item. If the value is truncated when assigning it to
|
|
the variable, a warning (SQLSTATE 01004) is returned and the GET_DIAGNOSTICS_DIAGNOSTICS
|
|
item of the diagnostics area is updated with the details of this condition.
|
|
<p>If a specified diagnostic item does not contain diagnostic information, then
|
|
the variable is set to a default value based on its data type: </p>
|
|
<ul>
|
|
<li>0 for an exact numeric diagnostic item,</li>
|
|
<li>an empty string for a VARCHAR diagnostic item,</li>
|
|
<li>and blanks for a CHAR diagnostic item.</li></ul>
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">condition-information</var></dt>
|
|
<dd>Returns information about the condition or conditions that occurred
|
|
when the last SQL statement was executed.
|
|
<dl class="parml">
|
|
<dt class="bold">CONDITION <var class="pv">variable–2</var> or <var class="pv">integer</var></dt>
|
|
<dd>Identifies the diagnostic for which information is requested. Each diagnostic
|
|
that occurs while executing an SQL statement is assigned an integer. The value
|
|
1 indicates the first diagnostic, 2 indicates the second diagnostic and so
|
|
on. If the value is 1, then the diagnostic information retrieved corresponds
|
|
to the condition indicated by the SQLSTATE value actually returned by the
|
|
execution of the previous SQL statement (other than a GET DIAGNOSTICS statement).
|
|
The variable specified must be declared in the program in accordance with
|
|
the rules for declaring numeric variables. The value specified must not be
|
|
less than one or greater than the number of available diagnostics.
|
|
</dd>
|
|
<dt class="bold"><var class="pv">variable–3</var></dt>
|
|
<dd>Identifies a variable declared in the program in accordance with the
|
|
rules for declaring variables. The data type of the variable must be compatible
|
|
with the data type as specified in <a href="rbafzmstgetdiag.htm#getditable">Table 58</a> for the specified
|
|
condition information item. The variable is assigned the value of the specified
|
|
statement information item. If the value is truncated when assigning it to
|
|
the variable, an error is returned and the GET_DIAGNOSTICS_DIAGNOSTICS item
|
|
of the diagnostics area is updated with the details of this condition.
|
|
<p>If a specified diagnostic item does not contain diagnostic information , then
|
|
the variable is set to a default value based on its data type: </p>
|
|
<ul>
|
|
<li>0 for an exact numeric diagnostic item,</li>
|
|
<li>an empty string for a VARCHAR diagnostic item,</li>
|
|
<li>and blanks for a CHAR diagnostic item.</li></ul>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">combined-information</var></dt>
|
|
<dd>Returns multiple information items combined into one string.
|
|
<dl class="parml">
|
|
<dt class="bold"><var class="pv">variable–4</var></dt>
|
|
<dd>Identifies a variable declared in the program in accordance with the
|
|
rules for declaring variables. The data type of the variable must be VARCHAR.
|
|
If the length of <var class="pv">variable–4</var> is not sufficient to
|
|
hold the full returned diagnostic string, the string is truncated, an error
|
|
is returned and the GET_DIAGNOSTICS_DIAGNOSTICS item of the diagnostics area
|
|
is updated with the details of this condition.
|
|
</dd>
|
|
<dt class="bold">ALL</dt>
|
|
<dd>Indicates that all diagnostic items that are set for the last SQL statement
|
|
executed should be combined into one string. The format of the string is a
|
|
semicolon separated list of all of the available diagnostic information in
|
|
the form:
|
|
<pre class="xmp"><var class="pv">item-name</var>=<var class="pv">character-form-of-the-item-value</var>;</pre>The character form of a positive numeric value will not contain a leading
|
|
plus sign (+) unless the item is RETURNED_SQLCODE. In this case, a leading
|
|
plus sign (+) is added. For example:
|
|
<pre class="xmp"> NUMBER=1;RETURNED_SQLSTATE=02000;DB2_RETURNED_SQLCODE=+100;</pre> Only items that contain diagnostic information are included in the
|
|
string.
|
|
</dd>
|
|
<dt class="bold">STATEMENT</dt>
|
|
<dd>Indicates that all <var class="pv">statement-information-item</var> diagnostic items
|
|
that contain diagnostic information for the last SQL statement executed should
|
|
be combined into one string. The format is the same as described above for
|
|
ALL.
|
|
</dd>
|
|
<dt class="bold">CONDITION</dt>
|
|
<dd>Indicates that <var class="pv">condition-information-item</var> diagnostic items
|
|
that contain diagnostic information for the last SQL statement executed should
|
|
be combined into one string. If <var class="pv">variable–5</var> or <var class="pv">integer</var> is specified, then the format is the same as described above
|
|
for the ALL option. If <var class="pv">variable–5</var> or <var class="pv">integer</var> is not specified, then the format includes a condition number entry
|
|
at the beginning of the information for that condition in the form:
|
|
<pre class="xmp">CONDITION_NUMBER=X;<var class="pv">item-name</var>=<var class="pv">character-form-of-the-item-value</var>;</pre> where X is
|
|
the number of the condition. For example:
|
|
<pre class="xmp">CONDITION_NUMBER=1;RETURNED_SQLSTATE=02000;RETURNED_SQLCODE=+100;
|
|
CONDITION_NUMBER=2;RETURNED_SQLSTATE=01004;</pre>
|
|
</dd>
|
|
<dt class="bold">CONNECTION</dt>
|
|
<dd>Indicates that <var class="pv">connection-information-item</var> diagnostic items
|
|
that contain diagnostic information for the last SQL statement executed should
|
|
be combined into one string. If <var class="pv">variable–5</var> or <var class="pv">integer</var> is specified, then the format is the same as described above
|
|
for ALL. If <var class="pv">variable–5</var> or <var class="pv">integer</var> is not
|
|
specified, then the format includes a connection number entry at the beginning
|
|
of the information for that condition in the form:
|
|
<pre class="xmp">DB2_CONNECTION_NUMBER=X;<var class="pv">item-name</var>=<var class="pv">character-form-of-the-item-value</var>;</pre>where X is
|
|
the number of the condition. For example:
|
|
<pre class="xmp">DB2_CONNECTION_NUMBER=1;CONNECTION_NAME=SVL1;DB2_PRODUCT_ID=DSN07010;</pre>
|
|
</dd>
|
|
<dt class="bold"><var class="pv">variable–5</var> or <var class="pv">integer</var></dt>
|
|
<dd>Identifies the diagnostic for which ALL CONDITION or ALL CONNECTION
|
|
information is requested. The variable specified must be declared in the program
|
|
in accordance with the rules for declaring numeric variables. The value specified
|
|
must not be less than one or greater than the number of available diagnostics.
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
</dl>
|
|
</dd>
|
|
</dl>
|
|
<a name="getdsitem"></a>
|
|
<h4 id="getdsitem"><span><var class="pv">statement-information-item</var></span></h4>
|
|
<p></p>
|
|
<dl class="parml">
|
|
<dt class="bold">COMMAND_FUNCTION</dt><a id="idx2672" name="idx2672"></a>
|
|
<dd>Returns the name of the previous SQL statement. For information on the
|
|
statement string values, see <a href="rbafzmstgetdiag.htm#stmtstrcode">Table 59</a>.
|
|
</dd>
|
|
<dt class="bold">COMMAND_FUNCTION_CODE</dt><a id="idx2673" name="idx2673"></a>
|
|
<dd>Returns an integer that identifies the previous SQL statement. For information
|
|
on the statement code values, see <a href="rbafzmstgetdiag.htm#stmtstrcode">Table 59</a>.
|
|
</dd>
|
|
<dt class="bold">DB2_DIAGNOSTIC_CONVERSION_ERROR</dt><a id="idx2674" name="idx2674"></a>
|
|
<dd>Returns the value 1 if there was a conversion error when converting
|
|
a character data value for one of the GET DIAGNOSTICS statement values. Otherwise,
|
|
the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_GET_DIAGNOSTICS_DIAGNOSTICS</dt><a id="idx2675" name="idx2675"></a>
|
|
<dd>After a GET DIAGNOSTICS statement, if any errors or warnings occurred
|
|
during the execution of the GET DIAGNOSTICS statement, DB2_GET_DIAGNOSTICS_DIAGNOSTICS
|
|
returns textual information about these errors or warnings. The format of
|
|
the information is similar to what would be returned by a GET DIAGNOSTICS
|
|
:hv = ALL statement.
|
|
<p>If a request was made for an information item that
|
|
the server does not understand, for example, if the server was at a lower DRDA® level than the requesting client, DB2_GET_DIAGNOSTICS_DIAGNOSTICS returns
|
|
the text 'Item not supported:' followed by a comma separated list of item
|
|
names that were requested but that the server does not support.</p>
|
|
</dd>
|
|
<dt class="bold">DB2_LAST_ROW</dt><a id="idx2676" name="idx2676"></a>
|
|
<dd>For a <span class="italic">multiple-row-fetch</span> statement, a value
|
|
of +100 may be returned if the set of rows that have been fetched contains
|
|
the last row currently in the table for cursors that are fetching forward,
|
|
or contains the first row currently in the table for cursors that are fetching
|
|
backward. For cursors that are not sensitive to updates, there would be no
|
|
need to do a subsequent FETCH since the result would be an end of data indication
|
|
(SQLSTATE 02000). For cursors that are sensitive to updates, a subsequent
|
|
FETCH may return more data if a row had been inserted before the FETCH was
|
|
executed. Otherwise, the value zero is returned.
|
|
<p>If the number of rows returned
|
|
is equal to the number of rows requested, then an end of data warning may
|
|
not occur and DB2_LAST_ROW may not contain +100.</p>
|
|
</dd>
|
|
<dt class="bold">DB2_NUMBER_CONNECTIONS</dt><a id="idx2677" name="idx2677"></a>
|
|
<dd>Returns the number of connections that were made in order to get to
|
|
the server that fulfilled the request from the client. Each such connection
|
|
may generate a connection information item area which would be available for
|
|
the single condition.
|
|
</dd>
|
|
<dt class="bold">DB2_NUMBER_PARAMETER_MARKERS</dt><a id="idx2678" name="idx2678"></a>
|
|
<dd>For a PREPARE statement, returns the number of parameter markers in
|
|
the prepared statement. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_NUMBER_RESULT_SETS</dt><a id="idx2679" name="idx2679"></a>
|
|
<dd>For a CALL statement, returns the actual number of result sets returned
|
|
by the procedure. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_NUMBER_ROWS</dt><a id="idx2680" name="idx2680"></a>
|
|
<dd>If the previous SQL statement was an OPEN or a FETCH which caused the
|
|
size of the result table to be known, returns the number of rows in the result
|
|
table. For SENSITIVE cursors, this value can be thought of as an approximation
|
|
since rows inserted and deleted will affect the next retrieval of this value.
|
|
If the previous statement was a PREPARE statement, returns the estimated number
|
|
of rows in the result table for the prepared statement. Otherwise, the value
|
|
zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_NUMBER_SUCCESSFUL_SUBSTMTS</dt><a id="idx2681" name="idx2681"></a>
|
|
<dd>For embedded compound SQL statements, returns a count of the number
|
|
of successful sub-statements. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_RELATIVE_COST_ESTIMATE</dt><a id="idx2682" name="idx2682"></a>
|
|
<dd>For a PREPARE statement, returns a relative cost estimate of the resources
|
|
required for every execution. It does not reflect an estimate of the time
|
|
required. When preparing a dynamically defined statement, this value can be
|
|
used as an indicator of the relative cost of the prepared statement. The value
|
|
varies depending on changes to statistics and can vary between releases of
|
|
the product. It is an estimated cost for the access plan chosen by the optimizer.
|
|
The value zero is returned if the statement is not a PREPARE statement.
|
|
</dd>
|
|
<dt class="bold">DB2_RETURN_STATUS</dt><a id="idx2683" name="idx2683"></a><a id="idx2684" name="idx2684"></a>
|
|
<dd>Identifies the status value returned from the previous SQL CALL statement.
|
|
If the previous statement is not a CALL statement, the value returned has
|
|
no meaning and is unpredictable. For more information, see <a href="rbafzmstreturnstmt.htm#returnstmt">RETURN statement</a>. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ROW_COUNT_SECONDARY</dt><a id="idx2685" name="idx2685"></a>
|
|
<dd>Identifies the number of rows associated with secondary actions
|
|
from the previous SQL statement that was executed. If the previous SQL statement
|
|
is a DELETE, the value is the total number of rows affected by referential
|
|
constraints, including cascaded actions and the processing of triggered SQL
|
|
statements from activated triggers. If the previous SQL statement is an INSERT
|
|
or an UPDATE, the value is the total number of rows affected as the result
|
|
of the processing of triggered SQL statements from activated triggers. Otherwise,
|
|
the value zero is returned.
|
|
<p>If the SQL statement is run using
|
|
isolation level No Commit, this value may be zero.</p>
|
|
</dd>
|
|
<dt class="bold">DB2_ROW_LENGTH</dt><a id="idx2686" name="idx2686"></a>
|
|
<dd>For a FETCH statement, returns the length of the row retrieved. Otherwise,
|
|
the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CONCURRENCY</dt><a id="idx2687" name="idx2687"></a>
|
|
<dd>For an OPEN statement, indicates the concurrency control option of read-only,
|
|
locking, optimistic using timestamps, or optimistic using values.
|
|
<ul>
|
|
<li>R indicates read-only.</li>
|
|
<li>L indicates locking.</li>
|
|
<li>T indicates comparing row versions using timestamps or ROWIDs.</li>
|
|
<li>V indicates comparing values.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CURSOR_CAPABILITY</dt><a id="idx2688" name="idx2688"></a>
|
|
<dd>For an OPEN statement, indicates the capability of the cursor, whether
|
|
a cursor is read-only, deletable, or updatable.
|
|
<ul>
|
|
<li>R indicates that this cursor can only be used to read.</li>
|
|
<li>D indicates that this cursor can be used to read as well as delete.</li>
|
|
<li>U indicates that this cursor can be used to read, delete as well as update.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CURSOR_HOLD</dt><a id="idx2689" name="idx2689"></a>
|
|
<dd>For an OPEN statement, indicates whether a cursor can be held open across
|
|
multiple units of work or not.
|
|
<ul>
|
|
<li>N indicates that this cursor will not remain open across multiple units
|
|
of work.</li>
|
|
<li>Y indicates that this cursor will remain open across multiple units of
|
|
work.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CURSOR_ROWSET</dt><a id="idx2690" name="idx2690"></a>
|
|
<dd>For an OPEN statement, whether a cursor can be accessed using rowset
|
|
positioning or not.
|
|
<ul>
|
|
<li>N indicates that this cursor only supports row positioned operations.</li>
|
|
<li>Y indicates that this cursor supports rowset positioned operations.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CURSOR_SCROLLABLE</dt><a id="idx2691" name="idx2691"></a>
|
|
<dd>For an OPEN statement, indicates whether a cursor can be scrolled forward
|
|
and backward or not.
|
|
<ul>
|
|
<li>N indicates that this cursor is not scrollable.</li>
|
|
<li>Y indicates that this cursor is scrollable.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CURSOR_SENSITIVITY</dt><a id="idx2692" name="idx2692"></a>
|
|
<dd>For an OPEN statement, indicates whether a cursor does or does not show
|
|
updates to cursor rows made by other connections.
|
|
<ul>
|
|
<li>I indicates insensitive.</li>
|
|
<li>P indicates partial sensitivity.</li>
|
|
<li>S indicates sensitive.</li>
|
|
<li>U indicates unspecified.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQL_ATTR_CURSOR_TYPE</dt><a id="idx2693" name="idx2693"></a>
|
|
<dd>For an OPEN statement, indicates whether a cursor type is dynamic, forward-only,
|
|
or static.
|
|
<ul>
|
|
<li>D indicates a dynamic cursor.</li>
|
|
<li>F indicates a forward-only cursor.</li>
|
|
<li>S indicates a static cursor.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DYNAMIC_FUNCTION</dt><a id="idx2694" name="idx2694"></a>
|
|
<dd>Returns a character string that identifies the type of the
|
|
SQL-statement being prepared or executed dynamically. For information on the
|
|
statement string values, see <a href="rbafzmstgetdiag.htm#stmtstrcode">Table 59</a>.
|
|
</dd>
|
|
<dt class="bold">DYNAMIC_FUNCTION_CODE</dt><a id="idx2695" name="idx2695"></a>
|
|
<dd>Returns a number that identifies the type of the SQL-statement being
|
|
prepared or executed dynamically. For information on the statement code values,
|
|
see <a href="rbafzmstgetdiag.htm#stmtstrcode">Table 59</a>.
|
|
</dd>
|
|
<dt class="bold">MORE</dt><a id="idx2696" name="idx2696"></a>
|
|
<dd>Indicates whether more errors were raised than could be handled.
|
|
<ul>
|
|
<li>N indicates that all the errors and warnings from the previous SQL statement
|
|
were stored in the diagnostics area.</li>
|
|
<li>Y indicates that more errors and warnings were raised from the previous
|
|
SQL statement than there are condition areas in the diagnostics area.</li></ul>
|
|
</dd>
|
|
<dt class="bold">NUMBER</dt><a id="idx2697" name="idx2697"></a>
|
|
<dd>Returns the number of errors and warnings detected by the execution
|
|
of the previous SQL statement, other than a GET DIAGNOSTICS statement, that
|
|
have been stored in the diagnostics area. If the previous SQL statement returned
|
|
success (SQLSTATE 00000), or no previous SQL statement has been executed,
|
|
the number returned is one. The GET DIAGNOSTICS statement itself may return
|
|
information via the SQLSTATE parameter, but does not modify the previous contents
|
|
of the diagnostics area, except for the DB2_GET_DIAGNOSTICS_DIAGNOSTICS item.
|
|
</dd>
|
|
<dt class="bold">ROW_COUNT</dt><a id="idx2698" name="idx2698"></a>
|
|
<dd>Identifies the number of rows associated with the previous SQL statement
|
|
that was executed. If the previous SQL statement is a DELETE, INSERT, REFRESH,
|
|
or UPDATE statement, ROW_COUNT identifies the number of rows deleted, inserted,
|
|
or updated by that statement, excluding rows affected by either triggers or
|
|
referential integrity constraints. If the previous statement is a PREPARE
|
|
statement, ROW_COUNT identifies the estimated number of result rows in the
|
|
prepared statement. If the previous SQL statement is a <span class="italic">multiple-row-fetch</span>, ROW_COUNT identifies the number of rows fetched.
|
|
Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">TRANSACTION_ACTIVE</dt><a id="idx2699" name="idx2699"></a>
|
|
<dd>Returns the value 1 if an SQL transaction is currently active, and 0
|
|
if an SQL transaction is not currently active.
|
|
</dd>
|
|
<dt class="bold">TRANSACTIONS_COMMITTED</dt><a id="idx2700" name="idx2700"></a>
|
|
<dd>If the previous statement was a CALL, returns the number of transactions
|
|
that were committed during the execution of the SQL or external procedure.
|
|
Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">TRANSACTIONS_ROLLED_BACK</dt><a id="idx2701" name="idx2701"></a>
|
|
<dd>If the previous statement was a CALL, returns the number of transactions
|
|
that were rolled back during the execution of the SQL or external procedure.
|
|
Otherwise, the value zero is returned.
|
|
</dd>
|
|
</dl>
|
|
<a name="getdcoitem"></a>
|
|
<h4 id="getdcoitem"><span><var class="pv">connection-information-item</var></span></h4>
|
|
<p></p>
|
|
<dl class="parml">
|
|
<dt class="bold">CONNECTION_NAME</dt><a id="idx2702" name="idx2702"></a>
|
|
<dd>If the previous SQL statement is a CONNECT, DISCONNECT, or SET CONNECTION,
|
|
returns the name of the server specified in the previous statement. Otherwise,
|
|
the name of the current connection.
|
|
</dd>
|
|
<dt class="bold">DB2_AUTHENTICATION_TYPE</dt><a id="idx2703" name="idx2703"></a>
|
|
<dd>Indicates the authentication type, whether server or client.
|
|
<ul>
|
|
<li>C for client authentication.</li>
|
|
<li>E for DCE security services authentication.</li>
|
|
<li>S for server authentication.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_AUTHORIZATION_ID</dt><a id="idx2704" name="idx2704"></a>
|
|
<dd>Returns the authorization id used by connected server. Because of userid
|
|
translation and authorization exits, the local userid may not be the authid
|
|
used by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_CONNECTION_METHOD</dt><a id="idx2705" name="idx2705"></a>
|
|
<dd>For a CONNECT or SET CONNECTION statement, returns the connection method.
|
|
<ul>
|
|
<li>D indicates *DUW (Distributed Unit of Work).</li>
|
|
<li>R indicates *RUW (Remote Unit of Work).</li></ul>
|
|
</dd>
|
|
<dt class="bold">DB2_CONNECTION_NUMBER</dt><a id="idx2706" name="idx2706"></a>
|
|
<dd>Returns the number of the connections.
|
|
</dd>
|
|
<dt class="bold">DB2_CONNECTION_STATE</dt><a id="idx2707" name="idx2707"></a>
|
|
<dd>Indicates the connection state, whether connected or not.
|
|
<ul>
|
|
<li>-1 indicates the connection is unconnected.</li>
|
|
<li>1 indicates the connection is connected.</li></ul>Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_CONNECTION_STATUS</dt><a id="idx2708" name="idx2708"></a>
|
|
<dd>Indicates whether commitable update can be performed or not.
|
|
<ul>
|
|
<li>1 indicates commitable updates can be performed on the connection for
|
|
this unit of work.</li>
|
|
<li>2 indicates no commitable updates can be performed on the connection for
|
|
this unit of work.</li></ul>Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_CONNECTION_TYPE</dt><a id="idx2709" name="idx2709"></a>
|
|
<dd>Indicated the connection type (either local, remote, or to a driver
|
|
program) and whether the conversation is protected or not.
|
|
<ul>
|
|
<li>1 indicates a connection to a local relational database.</li>
|
|
<li>2 indicates a connection to a remote relational database with the conversation
|
|
unprotected.</li>
|
|
<li>3 indicates a connection to a remote relational database with the conversation
|
|
protected.</li>
|
|
<li>4 indicates a connection to an application requester driver program.</li></ul>Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_DYN_QUERY_MGMT</dt><a id="idx2710" name="idx2710"></a>
|
|
<dd>Returns a value of 1 if DYN_QUERY_MGMT database configuration parameter
|
|
is enabled. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ENCRYPTION_TYPE</dt><a id="idx2711" name="idx2711"></a>
|
|
<dd>Returns the level of encryption.
|
|
<ul>
|
|
<li>A indicates only the authentication tokens (authid and password) are encrypted.</li>
|
|
<li> D indicates all data is encrypted for the connection.</li></ul>Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_PRODUCT_ID</dt><a id="idx2712" name="idx2712"></a>
|
|
<dd>Returns a product signature. If the application server is an IBM® relational database
|
|
product, the form is pppvvrrm, where:
|
|
<ul>
|
|
<li>ppp identifies the product as follows: ARI for DB2® for VM and VSE, DSN for DB2 UDB for z/OS®, QSQ for DB2 UDB for iSeries™, and SQL for all other DB2 UDB products</li>
|
|
<li>vv is a two-digit version identifier such as '04'</li>
|
|
<li>rr is a two-digit release identifier such as '01'</li>
|
|
<li>m is a one-digit modification level such as '0'</li></ul>For example, if the application server is Version 7 of DB2 UDB for z/OS, the value would be 'DSN07010'. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SERVER_CLASS_NAME</dt><a id="idx2713" name="idx2713"></a>
|
|
<dd>Returns the server class name. For example, DB2 for z/OS, DB2 for AIX®, DB2 for Windows®, and DB2 for iSeries.
|
|
</dd>
|
|
<dt class="bold">DB2_SERVER_NAME</dt><a id="idx2714" name="idx2714"></a>
|
|
<dd>For a CONNECT or SET CONNECTION statement, returns the relational database
|
|
name. Otherwise, the empty string is returned.
|
|
</dd>
|
|
</dl>
|
|
<a name="getdcitem"></a>
|
|
<h4 id="getdcitem"><span><var class="pv">condition-information-item</var></span></h4>
|
|
<p></p>
|
|
<dl class="parml">
|
|
<dt class="bold">CATALOG_NAME</dt><a id="idx2715" name="idx2715"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 09 (Triggered Action Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 27 (Triggered Data Change Violation), or</li>
|
|
<li>40002 (Transaction Rollback - Integrity Constraint Violation),</li></ul>and the constraint that caused the error is a referential, check, or
|
|
unique constraint, the server name of the table that owns the constraint is
|
|
returned.
|
|
<p>If the returned SQLSTATE is class 42 (Syntax Error or Access
|
|
Rule Violation), the server name of the table that caused the error is returned.</p>
|
|
<p> If the returned SQLSTATE is class 44 (WITH CHECK OPTION Violation),
|
|
the server name of the view that caused the error is returned. Otherwise,
|
|
the empty string is returned.</p>
|
|
</dd>
|
|
<dt class="bold">CLASS_ORIGIN</dt><a id="idx2716" name="idx2716"></a>
|
|
<dd>Returns 'ISO 9075' for those SQLSTATEs whose class is defined by ISO
|
|
9075. Returns 'ISO/IEC 13249' for those SQLSTATEs whose class is defined by
|
|
SQL/MM. Returns 'DB2 UDB SQL' for those SQLSTATEs whose class is defined by IBM DB2 Universal Database™ SQL. Returns the value set by user written code
|
|
if available. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">COLUMN_NAME</dt><a id="idx2717" name="idx2717"></a>
|
|
<dd>If the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation)
|
|
and the error was caused by an inaccessible column, the name of the column
|
|
that caused the error is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">CONDITION_IDENTIFIER</dt><a id="idx2718" name="idx2718"></a>
|
|
<dd>If the value of the RETURNED_SQLSTATE corresponds to an unhandled user-defined
|
|
exception (SQLSTATE 45000), then the condition name of the user-defined exception
|
|
is returned.
|
|
</dd>
|
|
<dt class="bold">CONDITION_NUMBER</dt><a id="idx2719" name="idx2719"></a>
|
|
<dd>Returns the number of the conditions.
|
|
</dd>
|
|
<dt class="bold">CONSTRAINT_CATALOG</dt><a id="idx2720" name="idx2720"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 27 (Triggered Data Change Violation), or</li>
|
|
<li>40002 (Transaction Rollback - Integrity Constraint Violation),</li></ul>the name of the server that contains the table that contains the constraint
|
|
that caused the error is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">CONSTRAINT_NAME</dt><a id="idx2721" name="idx2721"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 27 (Triggered Data Change Violation), or</li>
|
|
<li>40002 (Transaction Rollback - Integrity Constraint Violation),</li></ul>the name of the constraint that caused the error is returned. Otherwise,
|
|
the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">CONSTRAINT_SCHEMA</dt><a id="idx2722" name="idx2722"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 27 (Triggered Data Change Violation), or</li>
|
|
<li>40002 (Transaction Rollback - Integrity Constraint Violation),</li></ul>the name of the schema of the constraint that caused the error is returned.
|
|
Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">CURSOR_NAME</dt><a id="idx2723" name="idx2723"></a>
|
|
<dd>If the returned SQLSTATE is class 24 (Invalid Cursor State), the name
|
|
of the cursor is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ERROR_CODE1</dt><a id="idx2724" name="idx2724"></a>
|
|
<dd>Returns an internal error code. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ERROR_CODE2</dt><a id="idx2725" name="idx2725"></a>
|
|
<dd>Returns an internal error code. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ERROR_CODE3</dt><a id="idx2726" name="idx2726"></a>
|
|
<dd>Returns an internal error code. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ERROR_CODE4</dt><a id="idx2727" name="idx2727"></a>
|
|
<dd>Returns an internal error code. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_INTERNAL_ERROR_POINTER</dt><a id="idx2728" name="idx2728"></a>
|
|
<dd>For some errors, this will be a negative value that is an internal error
|
|
pointer. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_LINE_NUMBER</dt><a id="idx2729" name="idx2729"></a>
|
|
<dd>For a CREATE PROCEDURE for an SQL function, SQL procedure,
|
|
or SQL trigger where an error is encountered parsing the SQL procedure body,
|
|
returns the line number where the error possibly occurred. Otherwise, the
|
|
value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_MESSAGE_ID</dt><a id="idx2730" name="idx2730"></a>
|
|
<dd>Returns the message ID corresponding to the MESSAGE_TEXT.
|
|
</dd>
|
|
<dt class="bold">DB2_MESSAGE_ID1</dt><a id="idx2731" name="idx2731"></a>
|
|
<dd>Returns the underlying i5/OS CPF escape message that originally caused
|
|
this error. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_MESSAGE_ID2</dt><a id="idx2732" name="idx2732"></a>
|
|
<dd>Returns the underlying i5/OS CPD diagnostic message that originally
|
|
caused this error. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_MESSAGE_KEY</dt><a id="idx2733" name="idx2733"></a>
|
|
<dd>For a CALL statement, returns the i5/OS message key of the error that
|
|
caused the procedure to fail. For a trigger error in a DELETE, INSERT, or
|
|
UPDATE statement, returns the message key of the error that was signaled from
|
|
the trigger program. The i5/OS QMHRCVPM API can be used to return the message
|
|
description and message data for the message key. Otherwise, the value zero
|
|
is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_MODULE_DETECTING_ERROR</dt><a id="idx2734" name="idx2734"></a>
|
|
<dd>Returns an identifier indicating which module detected the error. For
|
|
a SIGNAL statement issued from a routine, the value 'ROUTINE' is returned.
|
|
For other SIGNAL statements, the value 'PROGRAM' is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_NUMBER_FAILING_STATEMENTS</dt><a id="idx2735" name="idx2735"></a>
|
|
<dd>For a NOT ATOMIC embedded compound SQL statement, returns the number
|
|
of statements that failed. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_OFFSET</dt><a id="idx2736" name="idx2736"></a>
|
|
<dd>For a CREATE PROCEDURE for an SQL procedure where an error is encountered
|
|
parsing the SQL procedure body, returns the offset into the line number where
|
|
the error possibly occurred, if available. For an EXECUTE IMMEDIATE or a PREPARE
|
|
statement where an error is encountered parsing the source statement, returns
|
|
the offset into the source statement where the error possibly occurred. Otherwise,
|
|
the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_ORDINAL_TOKEN_n</dt><a id="idx2737" name="idx2737"></a>
|
|
<dd>Returns the nth token. n must be a value from 1 to 100. For example,
|
|
DB2_ORDINAL_TOKEN_1 would return the value of the first token, DB2_ORDINAL_TOKEN_2
|
|
the second token. A numeric value for a token is converted to character before
|
|
being returned. If there is no value for the token, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_PARTITION_NUMBER</dt><a id="idx2738" name="idx2738"></a>
|
|
<dd>For a partitioned database, returns the partition number of the database
|
|
partition that encountered the error or warning. If no errors or warnings
|
|
were encountered, returns the partition number of the current node. Otherwise,
|
|
the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_REASON_CODE</dt><a id="idx2739" name="idx2739"></a>
|
|
<dd>Returns the reason code for errors that have a reason code token in
|
|
the message text. Otherwise, the value zero is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_RETURNED_SQLCODE</dt><a id="idx2740" name="idx2740"></a>
|
|
<dd>Returns the SQLCODE for the specified diagnostic.
|
|
</dd>
|
|
<dt class="bold">DB2_ROW_NUMBER</dt><a id="idx2741" name="idx2741"></a>
|
|
<dd>If the previous SQL statement is a multiple row insert or a multiple
|
|
row fetch, returns the number of the row where the condition was encountered,
|
|
when such a value is available and applicable. Otherwise, the value zero is
|
|
returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD_SET</dt><a id="idx2742" name="idx2742"></a>
|
|
<dd>Returns Y to indicate that the DB2_SQLERRD1 through DB2_SQLERRD6 items
|
|
may be set. Otherwise, a blank is returned.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD1</dt><a id="idx2743" name="idx2743"></a>
|
|
<dd>Returns the value of SQLERRD(1) from the SQLCA returned by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD2</dt><a id="idx2744" name="idx2744"></a>
|
|
<dd>Returns the value of SQLERRD(2) from the SQLCA returned by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD3</dt><a id="idx2745" name="idx2745"></a>
|
|
<dd>Returns the value of SQLERRD(3) from the SQLCA returned by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD4</dt><a id="idx2746" name="idx2746"></a>
|
|
<dd>Returns the value of SQLERRD(4) from the SQLCA returned by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD5</dt><a id="idx2747" name="idx2747"></a>
|
|
<dd>Returns the value of SQLERRD(5) from the SQLCA returned by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_SQLERRD6</dt><a id="idx2748" name="idx2748"></a>
|
|
<dd>Returns the value of SQLERRD(6) from the SQLCA returned by the server.
|
|
</dd>
|
|
<dt class="bold">DB2_TOKEN_COUNT</dt><a id="idx2749" name="idx2749"></a>
|
|
<dd>Returns the number of tokens available for the specified diagnostic.
|
|
</dd>
|
|
<dt class="bold">DB2_TOKEN_STRING</dt><a id="idx2750" name="idx2750"></a>
|
|
<dd>Returns a X'FF' delimited string of the tokens for the specified diagnostic.
|
|
</dd>
|
|
<dt class="bold">MESSAGE_LENGTH</dt><a id="idx2751" name="idx2751"></a>
|
|
<dd>Identifies the length (in characters) of the message text of the error,
|
|
warning, or successful completion returned from the previous SQL statement
|
|
that was executed.
|
|
</dd>
|
|
<dt class="bold">MESSAGE_OCTET_LENGTH</dt><a id="idx2752" name="idx2752"></a>
|
|
<dd>Identifies the length (in bytes) of the message text of the error, warning,
|
|
or successful completion returned from the previous SQL statement that was
|
|
executed.
|
|
</dd>
|
|
<dt class="bold">MESSAGE_TEXT</dt><a id="idx2753" name="idx2753"></a>
|
|
<dd>Identifies the message text of the error, warning, or successful completion
|
|
returned from the previous SQL statement that was executed.
|
|
</dd>
|
|
<dt class="bold">PARAMETER_MODE</dt><a id="idx2754" name="idx2754"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>and the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, the parameter mode of the <span class="italic">i</span>th parameter
|
|
is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">PARAMETER_NAME</dt><a id="idx2755" name="idx2755"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, and a parameter name was specified for the parameter when
|
|
the routine was created, the parameter name of the <span class="italic">i</span>th
|
|
parameter is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">PARAMETER_ORDINAL_POSITION</dt><a id="idx2756" name="idx2756"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>and the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, the value of <span class="italic">i</span> is returned. Otherwise,
|
|
the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">RETURNED_SQLSTATE</dt><a id="idx2757" name="idx2757"></a>
|
|
<dd>Returns the SQLSTATE for the specified diagnostic.
|
|
</dd>
|
|
<dt class="bold">ROUTINE_CATALOG</dt><a id="idx2758" name="idx2758"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li></ul>and the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, or if the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>and the condition was raised as the result of an assignment to an SQL
|
|
parameter during an routine invocation, the server name of the routine is
|
|
returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">ROUTINE_NAME</dt><a id="idx2759" name="idx2759"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li></ul>and the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, or if the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>and the condition was raised as the result of an assignment to an SQL
|
|
parameter during an routine invocation, the name of the routine is returned.
|
|
Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">ROUTINE_SCHEMA</dt><a id="idx2760" name="idx2760"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li></ul>and the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, or if the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>and the condition was raised as the result of an assignment to an SQL
|
|
parameter during an routine invocation, the schema name of the routine is
|
|
returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">SCHEMA_NAME</dt><a id="idx2761" name="idx2761"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 09 (Triggered Action Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 27 (Triggered Data Change Violation), or</li>
|
|
<li>40002 (Transaction Rollback - Integrity Constraint Violation),</li></ul>and the constraint that caused the error is a referential, check, or
|
|
unique constraint, the schema name of the table that owns the constraint is
|
|
returned.
|
|
<p>If the returned SQLSTATE is class 42 (Syntax Error or Access
|
|
Rule Violation), the schema name of the table that caused the error is returned.</p>
|
|
<p>If the returned SQLSTATE is class 44 (WITH CHECK OPTION Violation),
|
|
the schema name of the view that caused the error is returned. Otherwise,
|
|
the empty string is returned.</p>
|
|
</dd>
|
|
<dt class="bold">SERVER_NAME</dt><a id="idx2762" name="idx2762"></a>
|
|
<dd>If the previous SQL statement is a CONNECT, DISCONNECT, or SET CONNECTION,
|
|
the name of the server specified in the previous statement is returned. Otherwise,
|
|
the name of the server where the statement executed is returned.
|
|
</dd>
|
|
<dt class="bold">SPECIFIC_NAME</dt><a id="idx2763" name="idx2763"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 39 (External Routine Invocation Exception), or</li>
|
|
<li>class 38 (External Routine Exception), or</li>
|
|
<li>class 2F (SQL Routine Exception), or</li></ul>and the condition is related to the <span class="italic">i</span>th parameter
|
|
of the routine, or if the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 22 (Data Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 01 (Warning)</li></ul>and the condition was raised as the result of an assignment to an SQL
|
|
parameter during an routine invocation, the specific name of the procedure
|
|
or function is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">SUBCLASS_ORIGIN</dt><a id="idx2764" name="idx2764"></a>
|
|
<dd>Returns 'ISO 9075' for those SQLSTATEs whose subclass is defined by
|
|
ISO 9075. Returns 'ISO/IEC 9579' for those SQLSTATEs whose subclass is defined
|
|
by RDA. Returns 'ISO/IEC 13249-1', 'ISO/IEC 13249-2', 'ISO/IEC 13249-3', 'ISO/IEC
|
|
13249-4', or 'ISO/IEC 13249-5' for those SQLSTATEs whose subclass is defined
|
|
SQL/MM. Returns 'DB2 UDB SQL' for those SQLSTATEs whose subclass is defined
|
|
by IBM DB2 Universal Database SQL. Returns the value
|
|
set by user written code if available. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">TABLE_NAME</dt><a id="idx2765" name="idx2765"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 09 (Triggered Action Exception), or</li>
|
|
<li>class 23 (Integrity Constraint Violation), or</li>
|
|
<li>class 27 (Triggered Data Change Violation), or</li>
|
|
<li>40002 (Transaction Rollback - Integrity Constraint Violation),</li></ul>and the constraint that caused the error is a referential, check, or
|
|
unique constraint, the table name that owns the constraint is returned.
|
|
<p>If the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation),
|
|
the table name that caused the error is returned.</p>
|
|
<p>If the returned SQLSTATE
|
|
is class 44 (WITH CHECK OPTION Violation), the table name that caused the
|
|
error is returned. Otherwise, the empty string is returned.</p>
|
|
</dd>
|
|
<dt class="bold">TRIGGER_CATALOG</dt><a id="idx2766" name="idx2766"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 09 (Triggered Action Exception), or</li>
|
|
<li>class 27 (Triggered Data Change Violation),</li></ul>the name of the trigger is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">TRIGGER_NAME</dt><a id="idx2767" name="idx2767"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 09 (Triggered Action Exception), or</li>
|
|
<li>class 27 (Triggered Data Change Violation),</li></ul>the name of the trigger is returned. Otherwise, the empty string is returned.
|
|
</dd>
|
|
<dt class="bold">TRIGGER_SCHEMA</dt><a id="idx2768" name="idx2768"></a>
|
|
<dd>If the returned SQLSTATE is:
|
|
<ul>
|
|
<li>class 09 (Triggered Action Exception), or</li>
|
|
<li>class 27 (Triggered Data Change Violation),</li></ul>the schema name of the trigger is returned. Otherwise, the empty string
|
|
is returned.
|
|
</dd>
|
|
</dl>
|
|
<a name="wq1536"></a>
|
|
<h3 id="wq1536"><a href="rbafzmst02.htm#ToC_1103">Notes</a></h3>
|
|
<p><span class="bold">Effect of statement:</span> The GET DIAGNOSTICS statement
|
|
does not change the contents of the diagnostics area or the SQLCA. If an SQLSTATE
|
|
or SQLCODE special variable is declared in an SQL procedure, SQL function,
|
|
or SQL trigger, these are set to the SQLSTATE or SQLCODE returned from issuing
|
|
the GET DIAGNOSTICS statement.</p>
|
|
<p>If the GET DIAGNOSTICS statement is specified in an SQL function, SQL procedure,
|
|
or trigger, the GET DIAGNOSTICS statement must be the first executable statement
|
|
specified in the handler that will handle the error.</p>
|
|
<p>If information is desired about a warning, </p>
|
|
<ul>
|
|
<li>If a handler will get control for the warning condition, the GET DIAGNOSTICS
|
|
statement must be the first statement specified in that handler.</li>
|
|
<li>If a handler will not get control for the warning condition, the GET DIAGNOSTICS
|
|
statement must be the next statement executed after that previous statement.</li></ul>
|
|
<p><span class="bold">Case of return values:</span> Values for identifiers in
|
|
returned diagnostic items are not delimited and are case sensitive. For example,
|
|
a table name of "abc" would be returned, simply as abc.</p>
|
|
<p><span class="bold">Data types for items:</span> The following table shows,
|
|
the SQL data type for each diagnostic item. When a diagnostic item is assigned
|
|
to a variable, the variable must be compatible with the data type of the diagnostic
|
|
item.</p>
|
|
<a name="getditable"></a>
|
|
<table id="getditable" width="100%" summary="" border="1" frame="border" rules="all">
|
|
<caption>Table 58. Data Types for GET DIAGNOSTICS Items</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq1537" width="66%" align="left" valign="bottom">Item Name</th>
|
|
<th id="wq1538" width="33%" align="left" valign="bottom">Data Type</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td colspan="2" align="center" valign="top" headers="wq1537 wq1538"><span class="bold">Statement Information Item</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">COMMAND_FUNCTION</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">COMMAND_FUNCTION_CODE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_DIAGNOSTIC_CONVERSION_ERROR</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_GET_DIAGNOSTICS_DIAGNOSTICS</td>
|
|
<td headers="wq1538">VARCHAR(32740)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_LAST_ROW</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_NUMBER_CONNETIONS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_NUMBER_PARAMETER_MARKERS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_NUMBER_RESULT_SETS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_NUMBER_ROWS</td>
|
|
<td headers="wq1538">DECIMAL(31,0)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_NUMBER_SUCCESSFUL_SUBSTMTS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_RELATIVE_COST_ESTIMATE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_RETURN_STATUS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ROW_COUNT_SECONDARY</td>
|
|
<td headers="wq1538">DECIMAL(31,0)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ROW_LENGTH</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CONCURRENCY</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CURSOR_CAPABILITY</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CURSOR_HOLD</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CURSOR_ROWSET</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CURSOR_SCROLLABLE</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CURSOR_SENSITIVITY</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQL_ATTR_CURSOR_TYPE</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DYNAMIC_FUNCTION</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DYNAMIC_FUNCTION_CODE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">MORE</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">NUMBER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">ROW_COUNT</td>
|
|
<td headers="wq1538">DECIMAL(31,0)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TRANSACTION_ACTIVE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TRANSACTIONS_COMMITTED</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TRANSACTIONS_ROLLED_BACK</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td colspan="2" align="center" valign="top" headers="wq1537 wq1538"><span class="bold">Connection Information Item</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CONNECTION_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_AUTHENTICATION_TYPE</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_AUTHORIZATION_ID</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_CONNECTION_METHOD</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_CONNECTION_NUMBER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_CONNECTION_STATE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_CONNECTION_STATUS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_CONNECTION_TYPE</td>
|
|
<td headers="wq1538">SMALLINT</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_DYN_QUERY_MGMT</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ENCRYPTION_TYPE</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_PRODUCT_ID</td>
|
|
<td headers="wq1538">VARCHAR(8)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SERVER_CLASS_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SERVER_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td colspan="2" align="center" valign="top" headers="wq1537 wq1538"><span class="bold">Condition Information Item</span></td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CATALOG_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CLASS_ORIGIN</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">COLUMN_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CONDITION_IDENTIFIER</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CONDITION_NUMBER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CONSTRAINT_CATALOG</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CONSTRAINT_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CONSTRAINT_SCHEMA</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">CURSOR_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ERROR_CODE1</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ERROR_CODE2</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ERROR_CODE3</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ERROR_CODE4</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_INTERNAL_ERROR_POINTER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_LINE_NUMBER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_MESSAGE_ID</td>
|
|
<td headers="wq1538">CHAR(10)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_MESSAGE_ID1</td>
|
|
<td headers="wq1538">VARCHAR(7)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_MESSAGE_ID2</td>
|
|
<td headers="wq1538">VARCHAR(7)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_MESSAGE_KEY</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_MODULE_DETECTING_ERROR</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_NUMBER_FAILING_STATEMENTS</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_OFFSET</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ORDINAL_TOKEN_n</td>
|
|
<td headers="wq1538">VARCHAR(32740)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_PARTITION_NUMBER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_REASON_CODE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_RETURNED_SQLCODE</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_ROW_NUMBER</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD_SET</td>
|
|
<td headers="wq1538">CHAR(1)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD1</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD2</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD3</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD4</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD5</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_SQLERRD6</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_TOKEN_COUNT</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">DB2_TOKEN_STRING</td>
|
|
<td headers="wq1538">VARCHAR(70)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">MESSAGE_LENGTH</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">MESSAGE_OCTET_LENGTH</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">MESSAGE_TEXT</td>
|
|
<td headers="wq1538">VARCHAR(32740)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">PARAMETER_MODE</td>
|
|
<td headers="wq1538">VARCHAR(5)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">PARAMETER_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">PARAMETER_ORDINAL_POSITION</td>
|
|
<td headers="wq1538">INTEGER</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">RETURNED_SQLSTATE</td>
|
|
<td headers="wq1538">CHAR(5)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">ROUTINE_CATALOG</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">ROUTINE_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">ROUTINE_SCHEMA</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">SCHEMA_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">SERVER_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">SPECIFIC_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">SUBCLASS_ORIGIN</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TABLE_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TRIGGER_CATALOG</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TRIGGER_NAME</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1537">TRIGGER_SCHEMA</td>
|
|
<td headers="wq1538">VARCHAR(128)</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p><span class="bold">SQL statement codes and strings:</span> The following table
|
|
represents the possible values for COMMAND_FUNCTION, COMMAND_FUNCTION_CODE,
|
|
DYNAMIC_FUNCTION, and DYNAMIC_FUNCTION_CODE diagnostic items.</p>
|
|
<p>The values in the following table are assigned by the ISO and ANSI SQL
|
|
Standard and may change as the standard evolves. Include <span class="italic">sqlscds</span> in the include source files in library QSYSINC should be used
|
|
when referencing these values.</p>
|
|
<a name="stmtstrcode"></a>
|
|
<table id="stmtstrcode" width="100%" summary="" border="1" frame="border" rules="all">
|
|
<caption>Table 59. SQL Statement Codes and Strings</caption>
|
|
<thead valign="bottom">
|
|
<tr>
|
|
<th id="wq1539" width="42%" align="left" valign="bottom">Type of statement</th>
|
|
<th id="wq1540" width="42%" align="left" valign="bottom">Statement string</th>
|
|
<th id="wq1541" width="14%" align="left" valign="bottom">Statement code</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody valign="top">
|
|
<tr>
|
|
<td headers="wq1539">ALLOCATE DESCRIPTOR</td>
|
|
<td headers="wq1540">ALLOCATE DESCRIPTOR</td>
|
|
<td headers="wq1541">2</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">ALTER PROCEDURE</td>
|
|
<td headers="wq1540">ALTER ROUTINE</td>
|
|
<td headers="wq1541">17</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">ALTER SEQUENCE</td>
|
|
<td headers="wq1540">ALTER SEQUENCE</td>
|
|
<td headers="wq1541">134</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">ALTER TABLE</td>
|
|
<td headers="wq1540">ALTER TABLE</td>
|
|
<td headers="wq1541">4</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">assignment-statement</td>
|
|
<td headers="wq1540">ASSIGNMENT</td>
|
|
<td headers="wq1541">5</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CALL</td>
|
|
<td headers="wq1540">CALL</td>
|
|
<td headers="wq1541">7</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CASE</td>
|
|
<td headers="wq1540">CASE</td>
|
|
<td headers="wq1541">86</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CLOSE (static SQL)</td>
|
|
<td headers="wq1540">CLOSE CURSOR</td>
|
|
<td headers="wq1541">9</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CLOSE (dynamic SQL)</td>
|
|
<td headers="wq1540">DYNAMIC CLOSE CURSOR</td>
|
|
<td headers="wq1541">37</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">COMMENT</td>
|
|
<td headers="wq1540">COMMENT</td>
|
|
<td headers="wq1541">–7</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">COMMIT</td>
|
|
<td headers="wq1540">COMMIT WORK</td>
|
|
<td headers="wq1541">11</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">compound-statement</td>
|
|
<td headers="wq1540">BEGIN END</td>
|
|
<td headers="wq1541">12</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CONNECT</td>
|
|
<td headers="wq1540">CONNECT</td>
|
|
<td headers="wq1541">13</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE ALIAS</td>
|
|
<td headers="wq1540">CREATE ALIAS</td>
|
|
<td headers="wq1541">–8</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE DISTINCT TYPE</td>
|
|
<td headers="wq1540">CREATE TYPE</td>
|
|
<td headers="wq1541">83</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE FUNCTION</td>
|
|
<td headers="wq1540">CREATE ROUTINE</td>
|
|
<td headers="wq1541">14</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE INDEX</td>
|
|
<td headers="wq1540">CREATE INDEX</td>
|
|
<td headers="wq1541">–14</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE PROCEDURE</td>
|
|
<td headers="wq1540">CREATE ROUTINE</td>
|
|
<td headers="wq1541">14</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE SCHEMA</td>
|
|
<td headers="wq1540">CREATE SCHEMA</td>
|
|
<td headers="wq1541">64</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE SEQUENCE</td>
|
|
<td headers="wq1540">CREATE SEQUENCE</td>
|
|
<td headers="wq1541">133</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE TABLE</td>
|
|
<td headers="wq1540">CREATE TABLE</td>
|
|
<td headers="wq1541">77</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE TRIGGER</td>
|
|
<td headers="wq1540">CREATE TRIGGER</td>
|
|
<td headers="wq1541">80</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">CREATE VIEW</td>
|
|
<td headers="wq1540">CREATE VIEW</td>
|
|
<td headers="wq1541">84</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DEALLOCATE DESCRIPTOR</td>
|
|
<td headers="wq1540">DEALLOCATE DESCRIPTOR</td>
|
|
<td headers="wq1541">15</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DECLARE GLOBAL TEMPORARY TABLE</td>
|
|
<td headers="wq1540">DECLARE GLOBAL TEMPORARY TABLE</td>
|
|
<td headers="wq1541">–21</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DELETE Positioned (static SQL)</td>
|
|
<td headers="wq1540">DELETE CURSOR</td>
|
|
<td headers="wq1541">18</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DELETE Positioned (dynamic SQL)</td>
|
|
<td headers="wq1540">DYNAMIC DELETE CURSOR</td>
|
|
<td headers="wq1541">38</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DELETE Searched</td>
|
|
<td headers="wq1540">DELETE WHERE</td>
|
|
<td headers="wq1541">19</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DESCRIBE</td>
|
|
<td headers="wq1540">DESCRIBE</td>
|
|
<td headers="wq1541">20</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DESCRIBE TABLE</td>
|
|
<td headers="wq1540">DESCRIBE TABLE</td>
|
|
<td headers="wq1541">–24</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DISCONNECT</td>
|
|
<td headers="wq1540">DISCONNECT</td>
|
|
<td headers="wq1541">22</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP ALIAS</td>
|
|
<td headers="wq1540">DROP ALIAS</td>
|
|
<td headers="wq1541">–25</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP DISTINCT TYPE</td>
|
|
<td headers="wq1540">DROP TYPE</td>
|
|
<td headers="wq1541">35</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP FUNCTION</td>
|
|
<td headers="wq1540">DROP ROUTINE</td>
|
|
<td headers="wq1541">30</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP INDEX</td>
|
|
<td headers="wq1540">DROP INDEX</td>
|
|
<td headers="wq1541">–30</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP PACKAGE</td>
|
|
<td headers="wq1540">DROP PACKAGE</td>
|
|
<td headers="wq1541">–32</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP PROCEDURE</td>
|
|
<td headers="wq1540">DROP ROUTINE</td>
|
|
<td headers="wq1541">30</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP SCHEMA</td>
|
|
<td headers="wq1540">DROP SCHEMA</td>
|
|
<td headers="wq1541">31</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP SEQUENCE</td>
|
|
<td headers="wq1540">DROP SEQUENCE</td>
|
|
<td headers="wq1541">135</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP TABLE</td>
|
|
<td headers="wq1540">DROP TABLE</td>
|
|
<td headers="wq1541">32</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP TRIGGER</td>
|
|
<td headers="wq1540">DROP TRIGGER</td>
|
|
<td headers="wq1541">34</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">DROP VIEW</td>
|
|
<td headers="wq1540">DROP VIEW</td>
|
|
<td headers="wq1541">36</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">EXECUTE</td>
|
|
<td headers="wq1540">EXECUTE</td>
|
|
<td headers="wq1541">44</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">EXECUTE IMMEDIATE</td>
|
|
<td headers="wq1540">EXECUTE IMMEDIATE</td>
|
|
<td headers="wq1541">43</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">FETCH (static SQL)</td>
|
|
<td headers="wq1540">FETCH</td>
|
|
<td headers="wq1541">45</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">FETCH (dynamic SQL)</td>
|
|
<td headers="wq1540">DYNAMIC FETCH</td>
|
|
<td headers="wq1541">39</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">FOR</td>
|
|
<td headers="wq1540">FOR</td>
|
|
<td headers="wq1541">46</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">FREE LOCATOR</td>
|
|
<td headers="wq1540">FREE LOCATOR</td>
|
|
<td headers="wq1541">98</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">GET DESCRIPTOR</td>
|
|
<td headers="wq1540">GET DESCRIPTOR</td>
|
|
<td headers="wq1541">47</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">GOTO</td>
|
|
<td headers="wq1540">GOTO</td>
|
|
<td headers="wq1541">–37</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">GRANT (any type)</td>
|
|
<td headers="wq1540">GRANT</td>
|
|
<td headers="wq1541">48</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">HOLD LOCATOR</td>
|
|
<td headers="wq1540">HOLD LOCATOR</td>
|
|
<td headers="wq1541">99</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">IF</td>
|
|
<td headers="wq1540">IF</td>
|
|
<td headers="wq1541">88</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">INSERT</td>
|
|
<td headers="wq1540">INSERT</td>
|
|
<td headers="wq1541">50</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">ITERATE</td>
|
|
<td headers="wq1540">ITERATE</td>
|
|
<td headers="wq1541">102</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">LABEL</td>
|
|
<td headers="wq1540">LABEL</td>
|
|
<td headers="wq1541">–39</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">LEAVE</td>
|
|
<td headers="wq1540">LEAVE</td>
|
|
<td headers="wq1541">89</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">LOCK TABLE</td>
|
|
<td headers="wq1540">LOCK TABLE</td>
|
|
<td headers="wq1541">–40</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">LOOP</td>
|
|
<td headers="wq1540">LOOP</td>
|
|
<td headers="wq1541">90</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">OPEN (static SQL)</td>
|
|
<td headers="wq1540">OPEN</td>
|
|
<td headers="wq1541">53</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">OPEN (dynamic SQL)</td>
|
|
<td headers="wq1540">DYNAMIC OPEN</td>
|
|
<td headers="wq1541">40</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">PREPARE</td>
|
|
<td headers="wq1540">PREPARE</td>
|
|
<td headers="wq1541">56</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">Prepared DELETE Positioned (dynamic SQL)</td>
|
|
<td headers="wq1540">PREPARABLE DYNAMIC DELETE CURSOR</td>
|
|
<td headers="wq1541">54</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">Prepared UPDATE Positioned (dynamic SQL)</td>
|
|
<td headers="wq1540">PREPARABLE DYNAMIC UPDATE CURSOR</td>
|
|
<td headers="wq1541">55</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">REFRESH TABLE</td>
|
|
<td headers="wq1540">REFRESH TABLE</td>
|
|
<td headers="wq1541">–41</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">RELEASE (connection)</td>
|
|
<td headers="wq1540">RELEASE CONNECTION</td>
|
|
<td headers="wq1541">–42</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">RELEASE SAVEPOINT</td>
|
|
<td headers="wq1540">RELEASE SAVEPOINT</td>
|
|
<td headers="wq1541">57</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">RENAME INDEX</td>
|
|
<td headers="wq1540">RENAME INDEX</td>
|
|
<td headers="wq1541">–43</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">RENAME TABLE</td>
|
|
<td headers="wq1540">RENAME TABLE</td>
|
|
<td headers="wq1541">–44</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">REPEAT</td>
|
|
<td headers="wq1540">REPEAT</td>
|
|
<td headers="wq1541">95</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">RESIGNAL</td>
|
|
<td headers="wq1540">RESIGNAL</td>
|
|
<td headers="wq1541">91</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">RETURN</td>
|
|
<td headers="wq1540">RETURN</td>
|
|
<td headers="wq1541">58</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">REVOKE (any type)</td>
|
|
<td headers="wq1540">REVOKE</td>
|
|
<td headers="wq1541">59</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">ROLLBACK</td>
|
|
<td headers="wq1540">ROLLBACK WORK</td>
|
|
<td headers="wq1541">62</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SAVEPOINT</td>
|
|
<td headers="wq1540">SAVEPOINT</td>
|
|
<td headers="wq1541">63</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SELECT INTO</td>
|
|
<td headers="wq1540">SELECT</td>
|
|
<td headers="wq1541">65</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539"><var class="pv">select-statement</var> (dynamic SQL)</td>
|
|
<td headers="wq1540">SELECT CURSOR</td>
|
|
<td headers="wq1541">85</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET CONNECTION</td>
|
|
<td headers="wq1540">SET CONNECTION</td>
|
|
<td headers="wq1541">67</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET CURRENT DEBUG MODE</td>
|
|
<td headers="wq1540">SET CURRENT DEBUG MODE</td>
|
|
<td headers="wq1541">-75</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET CURRENT DEGREE</td>
|
|
<td headers="wq1540">SET CURRENT DEGREE</td>
|
|
<td headers="wq1541">–47</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET DESCRIPTOR</td>
|
|
<td headers="wq1540">SET DESCRIPTOR</td>
|
|
<td headers="wq1541">70</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET ENCRYPTION PASSWORD</td>
|
|
<td headers="wq1540">SET ENCRYPTION PASSWORD</td>
|
|
<td headers="wq1541">–48</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET PATH</td>
|
|
<td headers="wq1540">SET PATH</td>
|
|
<td headers="wq1541">69</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET RESULT SETS</td>
|
|
<td headers="wq1540">SET RESULT SETS</td>
|
|
<td headers="wq1541">–64</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET SCHEMA</td>
|
|
<td headers="wq1540">SET SCHEMA</td>
|
|
<td headers="wq1541">74</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET SESSION AUTHORIZATION</td>
|
|
<td headers="wq1540">SET SESSION AUTHORIZATION</td>
|
|
<td headers="wq1541">76</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET TRANSACTION</td>
|
|
<td headers="wq1540">SET TRANSACTION</td>
|
|
<td headers="wq1541">75</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET transition-variable</td>
|
|
<td headers="wq1540">ASSIGNMENT</td>
|
|
<td headers="wq1541">5</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SET variable</td>
|
|
<td headers="wq1540">ASSIGNMENT</td>
|
|
<td headers="wq1541">5</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">SIGNAL</td>
|
|
<td headers="wq1540">SIGNAL</td>
|
|
<td headers="wq1541">92</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">UPDATE Positioned (static SQL)</td>
|
|
<td headers="wq1540">UPDATE CURSOR</td>
|
|
<td headers="wq1541">81</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">UPDATE Positioned (dynamic SQL)</td>
|
|
<td headers="wq1540">DYNAMIC UPDATE CURSOR</td>
|
|
<td headers="wq1541">42</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">UPDATE Searched</td>
|
|
<td headers="wq1540">UPDATE WHERE</td>
|
|
<td headers="wq1541">82</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">VALUES</td>
|
|
<td headers="wq1540">STANDALONE FULLSELECT</td>
|
|
<td headers="wq1541">–69</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">VALUES INTO</td>
|
|
<td headers="wq1540">VALUES INTO</td>
|
|
<td headers="wq1541">–66</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">WHILE</td>
|
|
<td headers="wq1540">WHILE</td>
|
|
<td headers="wq1541">97</td>
|
|
</tr>
|
|
<tr>
|
|
<td headers="wq1539">Unrecognized statement</td>
|
|
<td headers="wq1540">a zero length string</td>
|
|
<td headers="wq1541">0</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
<p><span class="bold">Syntax alternatives:</span> The following keywords are synonyms
|
|
supported for compatibility to prior releases. These keywords are non-standard
|
|
and should not be used:</p>
|
|
<ul>
|
|
<li>The keyword EXCEPTION can be used as a synonym for CONDITION.</li>
|
|
<li>The keyword RETURN_STATUS can be used as a synonym for DB2_RETURN_STATUS.</li></ul>
|
|
<a name="wq1542"></a>
|
|
<h3 id="wq1542"><a href="rbafzmst02.htm#ToC_1104">Example</a></h3>
|
|
<p>In an SQL procedure, execute a GET DIAGNOSTICS statement to determine how
|
|
many rows were updated.</p>
|
|
<pre class="xmp"> <span class="bold">CREATE PROCEDURE</span> sqlprocg <span class="bold">(IN</span> deptnbr <span class="bold">VARCHAR(</span>3<span class="bold">))</span>
|
|
<span class="bold">LANGUAGE SQL</span>
|
|
<span class="bold">BEGIN</span>
|
|
<span class="bold">DECLARE SQLSTATE CHAR(</span>5<span class="bold">)</span>;
|
|
<span class="bold">DECLARE</span> rcount <span class="bold">INTEGER</span>;
|
|
<span class="bold">UPDATE</span> CORPDATA.PROJECT
|
|
<span class="bold">SET</span> PRSTAFF <span class="bold">=</span> PRSTAFF <span class="bold">+</span> 1.5
|
|
<span class="bold">WHERE</span> DEPTNO <span class="bold">=</span> deptnbr;
|
|
<span class="bold">GET DIAGNOSTICS</span> rcount <span class="bold">= ROW_COUNT</span>;
|
|
/* At this point, rcount contains the number of rows that were updated. */
|
|
<span class="bold">END</span>
|
|
</pre>
|
|
<p>Within an SQL procedure, handle the returned status value from the invocation
|
|
of a stored procedure called TRYIT. TRYIT could use the RETURN statement to
|
|
explicitly return a status value or a status value could be implicitly returned
|
|
by the database manager. If the procedure is successful, it returns a value
|
|
of zero.</p>
|
|
<pre class="xmp"> <span class="bold">CREATE PROCEDURE</span> TESTIT <span class="bold">()</span>
|
|
<span class="bold">LANGUAGE SQL</span>
|
|
A1: <span class="bold">BEGIN</span>
|
|
<span class="bold">DECLARE</span> RETVAL <span class="bold">INTEGER DEFAULT</span> 0;
|
|
...
|
|
<span class="bold">CALL</span> TRYIT
|
|
<span class="bold">GET DIAGNOSTICS</span> RETVAL <span class="bold">= RETURN_STATUS</span>;
|
|
<span class="bold">IF</span> RETVAL <span class="bold"><></span> 0 <span class="bold">THEN</span>
|
|
...
|
|
<span class="bold">LEAVE</span> A1;
|
|
<span class="bold">ELSE</span>
|
|
...
|
|
<span class="bold">END IF</span>;
|
|
<span class="bold">END</span> A1
|
|
</pre>
|
|
<p>In an SQL procedure, execute a GET DIAGNOSTICS statement to retrieve the
|
|
message text for an error.</p>
|
|
<pre class="xmp"> <span class="bold">CREATE PROCEDURE</span> divide2 <span class="bold">( IN</span> numerator <span class="bold">INTEGER,
|
|
IN</span> denominator <span class="bold">INTEGER,
|
|
OUT</span> divide_result <span class="bold">INTEGER,
|
|
OUT</span> divide_error <span class="bold">VARCHAR(70) )</span>
|
|
<span class="bold">LANGUAGE SQL</span>
|
|
<span class="bold">BEGIN</span>
|
|
<span class="bold">DECLARE CONTINUE HANDLER FOR SQLEXCEPTION</span>
|
|
<span class="bold">GET DIAGNOSTICS CONDITION 1</span>
|
|
divide_error <span class="bold">= MESSAGE_TEXT</span>;
|
|
<span class="bold">SET</span> divide_result = numerator / denominator;
|
|
<span class="bold">END</span>; </pre><a id="idx2769" name="idx2769"></a><a id="idx2770" name="idx2770"></a><a id="idx2771" name="idx2771"></a><a id="idx2772" name="idx2772"></a>
|
|
<hr /><br />
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzgetdescr.htm">Previous Page</a> | <a href="rbafzmstgntudtp.htm">Next Page</a> | <a href="rbafzmst02.htm#wq1">Contents</a> |
|
|
<a href="rbafzmstindex.htm#index">Index</a> ]
|
|
|
|
<a id="Bot_Of_Page" name="Bot_Of_Page"></a>
|
|
</body>
|
|
</html>
|