Specifies the name of a database and the source of the data
definitions to be accessed by interactive SQL or by a program
at run time. Makes the specified database part of the current
database environment. The database environment is the set of all
databases with unique aliases in the current connection.
The ATTACH statement lets you add new databases at run time; it
has no effect on the compile-time environment. To specify the
compile-time environment, use the DECLARE ALIAS statement.
You can name either a file or a repository path name to be used
for the data definitions.
If a transaction is currently active, SQL returns an
informational message and does not attach the specified database
environment to the connection.
If a database is currently attached and you attach to another
database without using an alias, SQL detaches the current
database environment and attaches to the specified one in its
place.
1 – Environment
You can use the ATTACH statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ATTACH ---+-> attach-string-literal ----+->
+-> <attach-parameter> -------+
+-> <attach-parameter-marker> +
attach-string-literal =
--> ' --> attach-expression --> ' -->
attach-expression =
-+------------------++-> FILENAME -> '<attach-spec>' -+-+
+-> ALIAS <alias> -++-> PATHNAME -> <path-name> ----+ |
+------------------------------------------------------+
++------------------------+----------------------------+
+--> literal-user-auth --+ |
+------------------------------------------------------+
++-----------------------------+------------------------>
+-+-+-> database-options --+-++
| +-> attach-options ----+ |
+----------- <-------------+
literal-user-auth =
---> USER '<username>' -+------------------------+-->
+-> USING '<password>' -+
attach-spec =
--+----------------+-> <file-spec> ----->
+-> <node-spec> -+
node-spec =
-+-> <nodename> -+-------------------+-+->
| +-> <access-string> + |
+------------------ :: <--------------+
access-string =
-+-> " <user-name> <password> " --+->
+-> " <VMS-proxy-user-name> " ---+
database-options =
--+--> ELN ---------------------+-->
+--> NSDS --------------------+
+--> rdb-options -------------+
+--> VIDA --------------------+
+--> VIDA V1 -----------------+
+--> VIDA V2 -----------------+
+--> VIDA V2N ----------------+
+--> NOVIDA ------------------+
+--> DBIV1 -------------------+
+--> DBIV31 ------------------+
+--> DBIV70 ------------------+
rdb-options =
-+-> RDBVMS --+-->
+-> RDB030 --+
+-> RDB031 --+
+-> RDB040 --+
+-> RDB041 --+
+-> RDB042 --+
+-> RDB050 --+
+-> RDB051 --+
+-> RDB060 --+
+-> RDB061 --+
+-> RDB070 --+
+-> RDB071 --+
attach-options =
-+-> DBKEY -+-> SCOPE IS -+-> ATTACH -------+-----------------------+->
+-> ROWID -+ +-> TRANSACTION --+ |
+-> MULTISCHEMA IS -+-> ON --+-------------------------------------+
| +-> OFF -+ |
+-> PRESTARTED TRANSACTIONS ARE -+-> ON --+------------------------+
| +-> OFF -+ |
+-+-------+-> RESTRICTED ACCESS -----------------------------------+
+-> NO -+
3 – Arguments
3.1 – ALIAS alias
A part of the attach expression that specifies a name for the
attach to the database. Specifying an alias lets your program or
interactive SQL statements refer to more than one database.
You do not have to specify an alias in the ATTACH statement.
The default alias in interactive SQL and in precompiled programs
is RDB$DBHANDLE. In the SQL module language, the default is the
alias specified in the module header. Using the default alias
(either by specifying it explicitly in the ATTACH statement or
by omitting any alias) makes the database part of the default
environment. Specifying a default database means that statements
that refer to that database do not need to use an alias.
If a default alias was already declared, and you specify the
default alias in the alias clause (or specify any alias that
was already declared), interactive SQL issues an informational
message.
In the following example, TESTDB is the first database attached
and uses the default alias. When no alias is specified for the
second database attached, SQL tries to assign it the default
alias but finds that the default alias is already declared.
SQL> ATTACH 'FILENAME testdb';
SQL> ATTACH 'FILENAME otherdb';
This alias has already been declared.
Would you like to override this declaration (No)? N
SQL-F-DEFDBDEC, A database has already been declared with the default alias
SQL> SHOW DATABASES;
Default alias:
Oracle Rdb database in file testdb
SQL> ATTACH 'FILENAME otherdb';
This alias has already been declared.
Would you like to override this declaration (No)? Y
SQL> SHOW DATABASES;
Default alias:
Oracle Rdb database in file otherdb
3.2 – attach-expression
Specifies a database to be added to the environment.
3.3 – attach-parameter
A host language variable in precompiled SQL or a formal parameter
in an SQL module language procedure that specifies the database
environment for the connection. The attach parameter must contain
an attach expression.
3.4 – attach-parameter-marker
A parameter marker, denoted by question marks (?), in a dynamic
SQL statement. The attach parameter marker refers to a parameter
that specifies the database environment for the connection. The
attach parameter marker must specify a parameter that contains an
attach expression.
3.5 – attach-options
Specifies characteristics of the particular database attach. You
can specify more than one of these clauses.
3.6 – attach-string-literal
A character string literal that specifies the database
environment for the connection. The attach string literal must
contain an attach expression enclosed in single quotation marks.
3.7 – database-options
By default, the SQL precompiler determines the type of database
it attaches to from the type of database specified in compiling
the program.
For more information on database options, see the Database_
Options HELP topic.
3.8 – DBKEY_SCOPE
Syntax options:
DBKEY SCOPE IS ATTACH | DBKEY SCOPE IS TRANSACTION
Controls when the database key of a deleted row can be used again
by SQL.
o The default DBKEY SCOPE IS TRANSACTION means that SQL can
reuse the database key of a deleted table row (to refer to a
newly inserted row) as soon as the transaction that deleted
the original row completes with a COMMIT statement. (If the
user who deleted the original row enters a ROLLBACK statement,
then the database key for that row cannot be used again by
SQL.)
During the connection of the user who entered the ATTACH
statement, the DBKEY SCOPE IS TRANSACTION clause specifies
that a database key is guaranteed to refer to the same row
only within a particular transaction.
o The DBKEY SCOPE IS ATTACH clause means that SQL cannot use the
database key again (to refer to a newly inserted row) until
all users who have attached with DBKEY SCOPE IS ATTACH have
detached from the database.
It only requires one process to attach with DBKEY SCOPE
IS ATTACH to force all database users to assume this
characteristic.
o Oracle Corporation recommends using DBKEY SCOPE IS TRANSACTION
to prevent excessive consumption of storage area space by
overhead space needed to support DBKEY SCOPE IS ATTACH, and to
prevent performance problems when storing new rows.
During the connection of the user who entered the ATTACH
statement, the DBKEY SCOPE IS ATTACH clause specifies that
a database key is guaranteed to refer to the same row until
the user detaches from the database.
For more information, see the DBKEY HELP topic.
3.9 – DISPLAY CHARACTER SET support-char-set
Specifies the character set encoding and characteristics expected
of text strings returned back to SQL from Oracle Rdb.
3.10 – FILENAME
A quoted string containing full or partial information needed to
access a database.
For an Oracle Rdb database, an attach specification contains the
file specification of the .rdb file.
When you use the FILENAME argument, any changes you make to
database definitions are entered only to the database system
file, not to the repository. If you specify FILENAME, your
application attaches to the database with that file name at run
time.
For information regarding node-spec and file-spec, see Oracle Rdb
Attach Specifications.
3.11 – literal-user-auth
Specifies the user name and password to enable access to
databases, particularly remote databases
This literal lets you explicitly provide user name and password
information in the attach expression.
When you use Oracle Rdb for OpenVMS to attach to a database in
the same cluster, you do not have to explicitly specify the user
name and password. Oracle Rdb implicitly authenticates the user
whenever the user attaches to a database.
However, when you use Oracle Rdb for OpenVMS to attach to a
database on a remote node, you must use one of the methods
provided by Oracle Rdb to access the database.
You can use one of the following methods to attach to a database
on a remote OpenVMS node.
o Explicitly provide the user name and password in the ATTACH
statement.
o Explicitly provide the user name and password in the
configuration file RDB$CLIENT_DEFAULTS.DAT. The following
example shows how to include the information in the
configuration file:
! User name to be used for authentication
SQL_USERNAME HELENG
! Password to be used for authentication
SQL_PASSWORD MYPASSWORD
o Use a DECnet proxy account on the remote system system.
o Embed the user name and password in the file specification.
o Use the RDB$REMOTE default account.
For information on proxy accounts, embedding the user name in
the file specification or using the RDB$REMOTE account, see the
Oracle Rdb Guide to SQL Programming.
3.12 – MULTISCHEMA_IS
Syntax options:
MULTISCHEMA IS ON | MULTISCHEMA IS OFF
The MULTISCHEMA IS ON clause enables multischema naming for the
duration of the database attach. The MULTISCHEMA IS OFF clause
disables multischema naming for the duration of the database
attach. On attach, multischema naming defaults to the setting
specified during database definition.
You can use multischema naming only when attached to a database
that was created with the multischema attribute. If you specify
the MULTISCHEMA IS ON clause with a database that was not created
with the multischema attribute, SQL returns an error message, as
shown in the following example:
SQL> ATTACH 'ALIAS PERS_ALIAS FILENAME personnel MULTISCHEMA IS ON';
%SQL-F-NOPHYSMULSCH, The physical multischema attribute was not specified for
the database
3.13 – PATHNAME
A full or relative repository path name that specifies the
source of the database definitions. When you use the PATHNAME
argument, any changes you make to database definitions are
entered in both the repository and the database system file.
Oracle Rdb recommends using the PATHNAME argument if you have the
repository on your system and you plan to use any data definition
statements.
If you specify PATHNAME, your application attaches to the
database file name extracted from the repository.
3.14 – PRESTARTED_TRANSACTIONS_ARE
Syntax options:
PRESTARTED TRANSACTIONS ARE ON | PRESTARTED TRANSACTIONS ARE OFF
Specifies whether Oracle Rdb enables or disables prestarted
transactions.
Use the PRESTARTED TRANSACTIONS ARE OFF clause only if your
application uses a server process that is attached to the
database for long periods of time and causes the snapshot file
to grow excessively. If you use the PRESTARTED TRANSACTIONS
ARE OFF clause, Oracle Rdb uses additional I/O because each SET
TRANSACTION statement must reserve a transaction sequence number
(TSN).
For most applications, Oracle Rdb recommends that you enable
prestarted transactions. The default is PRESTARTED TRANSACTIONS
ARE ON. If you use the PRESTARTED TRANSACTIONS ARE ON clause or
do not specify the PRESTARTED TRANSACTIONS clause, the COMMIT
or ROLLBACK statement for the previous read/write transaction
automatically reserves the TSN for the next transaction and
reduces I/O.
You can define the RDMS$BIND_PRESTART_TXN logical name to define
the default setting for prestarted transactions outside of an
application. The PRESTARTED TRANSACTION clause overrides this
logical name. For more information, see the Oracle Rdb7 Guide to
Database Performance and Tuning. See also the ALTER and CREATE
DATABASE clause PRESTARTED TRANSACTIONS ARE ENABLED for more
details.
3.15 – RESTRICTED_ACCESS
Restricts access to the database. This allows you to access the
database but locks out all other users until you disconnect from
the database. Setting restricted access to the database requires
DBADM privileges.
The default is NO RESTRICTED ACCESS if not specified.
3.16 – ROWID_SCOPE
Syntax options:
ROWID SCOPE IS ATTACH | ROWID SCOPE IS TRANSACTION
The ROWID keyword is a synonym for the DBKEY keyword. See the
DBKEY_SCOPE argument for more information.
3.17 – USER username
A character string literal that specifies the operating system
user name that the database system uses for privilege checking.
Because the user name literal is within the quoted attach-
string, you must enclose the user name within two sets of single
quotation marks in interactive SQL.
This clause also sets the value of the SYSTEM_USER value
expression.
3.18 – USING password
A character string literal that specifies the user's password for
the user name specified in the USER clause. Because the password
literal is within the quoted attach-string, you must enclose
surround the password within two sets of single quotation marks
in interactive SQL.
4 – Examples
Example 1: Attaching a database by file name in interactive SQL
and specifying restricted access
This interactive SQL statement attaches the database defined by
the file specification mf_personnel to the current connection,
and declares the alias pers_alias for that database. Use the SHOW
DATABASE statement to see the database settings.
SQL> ATTACH 'ALIAS pers_alias FILENAME mf_personnel -
cont> RESTRICTED ACCESS';
Example 2: Attaching a database by path name in interactive SQL
This interactive SQL statement attaches to the database file name
extracted from the repository. Use the SHOW DATABASE statement to
see the database settings.
SQL> ATTACH
cont> 'ALIAS PERS PATHNAME DISK3:[REPOSITORY.DEPT2]PERSONNEL';
Example 3: Using an attach parameter in a program
This excerpt from an SQL module language procedure shows how you
might declare a parameter to contain an attach string. You would
need to compile the module with the PARAMETER COLONS clause in
order to prefix the parameter with a colon.
PROCEDURE attach_db
SQLCODE
attach_string char(155);
ATTACH :attach_string;
You could then write a C program that calls this procedure. The
line that passes the attach string would need a format such as
the following:
main () {
long sqlcode;
attach_db( &sqlcode, "ALIAS CORP FILENAME corporate_data" );
/* Now dynamic statements can refer to alias CORP */
}
Example 4: Explicitly providing the user name and password in the
ATTACH statement
The following example shows how to explicitly provide the user
name and password in the ATTACH statement.
SQL> ATTACH 'FILENAME FARSID::USER1:[GREMBOWSKI.DB]MF_PERSONNEL -
cont> USER ''grembowski'' USING ''mypassword''';