DBConnect possible for ECC 6 ?

[Former Member]

Hi all,

My SAP is ECC6. using MSSQL, plan to access another legacy system database which is using MSSQL too. Now my worried is I don't hv BI, can I use DBConnect ?

Regards,

Janice

[Former Member]

Check the below note.

0000323151--Several DB connections with Native SQL

Page 1 from 6

323151 SAP Note

Jan 12, 2009

11 Version

Processor

new Processing Status

Cannot be implemented Implementation Status

E Language

Several DB connections with Native SQL Short Text

BC-DB-DBI Component

DB-Independent Database Interface

Description

Symptom This note describes the option (available as of Basis Release 4.0B) of using Native SQL language elements to establish one or several database connections to database systems of the same manufacturer (homogeneous multi-connect) or to databases of other manufacturers (heterogeneous multi-connect). Other terms Multi-connect, DBCON, Native SQL, EXEC SQL, Connect Reason and Prerequisites Solution When you start an R/3 application server, a connection is opened by default from the R/3 kernel to the R/3 database. This connection will be called the R/3 default connection from here on. All SQL commands that are transmitted from the R/3 kernel or from ABAP programs - no matter whether they are Open or Native SQL commands - automatically refer to this default connection, that is, they run in the context of the active DB transaction on this connection. The connection data (such as the DB user name, password of the user or the database name) is taken either from the profile parameters or from the correspondingly set environment variables (this is DB-specific). As of Release 4.0B, you have the option of opening other DB connections in addition to the default connection from within an ABAP program, and you can access these databases using Native SQL commands. These additional connections can be set up either o to the standard R/3 database or o to another database of the same DB manufacturer or o to a database of another DB manufacturer. These databases do not necessarily have to contain SAP components. However, you can only set up connections to database systems that are also supported by the R/3 Basis system.

Page 2 from 6

323151 SAP Note

Jan 12, 2009

11 Version

These are DB2, Informix, MS SQL Server, Oracle and SAP DB. If you only want to set up connections to databases of the same manufacturer (that is, set up homogeneous connections only), this is already possible using the delivered R/3 software. However, if you want to set up a connection to a database that does not correspond to the DB platform of the R/3 database, you also need to install a DB platform-specific DLL via which accesses to this database from R/3 can be routed. These DLLs are not part of a standard R/3 installation. In this case, contact your SAP consultant. You can find information on potential platform-specific restrictions in the attached special notes. Configuration: table DBCON Before you can open an additional DB connection, all connection data that is required to identify the target database and for authentication against this database must be communicated to the ABAP runtime environment. To do this, create an entry in the table DBCON that will contain all the required connection data for each database connection that you want to set up in addition to the R/3 default connection. The table DBCON is in the R/3 default database and can be maintained using the table maintenance tool (Transaction SM30) or, as of Release 4.6, using Transaction DBCO. For each connection you must enter the following information in this table: o A logical connection name. The name entered here explicitly identifies a database connection. o The database type. This determines which DB platform will be used for this connection. In principle, you can enter all DB platforms supported by the R/3 System. o The database user under whose name you want to set up the connection. o The password of this user for authentication against the database. This password is stored in encrypted form. o The technical connection data required to set up the connection to the database. The connection data is DB platform-dependent and generally includes the database name as well as the DB host on which the database runs. Native SQL language commands for administrating several database connections Only Native SQL interfaces allow you to set up additional database connections and access non-R/3 databases. However, all Open SQL commands will continue to be transferred to the default connection, that is, to the R/3 database. Commands for opening and closing a database connection, as well as setting it up, have been added to native SQL. If a new database connection is opened, a new DB transaction is started automatically on this connection. This transaction is independent of the transaction currently running on the R/3 default connection, that is, the transaction running there is not closed and all subsequent Open SQL commands will continue to be processed in this transaction (and not in the transaction started on the new DB connection). However, all subsequent Native SQL commands will be executed on the newly opened connection. No synchronization is carried out by the system between the transactions running on the different connections, that is, a 2-phase commit log is NOT supported.

Page 3 from 6

323151 SAP Note

Jan 12, 2009

11 Version

