Connecting SharePoint to AS400
To connect SharePoint 2013 with data on an AS400 there are 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.
SQL Server Link
So here are the steps for option 2:
- 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.
- 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.