SQL State 42502 when building Stored Procedures

Technote (troubleshooting)


Problem(Abstract)

When you try to create a Stored Procedure from Rational® Application Developer (RAD) you get a DB2® error:
*** DB2 SQL error: SQLCODE: -552, SQLSTATE: 42502 ***
You are able to create a stored procedure (using a DB2 admin-level userid and password) at a remote system using the DB2 Developer Center. But, from within RAD, using the same userid and password, you get a failure with the above message. You can create a Database Connection, using the same userid/password, and connect to the database in question successfully. However, at run-time, the SQL operation fails.

Cause

You have to use a Data Definition to create the SQL Stored Procedure. As far as Eclipse and RAD are concerned, a "Data Definition" is a project (It is just not called a project in the GUI, that is.).

The problem occurs when you create a project that is not a Data Definition, then switch to the Data Explorer, where this project is still shown. Then you create a SQL Stored Procedure within that project. At run-time, however, the project fails with message of the type
***  DB2 SQL error: SQLCODE: -552, SQLSTATE: 42502 ***
Only Data Definitions have the proper natures to use the SQL facilities correctly.
While the Data Explorer will show other types of projects, and allow you to do a New -> SQL -> <Whatever> (such as a new Stored Procedure or Query) within them, at run-time these elements fail, because the underlying project lacks the proper nature.

This will cause, at run-time, the normal Windows Userid/Password to be used, instead of the one indicated on the "Database Connection". The SQL will then fail, with the above error message.

SQL Stored Procedures must be created from a Data Definition, which will then include the proper natures.

Documentation mentions "should" for using the Data Definition / Project; in reality, it ought to state "must".


Resolving the problem

You must use a Data Definition as the basis for the New -> SQL -> <Whatever>. Then the proper natures will be attached, and the operation will succeed at run-time.

Cross Reference information
Segment Product Component Platform Version Edition
Software Development Rational Software Architect 6.0, 6.0.0.1, 6.0.1, 6.0.1.1
Software Development Rational Web Developer for WebSphere Software 6.0, 6.0.0.1, 6.0.1.0, 6.0.1.1

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Rational Application Developer for WebSphere Software
Data Access Applications

Software version:

6.0, 6.0.0.1, 6.0.1, 6.0.1.1

Operating system(s):

Windows

Reference #:

1264383

Modified date:

2008-04-12

Translate my page

Machine Translation

Content navigation