JDBC 2 JDBC

SAP-XI-JDBC 2 JDBC Senario


SCENARIO

Suppose, you want read data from a database say Access or SQL by hitting a simple SELECT query and insert it into the Receiver system which is again a database. May be in real world this is not a real scenario where you are going to read from database and write it back to the database. But I am trying to use the JDBC Adapter in both the sides as SENDER and RECEIVER to make you aware of how this procedure works!


Create the Products, Software Components, Technical System and Business system with the following information given below. If you dont know how to create this check this "Working with SLD".

PRODUCT
Vendor = www.sapplace.com
Name = pro_jdbc_2_jdbc
Version = 1.0

SOFTWARE COMPONENT
Product = pro_jdbc_2_jdbc, 1.0 of www.sapplace.com
Vendor = www.sapplace.com
Name = swcv_jdbc_2_jdbc
Version = 1.0

These Configuration are for 3rd Party Systems:

TECHNICAL SYSTEM
Type = Third-Party
System Name = ts_jdbc_sender
Host Name = starxi
Product = pro_jdbc_2_jdbc, 1.0 of www.sapplace.com
Software Component(s) = swcv_jdbc_2_jdbc, 1.0 of www.sapplace.com

Type = Third-Party
System Name = ts_jdbc_receiver
Host Name = starxi
Product = pro_jdbc_2_jdbc, 1.0 of www.sapplace.com
Software Component(s) = swcv_jdbc_2_jdbc, 1.0 of www.sapplace.com

BUSINESS SYSTEM
Name = bs_jdbc_sender
Type = Third-Party
Technical System = ts_jdbc_sender on starxi
Logical System Name = LEG005
Check the Product = pro_jdbc_2_jdbc, 1.0 of www.sapplace.com
Check the SWCV = swcv_jdbc_2_jdbc, 1.0 of www.sapplace.com
Integration Server = SXI

Name = bs_jdbc_receiver
Type = Third-Party
Technical System = ts_jdbc_receiver on starxi
Logical System Name = LEG006
Check the Product = pro_jdbc_2_jdbc, 1.0 of www.sapplace.com
Check the SWCV = swcv_jdbc_2_jdbc, 1.0 of www.sapplace.com
Integration Server = SXI

Integration Repository

  • Click to open the "Integration Repository" if you are using the XI for the first time this will take some loading time.
  • You will be shown an Integration Builder: Design Screen.
  • First step is to import the Software Component which you have created in the "System Landscape Directory"

  • Select you Software Component from the list shown and click on import button. Done importing - click on "Exit"
  • Now you can see the Software Component on the Left hand side with no objects in it.
    Double click your Software Component, a page will open up in the right hand side. Can you see it?
    Now everything will be in Disable mode to make it in Enable mode click in Edit or Change Icon.
  • Just enter the Namespace.

  • If you are not able to see the Namespace it will in the minimize mode. Just click on "Namespace" and then click on Cap button.
  • This will maximize the Namespace box.
  • Press save icon Can only create thumbnails for attached images
  • Can you see this on the left hand side?
  • Now we are going to create Two Data Types.
  • Right Click on Data types and click on "New".
  • Enter the details as per shown and press "Create" Button.
  • Create the Sender Data Type (dt_sender) which is some things like this.
  • In the Same way create one more Receiver Data Type(dt_receiver):

  • Now we need to create two Messages Types one for Sender (mt_sender) and other for Receiver (mt_receiver). In order to create anything new just right click on the object on the left side and select "New".