Opening a database connection EXEC SQL. CONNECT TO <con_name> [ AS <alias_name> ] ENDEXEC. This command opens the database connection designated by <con_name>. In this context, <con_name> is a user-defined logical name, under which all the information required to open the database connection such as the user name, password, database name and so on must be stored in the table DBCON. You can specify <con_name> as a string-literal or as a variable in the form :<var>. If the addition "AS <alias_name>" is entered, the open database connection is managed with the indicated alias name. If no alias name is entered, <con_name> is used as the name for the open connection. If you enter several alias names, you can open several connections to the same database simultaneously by using the same login name. This alias arrangement allows the execution of independent transactions on the same logical database connection (see example 2). The alias name can also be entered as a literal or as a variable. If no entry is found in the table DBCON for the indicated connection name <con_name>, an ABAP runtime error occurs. If, on the other hand, the setup connection fails due to a DB error, for example, because the database in question is not online, then SY-SUBRC = 4 is set and the ABAP program assumes control. After the connection has been successfully set up (SY-SUBRC = 0), the connection that was just opened automatically becomes the active connection, that is, from this point on, all subsequent Native SQL commands (and only these) will access the database associated with this connection. However, all subsequent Open SQL accesses still refer to the default connection. Nevertheless, the physical connection remains. If the connection is not closed explicitly with a "DISCONNECT" command (see below), it remains open until it is closed implicitly when the corresponding internal session is closed (the term "internal session" is explained in the section on "Data area and Modularization Unit Organization" in the ABAP online documentation). After that, you can only access this connection if it has been opened again explicitly with a new "CONNECT" command. Comments If a database connection that was opened using a "CONNECT" command is not explicitly closed again with a "DISCONNECT" command, it is invalidated (this means that it will have to be opened again with a "CONNECT" for further accesses) when the internal session is closed, but the physical connection remains in place. The advantage of this is that it will not have to be set up again if a new "CONNECT" is executed. However, "reusing" a connection that was previously opened only works if there are no open transactions on this connection, that is, if the last transaction on this connection was completed using COMMIT or ROLLBACK. Setting the active database connection EXEC SQL. SET CONNECTION { <alias_name> | DEFAULT } ENDEXEC. This command sets the data base connection <alias_name> that has been opened to active. This means that all subsequent Native SQL statements are executed on this connection. If a transaction is still open on the previous connection, it remains open and can then be

Page 4 from 6

323151 SAP Note

Jan 12, 2009

11 Version

continued by reverting to this connection. You can also enter the keyword DEFAULT as an alias name. This causes the R/3 default connection to become the active connection again. Again, <alias_name> can also be entered directly as a literal or as a variable value in form of :<var>. However, the entered alias name must be known from a preceding "CONNECT" statement (that is, a statement executed within the same internal mode).If the database connection was opened without explicitly specifying an alias name, the connection name <con_name> must be used in the "SET" statement as the <alias_name>. If there is no opened connection for the entered connection name, SY-SUBRC = 4 is set. Closing a database connection EXEC SQL. DISCONNECT <alias_name> ENDEXEC. The "DISCONNECT" command explicitly closes a database connection opened under the <alias_name> name. As of Basis Release 6.20: The transaction running on this link is rolled back. Up to 6.20, the physical link is retained if the transaction running on this link was not completed by COMMIT/ROLLBACK. After a "DISCONNECT", you can no longer access this link until it is reopened explicitly by a "CONNECT". If the closed connection was also the connection that was just active, then the R/3 default connection is automatically the active connection again after that. The "DISCONNECT" command also closes the physical connection to the database. If you are setting up a new connection, you must first re-establish this physical connection, which may be a rather time-consuming operation since the corresponding system resources will have to be requested on the client side as well as on the database server side. Therefore, you should only explicitly close a connection with the "DISCONNECT" command if the operations executed on this connection are only running occasionally. However, if you want to use this connection all the time, it should remain open. If the <alias_name> does not correspond to any previously opened DB connection, an ABAP runtime error occurs. Example 1: Accessing remote DB in a subroutine FORM remote_access USING con_name LIKE dbcon-con_name. * This form performs some database operations on connection 'con_name'. * The form does not require that the caller has already opened this * connection nor does it require that 'con_name' is "active". After * the form has finished the "default connection" is active * Test if connection 'con_name' has already been opened EXEC SQL. SET CONNECTION :con_name ENDEXEC. IF SY-SUBRC <> 0. * Connection not yet opened. EXEC SQL. CONNECT TO :con_name

