Logo: TUG TORONTO USERS GROUP for Midrange Systems
e -server magazine

January 1997: Volume 12, Number 3

Setting Up Your ODBC Driver

By Jackie Jansen

DBC is the standard interface defined by Microsoft for database connectivity. This standard was developed to allow PC applications to access a variety of databases. The database of concern to us today is DB2/400. Setting up the ODBC driver on your PC will allow you or your users to access AS/400 data directly from such applications as Lotus 123, Microsoft Excel, Microsoft Access, BrioQuery, Cognos Impromptu, Forest and Trees, Visual Basic and many more.  Flow Chart: ODBC Interface

ODBC Applications: These applications are programs such as Showcase Vista and Microsoft Excel that support ODBC access to data.

ODBC Driver Manager: This code is part of Windows. It loads the ODBC drivers and routes the application's database calls to the proper ODBC driver.

ODBC Drivers: These drivers are supplied by the various database vendors. The ODBC driver provided with Client Access/400 will process database function calls, submit requests to DB2/400, and return results to the driver manager which returns them to the application.

Data Source: For the purposes of this article the Data Source is your AS/400. Access can be restricted to specific libraries on the AS/400 as we will see shortly.

AS/400 Setup

Before using the ODBC driver you should enter the following command on the AS/400:

ADDRDBDIRE RDB(system name) +

This is all there is to do on the AS/400. Easy wasn't it! A "nice to have" for most end user applications is long meaningful names instead of CUSNM or worse yet XXAACN. This is now easy to do on the AS/400. If you create your files with SQL then you can directly enter the long column names. If you create your files with DDS you can use the ALIAS keyword. Most PC applications will pick up the name specified in the ALIAS keyword. Try and avoid blanks in the name as they are not accepted in all PC applications. Isn't Customer_Name much better than CUSNM or XXAACN? How about Last_Years_Revenue?

PC Setup

The following instructions will set up your ODBC driver on your PC.

PC Applications that require no additional setup

The remainder of this article contains special considerations for some of the more common PC tools.

IBM Visualizer for Windows

Microsoft Access

There are no special setup considerations for MS Access. Some things to be aware of are:

Lotus 123

Add the following entries to the LOTUS.BCF file in the WINDOWS\LOTUSAPP\DATALENS subdirectory

DD="ODBC for the AS/400"

Make sure that you end the DC line with a semicolon or you may get a multiple DN error from Lotus.

Lotus Approach

Edit the APPROACH.INI file in the WINDOWS subdirectory. Under the [ODBC] header enter

Client Access/400 ODBC Driver=16

After setting up your ODBC defaults edit your ODBC.INI file. Find your AS/400 data source and add the following line to the bottom of the data source description:


Powersoft Products

Add the statements below to the following files based on your Powersoft application:

Powerbuilder V3.0: PBODB030.INI in the PB3 subdirectory
Powerbuilder V4.0: PBODB040.INI in the PB4 subdirectory
PowerMaker: PBODB030.INI in the PM3 subdirectory
PowerViewer: PBODB030.INI in the PV3 subdirectory
InfoMaker V4.0: PBODB040.INI in the IM4 subdirectory

Add this section to the file specified above:

[DB2/400 SQL]
PBCatalogOwner='***' (where *** is some known library on the AS/400)

Add a section under pattern matching:

AlterForeignKey='ALTER TABLE &TableName FOREIGN KEY &KeyName (&ColumnName[,&ColumnName]...) REFERENCES &RefTableName'
AlterPrimaryKey='ALTER TABLE &TableName PRIMARY KEY (&ColumnName[,&ColumnName]...)'
CreateTable='CREATE TABLE &TableName (::ColumnElement[, ::ColumnElement]...)'
ColumnElement='&ColumnName &DataType &NotNull'
CreateIndex='CREATE &UNIQUE INDEX &IndexName ON &TableName (::ColumnIndex[, ::ColumnIndex]...)'
DropTable='DROP TABLE &TableName'
DropView='DROP VIEW &TableName'
DropIndex='DROP INDEX &IndexName'

Borland Products

Borland products include Paradox, Visual dBase, Delphi, and ReportSmith.

Visual Basic Professional V3.0

Here are some items that you should be aware of when you are using Visual Basic and the Jet Database Engine. These concerns can be avoided and performance greatly improved if you write directly to the ODBC APIs from Visual Basic.

My thanks to Randy Sendek in the Rochester lab for his help in putting together this article and for his manual "Client Access for Windows 3.1 ODBC User's Guide" SC41-3533-01. T < G

Jackie Jansen is an AS/400 IT Specialist and Consulting SE, providing national technical and marketing support for the AS/400 in Canada. Jackie frequently speaks at AS/400 Technical Conferences and User Group meetings. See her column "Tips from Jackie", also in this issue.