Usage Note 13849: Unable to access DB2 on AS/400 using the SQL Query Window
The following error messages may be issued when attempting to ACCESS
DB2 on AS/400 using the SQL Query Window.
ERROR: CLI error trying to establish connection: [IBM][CLI Driver]
SQL1024N A database connection does not exist.
SQLSTATE=08003
The above error may be issued because the ACCESS mode of DB2 was
selected. Select the ACCESS mode of DB2/6000. DB2 alone is DB2 on MVS.
After selecting the access mode of DB2/6000, you may receive the
following error message when attempting to query the DB2 database on
AS/400.
sqlxrc = -204
After selecting the database names:
sqlxmsg = [IBM][CLI Driver][AS] SQL0204N "SYSIBM.SYSTABLES" is an
undefined name. SQLSTATE=42704
Outside of the Query Window, creating a library using the LIBNAME access
engine works fine. Using the Query Window to access DB2 data on other
platforms work fine. This problem seems to be related to it being on
AS/400.
To work around this problem, create a library using the LIBNAME access
engine. Using the Query Window, select the LIBNAME, then select ACCESS
MODE as SAS. This will still require you to manually edit and modify
any views that are created and forces SAS to believe that the dataset
can be accessed via a libname definition. This will allow you to see
the tables and field names on the AS/400 from within the query tool, but
what query tool creates based on that information may not be what you
expect.
However, per development, it turns out that the Query Window does a
direct query of the system tables rather than going through the ACCESS
engine's directory services. As such, it's really meant for DB2 UDB
(i.e. PC/UNIX). AS/400 stores it's system tables in the QSYS2 catalog
instead of the SYSIBM catalog like UDB does.
So, in the DB2/6000 Access Mode Options screen, try putting
QSYS2.SYSTABLES in for the System Tables box and QSYS2.SYSCOLUMNS for
the System Columns. Hopefully the structure of the tables is the same
and you have your resolution.
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to DB2 | 64-bit Enabled AIX | 9.1 TS1M0 | |
| 64-bit Enabled HP-UX | 9.1 TS1M0 | |
| 64-bit Enabled Solaris | 9.1 TS1M0 | |
| Tru64 UNIX | 9.1 TS1M0 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Usage Note |
| Priority: | |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> DB2 SAS Reference ==> LIBNAME Engines
|
| Date Modified: | 2004-12-14 11:38:25 |
| Date Created: | 2004-11-19 13:31:24 |