1 ATTACH 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. 2 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-string-literal = --> ' --> attach-expression --> ' --> attach-expression = -+------------------++-> FILENAME -> '' -+-+ +-> ALIAS -++-> PATHNAME -> ----+ | +------------------------------------------------------+ ++------------------------+----------------------------+ +--> literal-user-auth --+ | +------------------------------------------------------+ ++-----------------------------+------------------------> +-+-+-> database-options --+-++ | +-> attach-options ----+ | +----------- <-------------+ literal-user-auth = ---> USER '' -+------------------------+--> +-> USING '' -+ attach-spec = --+----------------+-> -----> +-> -+ node-spec = -+-> -+-------------------+-+-> | +-> + | +------------------ :: <--------------+ access-string = -+-> " " --+-> +-> " " ---+ 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 -+ 2 Arguments 3 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 attach-expression Specifies a database to be added to the environment. 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 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 attach-options Specifies characteristics of the particular database attach. You can specify more than one of these clauses. 3 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 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 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 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 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 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 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 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 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 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 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 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 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. 2 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''';