
APAR= II09211
CA400OS2OPT DATABASE AND ODBC INFORMATION


** Last updated 5/14/96 **


***** NOTE *****
Because of a problem with the backslashes not printing,
all places that require a backslash have been changed to
a vertical bar "³".  Make sure when you are actually
entering the path names that you use a backslash.


Client Access/400 ODBC
______________________

An ODBC driver is shipped as part of the Client Access/400
Optimized for OS/2 client. This ODBC driver allows OS/2
application programs to access DB2/400 data residing on
the AS/400.

The Client Access/400 OS/2 ODBC driver meets level 2,
version 1.0 of the ODBC specification. The ODBC
specification has three levels of compliance:  core, level
one, and level two.  Core is the minimal level of
compliance and each additional level adds significant
function. The ODBC specification has undergone revision,
with some new APIs added to version 2.0. Further revisions
of the specification are expected.

In order to use the Client Access/400 ODBC driver, it is
necessary to have ODBC driver manager and administration
programs. These programs are available from companies such as
Visigenic Software (415-286-1900 or http://www.visigenic.com)
and Intersolv (800-547-4000 or 301-838-5000 or
http://www.intersolv.com).  They are not provided with Client
Access/400 at this time, but may be distributed with ODBC
application programs.

The OS/2 Client Access/400 ODBC driver is installed during
installation of the OS/2 Optimized client. The ODBC driver
consists of three files:  the driver program, EHNODBC2.DLL;
the set-up program, EHNSTP2.DLL; and the MRI file,
EHNODBC2.MR0. In addition two ODBC INI files get updated
or created during Client Access installation, ODBCINST.INI
and ODBC.INI.

The ODBC administration program is used to create and
configure data sources.  For convenience, think of a data
source as a connection to an AS/400 and a set of attribute
parameters for accessing data over that connection.

The ODBC administration program uses the information in the
ODBC.INI and ODBCINST.INI files to present menus of drivers
and data sources to manipulate.  The Client Access/400
ODBC set-up program will be called when: the Client
Access/400 driver is selected to add a new data source; a
data source associated with Client Access/400 is selected
for configuration; a data source associated with Client
Access/400 is selected for deletion.

Limitations and known problems
______________________________

The Client Access/400 Optimized for OS/2 ODBC driver relies
on the router to provide its EBCDIC<->ASCII translation
tables. If the router is not running, the ODBC driver will
encounter a trap in EHNCL000.DLL.  By default, the router
is started as part of the Client Access startup process.

The ODBC driver will return -1 from the SQLAllocEnv call
if the ODBC driver's MRI file, EHNODBC2.MR0, is not in a
subdirectory underneath the directory from which the
executing program is running.  The subdirectory containing
the MRI must begin with the letters MRI, such as MRI2924.

The ODBC driver will return -1 from the SQLAllocEnv call if
the application uses a calling convention other than the
convention used by the driver manager.  The Visigenic driver
manager expects parameters to be on the stack, not in the
registers.  Try using the /Ms parameter on the compile step.
If the calling conventions don't match you'll often see a
variety of traps, but not always!

If the AS/400 associated with a data source is down, the
SQLAllocEnv and SQLAllocConnect calls in a program will
return zero but the SQLConnect call will fail returning -1.

The Client Access/400 Optimized for OS/2 ODBC driver does
not support a user interface in the SQLDriverConnect API
at this time.

ODBC applications running under OS/2's WIN-OS2 environment
are not supported by this driver.  However, the Client
Access/400 for Windows 3.1 ODBC driver can be be used to
run Windows ODBC applications on the Optimized for OS/2
client.



Database Access only shows QGPL and QIWS libraries
__________________________________________________

Problem: Database Access only shows QGPL and QIWS libraries
  for the Query, Create option and the View catalog
  information options. Other libraries can only be reached
  by directly typing in an SQL query statement using the
  Query, Edit option.

Background:
 The OS2 optimized database access tool retrieves information
 by using ODBC, similar to the Windows client.  In the Windows
 client, the library list used by Database Access is defined
 in the data source for the connection.  The ODBC administrator
 is used to configure this value.  UNLIKE THE WINDOWS CLIENT,
 THE OS2 CLIENT DOES NOT SHIP WITH AN ADMINISTRATOR.
 An administrator can be purchased separately if the user
 chooses to do so.  Since an administration program is optional,
 IBM decided to do the following.

 The OS2 Database Access applet allows you to connect to an
 AS400 system name, not an ODBC data source name.
 When a system name is selected from the Add New Connection
 dialog box, the OS2 config.sys is searched for a path to
 a valid ODBC administration directory.  If one is not found
 it searches for an ODBC.INI file in the OS2 directory.
 If ODBC.INI is not found here, it will create an empty one.
 As soon as an ODBC.INI file is found (or created),
 it is searched for a data source entry that matches the
 system name.  If this is found, it will use the
 DefaultLibraries entry for the data source.  If ODBC.INI
 is empty (default), then the library list of the job is used.
 By default, this is jobd QDFTJOBD which in turn defaults
 to *SYSVAL.  *SYSVAL will use the libraries defined in
 system value QUSRLIBL.

Circumvention:
 Edit the ODBC.INI and add the appropriate data source. This
 process differs depending on whether or not you have
 purchased an OEM ODBC administration program.

ODBC ADMINISTRATOR INSTALLED
----------------------------
1) Click on the administrator icon.  Specific steps may
   vary depending on the administrator you purchased but the
   process is outlined below.
2) Select Add
3) Select ClientAccess/400.  If this option does not appear
   then consult II08874 on how to register ClientAccess with
   the administrator.
