Creating Database Management Systems as Source Systems 

Use

With DB Connect you have the option of opening extra database connections in addition to the SAP default connection. You use these connections during extraction to BI to access databases and transfer data into a BI system. To do this, you have to create a database source system in which the connection data is specified and made known to the ABAP runtime environment. The connection data is used to identify the source database and authenticate the database.

Prerequisites

●      You have made the following settings in the Implementation Guide (IMG) under SAP NetWeaver → Business Intelligence → Connections to Source Systems:

○       General connection settings

○       Perform automatic workflow customizing

●      As a rule, system changes are not permitted in productive systems. Connecting a system to BI as a source system, or connecting BI to a new source system, represents a change to the system. Therefore, you have to ensure that in the clients of the BI system that are affected, the following changes are permitted during the source system connection.

○       Cross-client Customizing and repository changes

In the Implementation Guide (IMG) under SAP NetWeaver  → Business Intelligence  → Links to Source Systems  → General Connection Settings → Assign Logical System to Client, select the relevant clients and choose Goto  →  Details. In the Cross-Client Object Changes field, choose the Changes to Repository and Cross-Client Customizing Allowed option.

○       Changes to the local developments andBusiness Information Warehouse software components

You use transaction SE03 (Organizer Tools) to set the change options. Choose Organizer Tools →Administration → Set Up System Change Option. Choose Execute. On the next screen, make the following settings:.

○       Changes to the customer name range.

Again, you use transaction SE03 to set the change option for the customer name range.

○       Changes to BI namespaces /BIC/ and /BI0/

Again, use transaction SE03 to set the changeability of the BI namespace.

●      If the source DBMS and BI DBMS are different:

○       You have installed the database-specific DB client software on your BI application server. You can get information about the database-specific DB client from the respective database manufacturers.

○       You have installed the database-specific DBSL on your BI application server.

●      In the database system, you have created a username and password that you want to use for the connection.

See Database Users and Database Schemas.

Procedure

Before you can open a database connection, all the connection data that is used to identify the source database and authenticate the database has to be made known to the ABAP runtime environment. For this, you need to specify the connection data for each of the database connections that you want to set up in addition to the SAP default connection.

...

       1.      In the source system tree in the Data Warehousing Workbench, choose Create in the context menu of the DB Connect folder.

       2.      On the following screen, specify the logical system name (= DB connection) and a descriptive text for the source system. Choose  Continue.

The Change “Description of Database Connection” View: Detail screen appears.

       3.      Select the database management system (DBMS) that you want to use to manage the database. This entry determines the database platform for the connection.

       4.      Under User Name, specify the database user under whose name you want the connection to be opened.

       5.      When establishing the connection, enter the user DB Password twice for authentication by the database. This password is encrypted and stored.

       6.      Under Connection Info, specify the technical information required to open the database connection. This information, which is needed when you establish a connection using NATIVE SQL, depends on the database platform and encompasses the database names and the database host on which the database runs. he string informs the client library of the database to which you want to establish the connection.

Connection information that depends on the database platform

Supported Database	CON_ENV Connection Information
SAP DB (ada) or MaxDB (dbs)	<server_name>-<db_name>
Microsoft SQL Server (mss)	MSSQL_SERVER=<server_name> MSSQL_DBNAME=<db_name> MSSQL_SERVER=10.17.34.80 MSSQL_DBNAME=Northwind (See SAP Note 178949 - MSSQL: Database MultiConnect with EXEC SQL)
Oracle (ora)	TNS Alias (See SAP Note 339092 - DB-MultiConnect with Oracle as a secondary database)
DB2/390 (db2)	PORT=4730;SAPSYSTEMNAME=D6B;SSID=D6B0;SAPSYSTEM=71;SAPDBHOST=ihsapfc; ICLILIBRARY=/usr/sap/D6D/SYS/exe/run/ibmiclic.o The parameters describe the target system for the connection (see installation handbook DB2/390). The individual parameters (PORT=... SAPSYSTEMNAME=...   .....) must be separated with ' ' , ',' or ';'. (See SAP Note 160484 - DB2/390: Database MultiConnect with EXEC SQL)
DB2/400 (db4)	<parameter_1>=<value_1>;...;<parameter_n>=<value_n>; You can specify the following parameters: ●      AS4_HOST: Host name for the Remote DB Server. You have to enter the host name in the same format as is used under TCP/IP or OptiConnect, according the connection type you are using. You have to specify the AS4_HOST parameter. ●      AS4_DB_LIBRARY: Library that the DB server job needs to use as the current library on the remote DB server. You have to enter parameter AS4_DB_LIBRARY. ●      AS4_CON_TYPE: Connection type; permitted values are OPTICONNECT and SOCKETS. SOCKETS means that a connection is used using TCP/IP sockets. Parameter AS4_CON_TYPE is optional. If you do not enter a value for this parameter, the system uses connection type SOCKETS. For a connection to the remote DB server as0001 on the RMTLIB library using TCP/IP sockets, you have to enter: AS4_HOST=as0001;AS4_DB_LIBRARY=RMTLIB;AS4_CON_TYPE=SOCKETS; The syntax must be exactly as described above. You cannot have any additional blank spaces between the entries and each entry has to end with a semicolon. Only the optional parameter AS4_CON_TYPE=SOCKETS can be omitted. (See SAP Note 146624 - AS/400: Database MultiConnect with EXEC SQL) (For DB MultiConnect from Windows AS to iSeries, see Note 445872)
DB2 UDB (db6)	DB6_DB_NAME=<db_name> , where <db_name> is the name of the DB2 UDB database on which you want to run Connect. You want to establish a connection to the ‘mydb’ database. Enter DB6_DB_NAME=mydb as the connection information. (See SAP Note 200164 - DB6: Database MultiConnect with EXEC SQL)

       7.      Specify whether your database connection needs to be permanent or not.

If you set this indicator, losing an open database connection (for example due to a breakdown in the database itself or in the database connection [network]) has a negative impact.

Regardless of whether this indicator is set, the SAP work process tries to reinstate the lost connection. If this fails, the system responds as follows:

a.       The database connection is not permanent, which means that the indicator is not set:

The system ignores the connection failure and starts the requested transaction. However, if this transaction accesses the connection that is no longer available, the transaction terminates.

b.       The database connection is permanent, which means that the indicator is set:

After the connection terminates for the first time, each transaction is checked to see if the connection can be reinstated. If this is not possible, the transaction is not started – independently of whether the current transaction would access this special connection or not. The SAP system can only be used again once all the permanent DB connections have been reestablished.

We recommend setting the indicator if an open DB connection is essential or if it is accessed often.

       8.      Save your entry and go back.

       9.      The Change “Description of Database Connections” View: Overview screen appears. The system displays the entry for your database connection in the table.

   10.      Go back.

Result

You have created IDoc basic types, port descriptions, and partner agreements. When you use the destinations that you have created, the ALE settings that enable a BI system to communicate with a database source system are created in BI in the background. In addition, the BI settings for the new connection are created in the BI system and the access paths from the BI system to the database are stored.

You have now successfully created a connection to a database source system. The system displays the corresponding entry in the source system tree. You can now create DataSources for this source system.