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

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/