I will just give you the inputs screen please create it by your self.

  • We are done with Message Types. Now we need to create two Interface Sender (mi_sender and mi_receiver). Just check the inputs from here.
  • It's the time to create Interface now. Say "New" on the Message Interface on the left hand side

  • Where we are?
    - We have Created two Data Types (dt_sender and dt_receiver)
    - Then we have created two Message Types (mt_sender and mt_receiver)
    - Last we have made 2 Interface (mi_sender and mi_receiver)
    My Screen says this. What about urs?
  • Same output! Let's move ahead.
  • Now its time to map the fields of Sender Message type and Receiver Message Type. This can be done by creating a Message Mapping Program. A "Graphical Mapping" which maps the field of Sender to Receiver.
  • Here are the inputs for the Message Mapping Screen. Press "Create"
  • You can drag and drop the Message Types in the corresponding boxes shown as Black. Drag mt_sender Message Type and drop it in the Black Box as shown
  • Same way drag and drop the mt_receiver Message type on the Target Box.
  • Now it little tricky here to map the fields. Please pay attention to what I am trying to explain you? Let see clearly that what are the fields that need to mapped first.
  • Before doing the mapping its really very important to understand why we are doing this and what happens if we do what I am going to say to do?
  • If you observe closely the Receiver data type, the format is default one which you need to follow in order to insert into the database? Did I said Insert then what about "UPDATE" or "DELETE". How XI will know what operation to do.
    Lets examine the fields one by one:
    action (attribute) - This bascially tells you the type of operation as we are doing INSERT this fields need to filled with default value "INSERT". So, the action value needs to be "INSERT"
    TABLE - It is clearly understood that we need to provide the name of the table in which we are going to insert the data into. "" needs to be provided to this field.
    access - needs to be default and all the other sub fields need to present in the database.
  • Lets start the mapping. We are going to use Data Flow Editor. First field to map is action. Just double click it and check the Data Flow Editor.
  • Next click on the Functions drop-down. Click to open it and select "Constants" from the option displayed.
  • Once you select it now you can apply the defined methods that are available in that area.
  • What are we trying to do? We are trying to assign a default value to fields using some constants. Why? Because these fields are interpreted as SQL statements. Click on "Constant" and you can see the "empty" tag is inserted in the data-flow editor.
  • Double click on the empty field and a box will popup just enter the value "INSERT"
  • Now you need drag a line from insert to action which will create a mapping to the field action.
  • Some interesting happens when you click this. Check this out!

  • Okay we have mapped action field. Now we need to map TABLE to the default value means again a constant value which will be a table in the reciever database.
  • Double click on the constant and enter this.
  • Map the other fields just by dragging and dropping on the corresponding fields. Please ensure that mapping fields are like this. How do you see this? >> Right Click on the fields >> Dependencies >> Show All
  • There is one more option to check the text view of the mapping. Click on text view on the tool bar.

  • Click on the Test Tab and check the inputs can compare with this output.
  • Okay we have done with the Mapping program between the messages but we need to create mapping between the Interfaces even.
    So, Select the Interface Mapping and say ?New?. Fill the details and Press ?Create?.
  • Now this is tricky. Here you can drag and drop the Interfaces on the white text boxes or you can use the help button to select the interface.
  • Once this is done click on Read Interface button below . This will add the interface in the below box and then u can select the message mapping program for these interface.
  • Now its testing time before saying yes we have done everything correct in the Designing Part. Click on the ?Test? tab in the same Interface Mapping Screen.
  • Enter the values and press "Execute" button on the same screen
  • If you want to add one more record to the left hand side row just right click on the row and say
  • Fill the values like this
  • When you press Execute button then the output should be something like this.
  • Does it works? If Yes wow! This is what I can see? and
    The message box says:

Messages:
10:38:43 Start of test
Compilation of mm_mapping successful
Executed successfully
10:38:44 End of test

  • Here is the list from my XI Tool. Now its time to activate. This is something like results time after you write the exam but here best thing is you can see the errors but in real exam result we can feel the beatings?
  • To activate click on the "Change List" tab
  • Expand your Component and Do it by yourself!
  • Don't think now. Just Press Activate.
  • Hurray ! My first design is Successful.

