connecting sap bi to db2 via dbc v1.0 (1)

IBM Americas Advanced Technical Support

IIBBMM SSAAPP TTeecchhnniiccaall BBrriieeff

AAcccceessssiinngg aa DDBB22 ddaattaa wwaarreehhoouussee uussiinngg DDBB CCoonnnneecctt iinn SSAAPP BBII 77..00

MMaarrkk GGoorrddoonn

IIBBMM SSoolluuttiioonnss AAddvvaanncceedd TTeecchhnniiccaall SSuuppppoorrtt

VVeerrssiioonn:: 11..00 DDaattee:: DDeecceemmbbeerr 2200,, 22000077

© Copyright IBM Corp, 2007 All rights reserved



1. Acknowledgements ....................................................................................................................................... 3

2. Disclaimers..................................................................................................................................................... 3

3. Trademarks ................................................................................................................................................... 3

4. Version Changes............................................................................................................................................ 3

5. Feedback ........................................................................................................................................................ 3

6. Introduction................................................................................................................................................... 4

7. Overview of Configuration Steps ................................................................................................................ 4

7.1. Configure DB2 Client ............................................................................................................................. 5 7.1.1. Gather required configuration information:.................................................................................... 5

7.2. Create SAP Logical System .................................................................................................................. 11

7.3. Create Source System ........................................................................................................................... 13

7.4. Create Views in DB2 data warehouse system....................................................................................... 15

7.5. Create Datasource ................................................................................................................................ 19 7.5.1. Error in Datasource field mapping................................................................................................ 24

7.6. Create Virtual Provider ........................................................................................................................ 25

7.7. Create Transformation.......................................................................................................................... 27

7.8. Create Data Transfer Process .............................................................................................................. 30

7.9. Test Reading from the Virtual Provider................................................................................................ 32

7.10. Other Actions To Complete The Virtual Provider Access ................................................................ 34

8. SAP Performance monitoring on DB Connect connection ..................................................................... 35

9. Summary...................................................................................................................................................... 37


1. Acknowledgements Thank you to Damir Rubic for advice and guidance on installing and configuring components of SAP ®. Thank you to Karl Fleckenstein and Joachim Rese for their advice and guidance on SAP BI with DB2 ®. Thank you to Steven Poon for his review and many suggestions for improving the paper.

2. Disclaimers This paper demonstrates a method to use SAP DB connect and Virtual Providers to query data in a remote DB2 system from an SAP Netweaver ® BI 7.0 system. The scenarios demonstrated worked on our test environment:

• BI System – BI 7.0 SPS13 with ABAP, JAVA, EP, EP CORE, and BI JAVA usage types installed on DB2 9 FP2 on SLES10

• DB2 external system – DB2 9 FP3 on SLES 10, using the SAMPLE database. The configuration process for your SAP, DB2, and operating system combination may be different. IBM has not formally reviewed this paper, and while effort has been made to verify the information, this paper may contain errors. IBM makes no warranties or representations with respect to the content hereof and specifically disclaims any implied warranties of merchantability or fitness for any particular purpose. IBM assumes no responsibility for any errors that may appear in this document. The information contained in this document is subject to change without any notice. IBM reserves the right to make any such changes without obligation to notify any person of such revision or changes. IBM makes no commitment to keep the information contained herein up to date.

3. Trademarks AIX ® is a registered trademark of IBM Corporation. Oracle ® is a registered trademark of ORACLE Corporation. DB2 ® is a registered trademark of IBM Corporation. DB2 Connect is a trademark of IBM Corporation. SAP ® is a registered trademark of SAP AG. Netweaver ® is a registered trademark of SAP AG.

4. Version Changes Version 1.0: November 2, 2007 – initial version

5. Feedback Please send comments or suggestions for changes to [email protected].


mailto:[email protected]


6. Introduction The goal of this paper is to draw together all the steps needed to configure a Virtual Provider in SAP to access external DB2 data. SAP documentation covers most of the SAP-related configuration issues, so this paper duplicates some of the information in the SAP help documents. However, the SAP help documents to not link the SAP activities together with the DB2 configuration, and they also pass over the wrinkles discussed later in this paper, such as incorrect field mappings, that can cause problems in configuring a DB connect Virtual Provider on an external DB2 system. SAP Netweaver BI users can initiate queries that are executed outside SAP (e.g. on an external DB2 data warehouse) using a BI “Virtual Provider” that is linked to a table or view in the external system. This virtual provider capability might be used in various scenarios, such as:

• Drill down from SAP queries on summarized data in SAP BI to detailed data in the external warehouse • Ad-hoc access to large tables in data warehouse, so the data does not have to be duplicated in the

