CONNECT Statement

CONNECT Statement

Establishes a connection with the DBMS

Syntax
Arguments
DB2 Connection Arguments
CONNECT Examples

Syntax

CONNECT TO DB2 <AS alias> <(DB2-connection-arguments)>;

Arguments

Use the following arguments with the CONNECT statement:

alias: specifies an optional alias that has 1 to 32 characters. If you specify an alias, the keyword AS must appear before the alias.
(DB2-connection-arguments): specifies the DBMS-specific arguments that are needed by PROC SQL in order to connect to the DBMS. These arguments must be enclosed in parentheses. For some databases, these arguments have default values and therefore are optional. The arguments for DB2 are described in the following sections.

DB2 Connection Arguments

You must specify the PROC SQL CONNECT statement when you connect to DB2. You can connect to only one DB2 database at a time; however, you can use multiple CONNECT statements to connect to multiple DB2 data sources by using the alias argument to distinguish your connections.

The following list describes the arguments that are used for DB2 in the CONNECT statement. You can use the arguments DATABASE=, USER=, and PASSWORD= to connect to most data sources. Use the PROMPT=, NOPROMPT=, COMPLETE=, REQUIRED=, or AUTOCOMMIT= arguments to provide additional information or to select and connect to the data source.

There are multiple ways that you can connect to the DBMS when using the CONNECT statement. Use only one of the following methods for each connection since they are mutually exclusive:

specify USER=, PASSWORD=, and DATABASE=, or
specify COMPLETE=, or
specify NOPROMPT=, or
specify PROMPT=, or
specify REQUIRED=.

DATABASE|DATASRC|DSN|DS =<'>database-name<'>

specifies the name of the database that you want to connect to; the name is limited to eight characters. Specify either DSN= or one (and only one) of the following arguments: PROMPT=, NOPROMPT=, COMPLETE=, or REQUIRED= . These arguments are all mutually exclusive of each other.

USER|UID=<'>username<'>

specifies the DBMS password.

The USER= and PASSWORD= connections are optional in DB2. If you specify USER=, you must also specify PASSWORD=.

PASSWORD|PW|PASS|PWD|USING=<'>password<'>

specifies the DB2 password that is associated with your user ID.

The USER= and PASSWORD= connection options are optional in DB2 because users may have default user IDs. If you specify USER=, you must specify PASSWORD=.

AUTOCOMMIT=YES | NO

indicates whether or not updates are committed immediately after they are submitted.

If AUTOCOMMIT=YES, no rollback is possible. This is the default for the SQL Procedure Pass-Through Facility and read-only connections.

If AUTOCOMMIT=NO, the SAS/ACCESS engine automatically does the commit when it reaches the end of the file.

COMPLETE=<'>CLI-connection-string<'>

specifies connection options for your data source or database. If you specify enough correct connection options, the SAS/ACCESS engine connects to your data source or database. Otherwise, you are prompted for the connection options with a dialog box. Separate multiple options with a semicolon. When a successful connection is made, the complete connect string is returned in the SYSDBMSG and SQLXMSG macro variables.

COMPLETE= is similar to the PROMPT= option. However, if COMPLETE= attempts to connect and fails, then a dialog box is displayed and you can edit values or enter additional values.

COMPLETE= is optional.

See your driver documentation for more details.

NOPROMPT=<'>CLI-connection-string <'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. If you specify enough correct connection options, the SAS/ACCESS engine connects to the data source or database. Otherwise, an error is returned and no dialog box is displayed. NOPROMPT= is optional. If it is omitted and other options settings are not specified, the default settings are used.

PROMPT=<'> CLI-connection-string<'>

specifies connection options to the data source.

A dialog box is displayed, using the values from the PROMPT= connection string. You can edit any field before you connect to the data source. When a successful connection is made, the complete connect string is returned in the SYSDBMSG and SQLXMSG macro variables.

PROMPT= is similar to the COMPLETE= option. However, unlike COMPLETE=, PROMPT= does not attempt to connect to the DBMS first. It displays the dialog box where you can edit or enter additional values.

REQUIRED=<'>CLI-connection-string<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon.

If you specify enough correct connection options, such as user ID, password, and data source name, the SAS/ACCESS engine connects to the data source or database. Otherwise, a dialog box is displayed to prompt you for the connection options. Options in the dialog box that are not related to the connection are disabled. REQUIRED= only allows you to modify required fields in the dialog box. When a successful connection is made, the complete connect string is returned in the SYSDBMSG and SQLXMSG macro variables.

REQUIRED= is similar to COMPLETE= because it attempts to connect to the DBMS first. However, if REQUIRED= attempts to connect and fails, then a dialog box is displayed and you can only edit values in the required fields.

REQUIRED= is optional.

CONNECT Examples

The following example connects to the SAMPLE database and sends it two EXECUTE statements to process.

proc sql;
   connect to db2 (database=sample);
   execute (create view 
               sasdemo.whotookorders as
            select ordernum, takenby, 
                   firstname, lastname, phone
              from sasdemo.orders, 
                   sasdemo.employees
              where sasdemo.orders.takenby=
                    sasdemo.employees.empid)
           by db2;
   execute (grant select on 
            sasdemo.whotookorders to testuser) 
           by db2;
   disconnect from db2;
quit;

The next example connects to the SAMPLE database by using an alias (DB1) and performs a query, shown in italic type, on the SASDEMO.CUSTOMERS table.

proc sql;
   connect to db2 as db1 (database=sample);
   select *
      from connection to db1
         (select
* from sasdemo.customers
             where
customer like '1%');
   disconnect from db1;
quit;

Chapter Contents
Previous
Next
Top of Page