Integration Directory

  • Close the Integration Builder:Design and move onto the Configuration part. Open the XI Startup screen. Click on "Integration Directory"
  • I am here now into the Integration Directory now right click on the left side box and select "New". It's okay that Scenario is selected.
  • Just fill the values and press "Create". Then you need to save it in order to see the Configuration Scenario on the left hand side.
  • Fine! Spit on your hand for some more work.
  • Next step is to import the Business System? Right click the Business system and select "Assign Business System" this will opens up a wizard.
  • First screen is Introduction (Sorry no theory as I promised) just say "Continue" button and then it will ask for Party - Sorry no Party say "Continue" button. Select the Business system and press "Finish" button.
  • Please make sure that the "Create Communication Channels Automatically" option is unselected.
  • Next step Right Click on the Communicate Channel and say "New".
  • Give a name to the communication Channel.
  • Now configure your Sender Communicate channel. First Click on the Help button to select the Adapters - Select "Jdbc"
  • Update the details as per shown below:

Parameters:

  • sun.jdbc.odbc.JdbcOdbcDriver
    jdbc:odbc:Driver=
    Unknown macro: {Microsoft Access Driver (*.mdb)}

    ;DBQ=C:\XI_FILES\SENDER\Student_Database.mdb
    SELECT * FROM STUDENT WHERE FLAG='TRUE'
    mt_sender
    http://jdbc_2_jdbc
    UPDATE STUDENT SET FLAG='FALSE' WHERE FLAG='TRUE'

    • Are you thinking what I am thinking? Yes we need to create the database. For our training purpose we will use Access. Here are the steps to create the Database
      Step 1: Goto the folder and create a Access File

    • Open the database and create a new table in Design View
    • Enter the fields name like this:
    • When u save it. This is ask you for a Database name enter as "STUDENT".
    • No Primary Keys
    • I am done what about u?
    • Enter some default value and save the table
    • Create the Receiver Communication Channel:
    • Yes you need to again create one more database table in the receiver folder. I think now u know how to create receiver database. Do it!
    • Fields are like this
    • Table Name: LIBRARY and no Primary Key

    • Back to our communication channel configuration:

    Parameters:

    sun.jdbc.odbc.JdbcOdbcDriver
    jdbc:odbc:Driver=

    ;DBQ=C:\XI_FILES\RECEIVER\LIBRARY_DETAILS.mdb

  • Done! Next is "Receiver Determination"
  • Did I told u that XI makes our job easier then here it is the proof. Just use the help button to select the Service means your Business system and in the Interface box it will present you the correct interface just select. Before closing, match the following input as below.
  • You know what you are doing here? We are trying to say to XI that this is the
    Sender Business system, Sender Interface, Sender Namespace (This is the key)
    And to this sender information we need to map the Receiver Business system. In order to do that select the Help option from the "Service" option in "Configured Receivers"
  • Now, we need to create the Interface Determination:
  • Fill as it has shown below.
  • This interface Determination will ask for Inbound Interface and also for Interface Mapping. Just use help button to select it. Always save when it is done.
  • Next Sender Agreement
  • Nothing special we do here. We just attach the Communication channel to the Sender Key (Sender Business system, Sender Interface, Sender Namespace)

  • Now same way create Receiver Agreement.

  • My Screen says this:
  • Activate the scenarios same steps as we have done in the Design part. You need to move to "Change List" tab in order to see the activate option.

  • DONE!

Now we need to just wait and check both the database. You will find the sender database fields FLAG will become from TRUE to FALSE (check the update command in the communication channel) and receiver will have all the records.

Sender Data Before

After:

Receiver Database:

  • How to debug if anything went wrong? Login into the XI server and type the TCode "SXMB_MONI" this will take u to the message monitoring screen. Just select the first option then press "Execute". One more screen opens up Just Press "Execute" again.

  • Some of you get the success message in the "SXMB_MONI" transaction but no clue why the file has not been generated in the Receiver Folder. There is one more place where you can check the errors. Yes you are right! "Runtime WorkBench".
  • Click on "Component Monitoring" >> "Display button" >> "Adapter Engine startxi" >> "Adapter Monitoring button"
  • Click on the "JDBC" Adapter if it is Green then we are safe or try to read the errors that is been displayed.

SAP Developer Network SAP Weblogs: SAP Process Integration (PI)

Loading...