warehouse and in SAP BI • Access to real-time data in an external database.

The connection to the external database can be done using either the SAP “DB connect” or SAP “UD connect”.

• SAP “DB connect” with DB2 uses the DB2 client to access the remote DB2 system from the SAP ABAP instance.

• SAP “UD connect” with DB2 uses the DB2 JDBC Type 4 connector, and accesses the remote DB2 system using both ABAP and Java instances of SAP

This paper describes defining an SAP virtual provider using SAP DB connect, so that end-users can run queries on the SAP BI system that are executed on the external DB2 system.

7. Overview of Configuration Steps To access an external database using DB connect, the SAP DBSL for the external database must be installed on each SAP application server. Since the BI system being used in this scenario uses the DB2 database on Linux, the DBSL and DB2 Client (which are required on the application server for DB connect) are already installed. See SAP note 200164 regarding installing DBSL. In this paper, we will:

• Configure the DB2 client to catalog the external data warehouse system – section 7.1. • Create an SAP Logical System to identify the external warehouse in SAP – section 7.2. • Create a Source System in SAP that links SAP to the DB2 data warehouse database – section 7.3. • Create Views (if necessary) on tables in DB2 data warehouse – section 7.4. • Create a DataSource for each table or view that will be used in the external DB2 system – section 7.5 • Create a Virtual Provider to represent the external table as a star schema object – section 7.6 • Create Transformation to link the Virtual Provider to the DataSource – section 7.7 • Create data transfer process to specify that the remote DB2 system is queried when a query references

the virtual provider – section 7.8 Here is the overview of our sample configuration:



• SAP SID is BPA • Hostname of sap central system (DB and CI) is linux00 • DB2 database of external warehouse – SAMPLE • Hostname of system where external warehouse is located – db2test1

7.1. Configure DB2 Client Perform this step on each SAP application server that will connect to the external DB2 data warehouse.

Since this example uses a SAP system running on DB2, the DB2 client is already installed on all the application servers. The “db2ca”command is run from the db2<sid> userid, on our test system, db2bpa.

7.1.1. Gather required configuration information: • Database name of DB2 data warehouse • Instance name of the DB2 data warehouse • Hostname where DB2 data warehouse is located • Port name and number of the DB2 connect port on the DB2 data warehouse • Username and Password of user with authorization to access the DB2 data warehouse tables

In this example, the information we need is:

• Database name – SAMPLE • Instance name of warehouse – db2inst1 • Hostname of warehouse – db2test1 • Port name and number of DB2 Connect on warehouse – db2c_db2inst1 / 50001 • Username/Password of DB2 user – db2inst1/password

Start db2ca on the system running the SAP instance (linux00, in this example):

Figure 1: db2ca with SAP system defined

Select: Selected > Add Database Using Wizard



Figure 2: db2ca add database wizard

Click Next to continue.

Figure 3: db2ca select communications protocol

TCP/IP should be selected. Click Next to continue.



On the following screen, enter the hostname of the remote DB2 data warehouse system, and the port name and number for DB2 connection to the remote system (section 7.1.1).

Figure 4: db2ca specify tcp/ip parameters

This modifies the /etc/services file on the system (the SAP application server) where you are running db2ca, and adds the named service. Click Next to continue. On the following screen, enter the database name of the data warehouse (Section 7.1.1)

Figure 5: db2ca specify database name



Click Next to continue. On the next screen, enter the instance name for the DB2 data warehouse (section 7.1.1).

Figure 6: db2ca specify node options

Click Next to continue. On the following screen, enter the hostname of the DB2 warehouse system.

Figure 7: db2ca specify system options

Click Next to continue.



On the following screen, select Server Authentication.

Figure 8: db2ca specify security options

Click Finish.

Figure 9: db2ca test connection

Click Test Connection to check the connection to the DB2 warehouse.



On the following screen, enter userid and password with authorization to read DB2 tables on the data warehouse (section 7.1.1)

Figure 10: db2ca user and password for test

Click Test Connection.

Figure 11: db2ca successful test

If the connection is successful, you will see the screen above. If it is not successful, check the parameters used in configuring the connection. Click Cancel to finish.



Figure 12: db2ca after definition of remote data warehouse system

Now, we know that the DB2 data warehouse system can be reached via the DB2 client from the SAP application server. After subsequent steps (DataSource, Virtual Provider) we will test the layered configuration works.

