Stored Procedures error SQL0440N on db2 8.1 fp5

[shalini]

All, we are trying to create and execute our stored procs on db2 ver
8.1 fp5. This is a new database that we setup and are having some
trouble. When I try and run the stored proc from the db2 command line,
I get the following error:

SQL0440N No authorized routine named "XXX" of type
"PROCEDURE" having compatible arguments was found. SQLSTATE=42884

When the application (java) tries to execute the sp. we see the
following error:

com.ibm.db2.jcc.c.....SqlException: DB2 SQL error: SQLCODE: -444,
SQLSTATE: 42724, SQLERRMC:

We have checked to make sure that:

- the schema exists
- the SP exists
- user executing the sp has execute permissions on the proc
- the user also has insert/update/delete rights on the table being
accessed in the sp

Please note that this is happening for all our stored procs.

Thanks!

[Serge Rielau]

sh*****@austin.rr.com wrote:

All, we are trying to create and execute our stored procs on db2 ver
8.1 fp5. This is a new database that we setup and are having some
trouble. When I try and run the stored proc from the db2 command line,
I get the following error:

SQL0440N No authorized routine named "XXX" of type
"PROCEDURE" having compatible arguments was found. SQLSTATE=42884

When the application (java) tries to execute the sp. we see the
following error:

com.ibm.db2.jcc.c.....SqlException: DB2 SQL error: SQLCODE: -444,
SQLSTATE: 42724, SQLERRMC:

We have checked to make sure that:

- the schema exists
- the SP exists
- user executing the sp has execute permissions on the proc
- the user also has insert/update/delete rights on the table being
accessed in the sp

Please note that this is happening for all our stored procs.

Thanks!

The number one reason for unexpected -440 is that the proc has a
CHAR() or SMALLINT argument and literals are provided (which are
VARCHAR() and INTEGER respectively).
Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
Once we get the -440 out of teh way let's look at the -444.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[shalini]

FYI...The same stored procs work on another database, same version. The
only thing that we changed when we created this new database, was the
schema. Any thoughts on that?

Serge Rielau wrote:

sh*****@austin.rr.com wrote:

All, we are trying to create and execute our stored procs on db2 ver 8.1 fp5. This is a new database that we setup and are having some
trouble. When I try and run the stored proc from the db2 command line, I get the following error:

SQL0440N No authorized routine named "XXX" of type
"PROCEDURE" having compatible arguments was found. SQLSTATE=42884

When the application (java) tries to execute the sp. we see the
following error:

com.ibm.db2.jcc.c.....SqlException: DB2 SQL error: SQLCODE: -444,
SQLSTATE: 42724, SQLERRMC:

We have checked to make sure that:

- the schema exists
- the SP exists
- user executing the sp has execute permissions on the proc
- the user also has insert/update/delete rights on the table being
accessed in the sp

Please note that this is happening for all our stored procs.

Thanks!

The number one reason for unexpected -440 is that the proc has a
CHAR() or SMALLINT argument and literals are provided (which are
VARCHAR() and INTEGER respectively).
Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
Once we get the -440 out of teh way let's look at the -444.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

sh*****@austin.rr.com wrote:

FYI...The same stored procs work on another database, same version. The
only thing that we changed when we created this new database, was the
schema. Any thoughts on that?

Serge Rielau wrote:

sh*****@austin.rr.com wrote:

All, we are trying to create and execute our stored procs on db2
ver
8.1 fp5. This is a new database that we setup and are having some
trouble. When I try and run the stored proc from the db2 command
line,
I get the following error:

SQL0440N No authorized routine named "XXX" of type
"PROCEDURE" having compatible arguments was found. SQLSTATE=42884

When the application (java) tries to execute the sp. we see the
following error:

com.ibm.db2.jcc.c.....SqlException: DB2 SQL error: SQLCODE: -444,
SQLSTATE: 42724, SQLERRMC:

We have checked to make sure that:

- the schema exists
- the SP exists
- user executing the sp has execute permissions on the proc
- the user also has insert/update/delete rights on the table being
accessed in the sp

Please note that this is happening for all our stored procs.

Thanks!

The number one reason for unexpected -440 is that the proc has a
CHAR() or SMALLINT argument and literals are provided (which are
VARCHAR() and INTEGER respectively).
Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
Once we get the -440 out of teh way let's look at the -444.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

SET PATH = CURRENT PATH, <thatnewschema>

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[shalini]

Thank you we figured out our issue. We were using a type 4 driver
instead of a type 2 driver to connect!
Serge Rielau wrote:

sh*****@austin.rr.com wrote:

FYI...The same stored procs work on another database, same version. The only thing that we changed when we created this new database, was the schema. Any thoughts on that?

Serge Rielau wrote:

sh*****@austin.rr.com wrote:

All, we are trying to create and execute our stored procs on db2

ver

8.1 fp5. This is a new database that we setup and are having some
trouble. When I try and run the stored proc from the db2 command

line,

I get the following error:

SQL0440N No authorized routine named "XXX" of type
"PROCEDURE" having compatible arguments was found. SQLSTATE=42884

When the application (java) tries to execute the sp. we see the
following error:

com.ibm.db2.jcc.c.....SqlException: DB2 SQL error: SQLCODE: -444,
SQLSTATE: 42724, SQLERRMC:

We have checked to make sure that:

- the schema exists
- the SP exists
- user executing the sp has execute permissions on the proc
- the user also has insert/update/delete rights on the table being
accessed in the sp

Please note that this is happening for all our stored procs.

Thanks!
The number one reason for unexpected -440 is that the proc has a
CHAR() or SMALLINT argument and literals are provided (which are
VARCHAR() and INTEGER respectively).
Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
Once we get the -440 out of teh way let's look at the -444.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

SET PATH = CURRENT PATH, <thatnewschema>

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab