Hints for Using ODBC

raphical access to the AS/400 database has certainly come into its own recently. More and more PC packages are able to quickly access the AS/400 and obtain the data required for decision support purposes. Open Database Connectivity or ODBC is a standard interface defined by Microsoft. An ODBC driver accepts formatted requests and passes these on to a specific database. The Client Access/400 ODBC driver will accept ODBC requests from a wide variety of applications and will pass the requests to DB2/400 and return the results through the ODBC driver to the originating application.

Everything you need for database connectivity comes with Client Access/400 for Windows. Prior to testing your ODBC setup you should execute the command "ADDRDBDIRE RDB(system name) RMTLOCNAME(*LOCAL)" on your AS/400. Verify that the subsystem QSERVER has been started. After starting a connection to your AS/400 via Client Access for Windows select the ODBC driver icon and then the 'ADD' button. Choose the Client Access/400 ODBC driver. 'Data Source name' is your choice. Choose the 'SYSTEM' you want this data source name associated with. Through 'Options' you can specify the libraries that you want your end users to have access to. For testing purposes you may want to put only the name of one small library as your Default Library. QIWS is not a good test library. A quick way to check out your ODBC driver is to query a file using the Data Base Access GUI software that comes with CA/400 for Windows. There are usually no special requirements for most applications once you have defined your AS/400 data source. Normally you either open a file and specify its type as CA/400 ODBC or you access the data through some "External Data" option. Some of the tools that have been tested and run without special setup include DB Access GUI, MS Access and MS Excel, Showcase Vista and Vista PRO, Borland's Paradox, Delphi and ReportSmith, Visual Basic, Crystal Reports, Cognos Impromptu, CA Visual Express and Brio Data Prism. Below are unique requirements for a few other common applications.

IBM's Visualizer for Windows:

For Visualizer, change the actual ODBC setup under OPTIONS to set ALWAYSSCROLLABLE to 1. You can have multiple data sources for the same AS/400. If you are using Visualizer and other tools you would probably 'ADD' 2 different data sources via the ODBC driver icon. One data source would have ALWAYSSCROLLABLE set to 0 (default) and the second data source would have it set to 1 for Visualizer.

Lotus 1-2-3:

In the WINDOWS\LOTUSAPP\DATALENS subdirectory, at the beginning of the file LOTUS.BCF add the following 3 lines:

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

This is the text describing your ODBC data source. If you have entries following this make sure that you include the semicolon.

Lotus Approach:

In the APPROACH.INI file in the WINDOWS subdirectory under the [ODBC] header, enter the following:

Client Access/400 ODBC Driver=16

In the ODBC.INI file in the Windows subdirectory add the statement:

Debug=2

under the heading for your datasource name.

Borland's ReportSmith:

In the WINDOWS subdirectory edit the RS_SQLIF.INI file. Enter a new section name[DB2] and below that the one line:

IDQUOTE=

Make sure that nothing follows the equal sign. ReportSmith does not support the correct syntax for Outer Joins.

Most applications require a unique index over a file if you want your users to be able to add or update data. A unique index can be created either via a logical file specifying UNIQUE or through the SQL "CREATE UNIQUE INDEX" statement.

For more details on the tools covered here and also various Powersoft products including PowerBuilder see the manual "Client Access Windows 3.1 Client for OS/400 ODBC User's Guide" SC41- 3533. ODBC access opens up a whole world of exciting client/server tools that will give your users easier and better access to their data. T < G

Jackie Jansen is an AS/400 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.