Question
Scott Roth · Dec 7, 2020

Linked Table vs Connecting to outside DB using JDBC and a Business Process (BPL)

I have a case where I need to look up the NPI provider against an External MS SQL database to retrieve our Provider Identifier to send to a downstream system. In the past I would use a Business Process (BPL) to connect to the outside MS SQL via JDBC and get that information for me. But I was thinking instead of creating a BPL process to do this, would it be better just to link to the outside MS SQL database table to retrieve the information in a SQL statement within a DTL?

So it is better to link to an outside database via a Linked Table wizard? or Use a BPL to retrieve the necessary data requirement?

What is the best practice to use?

Thanks

Scott

Product version: Ensemble 2018.1
$ZV: Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U)
00
1 0 3 145
Log in or sign up to continue

It depends. Essentially Interoperability Productions take care of:

  • Parallelization
  • Queues / Async
  • Error management, mitigation, and recovery
  • After-error investigation (Visual Trace / Audit)
  • Unified overview of integration flows

For each integration or part of an integration you need to decide if you need these features and usually you  do. In that case all you need to do is to develop one or more Business Hosts containing the business logic and as long as they conform to Interoperability Production structure you would automatically get all the above mentioned benefits.

You pay for the convenience with the overhead for messages and queues.

In the cases where some (most) of these conditions are true:

  • external system (whatever it is) is reliable - downtime is a scheduled rarity
  • external system does not throw errors much
  • response time is stable and in the sync realm
  • interaction with the system is one simple flow
  • integration is extremely highload

You can choose to interface more directly.

Furthermore it's not Interoperability/no Interoperability, but rather a scale of how much you expose as Interoperability Hosts. In your example maybe having only a BO is enough and you can forego the BP?

I figured querying a local table through an SQL call in the DTL would cut down on the amount of development that is needed. I created a view with only 2 columns of the data set and wrote a custom function so my other team members could use it. So far so good.

Great Answer!