Page 5 from 6

323151 SAP Note

Jan 12, 2009

11 Version

ENDEXEC. IF SY-SUBRC <> 0. * error handling ENDIF. ENDIF. * Do something on connection 'con_name' EXEC SQL. ... ENDEXEC. * Commit the changes on 'con_name' EXEC SQL. COMMIT ENDEXEC. * Reset to "default connection" EXEC SQL. SET CONNECTION DEFAULT ENDEXEC. ENDFORM. Example 2: Implementing independent transactions DATA con_name LIKE dbcon-con_name VALUE 'ABC'. * Open a connection C1 to a database identified by the logical name * 'ABC'. This implicitly starts a transaction T1. EXEC SQL. CONNECT TO :con_name AS 'C1' ENDEXEC. IF SY-SUBRC <> 0. * error handling ENDIF. * Do some database operations on connection C1 EXEC SQL. ... ENDEXEC. * Open a second connection C2 to the same database 'ABC'. This * implicitly starts transaction T2 which became the "active" one. * Transaction T1 still remains open. EXEC SQL. CONNECT TO :con_name AS 'C2' ENDEXEC. IF SY-SUBRC <> 0. * error handling ENDIF. * Do some operations on connection C2 EXEC SQL. ...

Page 6 from 6

323151 SAP Note

Jan 12, 2009

11 Version

ENDEXEC. * Commit transaction T2. This commit does not have any effect on * transaction T1. EXEC SQL. COMMIT ENDEXEC. * Resume transaction T1 on connection C1 EXEC SQL. SET CONNECTION 'C1' ENDEXEC. EXEC SQL. ... ENDEXEC. * Commit transaction T1 on connection C1 EXEC SQL. COMMIT ENDEXEC. * Reset to the default connection EXEC SQL. SET CONNECTION DEFAULT ENDEXEC. Repairs in the Code

Valid Releases:

Software Component

Release

SAP Application

45B

SAP Basis component

610

46C

46B

46D

620

640

[Former Member]

Check the below note.

0000323151--Several DB connections with Native SQL

Page 1 from 6

323151 SAP Note

Jan 12, 2009

11 Version

Processor

new Processing Status

Cannot be implemented Implementation Status

E Language

Several DB connections with Native SQL Short Text

BC-DB-DBI Component

DB-Independent Database Interface

Description

Symptom This note describes the option (available as of Basis Release 4.0B) of using Native SQL language elements to establish one or several database connections to database systems of the same manufacturer (homogeneous multi-connect) or to databases of other manufacturers (heterogeneous multi-connect). Other terms Multi-connect, DBCON, Native SQL, EXEC SQL, Connect Reason and Prerequisites Solution When you start an R/3 application server, a connection is opened by default from the R/3 kernel to the R/3 database. This connection will be called the R/3 default connection from here on. All SQL commands that are transmitted from the R/3 kernel or from ABAP programs - no matter whether they are Open or Native SQL commands - automatically refer to this default connection, that is, they run in the context of the active DB transaction on this connection. The connection data (such as the DB user name, password of the user or the database name) is taken either from the profile parameters or from the correspondingly set environment variables (this is DB-specific). As of Release 4.0B, you have the option of opening other DB connections in addition to the default connection from within an ABAP program, and you can access these databases using Native SQL commands. These additional connections can be set up either o to the standard R/3 database or o to another database of the same DB manufacturer or o to a database of another DB manufacturer. These databases do not necessarily have to contain SAP components. However, you can only set up connections to database systems that are also supported by the R/3 Basis system.

Page 2 from 6

323151 SAP Note

Jan 12, 2009

11 Version

