A user ID performing a select fails due to incorrect schema name in DB2

Technote (FAQ)
Problem
A servlet running in WebSphere® Application Server that performs a select fails when the DB2® query gets an "incorrect schema" error.
Cause
A WebSphere Application Server data source is created using a different user ID than the user ID used by the servlet performing the select.
Solution
Scenario that explains why the select fails:
  1. The DB2 administrator, db2admin for example, connects to SAMPLE database.

  2. Administrator creates a table called EMPLOYEE. Since DB2 uses the username as the schema, the table is created as DB2ADMIN.EMPLOYEE.
  3. A WebSphere Application Server data source is created, using user ID USER1.

  4. USER1 is granted permission to connect to the SAMPLE database.

  5. A servlet using this data source performs a SELECT * FROM EMPLOYEE. Because the schema name is not specified, USER1 is used. As a result, the select fails because USER1.EMPLOYEE does not exist.

Edit the db2cli.ini file and add an entry:

[SAMPLE]

CURRENTSCHEMA=DB2ADMIN

where SAMPLE is the database name and DB2ADMIN is the actual schema for the tables.












Document Information

Product categories: Software, Application Servers, Distributed Application & Web Servers, WebSphere Application Server, Java 2 Connectivity (J2C)
Operating system(s): Multi-Platform
Software version: 3.5, 4.0, 5.0, 5.1, 6.0
Reference #: 1136749
IBM Group: Software Group
Modified date: 2004-08-09