7.2. Create SAP Logical System Now that the SAMPLE database has been configured using db2ca, we will use the SAP GUI to do SAP configuration steps. The SAP logical system uniquely identifies the DB2 Instance/Database that SAP will connect to. Each Instance/Database combination requires a unique logical system name. Run transaction SPRO, then select SAP Reference IMG > SAP Netweaver > Links to Other Source Systems > General Connection Settings > Define Logical System.

Figure 13: Add Logical System

Click “New Entries”.



Add an identifier for the DB2 Instance/Database.

Figure 14: Create Logical System

Click Save. The logical system name will be used in subsequent configuration.



7.3. Create Source System Create the SAP Source System for DB Connect, and enter the connection parameters. This will link the SAP logical system with the DB2 database that was configured with db2ca in section 7.1. Transaction RSA1 > Source Systems > Right click on “DB connect”.

Figure 15: RSA1 Create DB Connect Source System

Select the Logical System Name created in section 7.2.

Figure 16: RSA1 Create DB Connect Source System - select logical system



On the next screen, enter the userid, password, and database name from section 7.1.1. Note that the database name is entered with the parameter “DB6_DB_NAME=”.

Figure 17: RSA1 Create DB Connect Source System - enter connection parameters

Click Save, then Back and Back. The DB Connect Source System is now defined.

Figure 18: RSA1 DB Connect Source System

If connection parameters are not correct, SAP will give an error message when you go back and back from the screen at Figure 17. If this step succeeds, you know that SAP can connect to the data warehouse and login as the DB2 user.



7.4. Create Views in DB2 data warehouse system For certain data types, SAP uses formats for DATE, TIME, and TIMESTAMP that are different from the format used by databases. For each database type (DB2, Oracle, etc) there are SAP notes that describe the specific differences between the SAP representation and the DB representation of the data type. For DB2, SAP note 523622 describes the differences between SAP and DB2 representation, and it describes how to convert a database table column (using a view) so that the rows selected by DB connect will be compatible with the SAP data types. In this example, we will convert DATE from the DB2 four character format to the SAP 8 character format. We use DB2 control center (the db2cc command) to display the structure of the table that will be connected to a virtual provider. The table EMPPROJACT has two date columns – we will use it with an SAP Virtual Provider.

Figure 19: EMPPROJACT Table

Select Views in the left panel.



Figure 20: db2cc create view

Select Create New View.



We create the view on EMPPROJACT, and in the view do the conversions of the two DATE columns. The view is called VEMPPROJACT_DC. The view name will be used to define the datasource in section 7.5.

Figure 21: EMPPROJACT - VEMPPROJACT_DC with DATE conversions



Next, select Views and VEMPPROJACT_DC to display the view, and note the length of the two view columns EMASDTCH (from EMSTDATE) and EMENDTCH (from EMENDATE) – they are now 8.

Figure 22: EMPPROJACT view with DATE conversions



7.5. Create Datasource Creating the Datasource maps the DB2 data warehouse table and columns to characteristics and key figures in SAP. On this step, one needs to ensure that the mapping has the correct information (field types and lengths) in order to retrieve useful data from the DB2 data warehouse.

Run RSA1 and select DataSources > right click on Unassigned Nodes to list actions

Figure 23: RSA1 Create DataSource

The source system is the Logical System defined in section 7.2.



Figure 24: RSA1 Create DataSource - name and source system

Enter the description.

Figure 25: RSA1 DataSource General info

Specify the view or table, and specify that direct access is allowed, as it will be needed later for Virtual Provider configuration. Select the Extraction tab.



Figure 26: RSA1 DataSource Extraction

Select Proposal tab. Now, carefully check the proposal. Note that RSA1 has proposed incorrect field types and lengths for EMASTDCH and EMENDTCH. If we accept the proposal, the data will be incorrect when it is retrieved by the SAP queries.

Figure 27: RSA1 DataSource Proposal with incorrect fields



Compare the field lengths and types with the information displayed in Figure 22, and then make the necessary changes.

Figure 28: RSA1 DataSource with corrected fields

Select Fields tab.

Figure 29: RSA1 DataSource Fields

On the Fields tab, we add the InfoObjects – they will be blank at first. This maps the DB2 column to an SAP field. The InfoObjects can be SAP standard business content, or can be custom. To demonstrate both in this example, we created the Z* InfoObjects with RSD1 (not shown in this paper), and mapped the DB2



column EMASTDTCH (which is created in the view from the table column EMSTDATE) to the SAP standard 0CALDAY Select DataSource > Check, then Save, then Activate. Select Preview tab, and “Read Preview Data”.

Figure 30: RSA1 DataSource Preview

Verify that all columns are correct. Once this step succeeds, you have correctly mapped the fields between DB2 and SAP, and tested that you can retrieve the rows and convert them to SAP format.



