DB2 Stored Procedure Problems

[Tarias]

Hello DBeaver Team

I have two problems by executing DB2 stored procedure
and I hope you can help me. I'm using DBeaver 3.4.1.

First problem:

There seems to be a problem of the input/output parameters.
If for example I execute this IBM stored procedure :

call sysproc.get_config(null,null,null,null,null,?,?)

I get a SQL -469 error (you can see it in the picture).
I tried different jdbc Drivers, but without any success.

If I execute the same statement in the db2 command window (clp) it works (you can see it in the next Picture).
I think the problem is that the parameter is defined as INOUT and not handled correctly.

Second problem:

DBeaver does not return the values of the output parameters, when a stored procedure is called.

For example I created this simple stored procedure:

CREATE OR REPLACE PROCEDURE
TEST.PROC( IN IN_DM char(2), OUT OUT_DM char(2))
SPECIFIC TEST.PROC
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
SET OUT_DM = 'AB';
END;

If I execute "call TEST.PROC(null,?);" I get in DBeaver only the values of the Query and Updated Rows.

With db2cmd I also get the value of the output parameter. You can see the output in one of the pictures.

My last question is can you please change the input-mask of the stored-procedure parameter list.
For example if you call a stored-procedure with several ? as parameters,
you get a seperate window where you can fill out every parameter.

The problem is if you don't want to change anything of the parameters you have to hit several times
the OK button, because the cursor jumps from one input field to the next until the end.

I think it is better if the OK button means that you want to execute the stored procedure now.
You can already jump with TAB between the input fields.

Thank you very much

Best Regards

[Serge]

Currently DBeaver lacks support of stored procedures execution.
I'll increase priority of this feature, hopefully it will be added in one of next versions.

[Tarias]

Thank you!

Best regards

[Tarias]

Hi Serge

First thank you for the great support.

I'm using now DBeaver Version 3.4.3 but I have still problems
if I use stored procedure.

The example above (TEST.PROC) works now, but not the stored procedure (sysproc.get_config).
I get an SQL Error -469, which I already posted in one of the pictures.

I think I found the problem. The problem occurs if the procedure have INOUT parameters.
So I changed the procedure TEST.PROC a little:

CREATE OR REPLACE PROCEDURE
TEST.PROC( INOUT INOUT_DM char(2), OUT OUT_DM char(2))
SPECIFIC TEST.PROC
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
SET OUT_DM = 'AB';
END;

If I call now the procedure in DBeaver I get a SQL -469.
With db2cmd I get the right output in picture "DB2CMD INOUT".

I think that DBeaver uses INOUT parameters in a wrong way.

Again thank you for your support.

Best Regards.

[Serge]

Yes, it is a yet another bug in DBeaver. It will be fixed in the next version.
Thanks for report.

[Tarias]

Thank you Serge

[Serge]

I've included this fix in hotfix version 3.4.4 - you can download it right now.

[Tarias]

Hi Serge

Sorry but the last fix does not solve the problem.
You can see the problem in the picture.
The same error also occurs with:

call sysproc.get_config(null,null,null,null,null,?,?)


Best Regards

[Serge]

Yes, apparently this issue is more complex than I thought. I'll need to parse SQL statement to be able to count number of actual parameters passed.
Hopefully the fix will be included in one of the next versions.
Thanks for info

[Serge]

Please check version 3.5.0.
There were some fixes regarding stored procedures execution.