Search code examples
intellij-ideadb2jetbrains-idedatagrip

How can I call a DB2 stored procedure with OUT params using IntelliJ/DataGrip?

The procedure definition:

CREATE OR REPLACE PROCEDURE MY_PROC (
   OUT @STATUS      INTEGER,
   IN  @STARTCODE   NUMERIC(8),
   IN  @DRIVER      VARCHAR(20)   DEFAULT NULL,
   IN  @UNIT        VARCHAR(20)   DEFAULT NULL,
   IN  @TRAILER     VARCHAR(20)   DEFAULT NULL,
   IN  @ORDERNUM    VARCHAR(10)   DEFAULT NULL,
   IN  @USERID      VARCHAR(10)   DEFAULT NULL,
   IN  @DRIVER2     VARCHAR(20)   DEFAULT NULL
)

I put this SQL into the IDE:

CALL LIB.ADD_DUTLINK ( ?, 99612, '0520', '101', 'TRL1', '13573333', 'A', '0085' );

I get this screen, and I have no idea what this means.

enter image description here

I have tried all permutations of checkboxes under settings -> database -> user params.

Solution

  • It appears that DataGrip version 2019.2.5 handles stored-procedure output parameters in a more hostile manner compared to other GUI database tools.

    Fortunately there are many alternative free tools such as IBM Data Studio, DBeaver, DB-Visualizer, Squirrel-SQL etc, some of which are more friendly for Db2.

    For the screen that you show in your question, it will (incorrectly) disable the 'Execute' button until and unless you enter a value for the indicated parameter and then press Enter. This seems undesirable default behaviour.

    I would expect DataGrip to recognize that a stored-procedure-parameter defined as "OUT" should not require any initialization, and so should not display the dialog screen waiting for a value before it enables the Execute button (if there are no other OUT or INOUT parameters).

    I note that if you replace the "?" output-parameter indicator by the name of a variable with a suitable datatype, then the stored procedure will execute and not display the dialog screen that you show in your question. For example:

    create or replace variable p_stat integer;

CALL LIB.ADD_DUTLINK ( p_stat, 99612, '0520', '101', 'TRL1', '13573333', 'A', '0085' );

    Perhaps you should open an issue on the DataGrip forum https://intellij-support.jetbrains.com/hc/en-us/community/topics/200381555-DataGrip