4) Specify a Data Source Name that EXACTLY matches the
   system name displayed in the database access add connection
   dialog box.
5) Select options.
6) Specify desired libraries in the Default Libraries text box.
7) Save the data source.

NO ADMINISTRATOR INSTALLED
--------------------------
1) You must obtain an OS/2 ini file editor (unlike Windows 3.1x
   OS/2 does not use a text ini file).  You can download an
   INI editor from the OS/2 BBS (919-517-0001 or
   ftp.pcco.ibm.com).  There are several editors to choose
   from.  Download all files.txt from the /pub directory for
   a full listing of all files.  Detailed instructions are
   given below for the inimaint.exe program included as part
   of INIEXE.ZIP in the pub/os2_util directory.
2) Example ini file:

     Application Name:  ODBC Data Sources
     KeyName:           RCHASTST
     Text:              ClientAccess/400

     Application Name:  RCHASTST
     KeyName:           DefaultLibraries
     Text:              QTEMP, MYTEST, DEVLIB

    where:
      RCHASTST is the system name as it appears in the add
        connection dialog
     QTEMP, MYTEST, DEVLIB are the libraries you wish to
        query against

  THESE ARE THE EXACT STEPS TO USE FOR INIMAINT
  ---------------------------------------------
  1) At an OS/2 command line run: inimaint C:³os2³ODBC.INI
  2) Click Actions, then Add Profile Name
  3) The "Enter New Application" dialog will appear.
     For Variable Data enter: ODBC Data Sources
     Click the OK button.
  4) The "Enter New Key" dialog box will appear.  For Variable
     Data enter: xxxxxxxx  (where xxxxxxxx is your system name).
  5) The "Add Key Value" dialog box will appear.  Check the
     "add Zero Terminator" box (if not already checked).  For
     Ascii Value enter: ClientAccess/400.  Click the OK button.
  6) Click Actions, then Add Profile Name
  7) The "Enter New Application" dialog will appear.  For
     Variable Data enter: xxxxxxxx  (where xxxxxxxx is the
     system name you entered in step 4).  Click the OK button.
  8) The "Enter New Key" dialog box will appear.  For Variable
     Data enter: DefaultLibraries.  Click the OK button.
  9) The "add Key Value" dialog box will appear.  Check the
     "Add Zero Terminator" box (if not already checked).  For
     Ascii Value enter: lib1, lib2  where libx is a library
     you wish to have in your library list.  Separate multiple
     libraries with commas.  Click the OK button.
 10) Select File, Exit from the menu bar.

ODBC driver returns security not valid error
____________________________________________

The AS/400 expects the user ID and password information to
be in uppercase. If your ODBC application prompts you for a
user ID and password, please be sure to use uppercase.


ODBC Administration program does not recognize the ODBC driver
______________________________________________________________

This problem may occur when Client Access/400 is installed
BEFORE the ODBC administrator.

During Client Access/400 installation, the OS/2 environment is
searched for the environment variable ODBC_PATH. If this
environment variable is set, then the ODBCINST.INI file in the
directory set in the variable (the default directory is ODBC)
is used by Client Access/400. The installation process will
created the ODBCINST.INI file in that directory or update an
existing ODBCINST.INI file if one is found.

If the ODBC_PATH environment variable is not set prior to
Client Access/400 installation, the ODBCINST.INI file will be
created (or updated) in the C: OS2 directory.

If you are using an ODBC administrator which uses the ODBC_PATH
to search for the IDBCINST.INI file and Client Access/400 used
the ODBCINST.INI in C: OS2, then your ODBC administration
program will not be aware of the Client Access ODBC driver.

If your ODBC administration program cannot find the Client
Access/400 ODBC driver, you will have to manually add
information to the ODBCINST.INI file.  There are two ways:

 1) Copy the ODBCINST.INI file created in C:³OS2 to the
    directory to the directory set in ODBC_PATH.

 2) Add Client Access to the existing ODBCINST.INI in the
    directory set in ODBC_PATH.

If you have no other OS/2 ODBC drivers installed, then the
first option is the easiest solution.

WARNING: Just to be safe, make a copy of the existing
  ODBCINST.INI. This will allow you to restore it should
  copying the ODBCINST.INI from C:³OS2 cause other ODBC
  applications to fail.

If you have other existing OS/2 ODBC applications, you will
need to manually update the ODBCINST.INI file set in ODBC_PATH
to add information about the Client Access/400 ODBC driver.
To do this you will need an OS/2 INI file editor.

The instructions on how to obtain the OS/2 INI file editor
are listed in the previous tip above.

Using the INI file editor, add the following to the
ODBCINST.INI file:

Field name          Data
---------------------------------
Application name:   ClientAccess/400
Key name:           Driver
Text:               C:³CAOS2³EHNODBC2.DLL

Application name:   ClientAccess/400
Key name:           Setup
Text:               C:³CAOS2³EHNSTP2.DLL


Note: The data above must be entered into the ODBCINST.INI
      file exactly as it appears above. The ClientAcces/400
      string has no blanks in it and the case of the letters
      must match.



