Question
Truist
US
Last activity: 16 Jan 2025 8:51 EST
Invoke Stored Procedure in DB2
we are trying to call a SP in external DB2 database, using the query
\{CALL DB02.MIGRATION({TempPage.CIF}) \} where
DBUVI02-->External Schema
CLIENT_MIGRATION-->SP Name
CIF --> input for the stored procedure , but we get below error
"There was a problem getting a list: code: -440 SQLState: 42884 Message: No authorized routine named "DB02.MIGRATION" of type "PROCEDURE" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.31.10
When we checked Pega Documentaion : Stored procedure execution is not available for Db2/S390 databases. (However, stored procedures can be executed in UDB and in Db2/AS400)
so does this mean we cannot infer a SP which is in DB2?
Pegasystems Inc.
GB
⚠ This answer is a GenAI-powered tool. All generated answers require validation against the provided references.
The stored procedure support in DB2 depends on the specific DB2 platform you're using:
⚠ This answer is a GenAI-powered tool. All generated answers require validation against the provided references.
The stored procedure support in DB2 depends on the specific DB2 platform you're using:
- DB2 Platform Support:
- DB2/S390: Stored procedure execution is NOT available
- DB2 UDB (Universal Database): Stored procedure execution IS supported
- DB2/AS400: Stored procedure execution IS supported
- Regarding Your Error Message: The error you're seeing (SQLCODE=-440, SQLSTATE=42884) indicates that the system cannot find an authorized routine named "DB02.MIGRATION" of type "PROCEDURE". This could be due to one of several reasons:
- The procedure name may not be correctly qualified with the schema name
- The user may not have the necessary permissions to execute the procedure
- The procedure may not exist in the specified location
- Important Considerations: If you're working with UDB or DB2/AS400 and want to execute stored procedures that return result sets, you need to:
- Include the clause "DYNAMIC RESULT SETS N" in the stored procedure source
- Where N is the number of result sets that will be returned
- This allows the system to process the output as a result set and populate the Code-Pega-List.pxResults Page List
To answer your specific question: The statement from the documentation about DB2/S390 limitations doesn't necessarily mean you cannot call stored procedures in other DB2 platforms. The support depends on which DB2 platform you're using.
Recommended Next Steps:
- Verify which DB2 platform you're using (S390, UDB, or AS400)
- If you're using UDB or AS400, ensure:
- The stored procedure is properly defined in the database
- The executing user has appropriate permissions
- The procedure name is correctly qualified with the schema name
- If you're using DB2/S390, you'll need to consider alternative approaches as stored procedures are not supported on this platform
References:
@LONGJ is there anything you could add here?