These are DB2, Informix, MS SQL Server, Oracle and SAP DB. If you only want to set up connections to databases of the same manufacturer (that is, set up homogeneous connections only), this is already possible using the delivered R/3 software. However, if you want to set up a connection to a database that does not correspond to the DB platform of the R/3 database, you also need to install a DB platform-specific DLL via which accesses to this database from R/3 can be routed. These DLLs are not part of a standard R/3 installation. In this case, contact your SAP consultant. You can find information on potential platform-specific restrictions in the attached special notes. Configuration: table DBCON Before you can open an additional DB connection, all connection data that is required to identify the target database and for authentication against this database must be communicated to the ABAP runtime environment. To do this, create an entry in the table DBCON that will contain all the required connection data for each database connection that you want to set up in addition to the R/3 default connection. The table DBCON is in the R/3 default database and can be maintained using the table maintenance tool (Transaction SM30) or, as of Release 4.6, using Transaction DBCO. For each connection you must enter the following information in this table: o A logical connection name. The name entered here explicitly identifies a database connection. o The database type. This determines which DB platform will be used for this connection. In principle, you can enter all DB platforms supported by the R/3 System. o The database user under whose name you want to set up the connection. o The password of this user for authentication against the database. This password is stored in encrypted form. o The technical connection data required to set up the connection to the database. The connection data is DB platform-dependent and generally includes the database name as well as the DB host on which the database runs. Native SQL language commands for administrating several database connections Only Native SQL interfaces allow you to set up additional database connections and access non-R/3 databases. However, all Open SQL commands will continue to be transferred to the default connection, that is, to the R/3 database. Commands for opening and closing a database connection, as well as setting it up, have been added to native SQL. If a new database connection is opened, a new DB transaction is started automatically on this connection. This transaction is independent of the transaction currently running on the R/3 default connection, that is, the transaction running there is not closed and all subsequent Open SQL commands will continue to be processed in this transaction (and not in the transaction started on the new DB connection). However, all subsequent Native SQL commands will be executed on the newly opened connection. No synchronization is carried out by the system between the transactions running on the different connections, that is, a 2-phase commit log is NOT supported.

Page 3 from 6

323151 SAP Note

Jan 12, 2009

11 Version

Opening a database connection EXEC SQL. CONNECT TO <con_name> [ AS <alias_name> ] ENDEXEC. This command opens the database connection designated by <con_name>. In this context, <con_name> is a user-defined logical name, under which all the information required to open the database connection such as the user name, password, database name and so on must be stored in the table DBCON. You can specify <con_name> as a string-literal or as a variable in the form :<var>. If the addition "AS <alias_name>" is entered, the open database connection is managed with the indicated alias name. If no alias name is entered, <con_name> is used as the name for the open connection. If you enter several alias names, you can open several connections to the same database simultaneously by using the same login name. This alias arrangement allows the execution of independent transactions on the same logical database connection (see example 2). The alias name can also be entered as a literal or as a variable. If no entry is found in the table DBCON for the indicated connection name <con_name>, an ABAP runtime error occurs. If, on the other hand, the setup connection fails due to a DB error, for example, because the database in question is not online, then SY-SUBRC = 4 is set and the ABAP program assumes control. After the connection has been successfully set up (SY-SUBRC = 0), the connection that was just opened automatically becomes the active connection, that is, from this point on, all subsequent Native SQL commands (and only these) will access the database associated with this connection. However, all subsequent Open SQL accesses still refer to the default connection. Nevertheless, the physical connection remains. If the connection is not closed explicitly with a "DISCONNECT" command (see below), it remains open until it is closed implicitly when the corresponding internal session is closed (the term "internal session" is explained in the section on "Data area and Modularization Unit Organization" in the ABAP online documentation). After that, you can only access this connection if it has been opened again explicitly with a new "CONNECT" command. Comments If a database connection that was opened using a "CONNECT" command is not explicitly closed again with a "DISCONNECT" command, it is invalidated (this means that it will have to be opened again with a "CONNECT" for further accesses) when the internal session is closed, but the physical connection remains in place. The advantage of this is that it will not have to be set up again if a new "CONNECT" is executed. However, "reusing" a connection that was previously opened only works if there are no open transactions on this connection, that is, if the last transaction on this connection was completed using COMMIT or ROLLBACK. Setting the active database connection EXEC SQL. SET CONNECTION { <alias_name> | DEFAULT } ENDEXEC. This command sets the data base connection <alias_name> that has been opened to active. This means that all subsequent Native SQL statements are executed on this connection. If a transaction is still open on the previous connection, it remains open and can then be

