SAP DB CONNECTIONS MNGMNT

Get Example source ABAP code based on a different SAP table

Management of Database Connections
Database connections are managed by the ABAP runtime framework. This is done at the work process level and the ABAP_ISESS level. Each time an AS ABAP is started, a standard connection is opened for every work process and this connection cannot be closed. In addition to the standard connection, 15 further secondary connections or service connections can be opened for each work process. A maximum of 16 database connections can be open for each work process. On certain databases, it may not be possible to reach this number. If more than 16 database connections are opened, the runtime error DBSQL_NO_MORE_CONNECTION occurs.
ITOC

Opening and Closing Secondary Connections and Service Connections
AB_SQL and Native SQL can request secondary connections or service connections. AMDP can only request service connections. A secondary connection or a service connection is requested as follows:

Implicitly in AB_SQL by specifying the name of the connection after the addition CONNECTION

Explicitly and implicitly in Native SQL using one of the following:

The methods GET_ABAP_CONNECTION and GET_CONNECTION of the ADBC class CL_SQL_CONNECTION

The statement CONNECT TO after EXEC SQL

The input parameter CONNECTION of AMDP procedure implementations (for service connections only)
If no inactive database connection can be activated after the specified name, it is opened for the current work process and activated for the current ABAP_ISESS .
A secondary connection or service connection is closed explicitly in Native SQL using the following:

The method CLOSE of the ADBC class CL_SQL_CONNECTION

The statement DISCONNECT after EXEC SQL
If closed explicitly in Native SQL, all database changes in the current database LUW of the connection that were not yet committed on the database are discarded.
AB_SQL does not have a statement that closes a database connection explicitly. Any secondary connection or service connection that is inactive for a specific period of time (approximately 15 minutes by default) is closed by the ABAP runtime framework implicitly.

Latest notes:
Generally speaking, database connections should only be closed implicitly by the ABAP runtime framework. Database connections should only be closed explicitly if it can be guaranteed that they are not required for a long time in the current process, since it takes a significant amount of resources to restore a connection.
ABAP_HINT_END

Example ABAP Coding

Opening of three service connections with AB_SQL , ADBC, and after EXEC SQL . The connection opened using AB_SQL is closed again using Native SQL, which requires its name to be specified in uppercase letters.
ABEXA 01185
ABAP_EXAMPLE_END

Active and Inactive Secondary Connections and Service Connections
An open secondary connection or service connection can be active or inactive. Once opened in AB_SQL , Native SQL, or AMDP, a database connection is active and can be used by AB_SQL , Native SQL, or AMDP. The secondary connection or service connection becomes inactive as soon as the current database LUW on this connection is ended. This can occur as follows:

Using the statements COMMIT CONNECTION or ROLLBACK CONNECTION for this connection.

Using the Native SQL statements COMMIT WORK or ROLLBACK WORK after EXEC SQL for this connection

Using the methods COMMIT and ROLLBACK of the ADBC class CL_SQL_CONNECTION for this connection

Using the statements COMMIT WORK and ROLLBACK WORK for all connections

In an implicit database commit or database rollback for all connections

When closing the ABAP_ISESS in which the connection was opened, where a COMMIT CONNECTION is executed for the connection implicitly.
An inactive open secondary connection or service connection is reused by the ABAP runtime framework if it is to be reopened for its work process. Once activated in an ABAP_ISESS , a secondary connection or service connection can be reused here regardless of whether it is active or inactive. When an inactive connection is reused, it is activated implicitly and a new database LUW is opened. In this case, it is not necessary to open it again explicitly in Native SQL either.

Latest notes:
When a database connection is closed explicitly in Native SQL it is actually closed and not just set to inactive. The next request must then reopen the connection for the current work process.
ABAP_HINT_END

Example ABAP Coding

A service connection R/3*DEMO is requested using ADBC and used for an SQL statement. The statement COMMIT CONNECTION ends the database LUW of the connection and sets it from active to inactive. The connection can be shared, and the connection name does not contain any lowercase letters, which means it is reused by being specified in the AB-SQL statement INSERT. The connection is reactivated here and a new database LUW is started. A further COMMIT CONNECTION statement ends this LUW and deactivates the connection.
ABEXA 01186
ABAP_EXAMPLE_END

Secondary Connections and Service Connections in the ABAP_ISESS_L
Active open secondary connections or service connections can only be used within the ABAP_ISESS in which they are opened. An active open secondary connection or service connection can be shared by AB_SQL , Native SQL, and AMDP within an ABAP_ISESS . In Native SQL or AMDP, the open connections cannot have any lowercase letters in the name and in ADBC, open connections must be intended for this purpose.
When the ABAP_ISESS is closed, any changes made using the connections are committed and the connections are set to inactive. When an ABAP program is called that returns to the called program (SUBMIT AND RETURN or CALL TRANSACTION), the states of any secondary connections or service connections opened here are preserved as active or inactive. They are not, however, passed to the called program. If a secondary connection or service connection with the same name is requested here, a further connection of the same type is opened.
Any secondary connection or service connection that becomes inactive within an ABAP_ISESS when its database LUW is ended can be reused in the same session without being requested explicitly, not just in AB_SQL , but also in Native SQL or AMDP.

Example ABAP Coding

An AB-SQL statement requests a service connection R/3*DEMO and then calls a further program. The called program requests a service connection with the same name. After this, two service connections with the same name are open and active for the current work process until the end of the ABAP_ISESS of the called program. When a return is made from the called program, its service connection is deactivated, just as the service connection of the calling program is deactivated when it is exited. No database commit was made before the call, which means that the isolation level of the database determines whether the change made in the caller is visible in the called program.
Calling Program DELETE FROM demo_update CONNECTION r/3*demo.

SUBMIT ... AND RETURN.
Called Program SELECT *
FROM demo_update
INTO TABLE @DATA(itab)
CONNECTION r/3*demo.
ABAP_EXAMPLE_END

Displaying Secondary Connections and Service Connections
The program DBCONINFO shows all database connection of all work processes in the current AS ABAP. The name R/3 in the column ConName identifies the standard connection. Other names indicate the secondary connections and service connections. The column ConState shows the states ACTIVE, INACTIVE, and DISCONNECTED. The column Hdl indicates whether a connection is a secondary connection or a service connection. Identically named secondary and service connections can occur for the following reasons:

Identically named connections are opened in called programs

Connections are opened using the method GET_CONNECTION of the class CL_SQL_CONNECTION in ADBC, where the value abap_false is used for the parameter SHARABLE

Connections are opened using CONNECT TO after EXEC SQL , where AS is used to specify an explicit name

Example ABAP Coding

The program DBCONINFO can be used to search the examples shown in this section while they are being executed step by step in the ABAP Debugger.
ABAP_EXAMPLE_END