7.5.1. Error in Datasource field mapping As mentioned in section 7.5, if the DataSource mapping is not correct, then the data will be corrupted in SAP. Here is an example where we did not correct the RSA1 proposal for VEMPPROJACT_DC.

Figure 31: RSA1 DataSource with incorrect field mapping



7.6. Create Virtual Provider To an end user, a Virtual Provider looks like an SAP star-schema InfoCube, but the data being queried actually resides on a remote system, and SAP converts the query on the Virtual Provider into SQL that is sent to the remote system.

In this example, we will convert VEMPPROJACT_DC into a very simple Virtual Provider Cube, with three characteristics and one key figure.

RSA1 > InfoProvider – right click on Unassigned Nodes to see list of actions.

Figure 32: RSA1 Virtual Provider



Give the InfoCube a name and description, and specify that it is based on Data Transfer Process for Direct Access.

Figure 33: RSA1 Virtual Provider - DTP for DA

SAP creates a template for the Virtual Provider. Drag the characteristics from the 3rd panel into their dimensions in the rightmost panel, and the key figure to the Key Figures Area.

Figure 34: RSA1 Virtual Provider - populate characteristics and key figures

Select InfoCube > Check, then Save, then Activate



7.7. Create Transformation The SAP transformation maps the DataSource fields to InfoCube (Virtual Provider) fields.

Figure 35: RSA1 Create Transformation

On the following screen, enter the DataSource and Source System to link the InfoCube to the DataSource.

Figure 36: RSA1 Create Transformation - specify parameters



SAP Makes a proposal for the transformation.

Figure 37: RSA1 Create Transformation – Proposal

But only three of the four fields that we need in the Virtual Provider are mapped. In Figure 37, right click on 0CALDAY and select Rule Details.

Figure 38: RSA1 Create Transformation Rule Details

Click Add Source Fields



Select the source field

Figure 39: RSA1 Create Transformation Select Source Field

Click Copy.

Figure 40: RSA1 Create Transformation Rule Details

Click Transfer Values.



Figure 41: RSA1 Create Transformation - corrected mapping

Last, Select Transformation > Check, then Save, then Activate. Now, the three characteristics (0CALDAY, ZPROJNO, ZEMPNO) and the key figure (ZEMTIME) are mapped to the virtual provider.

7.8. Create Data Transfer Process Last, create a Data Transfer Process, which specifies how the data gets to the InfoProvider.

Figure 42: RSA1 Create DTP



On the next screen, enter the name of the DataSource from section 7.5 and the source system on which it is defined (section 7.2).

Figure 43: RSA1 Create DTP – parameters

Click Continue.

Figure 44: RSA1 Create DTP from Database Table

Select the Execute tab.



Figure 45: RSA1 Create DTP finish

Select Data Transfer Process > Check, then Save and Activate. Now, we have performed all the steps needed to run a query against the InfoCube Z_EPA_DC.

7.9. Test Reading from the Virtual Provider As a quick test, we will use the LISTCUBE transaction to retrieve data from the virtual provider.

Figure 46: LISTCUBE Transaction

Click Execute to go to the following screen.



Specify one of the EMPNO values from Figure 30, so that we do not retrieve the entire table from DB2.

Figure 47: LISTCUBE parameter entry

Click Execute.

Figure 48: LISTCUBE data message

Click Continue.



Figure 49: LISTCUBE result

Success, we have defined a Virtual Provider, and retrieved data from an external DB2 System.

7.10. Other Actions To Complete The Virtual Provider Access LISTCUBE can be used to query a virtual provider when none of the provider’s characteristic master data has been loaded in SAP. But in order to run queries, with BEX or the Portal Web interface, master data for all the Virtual Provider characteristics must be loaded into the BI system. Processes must be put in place to periodically upload and activate new master data. In addition, queries or web templates need to be defined, so that end-users can report on the virtual provider. These steps are outside the scope of this paper.



8. SAP Performance monitoring on DB Connect connection Since DB Connect uses the DBSL, SQL being executed on the remote data warehouse system can be traced in SAP as it is being sent to DB2. But, the SQL cannot be explained in SAP, unlike normal ST05 trace processing.

Figure 50: ST05 trace of DB connect SQL

Select the PREPARE line of the statement, and Click Explain.

Figure 51: ST05 display SQL sent to data warehouse



STAD transaction is used to display historical performance statistics for dialog steps. Here, we display an individual statistics record for one dialog step of LISTCUBE transaction.

Figure 52: STAT records break out DB time by connection

