Multiple table integration with JDBC

[Former Member]

I have to update data from ECC to MS SQL

There are around thirty five tables in MS SQL table.

Some tables have around 70 to 80 fields.

I don't have to do mapping, just take the data from ECC and post to SQL, so mapping is simple

Table A in SQL has seventy fields. It's the receiver. ECC system has to fetch data from six or seven tables. Proxy will be used at ECC sytem

Like that Table B in SQL has fifty off fields

Should I configure this as thirty five different interfaces? Can I use same communication channel for each?

Is it possible to update two or more tables at the receiver SQL side?

What is the best approach that I should follow here?

[Ryan-Crosby · Accepted solution]

Hi Midhun,

Yes, you should be able to use one JDBC receiver channel for one database.  As far as the interfaces you might think about one or more interfaces logically grouped with the appropriate number of operations as per your requirements. 

Regards,

Ryan Crosby

[Former Member · Accepted solution]

I have thirty to forty database tables to be updated.

all are ECC to SQL scenarios

My question is should I need to create fory interfaces?

Or should I try to update multiple database tables in single interface?

what is the best approach

[suman_saha · Accepted solution]

Hi,

Its better if you can go for stored procedure.

Otherwise you can use multimapping or can use multiple statements in a single data type creating nodes like statement1, statement2 etc and under the statement, yoy can give action, table, key etc.

Regards,

Suman

[iaki_vila]

Hi Midhun,

My first question is if it is a period task or you only need to do one time. If it is not a periodic task you should skip PI development and the DB Team do Export/Import tasks between the two DBs tables.

I think to have all in one interface and as many interfaces as tables are not a good ideas. You can try to think an intermedium approach and to group on the same interfaces the similar tables, similar thinking on the data or in the business process.

Hope this helps.

Regards.

[vishnu_pallamreddy]

Hi Mithun,

Its good to go for Stored Procedure.

SQL programmers have an option to club tables in SQL based on some key fields.

in ECC also we options like inner joins r for all entries options to fetch data from multiple tables based key fields.

If you use same receiver channel for all interfaces its lead to performance issue.

[former_member183908]

the best option is to go with stored procedure which will take care of updating all the 35 tables and fields.If your proxy code fetches all the relevant data which needs to update all 35 tables in SQL in a single shot then go for the single interface in which PI calls SP.

If you create multiple interfaces if your DB is same you can make use of one single receiver JDBC channel however again it depends on the data load.

Thanks

[Former Member]

Pavan

We have around five hundred fields in the ms sql table.

I think it would be difficult to pull five hundred fields from ECC. there are so many tables in the SAP table and I have to fetch data from fields in each of the tables so the coding will be tough

I was thinkning of creating seperate interfaces for tabless with seventy fields and forty fifty fields

For tables with two or less fields I will join them in an interface

there are five tables with single fields

I will club them using multiple statemants in a single ECC to JDBC scenaro

[former_member183908]

agree with you Midhun then its better to go with the multiple interfaces and as i mentioned earlier if it is a single DB you can make use of single receiver JDBC channel.Good Luck and update this thread once you finalize and done with your design.

Thanks

[former_member184619]

Going for separate interface would be a good idea. Exception and error handling would be easy in longer run.

Regards,

Sachin

[Former Member]

I have used around thirty one interfaces.

we used one receiver jdbc ommunication channel for around 15 interfaces and another receiver jdbc com. channel for sixteen interfaces.

I am now only confusd with one thing. should i use more ccs.

is it appropriate to use one receiver com channel for fifteen interfaces

[former_member183908]

is it appropriate to use one receiver com channel for fifteen interfaces

I don't think this will be a problem using one receiver channel for 15 interfaces.creating separate channel for each interface might cause performance issue and all 15 interfaces pointing to one DB then 1 channel will be fine however some times tracking of the messages will be little bit tough if all 15 interfaces runs simultaneously since each interface will have different messageID in your receiver channel

Thanks

[iaki_vila]

Hi Midhun,

AFAIK PI has a maximum number of threads for adapter type and not for channel, therefore to share the same channel should not be a problem.

The only problem could be if the channel have any problem and it end hung for any problem then all the scenarios that share the channel will be hung.

In my experience, i have one time one case that i needed to take a blob field with a PDF, sometimes the PDF was so huge that the channel ended hung, then i decided to use the channel only with this scenario.

In my opinion, i would take an intermediate solution, grouping the scenarios relationated logically in the same channel, avoiding to share the channel with different business scenarios.

Regards.