Page 4 from 6

323151 SAP Note

Jan 12, 2009

11 Version

continued by reverting to this connection. You can also enter the keyword DEFAULT as an alias name. This causes the R/3 default connection to become the active connection again. Again, <alias_name> can also be entered directly as a literal or as a variable value in form of :<var>. However, the entered alias name must be known from a preceding "CONNECT" statement (that is, a statement executed within the same internal mode).If the database connection was opened without explicitly specifying an alias name, the connection name <con_name> must be used in the "SET" statement as the <alias_name>. If there is no opened connection for the entered connection name, SY-SUBRC = 4 is set. Closing a database connection EXEC SQL. DISCONNECT <alias_name> ENDEXEC. The "DISCONNECT" command explicitly closes a database connection opened under the <alias_name> name. As of Basis Release 6.20: The transaction running on this link is rolled back. Up to 6.20, the physical link is retained if the transaction running on this link was not completed by COMMIT/ROLLBACK. After a "DISCONNECT", you can no longer access this link until it is reopened explicitly by a "CONNECT". If the closed connection was also the connection that was just active, then the R/3 default connection is automatically the active connection again after that. The "DISCONNECT" command also closes the physical connection to the database. If you are setting up a new connection, you must first re-establish this physical connection, which may be a rather time-consuming operation since the corresponding system resources will have to be requested on the client side as well as on the database server side. Therefore, you should only explicitly close a connection with the "DISCONNECT" command if the operations executed on this connection are only running occasionally. However, if you want to use this connection all the time, it should remain open. If the <alias_name> does not correspond to any previously opened DB connection, an ABAP runtime error occurs. Example 1: Accessing remote DB in a subroutine FORM remote_access USING con_name LIKE dbcon-con_name. * This form performs some database operations on connection 'con_name'. * The form does not require that the caller has already opened this * connection nor does it require that 'con_name' is "active". After * the form has finished the "default connection" is active * Test if connection 'con_name' has already been opened EXEC SQL. SET CONNECTION :con_name ENDEXEC. IF SY-SUBRC <> 0. * Connection not yet opened. EXEC SQL. CONNECT TO :con_name

Page 5 from 6

323151 SAP Note

Jan 12, 2009

11 Version

ENDEXEC. IF SY-SUBRC <> 0. * error handling ENDIF. ENDIF. * Do something on connection 'con_name' EXEC SQL. ... ENDEXEC. * Commit the changes on 'con_name' EXEC SQL. COMMIT ENDEXEC. * Reset to "default connection" EXEC SQL. SET CONNECTION DEFAULT ENDEXEC. ENDFORM. Example 2: Implementing independent transactions DATA con_name LIKE dbcon-con_name VALUE 'ABC'. * Open a connection C1 to a database identified by the logical name * 'ABC'. This implicitly starts a transaction T1. EXEC SQL. CONNECT TO :con_name AS 'C1' ENDEXEC. IF SY-SUBRC <> 0. * error handling ENDIF. * Do some database operations on connection C1 EXEC SQL. ... ENDEXEC. * Open a second connection C2 to the same database 'ABC'. This * implicitly starts transaction T2 which became the "active" one. * Transaction T1 still remains open. EXEC SQL. CONNECT TO :con_name AS 'C2' ENDEXEC. IF SY-SUBRC <> 0. * error handling ENDIF. * Do some operations on connection C2 EXEC SQL. ...

Page 6 from 6

323151 SAP Note

Jan 12, 2009

11 Version

ENDEXEC. * Commit transaction T2. This commit does not have any effect on * transaction T1. EXEC SQL. COMMIT ENDEXEC. * Resume transaction T1 on connection C1 EXEC SQL. SET CONNECTION 'C1' ENDEXEC. EXEC SQL. ... ENDEXEC. * Commit transaction T1 on connection C1 EXEC SQL. COMMIT ENDEXEC. * Reset to the default connection EXEC SQL. SET CONNECTION DEFAULT ENDEXEC. Repairs in the Code

Valid Releases:

Software Component

Release

SAP Application

45B

SAP Basis component

610

46C

46B

46D

620

640