Setting Up Your ODBC Driver

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) +
RMTLOCNAME(*LOCAL)

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.

Double click the ODBC Driver icon in the Client Access/400 window
Choose the "Add" button
Choose the "Client Access/400 ODBC Driver" from the Installed Drivers and press 'OK'
ODBC Driver Setup Screen:
- The 'Data Source Name' text-entry box allows you to enter a descriptive name. This is the name your end users will select when they want to identify the AS/400 as the source for their data.
- 'System' is a drop down box identifying the AS/400 system you wish to connect to.
- The drop down box contains a list of active connections. You can manually enter a connection if your system isn't active.
- 'User ID' is not required.
- 'Default Libraries' specifies the list of libraries the user can access through this data source. The libraries entered in the Default Libraries edit box can either be added to the users library list or can replace the list entirely. To replace the list, specify a list of library names. Separate the library names by blanks. *USRLIBL is allowed anywhere in the list. QIWS is automatically added by some applications to the end of your list.
- When you choose the Options button the lower part of the setup screen is displayed. In most cases the defaults under the Options button are appropriate The options you can set are: time and date formats, decimal point format, performance enhancements, and a few miscellaneous options.

PC Applications that require no additional setup

Client Access Database Access GUI
MS Excel Query
Showcase Vista
Showcase Vista Pro
Computer Associates Visual Express
BrioQuery
Crystal Reports for Visual Basic
Crystal Reports Professional
Cognos Impromptu
Forest & Trees

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

IBM Visualizer for Windows

Set the ALWAYSSCROLLABLE parameter in the ODBC data source to 1. The default is 0.

Microsoft Access

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

The MEMO data type is not supported by AS/400. You must use the CHAR or VARCHAR data types.
On a Sort MS Access pulls your entire table into PC memory.
MS Access does not generate a Select with an Order By clause.
To join two tables MS Access performs three separate selects. It does not generate one Select with a proper join condition.

Lotus 123

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

DN="AS/400" DL="DLODBC"
DD="ODBC for the AS/400"
DC="driver-ehnodbc3.dll";

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:

DEBUG = 2

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]
DelimitIdentifier='YES'
PBCatalogOwner='***' (where *** is some known library on the AS/400)
PBDateTime='ISO_DATETIME'
PBSyntax='DB24_SYNTAX'
IdentifierCase=3

Add a section under pattern matching:

[DB24_SYNTAX]
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'
DropPrimaryKey='ALTER TABLE &TableName DROP PRIMARY KEY'
DropForeignKey='ALTER TABLE &TableName DROP FOREIGN KEY'

Borland Products

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

Set up an IDAPI connection
Double click on the IDAPI Configuration icon
Choose 'New Driver' button
Enter a name for the driver connection in the SQL Link text entry box
Select 'Client Access/400 ODBC' in the ODBC drop down box
Select your AS/400 data source in the drop down data source box and then press 'OK'
Choose Aliases tab at the bottom of the screen
Choose 'New Alias' button
Enter an alias to define the ODBC connection in the text entry box
In the Alias type drop down box select the ODBC connection you just defined and then press 'OK'

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.

When creating a sorted dynaset or sorting a dynaset, Visual Basic will pull the entire table into memory and then try to sort the contents.
VB won't generate a Select with an Order By.
The Visual Basic command Tabledef is not supported.
Joining two tables VB (or more accurately the Jet Database Engine) performs three separate selects. VB does not generate one Select with a proper join condition.

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.