Wednesday, November 26, 2008

Configure ODBC to access iSeries(AS/400) system

IBM provides software like WRKQRY, STRSQL on the iSeries (AS/400) system to access and query the database DB2 UDB and needs green screen terminal emulation to work with it Another way to access the DB2 database is to use iSeries navigator that provides option to run SQL statements.

The iSeries database can also be accessed using the ODBC and OLE DB drivers that can be installed on the PC and configured in the ODBC data source administration. Once the drivers are installed and configured you can use SQL to access and query the DB2 database using Excel or Access or any third party database management tool like WinSQL (http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp).

The OLE DB drivers allows developer to write application using OLE DB connection in VB or VB.NET/C# using Microsoft Visual studio etc.

To install ODBC or OLE DB driver, you will require the installation media iSeries Access for Window from IBM and is normally provided as a part of standard softwares when iSeries system are purchased from IBM. If you do not have the installation media contact the vendor or IBM for more information and cost involved in purchasing it.

During the installation process of iSeries Access for Window software, select the custom setup that allows to select the required program features you want to install on the PC. Below is the screenshot when performing custom setup and make sure you have selected the ODBC and OLE DB provider as required for installation.
Once the installation is complete, the drivers will be available for use for your tools or development.












To configure ODBC to access perform the following step (I am using Window XP):
Click on Start->Control Panel->Administrative Tools that will bring the Window to setup a Data Source on your PC.















Open the Data Source (ODBC) by double click on the shortcut to bring up the ODBC Data Source Administrator.
Select the System DSN and Click on the Add button to create a new Data source.















Select Client Access ODBC Driver (32-bit) and click on the Finish button to continue.

Enter a name in the Data source name field and the IP address in the System field.
















Click on the Server tab and enter the SQL default library.
















Click OK button to complete the process.

For additional references to the other options and tabs refer the documentation or user guide of the iSeries Access to Windows.

Once the setup is complete, the ODBC data source is available to access from other tools.
In next time post I will write on tools to access the ODBC data source.

2 comments:

Anonymous said...

Thank you your info on this!! It was most helpful in resolving my issue of receiving an SQL0901 error and trying to figure out what was causing the problem.

Anonymous said...

How do I get Client Access option on Windows 7???