| Author |
Message
|
| Bichu |
 Posted: Fri Jul 29, 2016 6:50 am Post subject: Is IIB a good option to integrate between 2 DBs |
 |
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
Hello,
I am pretty amateur in broker interaction with DB. Is IIB a good option to integrate a database with another remote database. Requirement is to fetch few rows and update it into another database resides in another server. The initial fetching will be in the range of hundreds of thousands afterwhich it will be in thousands range.
When I checked, I could see DB triggers and other server stuffs serving this purpose. But my client suggests to use IIB.
Your voices please. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Fri Jul 29, 2016 6:57 am Post subject: Re: Is IIB a good option to integrate between 2 DBs |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Bichu wrote: |
| Requirement is to fetch few rows and update it into another database resides in another server. |
If it's a straight "lift and shift" with no filtering, transformation or other capabilities of IIB in play then there are cheaper, easier and potentially more efficient ways of doing this. Even if you don't go DB, other ETL tools are probably a better fit.
Even if you end up using IIB, consider doing the initial large load with something else.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Bichu |
| Posted: Fri Jul 29, 2016 7:01 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
| Thanks Vitor. So is there a limit on size of the DB rows that broker can handle in a single time[/quote] |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 29, 2016 7:22 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Bichu wrote: |
| Thanks Vitor. So is there a limit on size of the DB rows that broker can handle in a single time |
In the same way there's a limit on the number of DB rows any solution can handle at a single time. "DB triggers and server stuffs" have limits.
My point was that for the larger initial load it will be much more efficient, cheaper and easier to use another tool in the same way it will be more efficient, cheaper and easier for the smaller load. If the cost/benefit case to use IIB stacks up for the regular loads, it may well not do so for the larger one. The cost, difficulties and efficiency change with the numbers involved.
That was my point.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 29, 2016 7:26 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
To put it plainly:
- IIB has the technological capability to do what you're describing
- It's a waste to use something as expensive, feature rich and complex as IIB to do this
- Other tools may well do a better job
- I wouldn't do this with IIB (and this site doesn't use IIB for straight database replication, a decision which I was no part of)
- In the end, you have to make the decision for your site because only you know all the intricacies of your individual requirement
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Bichu |
| Posted: Fri Jul 29, 2016 8:10 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
Many Thanks Vitor. That was some keypoints to look on. 
I plan to implement the first DB replications via a SFTP. For remaining, I will build a MF to act as an IIB service.
But I still wonder what value adds my new service is going to provide from an IIB perspective  |
|
| Back to top |
|
|
| harish_td |
| Posted: Mon Aug 01, 2016 4:58 am Post subject: Re: Is IIB a good option to integrate between 2 DBs |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
|
| Back to top |
|
|
| Bichu |
| Posted: Mon Aug 01, 2016 5:24 am Post subject: Re: Is IIB a good option to integrate between 2 DBs |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
Thanks Harish. Will go through this.
Mine is Oracle. |
|
| Back to top |
|
|
| adubya |
| Posted: Mon Aug 01, 2016 5:33 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Mon Aug 08, 2016 2:59 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
if using oracle why not use a database link and a set of stored procedures/triggers/database jobs to transfer rows from one database to remote database ?
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| Bichu |
| Posted: Mon Aug 08, 2016 3:20 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
| ruimadaleno wrote: |
| if using oracle why not use a database link and a set of stored procedures/triggers/database jobs to transfer rows from one database to remote database ? |
I don't have privileges nor the admins allow to alter the schema or to write SP in the database from which I should fetch. Its a secured one. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 08, 2016 5:35 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Bichu wrote: |
| I don't have privileges nor the admins allow to alter the schema |
That's going to effectively eliminate using the DatabaseInput node, which puts you in the position of some kind of scheduled flow with an open ended select statement in it. This has two problems:
- it's a scheduled flow
- you've got an open ended select statement in it
At best, you're reinventing a wheel which other ETL tools provide as standard. At worst, this is not going to work.
I find the position of the admins that "this is a secured database" to be slightly dubious. Are you claiming that they never allow any changes to the schema nor any new SP at all? This must seriously limit the functionality of the database if application programmers are denied these two abilities when they're developing code.
It sounds to me more like they're using "security" as code for "we don't want to change anything in case the magic stops working". You can have a database of highly sensitive data subject to the most stringent security controls and still have processes to modify it.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| ruimadaleno |
| Posted: Mon Aug 08, 2016 6:20 am Post subject: |
|
|
Master
Joined: 08 May 2014
Posts: 274
|
Oracle database has specialized tools for this job, please don't use IIB to execute this kind of data loading ... you are using the wrong tool for the job
_________________
Best regards
Rui Madaleno |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 08, 2016 6:34 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ruimadaleno wrote: |
| Oracle database has specialized tools for this job, please don't use IIB to execute this kind of data loading ... you are using the wrong tool for the job |
I stand by all of my previous comments.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Bichu |
| Posted: Mon Aug 08, 2016 6:51 am Post subject: |
|
|
Centurion
Joined: 16 Oct 2011
Posts: 124
Location: London
|
Many Thanks all for your comments.
I have put forward client another option of using OMG - Oracle Messaging Gateway in conjunction with MQ.
Hopefully client might approve this instead of IIB. |
|
| Back to top |
|
|
|
|
