problem with db2-stored-procedure in multiple schemas
started a topic
about 13 years ago
[This topic is migrated from our old forums. The original author name has been removed]
I have a problem with DB-Vis v7.1.4 related to DB2-stored-procedures *only* in the screen called "Enter Data For Variables" when multiple instances of a stored procedure exist in different schemas.
I think that earlier versions of DB-Vis had the same problem.
The DB-Vis is running on WinXP 32-bit. Java VM is 1.6 update 24.
The DB2 is Version 9.1.8 running on AIX (all 64-bit os + database).
In our development environments each of our developers has their own schema (or set of schemas) and stored-procedures related to that schema(or collection of schemas). Each developer can access objects only in his/her own schema and cannot access any other developer's schema. All developers share the same physical database in their their schemas reside.
Each developer can only access their own schema and can only run their own stored-procedures.
Each developer can be working on the same application, but each developer will often be working on a "different version" of the application-schema(s) depending on which build of the application on which they are working.
In that context suppose that developer A (who has schema name A) wants to run stored-procedure A.proc_name.
In the physical database there is also a schema for developer B (who has schema name B) and also wants to run stored-procedure B.proc_name.
Notice that procedures A.proc_name and B.proc_name *can be different versions* of the same basic stored procedure - they have the same signature, same specificname, just (potentially) different SQL code if they are different versions (but otherwise they are the same version, just accessing tables in the developer specific schema(s)).
Given the above context, DB-Vis cannot properly run the stored procedure A.proc_name (from user A) or B.proc_name (from user B) because it SQL it generates is incorrect because it gets the number of parameters-wrong for the stored-procedure on the screen called "Enter data for variables".
Note that there is no issue running the stored procedures from the db2-client, regardless of the number of copies of the same procedure that exist in multiple developer schemas. There is also no issue using "@call" in the SQL-command editor - the problem is visible only when using the gui to enter parameters.
Suppose we have Stored-procedure A.do_something( IN P1 integer, IN P2 integer);
and also we have Stored-procedure B.do_something( IN P1 integer, IN P2 integer);
If user A uses DB-Viz to run A.do_something the "Enter Data For Variables" correctly shows 2 parameters, but the "Show SQL" shows 4 parameters (assuming there were only 2 developers who had a version of that stored procedure in their schemas). So when that executes it gets an error (no matching signature for a 4 parameter version of the procedure).
If we had a third copy of the same stored procedure in schema C (for example), then DB-Vis would show 6 parameters in the "@call C.do_something".
If there is only a *single* schema in the database that has the stored procedure defined within it then DB-Vis works properly on the "Enter data for variables" window.
There are no problems invoking the stored procedure directly from the SQL-commander (via a directly coded @CALL A.do_something(1,2) , or @CALL B.do_something(3,4) ) - the problem only happens when using the "Exceute the Procedure" function from the object-view when multiple instances of the procedure are present in different schemas.
Is this a known issue ?