DB Table handling dynamically

[Former Member]

Hi,

I am going to handle the scenario Proxy(SAP) to JDBC (MES) , this interface is mainly using for the SAP response interface.Here i am having 40 Database tables at JDBC receiver side, I can't able to create 40 interfaces for this, because source and target structure is same for all 40 tables. based on the one field i want to send the data to database table.

Now what i have done is created one Source Structure as per requirement and in Target side i created the structure and added all 40 tables at Target side, now its structure is very big.

Please let me know how can i handle all these tables dynamically ...

Regards,

Pasi.

[baskar_gopalakrishnan2]

>>Please let me know how can i handle all these tables dynamically ...

Since you mentioned structure is same for all the 40 tables. I would recommend you to create one source and one target.

During mapping decide what would be table name and pass that table name to target jdbc table name field.

You can keep access node and key node pretty much same and action is insert or whatever you need. only changing the table name based on the incoming input from the source.

[stefan_grube]

if the structure is the same, then you can simply fill the field table with the table name field. Then you can use the same structure.

[Former Member]

I quote Stefan if you have to insert/update only one table per time.

I don't understand if you have to insert in ALL tables togheter or in 1 of 40tables per time.

[Former Member]

Hi,

I want to handle this interface for all 40 interfaces, my requirement is i am having the interfaces like GR, GI, Byproduct,SLAB,Rolled Result, etc. i will get the data for each interface how can i know to which table i need to send the data for using the stored procedure.

Here i am handling this is created the 40 Interfaces structrue with 40 tables at target side. based on the interface code i am identifying the data for which table it has to go and related to which interface..

i am looking for simple procedure for this, i don't want to create whole structure at target side , i want to handle this interface dynamically...can u please suggest !!!

Regards,

Pasi.

[maciej_jarecki]

Hi Passi,

Your flow looks rather complicated. i You don not want to implement this logic in PI, do like that.

In target system create Stored procedure with parameter as string. In mapping pass to this parameter whole source xml message by chceking on source root element "return as xml". Now PI will call SP on Db.

I propose you this solution because it will be much easier to implement this login on DB side

Kind Regards

Maciej

[Former Member]

Hi Pasi,

First of all, if you have 40 different tables on the target DB with different table structures, you have to create 40 target structures in PI whether in one message type or 40 message types.

The mostly suggested way to pass the data as it is to the Target DB via a stored proc call, and let the DB PL/SQL developer take all the pain to implement the logic to identify and distribute the data in 40 tables within the stored proc. That way the PI developer's life becomes easier! But to me that's not a good design!

Reasons being:

1) Whether the data has correct conditions (to identify a table or interface) or not, PI passes the data to DB. No mechanism to track and monitor such scenario in PI.

2) From PI monitoring point you can never distiguish between instances of data sent. So if there is a question from functional team, "we sent some data for GL, did you receive that?" - you have no answer unless you go and check in DB or start checking the payload of each individual message.

3) The load of processing business logic on data would be totally on DB, which is not at all recommended.

4) The stored procedure would be large in terms of input params, and code lines and hard to maintain. Any small changes to any of the interface logic would need changes to be done on the DB. And the DB being the single processing module, any changes would put the other interface logic at stake.

There are many other points to be considered and from all perspective it wouldn't be suggested.

If you dont want to have a single message type with all 40 structures attached to it, create 40 different message types, 40 different message mappings, 40 interface mappings and then a conditional Interface determination and call mappings conditionally.This way it would be easy to maintain individual structures and mappings in PI.

Regards.

Suddha

[Former Member]

I think is better to use a Oracle stored procedure, to insert data in all table, and then call it by receiver JDBC channel passing all fields.

[Former Member]

Hi Pasi,

I think that's a correct approach with a separte statement node on target structure for each of the 40 tables and then doing a selective conditional mapping, except the mapping would be too long to handle.

The other approach could be using Enhanced Interface determination and using seperate mappings for each of the tables, but to me that would need more manual effort!

Choice is yours!

Regards,

Suddhasatta