259 lines
16 KiB
HTML
259 lines
16 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="CONNECT (Type 2), SQL statements,
|
||
|
|
CONNECT (Type 2) statement, in CONNECT (Type 2) statement, server-name, variable,
|
||
|
|
USER clause, authorization-name, USING clause, password, RESET clause,
|
||
|
|
SQL (Structured Query Language)" />
|
||
|
|
<title>CONNECT (Type 2)</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="con2t"></a>
|
||
|
|
<h2 id="con2t"><a href="rbafzmst02.htm#ToC_830">CONNECT (Type 2)</a></h2><a id="idx1577" name="idx1577"></a><a id="idx1578" name="idx1578"></a>
|
||
|
|
<a name="aon2t"></a>
|
||
|
|
<p id="aon2t">The CONNECT (Type 2) statement connects an activation group
|
||
|
|
within an application process to the identified application server using the rules
|
||
|
|
for application directed distributed unit of work. This server is then the
|
||
|
|
current server for the activation group. This type of CONNECT statement is
|
||
|
|
used if RDBCNNMTH(*DUW) was specified on the CRTSQLxxx command. Differences
|
||
|
|
between the two types of statements are described in <a href="rbafzmstdiftab2.htm#rcncdif">CONNECT (Type 1) and CONNECT (Type 2) differences</a>. Refer to <a href="rbafzmstch2drda.htm#adduwmg">Application-directed distributed unit of work</a> for more information
|
||
|
|
about connection states.</p>
|
||
|
|
<a name="wq1165"></a>
|
||
|
|
<h3 id="wq1165"><a href="rbafzmst02.htm#ToC_831">Invocation</a></h3>
|
||
|
|
<p>This statement can only be embedded in an application program or issued
|
||
|
|
interactively. It is an executable statement that cannot be dynamically prepared.
|
||
|
|
It must not be specified in Java™ or REXX.</p>
|
||
|
|
<p>CONNECT is not allowed in a trigger, a function, or a procedure if the
|
||
|
|
procedure is called on a remote application server.</p>
|
||
|
|
<a name="wq1166"></a>
|
||
|
|
<h3 id="wq1166"><a href="rbafzmst02.htm#ToC_832">Authorization</a></h3>
|
||
|
|
<p>The privileges held by the authorization ID of the statement must include
|
||
|
|
communications-level security. (See the section about security in the <a href="../ddp/rbal1kickoff.htm">Distributed Database Programming</a> book.)</p>
|
||
|
|
<p>If the application server is DB2 UDB for iSeries, the profile ID of the person issuing the statement
|
||
|
|
must also be a valid user profile on the application server system, UNLESS:</p>
|
||
|
|
<ul>
|
||
|
|
<li>USER is specified. If USER is specified, the USER clause must specify
|
||
|
|
a valid user profile on the application server system.</li>
|
||
|
|
<li>TCP/IP is used with a server authorization entry for the application server. If
|
||
|
|
this is the case, the server authorization entry must specify a valid user
|
||
|
|
profile on the application server system.</li></ul>
|
||
|
|
<a name="wq1167"></a>
|
||
|
|
<h3 id="wq1167"><a href="rbafzmst02.htm#ToC_833">Syntax</a></h3>
|
||
|
|
<a href="rbafzmstcon2t.htm#synscnct2"><img src="c.gif" alt="Click to skip syntax diagram" /></a>
|
||
|
|
<pre class="cgraphic"><span><img src="c.gif" alt="Read syntax diagram" longdesc="rbafzmstsyn267.htm"
|
||
|
|
border="0" /></span><a href="#skipsyn-266"><img src="c.gif" alt="Skip visual syntax diagram"
|
||
|
|
border="0" /></a>>>-CONNECT--+----------------------------------------+---------><
|
||
|
|
+-TO--+-<span class="italic">server-name</span>-+--+---------------+-+
|
||
|
|
| '-<span><span class="italic">variable</span></span>----' '-<span class="italic">authorization</span>-' |
|
||
|
|
'-RESET----------------------------------'
|
||
|
|
|
||
|
|
authorization:
|
||
|
|
|
||
|
|
|--USER--+-<span class="italic">authorization-name</span>-+--USING--+-<span class="italic">password</span>-+------------|
|
||
|
|
'-<span><span class="italic">variable</span></span>-----------' '-<span><span class="italic">variable</span></span>-'
|
||
|
|
|
||
|
|
</pre>
|
||
|
|
<a name="skipsyn-266" id="skipsyn-266"></a>
|
||
|
|
<a name="synscnct2"></a>
|
||
|
|
<h3 id="synscnct2"><a href="rbafzmst02.htm#ToC_834">Description</a></h3>
|
||
|
|
<dl class="parml">
|
||
|
|
<dt class="bold">TO <var class="pv">server-name</var><span class="base"> or </span> <var class="pv">variable</var> </dt><a id="idx1579" name="idx1579"></a><a id="idx1580" name="idx1580"></a>
|
||
|
|
<dd>Identifies the application server by the specified server name or the server
|
||
|
|
name contained in the variable. If a variable is specified:
|
||
|
|
<ul>
|
||
|
|
<li>It must be a character-string variable.</li>
|
||
|
|
<li>It must not be followed by an indicator variable</li>
|
||
|
|
<li>The server name must be left-justified within the variable and must conform
|
||
|
|
to the rules for forming an ordinary identifier</li>
|
||
|
|
<li>If the length of the server name is less than the length of the variable,
|
||
|
|
it must be padded on the right with blanks.</li>
|
||
|
|
<li>The value of the server name must not contain lowercase characters.</li></ul>
|
||
|
|
<p>When the CONNECT statement is executed, the specified server name
|
||
|
|
or the server name contained in the variable must identify an application server described
|
||
|
|
in the local directory.</p>
|
||
|
|
<p>Let S denote the specified server name or the
|
||
|
|
server name contained in the variable. S must not identify an existing connection
|
||
|
|
of the application process.</p>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">USER <var class="pv">authorization-name or variable</var> </dt><a id="idx1581" name="idx1581"></a><a id="idx1582" name="idx1582"></a><a id="idx1583" name="idx1583"></a>
|
||
|
|
<dd>Identifies the authorization name that will be used to connect to the
|
||
|
|
application server.
|
||
|
|
<p>If a <var class="pv">variable</var> is specified, </p>
|
||
|
|
<ul>
|
||
|
|
<li>It must be a character string variable.</li>
|
||
|
|
<li>It must not be followed by an indicator variable. The authorization name
|
||
|
|
must be left-justified within the variable and must conform to the rules of
|
||
|
|
forming an authorization name.</li>
|
||
|
|
<li>If the length of the authorization name is less than the length of the
|
||
|
|
variable, it must be padded on the right with blanks.</li></ul>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">USING <var class="pv">password or variable</var> </dt><a id="idx1584" name="idx1584"></a><a id="idx1585" name="idx1585"></a><a id="idx1586" name="idx1586"></a>
|
||
|
|
<dd>Identifies the password that will be used to connect to
|
||
|
|
the application server.
|
||
|
|
<p>If password is specified as a literal, it must be
|
||
|
|
a character string. The maximum length is 128 characters. It must be left
|
||
|
|
justified. The literal form of the password is not allowed in static SQL or
|
||
|
|
REXX.</p>
|
||
|
|
<p>If a <var class="pv">variable</var> is specified, </p>
|
||
|
|
<ul>
|
||
|
|
<li>It must be a character-string variable.</li>
|
||
|
|
<li>It must not be followed by an indicator variable.</li>
|
||
|
|
<li>The password must be left-justified within the variable.</li>
|
||
|
|
<li>If the length of the password is less than that of the variable, it must
|
||
|
|
be padded on the right with blanks.</li></ul>
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">RESET </dt><a id="idx1587" name="idx1587"></a>
|
||
|
|
<dd>CONNECT RESET is equivalent to CONNECT TO <span class="italic">x</span> where <span class="italic">x</span> is the local server name.
|
||
|
|
</dd>
|
||
|
|
<dt class="bold">CONNECT <span class="base">with no operand</span></dt>
|
||
|
|
<dd>This form of the CONNECT statement returns information about the current
|
||
|
|
server and has no effect on connection states, open cursors, prepared statements,
|
||
|
|
or locks. The connection information is returned in the connection information
|
||
|
|
items in the SQL Diagnostics Area (or the SQLCA).
|
||
|
|
<p>In addition, the DB2_CONNECTION_STATUS
|
||
|
|
connection information item in the SQL Diagnostics Area (or the SQLERRD(3)
|
||
|
|
field of the SQLCA) will indicate the status of connection for this unit of
|
||
|
|
work. It will have one of the following values: </p>
|
||
|
|
<ul>
|
||
|
|
<li> 1 - Commitable updates can be performed on the connection for this unit
|
||
|
|
of work.</li>
|
||
|
|
<li> 2 - No commitable updates can be performed on the connection for this
|
||
|
|
unit of work.</li></ul>
|
||
|
|
</dd>
|
||
|
|
</dl>
|
||
|
|
<a name="wq1168"></a>
|
||
|
|
<h3 id="wq1168"><a href="rbafzmst02.htm#ToC_835">Notes</a></h3>
|
||
|
|
<p><span class="bold">Successful connection:</span> If the CONNECT statement is
|
||
|
|
successful: </p>
|
||
|
|
<ul>
|
||
|
|
<li>A connection to application server S is created and placed in the current and
|
||
|
|
held states. The previous connection, if any, is placed in the dormant state.</li>
|
||
|
|
<li>S is placed in the CURRENT SERVER special register.</li>
|
||
|
|
<li>Information about the application server is placed in the <var class="pv">connection-information-items</var> in the SQL Diagnostics Area.</li>
|
||
|
|
<li>Information about application server S is also placed in the SQLERRP and SQLERRD(4)
|
||
|
|
fields of the SQLCA. If the application server is an IBM® relational database product, the information
|
||
|
|
in the SQLERRP field has the form <var class="pv">pppvvrrm</var>, where:
|
||
|
|
<ul>
|
||
|
|
<li><var class="pv">ppp</var> identifies the product as follows:
|
||
|
|
<ul class="simple">
|
||
|
|
<li>ARI for DB2® for VM and VSE</li>
|
||
|
|
<li>DSN for DB2 UDB for z/OS</li>
|
||
|
|
<li>QSQ for DB2 UDB for iSeries</li>
|
||
|
|
<li>SQL for all other DB2 UDB products</li></ul></li>
|
||
|
|
<li><var class="pv">vv</var> is a two-digit version identifier such as '07'</li>
|
||
|
|
<li><var class="pv">rr</var> is a two-digit release identifier such as '01'</li>
|
||
|
|
<li><var class="pv">m</var> is a one-digit modification level such as '0'</li></ul>
|
||
|
|
<p>For example, if the application server is Version 7 of DB2 UDB for z/OS, the value
|
||
|
|
of SQLERRP is 'DSN07010'.</p>
|
||
|
|
<p>The SQLERRD(4) field of the SQLCA
|
||
|
|
contains values indicating whether application server S allows commitable updates
|
||
|
|
to be performed. Following is a list of values and their meanings for the
|
||
|
|
SQLERRD(4) field of the SQLCA on the CONNECT: </p>
|
||
|
|
<ul>
|
||
|
|
<li> 1 - commitable updates can be performed. Conversation is unprotected. <sup class="fn"><a href="rbafzmstconj1.htm#fn001">63</a></sup></li>
|
||
|
|
<li> 2 - No commitable updates can be performed. Conversation is unprotected.</li>
|
||
|
|
<li> 3 - It is unknown if commitable updates can be performed. Conversation
|
||
|
|
is protected.</li>
|
||
|
|
<li> 4 - It is unknown if commitable updates can be performed. Conversation
|
||
|
|
is unprotected.</li>
|
||
|
|
<li> 5 - It is unknown if commitable updates can be performed. The connection
|
||
|
|
is either a local connection or a connection to an application requester driver
|
||
|
|
program.</li></ul></li>
|
||
|
|
<li>Additional information about the connection is placed in the SQLERRMC
|
||
|
|
field of the SQLCA. Refer to <a href="rbafzmstsqlcca.htm#sqlcca">Appendix C. SQLCA (SQL communication area)</a>.</li></ul>
|
||
|
|
<p><span class="bold">Unsuccessful connection:</span> If the CONNECT statement
|
||
|
|
is unsuccessful, the connection state of the activation group and the states
|
||
|
|
of its connections are unchanged.</p>
|
||
|
|
<p><span class="bold">Implicit connect:</span> Implicit connect will always send
|
||
|
|
the <var class="pv">authorization-name</var> of the application requester job and will
|
||
|
|
not send passwords. If the <var class="pv">authorization-name</var> of the application server job
|
||
|
|
is different or if a password must be sent, an explicit connect statement
|
||
|
|
must be used.</p>
|
||
|
|
<p>When TCP/IP is used for connecting to an RDB, an implicit connect is not
|
||
|
|
bound by the above restrictions. Use of the ADDSVRAUTE and other -SVRAUTE
|
||
|
|
commands allows one to specify, for a given user under which the implicit
|
||
|
|
(or explicit) CONNECT is done, the remote authorization-name and password
|
||
|
|
to be used in connecting to a given RDB.</p>
|
||
|
|
<p>In order for the password to be stored with the ADDSVRAUTE or CHGSVRAUTE
|
||
|
|
command, the QRETSVRSEC system value must be set to '1' rather than the default
|
||
|
|
of '0'. When using these commands for DRDA® connection, it is very important to realize
|
||
|
|
that the value of the RDB name entered into the SERVER parameter must be in
|
||
|
|
UPPER CASE. For more information, see Example 2 under Type 2 CONNECT.</p>
|
||
|
|
<p>For more information about implicit connect, refer to the <a href="../sqlp/rbafykickoff.htm">SQL Programming</a> book. Once a connection to a relational database for a
|
||
|
|
user profile is established, the password, if specified, may not be validated
|
||
|
|
again on subsequent connections to the same relational database with the same
|
||
|
|
user profile. Revalidation of the password depends on if the conversation
|
||
|
|
is still active. See the <a href="../ddp/rbal1kickoff.htm">Distributed Database Programming</a> book for more details.</p>
|
||
|
|
<p><span class="bold">SET SESSION AUTHORIZATION:</span> If a SET SESSION
|
||
|
|
AUTHORIZATION statement has been executed in the thread, a CONNECT to the
|
||
|
|
local server will fail unless prior to the connect statement, the SYSTEM_USER
|
||
|
|
value is the same as SESSION_USER.</p>
|
||
|
|
<p>This incudes an implicit connect due to invoking a program that
|
||
|
|
specifies ACTGRP(*NEW).</p>
|
||
|
|
<a name="wq1169"></a>
|
||
|
|
<h3 id="wq1169"><a href="rbafzmst02.htm#ToC_836">Examples</a></h3>
|
||
|
|
<p><span class="italic">Example 1:</span> Execute SQL statements at TOROLAB and
|
||
|
|
SVLLAB. The first CONNECT statement creates the TOROLAB connection and the
|
||
|
|
second CONNECT statement places it in the dormant state. </p>
|
||
|
|
<pre class="xmp"> EXEC SQL <span class="bold">CONNECT TO</span> TOROLAB;
|
||
|
|
|
||
|
|
(execute statements referencing objects at TOROLAB)
|
||
|
|
|
||
|
|
EXEC SQL <span class="bold">CONNECT TO</span> SVLLAB;
|
||
|
|
|
||
|
|
(execute statements referencing objects at SVLLAB)</pre>
|
||
|
|
<p><span class="italic">Example 2:</span> Connect to a remote server specifying
|
||
|
|
a userid and password, perform work for the user and then connect as another
|
||
|
|
user to perform further work. </p>
|
||
|
|
<pre class="xmp"> EXEC SQL <span class="bold">CONNECT TO</span> SVLLAB <span class="bold">USER</span> :AUTHID <span class="bold">USING</span> :PASSWORD;
|
||
|
|
|
||
|
|
(execute SQL statements accessing data on the server)
|
||
|
|
|
||
|
|
EXEC SQL COMMIT;
|
||
|
|
|
||
|
|
(set AUTHID and PASSWORD to new values)
|
||
|
|
|
||
|
|
EXEC SQL <span class="bold">CONNECT TO</span> SVLLAB <span class="bold">USER</span> :AUTHID <span class="bold">USING</span> :PASSWORD;
|
||
|
|
|
||
|
|
(execute SQL statements accessing data on the server)</pre>
|
||
|
|
<p><span class="italic">Example 3:</span> User JOE wants to connect to TOROLAB3
|
||
|
|
and execute SQL statements under the user ID ANONYMOUS which has a password
|
||
|
|
of SHIBBOLETH. The RDB directory entry for TOROLAB3 specifies *IP for the
|
||
|
|
connection type.</p>
|
||
|
|
<p>Before running the application, some setup must be done.</p>
|
||
|
|
<p>This command will be required to allow server security information to be
|
||
|
|
retained in i5/OS, if it has not been previously run:</p>
|
||
|
|
<pre class="xmp"> CHGSYSVAL SYSVAL(QRETSVRSEC) VALUE('1')</pre><p class="indatacontent">This command adds the required server authorization entry:</p>
|
||
|
|
<pre class="xmp"> ADDSVRAUTE USRPRF(JOE) SERVER(TOROLAB3) USRID(ANONYMOUS) +
|
||
|
|
PASSWORD(SHIBBOLETH)</pre><p class="indatacontent">This statement, run under JOE's user
|
||
|
|
profile, will now make the desired connection:</p>
|
||
|
|
<pre class="xmp"> EXEC SQL <span class="bold">CONNECT TO</span> TOROLAB3;
|
||
|
|
(execute statements referencing objects at TOROLAB3)</pre>
|
||
|
|
<p><a id="idx1588" name="idx1588"></a><a id="idx1589" name="idx1589"></a><a id="idx1590" name="idx1590"></a></p>
|
||
|
|
<hr /><br />
|
||
|
|
[ <a href="#Top_Of_Page">Top of Page</a> | <a href="rbafzmstconj1.htm">Previous Page</a> | <a href="rbafzmsthcalias.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>
|