|
View previous topic :: View next topic
|
| Author |
Message |
kishpra
New User
Joined: 24 May 2010
Posts: 92
Location: Pune
|
|
|
|
Hello Team,
My Java application is trying to call a Native SQL stored procedure AA400400 which is using a DB2 table.
When calling that stored procedure, we are getting the below error in Java application logs:
[0m[0m10:49:31,261 INFO [stdout] (http-/10.1.109.2:8443-1) DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PROCEDURE;AA400400 DRIVER=4.14.113
My application connection URL in standalone file:
<connection-url> jdbc:db2://TZZSST.zpc.us.com:1330/TZZDB2T:currentSchema=TZZ1;resultSetHoldability=2;
</connection-url>
Application call to stored procedure
Call AA400400(NULL,NULL);
It does not points to correct schema TZZ1 and we can see the stored proc running in different schema i.e. SYSPROC.
We updated the connection URL as:
<connection-url> jdbc:db2://TZZSST.zpc.us.com:1330/TZZDB2T:currentSchema=TZZ1;currentFunctionPath="SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TZZ1";resultSetHoldability=2;
</connection-url>
But it still runs under SYSPROC.
Then we changed the connection URL as below:
<connection-url> jdbc:db2://TZZSST.zpc.us.com:1330/TZZDB2T:currentSchema=TZZ1;currentFunctionPath="TZZ1","SYSFUN","SYSPROC","SYSIBMADM","SYSIBM";resultSetHoldability=2;
</connection-url>
But still getting -440.
Please advise.
Thanks. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
Did you contact DBA at your site first?
SQLCODE -440 with SQLSTATE 42884 in DB2 typically indicates an undefined function error. This error occurs when a function referenced in your SQL statement is not defined or does not exist in the database. Here's how you can fix it:
Check Function Name: Verify that the function name used in your SQL statement is spelled correctly and matches the actual name of the function defined in the database. Even a small typo can cause SQLCODE -440.
-- Example: Check the function name used in your SQL statement
| Code: |
| SELECT MY_FUNCTION() FROM MY_TABLE; |
Ensure Function Existence: Confirm that the function is defined in the database and is accessible to the user executing the SQL statement. Use database management tools or query the system catalogs to check the existence of the function.
-- Example: Query to check if a function exists in DB2
| Code: |
| SELECT ROUTINENAME FROM SYSCAT.ROUTINES WHERE ROUTINENAME = 'MY_FUNCTION'; |
Schema Qualification: If the function is defined in a specific schema, ensure that you are referencing it with the correct schema qualification in your SQL statement.
-- Example: Schema-qualified function reference
| Code: |
| SELECT SCHEMA_NAME.MY_FUNCTION() FROM MY_TABLE; |
Permissions and Privileges: Make sure that the user executing the SQL statement has the necessary permissions and privileges to access and execute the function. Check if any authorization issues are causing the function to be inaccessible.
Function Parameters: If the function requires parameters, ensure that you are providing the correct number and data types of parameters in your SQL statement.
-- Example: Function call with parameters
| Code: |
| SELECT MY_FUNCTION('param1', 123) FROM MY_TABLE; |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
