Skip to Content
Introduction

In this scenario I want to depict how to process data communication between databases in SAP XI/PI. The data can 

be fetched from source db and insert these data into target db. Then the response data from target db is transferred 

back to the source db with updating the related data.

The sender transportation is asychronized and the receiver is synchronized. Here I use BPM to make the data communication 

looks like synchronized. The data can be processed at real time.

Create DB Objects

1. Create source and target table.

In this scenario I use one database with two tables to simulate the sender and receiver. The source table is JDBC_SENDER, 

the target table is JDBC_RECEIVER. Their tables’ structure is as following.

create table JDBC_SENDER

(

  ID          NUMBER, 

  RECORD      VARCHAR2(100),

  IS_FETCHED  NUMBER(1), — 0 is unfetched, 1 is fetched

  OASIS_ID    NUMBER — it gets from target table JDBC_RECEIVER

)

create table JDBC_RECEIVER

(

  OASIS_ID NUMBER, — auto generated by sequence id

  ID       NUMBER, — get from source table

  RECORD   VARCHAR2(100) — get from source table

)

I insert 2 sample records into the source table and leave the empty in the target table.

So now the scenario is looks like below.

  • Fetch records (ID,RECORD) from source table JDBC_SENDER. If it success, the value in field IS_FETCHED is updated to 1 (1 means the data fetch is successful, 0 is inverse).
  • Insert these records (ID,RECORD) into target table JDBC_RECEIVER and the value in field OASIS_ID is auto generated by sequence id.
  • If the above inserting succeed, the value in field OASIS_ID will be updated in source table JDBC_SENDER exactly.

Then I create a procedure to insert data into target table. There are 3 parameters.

  • P_ID (input output parameter)
  • P_RECORD (input)
  • P_OASIS_ID (output)

The procedure’s source code is as following.

CREATE OR REPLACE PROCEDURE jdbc_operation(p_id       IN OUT jdbc_sender.id%TYPE,

                                           p_record   jdbc_sender.RECORD%TYPE,

                                           p_oasis_id OUT jdbc_receiver.oasis_id%TYPE) AS

  current_id NUMBER;

BEGIN

  SELECT seq1.NEXTVAL INTO current_id FROM dual;

  INSERT INTO jdbc_receiver VALUES (current_id, p_id, p_record);

  p_oasis_id := current_id;

END;

 

Maybe now you want to ask why we can’t insert data directly by sql statement. Because I can designate the values 

which I want to return after inserting succeed. We can regard these values as response data.

 

Create IR Objects 

1. Data Type Objects

The sender request:

 

 

The sender response:

 

 

The receiver request:

 

The receiver response:

 

 

2. Message Type Objects

The sender request:

 

The sender response:

 

The receiver request:

 

 

The receiver response:

 

 

3. Message Interface Objects

a. Sender asyn request: INF420590_MI_JDBC_SENDER_Request

 

 

b. Sender Abstract request: INF420590_AI_JDBC_Sender_Request

 

c. Sender asyn response: INF420590_MI_JDBC_Sender_Response

 

d. Sender abstract response: INF420590_AI_JDBC_Sender_Response

 

e. Sender syn abstract interface: INF420590_AI_JDBC_Sender_Syn

 

f. Reveiver syn interface: INF420590_MI_JDBC_Receiver

 

 

4. Message Mapping

INF420590_MM_BPM_to_JDBC:

 

 

 

INF420590_MM_JDBC_to_BPM: the message mapping from receiver db to BPM.

 

 

 

5. Interface Mapping

We only need one interface mapping as shown below.

INF420590_IM_BPM_to_JDBC

6. Integration Process

INF420590_IP_DB_to_DB

Then please activate all above objects.

 

Confirgure ID

  1. Create a sender business service with two communication channel.

One channel is to fetch data from source db.

The document name and namespace is the sender message type name and namespace.

  

Another channel is to update the source data with the response information.

2. Create receiver business service with the receiver communication channel.

3. Create 3 receiver determinations.

  • Source DB -> BPM

  • BPM <-> Target DB

  • BPM -> Source DB

4. Create 3 interface determinations.

  • Source DB -> BPM

  • BPM <-> Target DB

  • BPM -> Source DB

5. Create 1 sender agreement for source DB to BPM.

6. Create 2 receiver agreements.

  • BPM -> Target DB

  • BPM -> Source DB

Then activate all objects.

 

Testing

  1. The original data in source DB and target DB.

2. After processing, the data is inserted into the target DB and the OASIS_ID genereated in targetDB is updated in source DB.

 

Everything is done. Congratulations! Now you have learned the basis of BPM and DB data processing.
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. david min Post author
    During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.
    (0) 

Leave a Reply