I have tried to execute a stored procedure adapter created using DB2 connection by selecting as schema name without mentioning the actual schema name. but i am not able to run the stored procedure.
I could run dynamic sqls with the same connection without giving schema name before table name but stored procedure is not working.
Selecting schema name while creating stored proc adapter will create issue while moving the package to higher environments like UAT and Prod.
could you please suggest how to handle this?
Hi,
May I know the JDBC Adapter version and the fix level you have in your environment?
Regards,
Mansoor
1 Like
Hi,
Also could you let me know which flavor of DB2 are using? If its DB2 UDB then you need to pass currentschema=<your_schema> in Adapter Connection’s Other Properties connection parameter.
Regards,
Mansoor
Thanks Mansoor,
We are using webMethods 9.8 and jdbc 4.4…i am not sure of DB2 but i believe it should be enterprise edition. Also, I have added schema name in connection properties in Admin console.please find below.
Other Properties currentSchema=CLMDEV4;driverType=4;
Hi Vignesh,
I think Mansoor was asking for the JDBC Adapter Version, not for the Driver Type Version.
Depending on the time you have installed your server, this should be either JDBC Adapter 6.5 or JDBC Adapter 9.0.
Please check its fix level as well as the fix level of IS (i.e. for ART package).
Regards,
Holger
Hi Vignesh,
May I know the DB details, driver version and datasource classname you specified in Adapter connection page?
You might be using 9.0v JDBC Adapter. I would suggest you do the following
- Enable trace logs for JDBC Adapter.
- Execute stored procedure adapter service.
- Verify the SQL generated in Server log to see if the procedure name indeed preceded with schema name.
If adapter is not prefixing schema name to procedure name then it must be a driver issue, upgrade the driver that supports currentSchema connection property.
Regards,
Mansoor
Hi Mansoor,
Your help is much Appreciated!.
Please find the data source name and driver version below.
com.ibm.db2.jcc.DB2SimpleDataSource - DRIVER = 4.21.29
The adapter is not prefixing the schema name to the procedure name if i don’t add it explicitly but same case with sql’s when i try with dynamic or custom sql. But, sqls are executing fine without the schema name only stored proc is creating issue.
Hi Vignesh,
Give a try by passing currentFunctionPath=schemaName in Other Properties of Adapter’ connection page.
Regards,
Mansoor
Thanks a lot Mansoor!
currentFunctionPath property resolved the issue. Thanks again.
My pleasure 