256 lines
15 KiB
HTML
256 lines
15 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="DESCRIBE TABLE statement, DESCRIBE TABLE,
|
||
|
|
SQL statements, with DESCRIBE TABLE, obtaining information, table,
|
||
|
|
obtaining table information with, dynamic SQL, in DESCRIBE TABLE statement,
|
||
|
|
variable, USING keyword, SQL-descriptor-name, INTO keyword, SQLN, variables,
|
||
|
|
SQLN field of SQLDA, SQLDAID, SQLDAID field of SQLDA, SQLDABC,
|
||
|
|
SQLDABC field of SQLDA, SQLD, SQLD field of SQLDA, SQLVAR, SQLVAR field of SQLDA,
|
||
|
|
USING clause, in USING clause, NAMES, SYSTEM NAMES, LABELS, ANY clause,
|
||
|
|
BOTH clause, ALL clause, system column name, SQL (Structured Query Language),
|
||
|
|
description" />
|
||
|
|
<title>DESCRIBE TABLE</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="descrtb"></a>
|
||
|
|
<h2 id="descrtb"><a href="rbafzmst02.htm#ToC_1030">DESCRIBE TABLE</a></h2>
|
||
|
|
<a name="desctbl"></a>
|
||
|
|
<p id="desctbl"><a id="idx2437" name="idx2437"></a> <a id="idx2438" name="idx2438"></a> <a id="idx2439" name="idx2439"></a> <a id="idx2440" name="idx2440"></a></p>
|
||
|
|
<p>The DESCRIBE TABLE statement obtains information about a table or view.</p>
|
||
|
|
<a name="wq1461"></a>
|
||
|
|
<h3 id="wq1461"><a href="rbafzmst02.htm#ToC_1031">Invocation</a></h3>
|
||
|
|
<p>This statement can only be embedded in an application program,
|
||
|
|
SQL function, SQL procedure, or trigger. It is an executable statement that
|
||
|
|
cannot be dynamically prepared. It must not be specified in Java™.</p>
|
||
|
|
<a name="wq1462"></a>
|
||
|
|
<h3 id="wq1462"><a href="rbafzmst02.htm#ToC_1032">Authorization</a></h3>
|
||
|
|
<p>The privileges held by the authorization ID of the statement must include
|
||
|
|
at least one of the following: </p>
|
||
|
|
<ul>
|
||
|
|
<li>For the table or view identified in the statement:
|
||
|
|
<ul>
|
||
|
|
<li>The system authority of *OBJOPR on the table or view</li>
|
||
|
|
<li>The system authority *EXECUTE on the library containing the table or view</li></ul></li>
|
||
|
|
<li>Administrative authority</li></ul>
|
||
|
|
<a name="wq1463"></a>
|
||
|
|
<h3 id="wq1463"><a href="rbafzmst02.htm#ToC_1033">Syntax</a></h3>
|
||
|
|
<a href="rbafzmstdescrtb.htm#synsdtbl"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
||
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn331.htm"
|
||
|
|
border="0" /></span><a href="#skipsyn-330"><img src="c.gif" alt="Skip visual syntax diagram"
|
||
|
|
border="0" /></a>>>-DESCRIBE TABLE--<span><span class="italic">variable</span></span>------------------------------------->
|
||
|
|
|
||
|
|
.-<span>SQL</span>-. .-<span>LOCAL</span>--.
|
||
|
|
>--+-<span>USING</span>--+-----+--<span>DESCRIPTOR</span>--+--------+--<span><span class="italic">SQL-descriptor-name</span></span>-+-><
|
||
|
|
| '-<span>GLOBAL</span>-' |
|
||
|
|
'-<span>INTO</span>----<span><span class="italic">descriptor-name</span></span>----+-------------------------+------'
|
||
|
|
'-<span>USING</span>--+-<span>NAMES</span>--------+-'
|
||
|
|
+-<span>SYSTEM NAMES</span>-+
|
||
|
|
+-<span>LABELS</span>-------+
|
||
|
|
+-<span>ANY</span>----------+
|
||
|
|
+-<span>BOTH</span>---------+
|
||
|
|
'-<span>ALL</span>----------'
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
<a name="skipsyn-330" id="skipsyn-330"></a>
|
||
|
|
<a name="synsdtbl"></a>
|
||
|
|
<h3 id="synsdtbl"><a href="rbafzmst02.htm#ToC_1034">Description</a></h3>
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><var class="pv">variable</var> <a id="idx2441" name="idx2441"></a></dt>
|
||
|
|
<dd>Identifies the table or view to describe. When the DESCRIBE TABLE statement
|
||
|
|
is executed:
|
||
|
|
<ul>
|
||
|
|
<li>The name must identify a table or view that exists at the application server.</li>
|
||
|
|
<li>The <var class="pv">variable</var> must be a character-string or UTF-16
|
||
|
|
or UCS-2 graphic-string variable and must not include an indicator variable.</li>
|
||
|
|
<li>The table name that is contained within the <var class="pv">variable</var> must
|
||
|
|
be left-justified and must be padded on the right with blanks if its length
|
||
|
|
is less than that of the <var class="pv">variable</var>.</li>
|
||
|
|
<li>The name of the table must be in uppercase unless it is a delimited name.</li></ul>
|
||
|
|
<p>When the DESCRIBE TABLE statement is executed, the database
|
||
|
|
manager assigns values to the variables of the SQL descriptor or SQLDA as
|
||
|
|
follows:</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">USING <a id="idx2442" name="idx2442"></a></dt>
|
||
|
|
<dd>Identifies an SQL descriptor.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold">LOCAL</dt>
|
||
|
|
<dd>Specifies the scope of the name of the descriptor to be local to program
|
||
|
|
invocation.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">GLOBAL</dt>
|
||
|
|
<dd>Specifies the scope of the name of the descriptor to be global to the
|
||
|
|
SQL session.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold"><var class="pv">SQL-descriptor-name</var></dt>
|
||
|
|
<dd>Names the SQL descriptor. The name must identify a descriptor that already
|
||
|
|
exists with the specified scope. <a id="idx2443" name="idx2443"></a>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">INTO <var class="pv">descriptor-name</var> <a id="idx2444" name="idx2444"></a></dt>
|
||
|
|
<dd>Identifies an SQL descriptor area (SQLDA), which is described in <a href="rbafzmstsqldda.htm#sqldda">Appendix D. SQLDA (SQL descriptor area)</a>. Before the DESCRIBE TABLE statement is executed, the following
|
||
|
|
variable in the SQLDA must be set.
|
||
|
|
<dl>
|
||
|
|
<dt class="bold">SQLN</dt><a id="idx2445" name="idx2445"></a><a id="idx2446" name="idx2446"></a>
|
||
|
|
<dd>Specifies the number of SQLVAR occurrences provided in the SQLDA. SQLN
|
||
|
|
must be set to a value greater than or equal to zero before the DESCRIBE TABLE
|
||
|
|
statement is executed. For information on techniques to determine the number
|
||
|
|
of occurrences requires, see <a href="rbafzmstfielddescsqlda.htm#howmany">Determining how many SQLVAR occurrences are needed</a>.
|
||
|
|
</dd>
|
||
|
|
</dl>The rules for REXX are different. For more information, see the <a href="../rzajp/rzajpkickoff.htm">Embedded SQL Programming</a> book.
|
||
|
|
<p>When the DESCRIBE statement is executed,
|
||
|
|
the database manager assigns values to the variables of the SQLDA as follows: </p>
|
||
|
|
<dl>
|
||
|
|
<dt class="bold">SQLDAID</dt><a id="idx2447" name="idx2447"></a><a id="idx2448" name="idx2448"></a>
|
||
|
|
<dd>The first 6 bytes are set to 'SQLDA ' (that is, 5 letters followed by
|
||
|
|
the space character).
|
||
|
|
<p>The seventh byte is set based on the column described:</p>
|
||
|
|
<ul>
|
||
|
|
<li>If the SQLDA contains two, three, or four SQLVAR entries for
|
||
|
|
every column of the table, the seventh byte is set to '2', '3', or '4'. This
|
||
|
|
technique is used in order to accommodate LOB or distinct type result columns,
|
||
|
|
labels, and system names.</li>
|
||
|
|
<li>Otherwise, the seventh byte is set to the space character.</li></ul>
|
||
|
|
<p>The seventh byte is set to the space character if there is not
|
||
|
|
enough room in the SQLDA to contain the description of all columns.</p>
|
||
|
|
<p>The eighth byte is set to the space character.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">SQLDABC</dt><a id="idx2449" name="idx2449"></a><a id="idx2450" name="idx2450"></a>
|
||
|
|
<dd>Length of the SQLDA in bytes.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">SQLD</dt><a id="idx2451" name="idx2451"></a><a id="idx2452" name="idx2452"></a>
|
||
|
|
<dd>The number of columns in the table.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">SQLVAR</dt><a id="idx2453" name="idx2453"></a><a id="idx2454" name="idx2454"></a>
|
||
|
|
<dd>If the value of SQLD is 0, or greater than the value of SQLN, no values
|
||
|
|
are assigned to occurrences of SQLVAR.
|
||
|
|
<p>If the value of SQLD is <span class="italic">n</span>, where <span class="italic">n</span> is greater than 0 but less than or
|
||
|
|
equal to the value of SQLN, values are assigned to the first <span class="italic">n</span> occurrences of SQLVAR so that the first occurrence of SQLVAR contains
|
||
|
|
a description of the first column of the table, the second occurrence of SQLVAR
|
||
|
|
contains a description of the second column of the table, and so on. For information
|
||
|
|
on the values assigned to SQLVAR occurrences, see <a href="rbafzmstsqlvaroc.htm#sqlvaroc">Field descriptions in an occurrence of SQLVAR</a>.</p>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<p></p>
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold"><span class="pk">USING</span> <a id="idx2455" name="idx2455"></a></dt>
|
||
|
|
<dd>Specifies what value to assign to each SQLNAME variable in
|
||
|
|
the SQLDA. If the requested value does not exist or if the length of a name
|
||
|
|
is greater than 30, SQLNAME is set to a length of 0.
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold">NAMES <a id="idx2456" name="idx2456"></a></dt>
|
||
|
|
<dd>Assigns the name of the column. The column name returned
|
||
|
|
is case sensitive and without delimiters. This is the default.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">SYSTEM NAMES</dt>
|
||
|
|
<dd>Assigns the system column name of the column. <a id="idx2457" name="idx2457"></a>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">LABELS <a id="idx2458" name="idx2458"></a></dt>
|
||
|
|
<dd>Assigns the label of the column. (Column labels are defined by the LABEL
|
||
|
|
statement.) Only the first 20 bytes of the label are returned.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">ANY <a id="idx2459" name="idx2459"></a></dt>
|
||
|
|
<dd>Assigns the column label. If the column has no label, the column name
|
||
|
|
is used instead.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">BOTH <a id="idx2460" name="idx2460"></a></dt>
|
||
|
|
<dd>Assigns both the label and name of the column. In this case,
|
||
|
|
two or three occurrences of SQLVAR per column, depending on whether the table
|
||
|
|
contains distinct types, are needed to accommodate the additional information.
|
||
|
|
To specify this expansion of the SQLVAR array, set SQLN to 2*<span class="italic">n</span> or 3*<span class="italic">n</span>(where <span class="italic">n</span> is the
|
||
|
|
number of columns in the table or view). The first <span class="italic">n</span> occurrences
|
||
|
|
of SQLVAR contain the column names if they are different from the system column
|
||
|
|
name. Either the second or third <span class="italic">n</span> occurrences contain
|
||
|
|
the column labels. If there are no distinct types, the labels are returned
|
||
|
|
in the second set of SQLVAR entries. Otherwise, the labels are returned in
|
||
|
|
the third set of SQLVAR entries.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">ALL <a id="idx2461" name="idx2461"></a> <a id="idx2462" name="idx2462"></a></dt>
|
||
|
|
<dd>Assigns the label, column name, and system column name. In this case
|
||
|
|
three or four occurrences of SQLVAR per column, depending on whether the table
|
||
|
|
contains distinct types, are needed to accommodate the additional information.
|
||
|
|
To specify this expansion of the SQLVAR array, set SQLN to 3*<span class="italic">n</span> or 4*<span class="italic">n</span> (where <span class="italic">n</span> is
|
||
|
|
the number of columns in the table). The first <span class="italic">n</span> occurrences
|
||
|
|
of SQLVAR contain the system column names. The second or third <span class="italic">n</span> occurrences contain the column labels. The third or fourth <span class="italic">n</span> occurrences contain the column names. If there are no distinct types,
|
||
|
|
the labels are returned in the second set of SQLVAR entries and the column
|
||
|
|
names are returned in the third set of SQLVAR entries. Otherwise, the labels
|
||
|
|
are returned in the third set of SQLVAR entries and the column names are returned
|
||
|
|
in the fourth set of SQLVAR entries.
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<a name="wq1464"></a>
|
||
|
|
<h3 id="wq1464"><a href="rbafzmst02.htm#ToC_1035">Notes</a></h3>
|
||
|
|
<p><span class="bold">Allocating the SQL descriptor:</span> Before
|
||
|
|
the DESCRIBE TABLE statement is executed, the SQL descriptor must be allocated
|
||
|
|
using the ALLOCATE DESCRIPTOR statement. If the number of descriptor items
|
||
|
|
allocated is less than the number of columns in the table or view, a warning
|
||
|
|
(SQLSTATE 01005) is returned.</p>
|
||
|
|
<p><span class="bold">Allocating the SQLDA:</span> Before the DESCRIBE TABLE statement
|
||
|
|
is executed, the value of SQLN must be set to a value greater than or equal
|
||
|
|
to zero to indicate how many occurrences of SQLVAR are provided in the SQLDA
|
||
|
|
and enough storage must be allocated to contain SQLN occurrences. To obtain
|
||
|
|
the description of the columns of the table or view, the number of occurrences
|
||
|
|
of SQLVAR must not be less than the number of columns. Furthermore, if USING
|
||
|
|
BOTH or USING ALL is specified, or if the columns include LOBs or distinct
|
||
|
|
types, the number of occurrences of SQLVAR should be two, three, or four times
|
||
|
|
the number of columns. See <a href="rbafzmstfielddescsqlda.htm#howmany">Determining how many SQLVAR occurrences are needed</a> for more information.</p>
|
||
|
|
<p>If not enough occurrences are provided to return all sets of occurrences,
|
||
|
|
SQLN is set to the total number of occurrences necessary to return all information.
|
||
|
|
Otherwise, SQLN is set to the number of columns.</p>
|
||
|
|
<p>For a description of techniques that can be used to allocate the SQLDA,
|
||
|
|
see <a href="rbafzmstsqldda.htm#sqldda">Appendix D. SQLDA (SQL descriptor area)</a>.</p>
|
||
|
|
<a name="wq1465"></a>
|
||
|
|
<h3 id="wq1465"><a href="rbafzmst02.htm#ToC_1036">Example</a></h3>
|
||
|
|
<p>In a C program, execute a DESCRIBE statement with an SQLDA that has no
|
||
|
|
occurrences of SQLVAR. If SQLD is greater than zero, use the value to allocate
|
||
|
|
an SQLDA with the necessary number of occurrences of SQLVAR and then execute
|
||
|
|
a DESCRIBE statement using that SQLDA. </p>
|
||
|
|
<pre class="xmp"> EXEC SQL <span class="bold">BEGIN DECLARE SECTION</span>;
|
||
|
|
char table_name[201];
|
||
|
|
EXEC SQL <span class="bold">END DECLARE SECTION</span>;
|
||
|
|
EXEC SQL <span class="bold">INCLUDE SQLDA</span>;
|
||
|
|
EXEC SQL <span class="bold">DECLARE</span> DYN_CURSOR <span class="bold">CURSOR FOR</span> STMT1_NAME;
|
||
|
|
|
||
|
|
.../*code to prompt user for a table or view */
|
||
|
|
.../*code to set SQLN to zero and to allocate the SQLDA */
|
||
|
|
EXEC SQL <span class="bold">DESCRIBE TABLE</span> :table_name <span class="bold">INTO</span> :sqlda;
|
||
|
|
|
||
|
|
... /* code to check that SQLD is greater than zero, to set */
|
||
|
|
/* SQLN to SQLD, then to re-allocate the SQLDA */
|
||
|
|
EXEC SQL <span class="bold">DESCRIBE TABLE</span> :table_name <span class="bold">INTO</span> :sqlda;
|
||
|
|
|
||
|
|
.
|
||
|
|
.
|
||
|
|
.</pre><a id="idx2463" name="idx2463"></a><a id="idx2464" name="idx2464"></a><a id="idx2465" name="idx2465"></a><a id="idx2466" name="idx2466"></a>
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzdescrinput.htm">Previous Page</a> | <a href="rbafzmstdiscon.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>
|