There should be two options available.
1. Connect your BDC to the AS400 system by creating an ODBC connection on your SharePoint servers and then specifying the ODBC DSN in the connection details.
2. On the SQL Server link  create a linked server to the AS400 system and then connect the BDC to the SQL server instead.

I tried option 1 but I couldn’t get this to work.

So here are the steps for option 2:

  1. install the iSeries drivers on the SQL server first. There are different version of the DB2 driver. make sure that you use the iSeries and not the Client version.

  2. Then create a DSN configuration

DSN Configuration

Two System DSNs must be configured on the SQL Server to be used by the application. These DSN will reference an ODBC driver supplied by IBM for the iSeries servers.

The first DSN will be used to reference data on the AS400. The second DSN will be used to update data on the AS400. (This is for performance only).

DSN Configuration Settings:

Driver Name: “Client Access ODBC Driver (32-bit)”

DSN Setup – Tab = “General”

Name: <>

Description: Leave default

System: <>

DSN Setup – Tab = “Server”

Update settings (All others leave default):

Library List: <>

Connection type: set to “Read/Write (all SQL statements allowed)

DSN Setup – Tab = “Packages”

Leave all settings at default

DSN Setup – Tab = “Performance” —- Reference DSN Settings

Click the “Advanced” button.

“Large Objects (LOB) threshold (KB)”: 15360 (should be the default )

“Use blocking with a fetch of 1 row”: De-select

“Open all cursors as updateable”: De-select

“Retrieve extended column information”: De-select

“Allow query timeout”: Selected

Click “OK”

DSN Setup – Tab = “Performance” —- Update DSN Settings

Click the “Advanced” button.

“Large Objects (LOB) threshold (KB)”: 15360 (should be the default)

“Use blocking with a fetch of 1 row”: De-select

“Open all cursors as updateable”: De-select

“Retrieve extended column information”: Selected

“Allow query timeout”: Selected

Click “OK”

DSN Setup – Tab = “Language”

No changes

DSN Setup – Tab = “Catalog

No changes

DSN Setup – Tab = “Translation”

No changes

DSN Setup – Tab = “Diagnostic

No changes

Linked Server Configuration

Linked servers can be setup using the SQL Server Enterprise Manager. There are two linked servers; one for reference the other for updating.

1.Select the appropriate server from the list
2.Expand the “Security” node
3.Expand the “Linked Servers” node
4.Right-click on the “Linked Servers” node
5.Select “New Linked Server …”

Once

“Linked Server Properties – New Linked Server” window

Linked Server: <>

Server Type: Select “Other data source”

Provider name: “Microsoft OLE DB Provider for ODBC Drivers”

Product name: <>

Data source: <>

Provider String:

“Driver={iSeries Access ODBC Driver}; System=<> ; Uid=<>; Pwd=<>; database=<>;”

NOTE: The IP address listed in the system parameter must match the IP address entered in the DSN configuration.

Location: <>

Catalog: <>

Security tab:

In the “Local server login to remote server login mappings” table, enter the SQL user account to be used on the target database. The “Remote User” and “Remote Password” are to be set to the user name and password used to login and access the data on the AS400. (User: <> PW: <>)

Under “For a login not defined in the list above, connections will:”

Select “Be made without using a security context”

Server Options tab:

“Collation Compatible”: De-select

“Data Access”: Selected

“RPC”: Selected

“RPC Out”: Selected

“Use Remote Collation”: Selected

“Collation Name”: <>

“Connection Timeout”: 0

“Query Timeout”: 0

 

Once the linked server is in place it’s easy to configure the BDC.

 

Advertisements