Open SQL - Secondary Database Connections
Open SQL statements access the central AS ABAP by default. The addition
CONNECTION can be used to execute Open SQL statements on databases other
than the standard SAP database. This presents a number of options, for example, data can be passed to and updated in other databases. It is irrelevant whether the other database belongs to a
AS ABAP. However, it is
a prerequisite that it is a database system supported by SAP, because only in such a system is the software needed for an Open SQL access available in form of a shared library.
Setting Up a Secondary Database Connection
To a access another database, an entry in the table DBCON is required that describes the access data of the database. If the secondary database is from a vendor other than the vendor of the database of the current AS ABAP, the SAP shared library is required as well as the client software provided by the vendor. Information about creating the DBCON entry and installing the additional software is described in the standard SAP Notes for every supported database system.
On AS ABAP, entries in the database table DBCON can be created and modified using the central DBA Cockpit tool.
The Standard Database Connection
Every work process has a standard database connection to the SAP standard database. It is shared by all
This database connection can also be accessed explicitly using the name
DEFAULT connection can also be specified dynamically using CONNECTION
(name), where the
name field must have the value
Service Connections to the SAP Standard Database
In addition to the secondary database connections stored in database table DBCON, service connections to the SAP standard database can also be used. The name of this connection is R/3name, which consist of the prefix R/3 and a user-defined name. A service connection automatically uses all the settings of the standard database connection.
- Service connections for the SAP standard database are useful for performing operations in a database LUW, which does not depend on the LUW standard connection. It should be noted that the current isolation level is responsible for determining whether a read from a database LUW can access data modified in a different database LUW, before this data is committed using a database commit.
- Service connections to the SAP standard database can also be used for calling an
AMDP method, if this method has an input parameter with the predefined name
Accessing Other Database Tables
If a database connection is used to access database tables or views that are not in the SAP standard database, there must also be a database table with the same name and identical type in ABAP Dictionary in the local AS ABAP. Open SQL assumes that the type information of these "remote" database tables or views corresponds exactly with that of the local database table. This prerequisite is essential for the correct interpretation of the database contents, for example, and, if necessary, their conversion with regards to the ABAP type of the target fields (see INTO Clause). If this prerequisite is not met, this can lead to wrong data or runtime errors when reading or writing, depending on the database system. Because the ABAP runtime environment cannot ensure the consistency of the type descriptions in the local and remote databases, the consistency must be guaranteed by the relevant application program.
- The type of a database object specified in an Open SQL statement using the addition
CONNECTIONdoes not necessarily need to match the type of the database object with the same name in the secondary database. For example, a view with the same name in the secondary database can be accessed by specifying a database table (or a database table by specifying a view) if they have the same structure.
- The prerequisite that an identically named database object with a suitable structure must exist in ABAP Dictionary in the current system for views accessed using a secondary database connection is particularly important for external views.
Database Connections and Transactions
Every database connection forms its own transaction context. This means that database changes on one
connection can be saved (using
or discarded (using
independently of changes on other database connections. It is possible, for example, to commit and store log data on a secondary database connection without affecting the current
transaction running on the standard SAP database connection.
Secondary database connections are not known outside the limits of an
So, if a program opens a database connection and then calls another program (using
SUBMIT, for example) that opens a connection to the same database, there are two different connections and therefore two different database transactions.
For the first Open SQL command that requests a specific database connection, the system opens a corresponding connection. All subsequent commands (in the same internal session) for the same "remote" database use the same database connection and all form a database transaction. The transaction is ended by:
ROLLBACK CONNECTIONon this connection
WAIT FOR ASYNCHRONOUS TASKS.
In summary, a database transaction is completed at the latest, when the application program reaches a state in which a change of work process could occur.
Working with parallel database connections, that is parallel transactions, can lead to lock situations that only one work process is involved in: A program changes a database row on the first connection and tries to change the same row on a second connection. This results in the program waiting for the lock of the first transaction, without this first transaction ever being able to continue. This situation can only be resolved by ending the work process. This is done automatically for dialog processes, but must be done manually for background jobs. It is therefore not recommended to change the same table in one program on multiple database connections.
Interaction with Native SQL Connections
In addition to the above, a connection can be opened explicitly using a
Native SQL. Native SQL also
makes it possible to close and disconnect a database connection explicitly using
In this case, the database transaction in question is closed and corresponding database changes are discarded. Using the method GET_CONNECTION (from the
ADBC class CL_SQL_CONNECTION
or the embedded Native SQL statement GET
CONNECTION, it is possible to configure the current Native SQL connection. Using Native SQL,
it is also possible to access a connection that was opened with Open SQL. In this case it is sufficient
to convert the active Native SQL connection using the method SET CONNECTION or the statement
SET CONNECTION. It is not necessary to open it using
Management of Database Connections
The database connections are automatically managed by the runtime system. If a transaction is saved on a database connection
COMMIT) or rolled back
ROLLBACK), it can be reused
by the runtime system. A maximum of 16 database connections can be opened for each work process. This
means a maximum of 15 secondary database connections is possible for a standard database connection. On certain databases, this number cannot always be reached.