The
CONNECT (Type 1) statement connects an application process to the
identified application server according to the rules for remote unit
of work.
An application process can only be connected to one application
server at a time. This is called the current server. A default
application server may be established when the application requester
is initialized. If implicit connect is available and an application
process is started, it is implicitly connected to the default application
server. The application process can explicitly connect to a different
application server by issuing a CONNECT statement. A connection
lasts until a CONNECT RESET statement or a DISCONNECT statement is
issued or until another CONNECT statement changes the application
server.
Invocation
Although an interactive SQL
facility might provide an interface that gives the appearance of interactive
execution, this statement can only be embedded within an application
program. It is an executable statement that cannot be dynamically
prepared. When invoked using the command line processor, additional
options can be specified.
For more information, refer to Using command line SQL statements and XQuery statements
.
Authorization
CONNECT
processing goes through two levels of access control. Both levels
must be satisfied for the connection to be successful.
The
first level of access control is authentication, where the user ID
associated with the connection must be successfully authenticated
according to the authentication method set up for the server. At
successful authentication, a database authorization ID is derived
from the connection user ID according to the authentication plug-in
in effect for the server. This database authorization ID must then
pass the second level of access control for the connection, that is,
authorization. To do so, this authorization ID must hold at least
one of the following authorities:
- CONNECT authority
- SECADM authority
- DBADM authority
- SYSADM authority
- SYSCTRL authority
- SYSMAINT authority
- SYSMON authority
Note: For a partitioned database, the user and group definitions
must be identical across all database partitions.
Syntax
>>-CONNECT------------------------------------------------------>
>--+------------------------------------------------------------------+-><
+-TO--+-server-name---+--+----------------+--+-------------------+-+
| '-host-variable-' '-| lock-block |-' '-| authorization |-' |
+-RESET------------------------------------------------------------+
| (1) |
'-| authorization |------------------------------------------------'
authorization
|--USER--+-authorization-name-+--USING--+-password------+------->
'-host-variable------' '-host-variable-'
>--+-------------------------------------------+----------------|
'-NEW--+-password------+--CONFIRM--password-'
'-host-variable-'
lock-block
.-IN SHARE MODE---------------------------.
|--+-----------------------------------------+------------------|
'-IN EXCLUSIVE MODE--+------------------+-'
'-ON SINGLE MEMBER-'
Notes:
- This form is only valid if implicit connect is enabled.
Description
- CONNECT (with no operand)
- Returns information about the current server. The information
is returned in the SQLERRP field of the SQLCA as described in "Successful
Connection".
If a connection state exists,
the authorization ID and database alias are placed in the SQLERRMC
field of the SQLCA. If the authorization ID is longer than 8 bytes,
it will be truncated to 8 bytes, and the truncation will be flagged
in the SQLWARN0 and SQLWARN1 fields of the SQLCA, with 'W' and 'A',
respectively.
If no connection
exists and implicit connect is possible, then an attempt to make an
implicit connection is made. If implicit connect is not available,
this attempt results in an error (no existing connection). If no connection,
then the SQLERRMC field is blank.
The territory code and code
page of the application server are placed in the SQLERRMC field (as
they are with a successful CONNECT statement).
This
form of CONNECT:
- Does not require the application process to be in the connectable
state.
- If connected, does not change the connection state.
- If unconnected and implicit connect is available, a connection
to the default application server is made. In this case, the country
or region code and code page of the application server are placed
in the SQLERRMC field, like a successful CONNECT statement.
- If unconnected and implicit connect is not available, the application
process remains unconnected.
- Does not close cursors.
- TO server-name or host-variable
- Identifies the application server by the specified server-name or
a host-variable which contains the server-name.
If
a host-variable is specified, it must be a character
string variable with a length attribute that is not greater than 8,
and it must not include an indicator variable. The server-name that
is contained within the host-variable must be left-aligned
and must not be delimited by quotation marks.
Note that the server-name is
a database alias identifying the application server. It must be listed
in the application requester's local directory.
When the CONNECT
statement is executed, the application process must be in the
connectable state.
- Successful Connection
- If the CONNECT statement is successful:
- Unsuccessful Connection
- If the CONNECT statement is unsuccessful:
- The SQLERRP field of the SQLCA is set to the name of the module
at the application requester that detected the error. The first three
characters of the module name identify the product.
- If the CONNECT statement is unsuccessful because the
application process is not in the connectable state, the connection
state of the application process is unchanged.
- If the CONNECT statement is unsuccessful because the server-name is
not listed in the local directory, an error message (SQLSTATE 08001)
is issued and the connection state of the application process remains
unchanged:
- If the application requester was not connected to an application
server then the application process remains unconnected.
- If the application requester was already connected to an application
server, the application process remains connected to that application
server. Any further statements are executed at that application server.
- If the CONNECT statement is unsuccessful for any other
reason, the application process is placed into the unconnected state.
- IN SHARE MODE
- Allows other concurrent connections to the database and prevents
other users from connecting to the database in exclusive mode.
- IN EXCLUSIVE MODE
- Prevents concurrent application
processes from executing any operations at the application server, unless they have the same
authorization ID as the user holding the exclusive lock. This
option is not supported by DB2 Connect.
- ON SINGLE MEMBER
- Specifies that the coordinator database member is connected in
exclusive mode and all other members are connected in
share mode.
If the database is neither in a
partitioned environment nor a DB2 pureScale environment, this
option can be specified, but it has no effect.
- RESET
- Disconnects the application process from the current server. A
commit operation is performed. If implicit connect is available, the
application process remains unconnected until an SQL statement is
issued.
- USER authorization-name/host-variable
- Identifies the user ID trying to connect to the application server.
If a host-variable is specified, it must be a character string variable
that does not include an indicator variable. The user ID that is contained
within the host-variable must be left-aligned and
must not be delimited by quotation marks.
- USING password/host-variable
- Identifies the password of the user ID trying to connect to the
application server. The password or host-variable can
be up to 14 bytes long. If a host variable is specified, it must be
a character string variable with a length attribute not greater than
14, and it must not include an indicator variable.
- NEW password/host-variable CONFIRM password
- Identifies
the new password that should be assigned to the user ID identified
by the USER option. The password or host-variable can
be up to 14 bytes long. If a host variable is specified, it must be
a character string variable with a length attribute not greater than
14, and it must not include an indicator variable. The system on which
the password will be changed depends on how the user authentication
has been set up. To support the changing passwords on Linux, the database instance must be configured
to use the security plug-ins IBMOSchgpwdclient and IBMOSchgpwdserver.
Notes
- It is good practice for the first SQL statement executed by an
application process to be the CONNECT statement.
- If a CONNECT statement is issued to the current application
server with a different user ID and password then the conversation
is deallocated and reallocated. All cursors are closed by the database
manager (with the loss of the cursor position if the WITH HOLD option
was used).
- If a CONNECT statement is issued to the current application
server with the same user ID and password then the conversation is
not deallocated and reallocated. Cursors, in this case, are not closed.
- To use a multiple-partition partitioned database environment,
the user or application must connect to one of the database partitions
listed in the db2nodes.cfg file. You should
try to ensure that not all users use the same database partition as
the coordinator partition.
- The authorization-name SYSTEM
cannot be explicitly specified in the CONNECT statement. However, on Windows operating
systems, local applications running under the Local System Account
can implicitly connect to the database, such that the user ID is SYSTEM.
- When
connecting to Windows Server
explicitly, the authorization-name or user host-variable can
be specified using the Microsoft Windows Security Account Manager
(SAM)-compatible name.
- The database can be inaccessible if the database was not explicitly
activated, a client application performs frequent reconnections, or
the time interval between issuing the DEACTIVATE DATABASE and ACTIVATE
DATABASE commands is very short. Activate the database by
issuing the ACTIVATE DATABASE command and then
attempt to connect to the database.
- Syntax alternatives:
The following are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and
should not be used.
- DBPARTITIONNUM or NODE can be specified in place of MEMBER, except when
the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
Examples
- Example 1: In a C program, connect to the application server
TOROLAB, using database alias TOROLAB, user ID FERMAT, and password
THEOREM.
EXEC SQL CONNECT TO TOROLAB USER FERMAT USING THEOREM;
- Example 2: In a C program, connect to an application server
whose database alias is stored in the host variable APP_SERVER (varchar(8)).
Following a successful connection, copy the 3-character product identifier
of the application server to the variable PRODUCT (char(3)).
EXEC SQL CONNECT TO :APP_SERVER;
if (strncmp(SQLSTATE,'00000',5))
strncpy(PRODUCT,sqlca.sqlerrp,3);
DB2 10.5 for Linux, UNIX, and Windows