DEFAULT is the connection to the SAP DB2 database, and DCDB2TEST1 (the logical system from section 7.2 again) is the connection to the remote DB2 data warehouse system. We can see with this how much DB time is spent in the call to the DB2 data warehouse, and how much DB time is for calls to the local database.



9. Summary SAP Virtual Providers can provide a straightforward way to access tables in a DB2 data warehouse from the SAP query front-ends (BEX or Portal), when there are large volumes of data in the warehouse that are not duplicated in SAP. The transformation of the data between systems is not automatic; it requires analysis of the fields in DB2 tables, and may require additional views in DB2 in order to transform the DB2 fields into a format that matches SAP conventions.



Figure 1: db2ca with SAP system defined.............................................................................................................. 5 Figure 2: db2ca add database wizard ...................................................................................................................... 6 Figure 3: db2ca select communications protocol.................................................................................................... 6 Figure 4: db2ca specify tcp/ip parameters .............................................................................................................. 7 Figure 5: db2ca specify database name .................................................................................................................. 7 Figure 6: db2ca specify node options ..................................................................................................................... 8 Figure 7: db2ca specify system options .................................................................................................................. 8 Figure 8: db2ca specify security options ................................................................................................................ 9 Figure 9: db2ca test connection .............................................................................................................................. 9 Figure 10: db2ca user and password for test......................................................................................................... 10 Figure 11: db2ca successful test ........................................................................................................................... 10 Figure 12: db2ca after definition of remote data warehouse system .................................................................... 11 Figure 13: Add Logical System ............................................................................................................................ 11 Figure 14: Create Logical System......................................................................................................................... 12 Figure 15: RSA1 Create DB Connect Source System.......................................................................................... 13 Figure 16: RSA1 Create DB Connect Source System - select logical system...................................................... 13 Figure 17: RSA1 Create DB Connect Source System - enter connection parameters.......................................... 14 Figure 18: RSA1 DB Connect Source System ..................................................................................................... 14 Figure 19: EMPPROJACT Table ......................................................................................................................... 15 Figure 20: db2cc create view ................................................................................................................................ 16 Figure 21: EMPPROJACT - VEMPPROJACT_DC with DATE conversions .................................................... 17 Figure 22: EMPPROJACT view with DATE conversions................................................................................... 18 Figure 23: RSA1 Create DataSource .................................................................................................................... 19 Figure 24: RSA1 Create DataSource - name and source system.......................................................................... 20 Figure 25: RSA1 DataSource General info .......................................................................................................... 20 Figure 26: RSA1 DataSource Extraction.............................................................................................................. 21 Figure 27: RSA1 DataSource Proposal with incorrect fields ............................................................................... 21 Figure 28: RSA1 DataSource with corrected fields.............................................................................................. 22 Figure 29: RSA1 DataSource Fields..................................................................................................................... 22 Figure 30: RSA1 DataSource Preview ................................................................................................................. 23 Figure 31: RSA1 DataSource with incorrect field mapping................................................................................. 24 Figure 32: RSA1 Virtual Provider ........................................................................................................................ 25 Figure 33: RSA1 Virtual Provider - DTP for DA................................................................................................. 26 Figure 34: RSA1 Virtual Provider - populate characteristics and key figures ..................................................... 26 Figure 35: RSA1 Create Transformation.............................................................................................................. 27 Figure 36: RSA1 Create Transformation - specify parameters ............................................................................ 27 Figure 37: RSA1 Create Transformation – Proposal............................................................................................ 28 Figure 38: RSA1 Create Transformation Rule Details ......................................................................................... 28 Figure 39: RSA1 Create Transformation Select Source Field.............................................................................. 29 Figure 40: RSA1 Create Transformation Rule Details ......................................................................................... 29 Figure 41: RSA1 Create Transformation - corrected mapping............................................................................. 30 Figure 42: RSA1 Create DTP ............................................................................................................................... 30 Figure 43: RSA1 Create DTP – parameters.......................................................................................................... 31 Figure 44: RSA1 Create DTP from Database Table............................................................................................. 31 Figure 45: RSA1 Create DTP finish ..................................................................................................................... 32




Figure 46: LISTCUBE Transaction...................................................................................................................... 32 Figure 47: LISTCUBE parameter entry................................................................................................................ 33 Figure 48: LISTCUBE data message.................................................................................................................... 33 Figure 49: LISTCUBE result ................................................................................................................................ 34 Figure 50: ST05 trace of DB connect SQL........................................................................................................... 35 Figure 51: ST05 display SQL sent to data warehouse.......................................................................................... 35 Figure 52: STAT records break out DB time by connection ................................................................................ 36

connecting sap bi to db2 via dbc v1.0 (1)

Documents