Skip to Content

Extracting data from an AS400 with multimember tables using XI

This weblog explains how to extract data from multimember tables on an AS400 through a DB2 database using an ODBC driver.

The general idea is to use the JDBC Adapter with the IBM “iSeries Access ODBC Driver” in order to have full access to de AS400 DB2 multimember table without worrying about its internal structure. A multimember table can be seen as a normal table with several subtables called members, each member is identified by a string.

The architecture of the scenario is shown on the following image

image


Tasks to be completed:

  

  • Install the “iSeries Access ODBC Driver” or similar driver
  •   
  • Configure ODBC connection parameters
  •   
  • Create an alias for each member
  •   
  • Configure the JDBC-ODBC bridge in XI
  •   
  • Configure the XI scenario like any other data polling application

Installing the ODBC won’t be covered in this weblog. I’ll just give you some insights of the parameters that need to be set. In the configuration tab don’t miss the Data source name and the destination IP. In the server configuration tab set the default library and the “SQL naming convention” from the dropdown menu.

image

In order to connect from XI will need to prepare the AS400 table members using aliases. They can be created using your database administration software, or however you want. To create the alias from java use the following code as template

import java.net.*; import java.sql.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class TestODBC { public static void main(String[] arg) throws Exception { Connection con; Statement st; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); } catch (ClassNotFoundException cnfe) { System.err.println ("Unable to load database driver. " + cnfe); System.exit(0); } con = DriverManager.getConnection("jdbc:odbc:DBModelo","UserName","PassWord"); st = con.createStatement(); st.execute("CREATE ALIAS AliasName FOR LibraryName.TableName(MemberName)"); } }

Finally we’ll use the ODBC-JDBC bridge “sun.jdbc.odbc.JdbcOdbcDriver” which is by default available. The DBName parameter in the connection string refers to the ODBC data source that was specified in the ODBC configuration tool.

image

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Anonymous
    Hi Javier –

    Thanks for this blog.

    In the past, during my business connector days, I have had issues with “iSeries Access ODBC Driver” mainly on performance. I had better experience by using JTOpen (http://jt400.sourceforge.net/) which come with powerful drivers as well as set of java classes for AS400 emulation.

    It was easy for me to build services in BC using these classes but in XI, I think I should build a small adapter.

    Did you happen to check how the performance was with “iSeries Access ODBC Driver”. May be you can try to send huge IDOCs and measure the performance.

    Thanks
    KK

    (0) 

Leave a Reply