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 DB2® authorization ID is derived
from the connection user ID according to the authentication plug-in
in effect for the server. This DB2 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 DBPARTITIONNUM-'
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 the
database configuration parameter dyn_query_mgmt is
enabled, then the SQLWARN0 and SQLWARN7 fields of the SQLCA will be
flagged with 'W' and 'E', 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-justified
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 DBPARTITIONNUM
- Specifies that the coordinator database partition is connected
in exclusive mode and all other database partitions are connected
in share mode. This option is only effective in a partitioned database.
- 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 justified
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. New passwords can be assigned using this clause on
the following servers for the indicated (and later) releases: DB2 Universal Database™ Version
8 on AIX and Windows operating systems, DB2 Version 9.1 Fix Pack 3 or later on Linux operating systems, DB2 for z/OS Version 7, DB2 for i5/OS V6R1. To
support the changing passwords for DB2 database
products on Linux, the DB2 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 qualifier must be a NetBIOS style name,
which has a maximum length of 15 bytes. For example, 'Domain\User'.
- 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.
- NODE can be specified in place of DBPARTITIONNUM
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);