cancel
Showing results for 
Search instead for 
Did you mean: 

POC on JDBC adapter capabilities

Former Member
0 Kudos

We have a customer requesting us to accomplish a Proof of Concept work with PI 7.1 SP08. The customer came up with a predefined scenario, more like a flowchart, and asked if PI is capable of doing the scenario according to steps just like documented below. Although we warned them about their u201Cnot that goodu201D design decisions, like they dont want to use stored procedures, and conceptual misunderstandings like rollbacking a committed transaction, they want us to make use of PI in every possible operation. Please make comments on the scenario described below, is it possible to do it with PI? Is it possible to do it with PI only using BPM not pure java or abap coding (java or abap proxy on PI machine) ?


 


    - In the AS400 Database the TSLSPEC table will be checked in certain intervals for records with the field AENDIK=1


    - After records are read, the AENDIK field is set to u201C2u201D.


        For each record that has been read:


          + Insert the records taken from TSLSPEC into TSSpecification table in Oracle DB (10g)


          + At the same time read the detail records which belong to the Master records from TSLBOMM table in AS400 (Example: Master A has 3 details)


          + For every record taken from TSLBOMM check with a table in Oracle (INTRACK) if the record exists. If the records exist insert the detail into Billofmetarial table of Oracle.


          + If the record does not exist ROLLBACK (db rollback, not deleting a row) all detail records inserted before and the master data that belongs to the details inserted at the beginning.


    - Depending on the success or failure update(make them 3) AENDIK field of related master data in AS400-TSLSPEC


 


For example:


   1. AS400-TSLSPEC has 3 Master records: A, B, C -> These will be inserted into Oracle-Specification


   2. For each Master record, the detail records will be selected from AS400-TSLBOMM.


  Like; Master record A has 3 details, 1,2,3


           Master record B has 2 details, 1,2


           Master record C has 5 details, 1,2,3,4,5


   3. Every detail will first be checked with a table in Oracle-INTRACK and then inserted.


 


      Example;


      Master A: Detail 1 -> Does this record exist in Oracle-INTRACK ? Yes! Then insert it into Oracle-Billofmetarial


      Master A: Detail 2 -> Does this record exist in Oracle-INTRACK ? Yes! Then insert it into Oracle-Billofmetarial


      Master A: Detail 3 -> Does this record exist in Oracle-INTRACK ? NO! Then u201CROLLBACKu201D this record, the other 2 Detail records and the corresponding Master record which has been inserted before. (db rollback, not deleting)


 


   4. Set AENDIK field of Master A to initial value(=1)


   5. Go on like this with other master and detail records.


 


I really appreciate any of your comments on the topic. However, please do not suggest other designs like using &quot;stored procedure&quot;, we also suggested to use it but the customer insists on to see the transaction managing capabilities (explicit commit / rollback etc. ) of SAP PI.</span></p> <p class=MsoNormal><span lang=tr style='font-family:"Times New Roman"; language:tr'> </span></p> <p class=MsoNormal><span lang=tr style='font-family:"Times New Roman"; language:tr'>Thanks and Regards,</span></p> <p class=MsoNormal><span lang=tr style='font-family:"Times New Roman"; language:tr'> </span></p> <p class=MsoNormal><span lang=tr style='language:tr'>-Tarik    </span></p> </div> <![if !vml]></td> </tr> </table>

Edited by: Tarik Acikgoz on Sep 15, 2009 11:27 AM

Edited by: Tarik Acikgoz on Sep 15, 2009 11:41 AM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

I think you can achieve most of the functionality with the use of DBlookup and would make this scenario more easy.

Use below kind of desgin for the interface



Sender JDBC Channel (TSLSPEC)  ---> In Mapping --->DBLookup (TSLBOMM) ------->  DBLookup(INTRACK ) -->if fail don't insert
                                                                                To get all the details.                    Verify the data. 
                                                                   
 

With using DBLokup you may achieve your purpose. Even no need to use any Rollback for the instered data that will be validated in mapping only.

Thanks

Swarup

Former Member
0 Kudos

Hi Swarup,

Thank you but we have to do it just like they say without any change in the flow.

May be I could not explain my problem very well. Let me ask the core parts:

a. Is it possible to configure auto commit time (even to "never implicitly") of Oracle 10g for specific tables?

b. Is it possible to give explicit commit or rollback orders for recently successfully inserted records ?

c. Is 'b' possible in a time range of say 2-3 seconds and for records on diffrent tables (master/detail)?

d. Is 'b' and 'c' possible with PI 7.1 SP08 on Oracle 10g using BPM?

Regards

-Tarik

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

I am not able to see your further comments...may be some problem with post...

But I think you don't wish to change the design i.e. Rollback option right??

I doubt this RollBack feature can be achieved in between the mapping.

Thanks

Swarup

Former Member
0 Kudos

Repost:

Hi Swarup,

Thank you but we have to do it just like they say without any change in the flow.

May be I could not explain my problem very well. Let me ask the core parts:

a. Is it possible to configure auto commit time (even to "never implicitly") of Oracle 10g for specific tables?

b. Is it possible to give explicit commit or rollback orders for recently successfully inserted records ?

c. Is 'b' possible in a time range of say 2-3 seconds and for records on diffrent tables (master/detail)?

d. Is 'b' and 'c' possible with PI 7.1 SP08 on Oracle 10g using BPM?

Regards

-Tarik

Former Member
0 Kudos

Any ideas?

Regards,

-Tarik