ibm-information-center/dist/eclipse/plugins/i5OS.ic.cli_5.4.0.1/rzadpfnsconx.htm

373 lines
22 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en-us" xml:lang="en-us">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="security" content="public" />
<meta name="Robots" content="index,follow" />
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
<meta name="DC.Type" content="reference" />
<meta name="DC.Title" content="SQLSetConnectAttr - Set a connection attribute" />
<meta name="DC.Relation" scheme="URI" content="rzadphdapi.htm" />
<meta name="DC.Relation" scheme="URI" content="rzadpfnsstmo.htm" />
<meta name="DC.Relation" scheme="URI" content="rzadpfnscono.htm" />
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="rzadpfnsconx" />
<meta name="DC.Language" content="en-us" />
<!-- All rights reserved. Licensed Materials Property of IBM -->
<!-- US Government Users Restricted Rights -->
<!-- Use, duplication or disclosure restricted by -->
<!-- GSA ADP Schedule Contract with IBM Corp. -->
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
<link rel="stylesheet" type="text/css" href="./ic.css" />
<title>SQLSetConnectAttr</title>
</head>
<body id="rzadpfnsconx"><a name="rzadpfnsconx"><!-- --></a>
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
<h1 class="topictitle1">SQLSetConnectAttr - Set a connection attribute</h1>
<div><div class="section"></div>
<div class="section"><h4 class="sectiontitle">Purpose</h4> <p><samp class="codeph">SQLSetConnectAttr()</samp> sets
connection attributes for a particular connection.</p>
</div>
<div class="section"><h4 class="sectiontitle">Syntax</h4><pre>SQLRETURN SQLSetConnectAttr (SQLHDBC hdbc,
SQLINTEGER fAttr,
SQLPOINTER vParam,
SQLINTEGER sLen);</pre>
</div>
<div class="section"><h4 class="sectiontitle">Function arguments</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="all"><caption>Table 1. SQLSetConnectAttr arguments</caption><thead align="left"><tr><th align="left" valign="top" width="20%" id="d0e54">Data type</th>
<th align="left" valign="top" width="20%" id="d0e56">Argument</th>
<th align="left" valign="top" width="20%" id="d0e58">Use</th>
<th align="left" valign="top" width="40%" id="d0e60">Description</th>
</tr>
</thead>
<tbody><tr><td align="left" valign="top" width="20%" headers="d0e54 ">SQLHDBC</td>
<td align="left" valign="top" width="20%" headers="d0e56 "><em>hdbc</em></td>
<td align="left" valign="top" width="20%" headers="d0e58 ">Input</td>
<td align="left" valign="top" width="40%" headers="d0e60 ">Connection handle.</td>
</tr>
<tr><td align="left" valign="top" width="20%" headers="d0e54 ">SQLINTEGER</td>
<td align="left" valign="top" width="20%" headers="d0e56 "><em>fAttr</em></td>
<td align="left" valign="top" width="20%" headers="d0e58 ">Input</td>
<td align="left" valign="top" width="40%" headers="d0e60 ">Connect attribute to set, refer to <a href="#rzadpfnsconx__tbcono">Table 2</a> for more information.</td>
</tr>
<tr><td align="left" valign="top" width="20%" headers="d0e54 ">SQLPOINTER</td>
<td align="left" valign="top" width="20%" headers="d0e56 "><em>vParam</em></td>
<td align="left" valign="top" width="20%" headers="d0e58 ">Input</td>
<td align="left" valign="top" width="40%" headers="d0e60 ">Value associated with <em>fAttr</em>. Depending
on the option, this can be a pointer to a 32-bit integer value, or a character
string.</td>
</tr>
<tr><td align="left" valign="top" width="20%" headers="d0e54 ">SQLINTEGER</td>
<td align="left" valign="top" width="20%" headers="d0e56 "><em>sLen</em></td>
<td align="left" valign="top" width="20%" headers="d0e58 ">Input</td>
<td align="left" valign="top" width="40%" headers="d0e60 ">Length of input value, if it is a character
string; otherwise, unused.</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="section"><h4 class="sectiontitle">Usage</h4><p>All connection and statement options set through
the <samp class="codeph">SQLSetConnectAttr()</samp> persist until <samp class="codeph">SQLFreeConnect()</samp> is
called or the next <samp class="codeph">SQLSetConnectAttr()</samp> call.</p>
<div class="p">The format
of information set through <em>vParam</em> depends on the specified <em>fAttr</em>.
The option information can be either a 32-bit integer or a pointer to a null-terminated
character string.
<div class="tablenoborder"><img src="./delta.gif" alt="Start of change" /><a name="rzadpfnsconx__tbcono"><!-- --></a><table cellpadding="4" cellspacing="0" summary="" id="rzadpfnsconx__tbcono" width="100%" frame="hsides" border="1" rules="all"><caption>Table 2. Connect options</caption><thead align="left"><tr><th align="left" valign="top" width="50%" id="d0e140"><em>fAttr</em></th>
<th align="left" valign="top" width="50%" id="d0e143">Contents</th>
</tr>
</thead>
<tbody><tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_2ND_LEVEL_TEXT</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_TRUE Error text obtained by calling <samp class="codeph">SQLError()</samp> contains
the complete text description of the error.</li>
<li>SQL_FALSE Error text obtained by calling <samp class="codeph">SQLError()</samp> contains
the first-level description of the error only. This is the default.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_AUTOCOMMIT</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit value that sets the commit behavior
for the connection. The following are possible values: <ul><li>SQL_TRUE Each SQL statement is automatically committed as it is processed.</li>
<li>SQL_FALSE The SQL statements are not automatically committed. If running
with commitment control, changes must be explicitly committed or rolled back
using either <samp class="codeph">SQLEndTran()</samp> or <samp class="codeph">SQLTransact()</samp>.
This is the default.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 "><p>SQL_ATTR_COMMIT<br />
or<br />
SQL_TXN_ISOLATION</p>
</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit value that sets the transaction-isolation
level for the current connection referenced by <em>hdbc</em>. The following
values are accepted by DB2<sup>®</sup> UDB CLI, but each server might only support some
of these isolation levels: <ul><li>SQL_TXN_NO_COMMIT Commitment control is not used.</li>
<li>SQL_TXN_READ_UNCOMMITTED Dirty reads, nonrepeatable reads, and phantoms
are possible.</li>
<li>SQL_TXN_READ_COMMITTED Dirty reads are not possible. Non-repeatable
reads and phantoms are possible.</li>
<li>SQL_TXN_REPEATABLE_READ Dirty reads and nonrepeatable reads are not
possible. Phantoms are possible.</li>
<li>SQL_TXN_SERIALIZABLE Transactions are serializable. Dirty reads, non-repeatable
reads, and phantoms are not possible.</li>
</ul>
<div class="p">In IBM<sup>®</sup> terminology,
<ul><li>SQL_TXN_READ_UNCOMMITTED is uncommitted read</li>
<li>SQL_TXN_READ_COMMITTED is cursor stability</li>
<li>SQL_TXN_REPEATABLE_READ is read stability</li>
<li>SQL_TXN_SERIALIZABLE is repeatable read</li>
</ul>
</div>
<p>For a detailed explanation of isolation levels, refer to the IBM DB2 SQL
Reference.</p>
<p>The SQL_ATTR_COMMIT attribute should be set before the <samp class="codeph">SQLConnect()</samp>.
If the value is changed after the connection has been established, and the
connection is to a remote data source, the change does not take effect until
the next successful <samp class="codeph">SQLConnect()</samp> for the connection handle.</p>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_DATE_FMT</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_FMT_ISO The International Organization for Standardization (ISO)
date format yyyy-mm-dd is used. This is the default.</li>
<li>SQL_FMT_USA The United States date format mm/dd/yyyy is used.</li>
<li>SQL_FMT_EUR The European date format dd.mm.yyyy is used.</li>
<li>SQL_FMT_JIS The Japanese Industrial Standard date format yyyy-mm-dd
is used.</li>
<li>SQL_FMT_MDY The date format mm/dd/yy is used.</li>
<li>SQL_FMT_DMY The date format dd/mm/yy is used.</li>
<li>SQL_FMT_YMD The date format yy/mm/dd is used.</li>
<li>SQL_FMT_JUL The Julian date format yy/ddd is used.</li>
<li>SQL_FMT_JOB The job default is used.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_DATE_SEP</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_SEP_SLASH A slash ( / ) is used as the date separator. This is the
default.</li>
<li>SQL_SEP_DASH A dash ( - ) is used as the date separator.</li>
<li>SQL_SEP_PERIOD A period ( . ) is used as the date separator.</li>
<li>SQL_SEP_COMMA A comma ( , ) is used as the date separator.</li>
<li>SQL_SEP_BLANK A blank is used as the date separator.</li>
<li>SQL_SEP_JOB The job default is used.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_DBC_DEFAULT_LIB</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A character value that indicates the default
library that is used for resolving unqualified file references. This is not
valid if the connection is using system naming mode.</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_DBC_SYS_NAMING</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_TRUE DB2 UDB
CLI uses the iSeries™ system
naming mode. Files are qualified using the slash (/) delimiter. Unqualified
files are resolved using the library list for the job.</li>
<li>SQL_FALSE DB2 UDB
CLI uses the default naming mode, which is SQL naming. Files are qualified
using the period (.) delimiter. Unqualified files are resolved using either
the default library or the current user ID.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_DECIMAL_SEP</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_SEP_PERIOD A period ( . ) is used as the decimal separator. This
is the default.</li>
<li>SQL_SEP_COMMA A comma ( , ) is used as the decimal separator.</li>
<li>SQL_SEP_JOB The job default is used.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_EXTENDED_COL_INFO</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_TRUE Statement handles allocated against this connection handle
can be used on <samp class="codeph">SQLColAttributes()</samp> to retrieve extended column
information, such as base table, base schema, base column, and label.</li>
<li>SQL_FALSE Statement handles allocated against this connection handle
cannot be used on the <samp class="codeph">SQLColAttributes()</samp> function to retrieve
extended column information. This is the default.</li>
</ul>
</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 ">SQL_ATTR_HEX_LITERALS</td>
<td valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_HEX_IS_CHAR Hexadecimal constants are treated as character data.
This is the default.</li>
<li>SQL_HEX_IS_BINARY Hexadecimal constants are treated as binary data.</li>
</ul>
</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 ">SQL_ATTR_MAX_PRECISION</td>
<td valign="top" width="50%" headers="d0e143 ">An integer constant that is the maximum precision (length)
that should be returned for the result data types. The value can be 31 or
63.</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 ">SQL_ATTR_MAX_SCALE</td>
<td valign="top" width="50%" headers="d0e143 ">An integer constant that is the maximum scale (number
of decimal positions to the right of the decimal point) that should be returned
for the result data types. The value can range from 0 to the maximum precision.</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 ">SQL_ATTR_MIN_DIVIDE_SCALE</td>
<td valign="top" width="50%" headers="d0e143 ">Specify the minimum divide scale (number of decimal
positions to the right of the decimal point) that should be returned for the
result data types resulting from a divide operation. The value can range from
0 to 9, not to exceed the maximum scale. If 0 is specified, minimum divide
scale is not used.</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 "><p><img src="./delta.gif" alt="Start of change" />SQL_ATTR_QUERY_OPTIMIZE_GOAL<img src="./deltaend.gif" alt="End of change" /></p>
</td>
<td valign="top" width="50%" headers="d0e143 ">A 32-bit integer value that tells the optimizer to behave
in a specified way when processing a query:<ul><li>SQL_FIRST_IO All queries are optimized with the goal of returning the
first page of output as fast as possible. This goal works well when the output
is controlled by a user who is most likely to cancel the query after viewing
the first page of output data. Queries coded with an OPTIMIZE FOR nnn ROWS
clause honor the goal specified by the clause.</li>
<li>SQL_ALL_IO All queries are optimized with the goal of running the entire
query to completion in the shortest amount of elapsed time. This is a good
option when the output of a query is being written to a file or report, or
the interface is queuing the output data. Queries coded with an OPTIMIZE FOR
nnn ROWS clause honor the goal specified by the clause. This is the default.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_TIME_FMT</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_FMT_ISO The International Organization for Standardization (ISO)
time format hh.mm.ss is used. This is the default.</li>
<li>SQL_FMT_USA The United States time format hh:mmxx is used, where xx
is AM or PM.</li>
<li>SQL_FMT_EUR The European time format hh.mm.ss is used.</li>
<li>SQL_FMT_JIS The Japanese Industrial Standard time format hh:mm:ss is
used.</li>
<li>SQL_FMT_HMS The hh:mm:ss format is used.</li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_ATTR_TIME_SEP</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_SEP_COLON A colon ( : ) is used as the time separator. This is the
default.</li>
<li>SQL_SEP_PERIOD A period ( . ) is used as the time separator.</li>
<li>SQL_SEP_COMMA A comma ( , ) is used as the time separator.</li>
<li>SQL_SEP_BLANK A blank is used as the time separator.</li>
<li>SQL_SEP_JOB The job default is used.</li>
</ul>
</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 "><p><img src="./delta.gif" alt="Start of change" />SQL_ATTR_TXN_EXTERNAL<img src="./deltaend.gif" alt="End of change" /></p>
</td>
<td valign="top" width="50%" headers="d0e143 "><p><img src="./delta.gif" alt="Start of change" />A 32-bit integer value that must
be SQL_TRUE to enable the use of XA transaction setting in the CLI connection.
SQL_ATTR_TXN_EXTERNAL must be set to SQL_TRUE to use the XA transaction options
by the SQL_ATTR_TXN_INFO connection attribute.<img src="./deltaend.gif" alt="End of change" /></p>
<p><img src="./delta.gif" alt="Start of change" />The default
is SQL_FALSE, which is not to enable XA transaction support. However, as soon
as transaction support is enabled for the connection, it cannot be disabled.
(Attempting to set SQL_ATTR_TXN_EXTERNAL to SQL_FALSE results in a CLI error.)<img src="./deltaend.gif" alt="End of change" /></p>
<p><img src="./delta.gif" alt="Start of change" />Further information as well as an example of use of the SQL_ATTR_TXN_EXTERNAL
connection attribute can be found in <a href="rzadpexusing.htm#rzadpexusing">Example: Use the CLI XA transaction connection attributes</a>. <img src="./deltaend.gif" alt="End of change" /></p>
</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 "><p><img src="./delta.gif" alt="Start of change" />SQL_ATTR_TXN_INFO<img src="./deltaend.gif" alt="End of change" /></p>
</td>
<td valign="top" width="50%" headers="d0e143 ">A 32-bit integer value:<ul><li>SQL_TXN_CREATE Create and start a transaction. This parallels the xa_start(TMNOFLAGS)
XA option.</li>
<li> SQL_TXN_END End the specified transaction. The user is responsible
to commit or roll back the work. This parallels the xa_end(TMSUCCESS) XA option.</li>
<li>SQL_TXN_END_FAIL End the specified transaction and mark the transaction
as rollback required. This parallels the xa_end(TMFAIL) XA option.</li>
<li>SQL_TXN_CLEAR Suspend the transaction to work on a different transaction.
This parallels the xa_end(TMSUSPEND) XA option. </li>
<li>SQL_TXN_FIND Find, retrieve, and use the nonsuspended transaction specified
in vParam for the current connection. This allows work to continue on the
open cursors for the previously nonsuspended transaction. This parallels the
xa_start(TMJOIN) XA option. </li>
<li>SQL_TXN_RESUME Find, retrieve, and use the suspended transaction specified
in vParam for the current connection. This allows work to continue on the
open cursors for the previously suspended transaction. This parallels the
xa_start(TMRESUME) XA option. </li>
</ul>
<p>Use of this connection attribute requires the user to be running in
server mode. Keep in mind, a user cannot toggle between a non-server mode
and server mode environment. </p>
<p>The input argument vParam must point to
a TXN_STRUCT object. This structure can be found in the header file QSYSINC/h.SQLCLI.</p>
<p>The
xa_info argument for the xa_open XA API must include the THDCTL=C keyword
and value when using CLI with XA transactions. </p>
<p>See <a href="../rzakj/rzakjxatransaction.htm">XA transaction support
for commitment control</a> in the Commitment control topic for more information
about XA transactions.</p>
<p>See <a href="../apis/unix12.htm">XA API</a> for more information.</p>
<p>See <a href="rzadpexusing.htm#rzadpexusing">Example: Use the CLI XA transaction connection attributes</a> for
more information and an example that shows how you can use the SQL_ATTR_TXN_INFO
connection attribute.</p>
<p>When running XA calls through CLI, the return
codes from CLI reflect the XA return code specifications. These values can
be found in the XA specification documentation, as well as in the XA.h include
file. Note that the return code values that are listed in the XA include file
take precedence over the CLI return code values when calling XA through this
connection attribute. </p>
</td>
</tr>
<tr><td valign="top" width="50%" headers="d0e140 ">SQL_ATTR_UCS2</td>
<td valign="top" width="50%" headers="d0e143 ">A 32-bit integer value: <ul><li>SQL_TRUE When using statement handles allocated against this connection
handle for SQLPrepare() and SQLExecDirect(), the statement text is passed
in the UCS-2 (Unicode) coded character set identifier (CCSID).</li>
<li>SQL_FALSE When using statement handles allocated against this connection
handle for SQLPrepare() and SQLExecDirect(), the statement text is passed
in the job's CCSID. This is the default. </li>
</ul>
</td>
</tr>
<tr><td align="left" valign="top" width="50%" headers="d0e140 ">SQL_SAVEPOINT_NAME</td>
<td align="left" valign="top" width="50%" headers="d0e143 ">A character value that indicates the savepoint
name to be used by <samp class="codeph">SQLEndTran()</samp> on the functions SQL_SAVEPOINT_NAME_ROLLBACK
or SQL_SAVEPOINT_NAME_RELEASE.</td>
</tr>
</tbody>
</table>
<img src="./deltaend.gif" alt="End of change" /></div>
</div>
</div>
<div class="section"><h4 class="sectiontitle">Return codes</h4><ul><li>SQL_SUCCESS</li>
<li>SQL_SUCCESS_WITH_INFO</li>
<li>SQL_ERROR</li>
<li>SQL_INVALID_HANDLE</li>
</ul>
</div>
<div class="section"><h4 class="sectiontitle">Diagnostics</h4>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" width="100%" frame="hsides" border="1" rules="rows"><caption>Table 3. SQLSetConnectAttr SQLSTATEs</caption><thead align="left"><tr><th align="left" valign="top" width="25%" id="d0e496">SQLSTATE</th>
<th align="left" valign="top" width="25%" id="d0e498">Description</th>
<th align="left" valign="top" width="50%" id="d0e500">Explanation</th>
</tr>
</thead>
<tbody><tr><td align="left" valign="top" width="25%" headers="d0e496 "><strong>HY</strong>001</td>
<td align="left" valign="top" width="25%" headers="d0e498 ">Memory allocation failure</td>
<td align="left" valign="top" width="50%" headers="d0e500 ">The driver is unable to allocate memory required
to support the processing or completion of the function.</td>
</tr>
<tr><td align="left" valign="top" width="25%" headers="d0e496 "><strong>HY</strong>009</td>
<td align="left" valign="top" width="25%" headers="d0e498 ">Argument value that is not valid</td>
<td align="left" valign="top" width="50%" headers="d0e500 ">Given the <em>fAttr</em> value, a value that
is not valid is specified for the argument <em>vParam</em>. <p>An <em>fAttr</em> that
is not valid value is specified.</p>
</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzadphdapi.htm" title="This topic provides a description of each CLI function.">DB2 UDB CLI functions</a></div>
</div>
<div class="relref"><strong>Related reference</strong><br />
<div><a href="rzadpfnsstmo.htm">SQLSetStmtOption - Set statement option</a></div>
<div><a href="rzadpfnscono.htm">SQLSetConnectOption - Set connection option</a></div>
</div>
</div>
</body>
</html>