Installing and Configuring ODBC/400 LITE Drivers

This technical note describes how to install and configure the ODBC (Open Database Connectivity) drivers that ship with Reflection for IBM.

About ODBC Drivers

For information on the HiT ODBC drivers included with your Reflection for IBM product, see KB 7021477.

The drivers included with Reflection are a read-only version called HS ODBC/400 LITE and ODBC/DB2 LITE. These drivers, when properly configured, allow an application to connect to a database on an AS/400 and execute commands to retrieve information.

ODBC Transports

HiT Software's ODBC drivers can be configured to work over a variety of transports. TCP/IP is recommended. For ODBC to work, you must have a transport configured correctly and be able to make a connection to the AS/400.

Installing the HiT ODBC Driver

Follow the steps below to install the HiT ODBC driver:

1. Locate the HiT ODBC driver as described in KB 7021477.
2. Run the Setup.exe file.
3. In the HiT ODBC/400 32-bit LITE Installation Options dialog box, select the "Standard Client: Installs all ODBC files on a single client" option.
4. Continue the installation, accepting the default values. In the Setup Complete dialog box, verify that the "Configure a data source" check box is selected, and then click Finish.

Configuring the HiT ODBC Driver

Once you have installed the driver, you must create and configure a data source. Data sources store information about how to connect to a particular database. You will be prompted to configure a data source if you selected the "Configure a data source" check box as instructed above. If you deselected this option, you can configure a data source by clicking Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). There are three types of ODBC data source names (DSN).

• User: A User data source is only visible to the currently logged on user and is only available on the current machine.
• System: A System data source is available to all users on this machine, including NT services.
• File: A File data source can be shared by all users who have the same drivers installed.

After deciding what type of data source you want, you will need to add it.

Adding a User or System Data Source

To add a User or System data source, follow the steps below:

1. Select the appropriate DSN tab (User or System), and click Add.
2. Select "HiT ODBC Optimized 32bit" as the type of driver (data source) you want to use. Click Finish.

In the HiT ODBC/400 LITE Optimized Data Source Configuration dialog box, enter the following information:

General Tab

• Data Source Name: Type a unique name for the data source you are creating. It is a good idea to choose a name that associates the data source with an AS/400 library (database).
• Description (optional): Type a description for the data source.
• User ID (optional): Type your user ID for the AS/400.
• Library: Enter the AS/400 library you want to access.
• Isolation Level: Use the default.
• Host Code Page: Make an appropriate choice for the target AS/400.
• Read Only: The ODBC version that we ships is a read-only version, so this box is dimmed.

Communications Tab

• Network Connection: Select "TCP/IP Native."
• Host System Name: Enter AS/400 name.
• Host IP Address: Enter AS/400 IP address.
• Socket Timeout Value (sec): Use default of 30.
• Port Number: Use default of 8471.

For the remaining tabs (Language, Performance, Security, DPC and Advanced), accept default values, unless you have a compelling reason to do otherwise.

Adding a File Data Source

Follow these steps to add a File data source:

1. Select the File DSN tab, and click Add.
2. Select "HiT ODBC Optimized 32bit" as the type of driver (data source) you want to use. Click Next.
3. Browse to or enter the name of the file data source you want to save the connection to. Click Next, and then click Finish.

Testing the Data Source

HiT ODBC provides a way for you to run a self-test on your data source to make sure it is configured properly. To run the self-test, launch the HiT ODBC Toolbox by clicking Start > Programs > HiT ODBC Driver for AS400 Optimized LITE. In the Data Sources folder, expand the data source you just created, double-click Test, and then log in.

Note: If you pass the Transfer test, but fail the DRDA test, check security for the database you are trying to attach to.

Using the Data Source – An Example

An ODBC driver is a tool an application uses to access a host database file. Therefore, using the data source will depend on how the application works. For example, if you are using Microsoft Excel, you would import data from a HiT ODBC data source into Excel using the following steps:

1. Create a new data source as described above.
2. Create a new query in Excel by clicking Data > Get External Data > New Database Query. This launches Microsoft Query, if it is installed. If it is not installed, re-run Excel’s setup and install Microsoft Query by installing the Office Tools install option. Then, the next time you attempt to run a New Database Query, you will be prompted to install Microsoft Query.
3. After you have selected New Database Query, you will be prompted to choose a data source. Select the ODBC data source you created, click OK, and enter your AS/400 password. At this point, Microsoft Query will use HiT ODBC to connect to the AS/400 and return the data source's tables and columns. You can select which fields you want to include and how you want to sort them in Excel.
4. On the last screen of the Microsoft Query Wizard, select "Return Data to Microsoft Excel" and click Finish. One last prompt asks which cell to put the data in, and then the data is inserted into your spreadsheet.

Legacy KB ID