Outsystems and DB2 on System I, ISeries, AS/400

Outsystems and DB2 on System I, ISeries, AS/400

  
Hi, I'm new to outsystems and was looking for some information on connecting to DB2 on the Iseries/SystemI/As400.

I've searched the boards, but there never really seems to be anyone who has it working and it willing to share.

We have a huge amount of business logic being powered on the platform and I'm looking to give the system a facelift without having to rewrite everything. 

So if you have tips, suggestions, horror stories it would give me some ideas what I'm getting myself into.

Eamonn
Hi Eamonn,

In order to connect your OutSystems app to your AS400 database, you can setup a DB link from MS SQL Server to AS400. Then, you have two alternatives for accessing the data (see below).

Instructions below assume you have a SQL Server / .Net installation, but they can be adapted to different settings.

Setup the linked server

First, go to SQL Server, and setup a linked server to the AS400 database. You may have to setup a specific DB provider - get help from your DB Admin if needed. See also Paulo Silva's message on this topic:
http://www.outsystems.com/NetworkForums/ViewTopic.aspx?Topic=Hub-Server-supported-platforms

Once this is done, you have two options for accessing the data.

A. Import entities

A1. Go to IS, click File > Import > Import Entities from Database...
A2. Search and select the tables to import.
A3. Publish the extension.
A4. In ServiceStudio (SS), use Add/Remove References to select the entities from the extension you have just published. You can now use them in queries.

B. Use Openquery

B1. Go to IS, and create an action for retrieving your data (ex: GetData); add the relevant input / output parameters.
B2. Click Edit Source Code .NET to open Visual Studio. Add code for accessing the data - use Openquery:
OPENQUERY ( linked_server ,'query' )
  More info here: http://msdn.microsoft.com/en-us/library/ms188427.aspx
B3. Publish the extension.
B4. In ServiceStudio (SS), use Add/Remove References to select the actions from the extension you have just published.
B5. Repeat for other tables, adding new actions to your API...

What option is the best?

Solution A is more friendly, because you can use the referenced entities in your queries, as if they were local entities.
Solution B uses an API (actions) instead. You won't see the entities in SS, but... this solution is potentially much better in terms of performance.

In the end, you may want to follow a mixed approach.
  • Use option A for small tables, where performance is not an issue.
  • Use option B when you need to retrieve lots of data and option A has performance issues.

Hope this helps.

Cheers,
Paulo Ramos
Just wanted to add another option for connecting to a DB2 database... this Forge component does it through a IBM DLL (IBM.Data.DB2.iSeries):

http://www.outsystems.com/forge/384/db2-iseries-connector/

Hi Paulo,

We're running into issues with the Linked Server approach when trying to UPDATE records in the DB2 database. using Adv.Query implementations. Any idea of how to overcome this (besides resorting to OPENQUERY)?

Thanks!
Hi Pedro,

From what I remember we've used an API (via WS) for modifying data in DB2 (I never tried doing it through direct Sql statements). Is this an option for you? If not, I'd suggest you try the connector available in the Forge.
Unfortunately, both API (via WS) and DB2 Connector approaches are not a option due to a legacy migration agenda. Customer has already a migration planned from DB2 to SQL Server in a few months. So using Integration Studio Import Entities would be the perfect match here: we would just have to remap the entities by then.
Great! This answer seems to have resolved the issue.