DB2: Procedure with a simple select return more than 15x slow compared to the same select in a query

Question

I have a select that return a single column, using as where clause 4 columns, and I created a specific index for this query. When I do this select using Dbeaver, the result return in 30~50 milliseconds.

      SELECT
       column1
      FROM 
       myTable
      WHERE
       column2 = a
       AND column3 = b
       AND (column4 = c AND column5 = d )
       FOR READ ONLY WITH UR; 

Now I created a procedure with this same simple select. The proc declare a P1, a 'cursor with return', do the select, open the cursor and close the P1. When I use the same dbeaver connection the result is returning between 1.2 ~ 1.6 seconds.

 CREATE  PROCEDURE myProc (
 IN a  DECIMAL(10),
 IN b  DECIMAL(10),
 IN c  DECIMAL(10),
 IN d  DECIMAL(10)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
SPECIFIC myProc

P1: BEGIN

 DECLARE cursor1 CURSOR WITH RETURN for
 
  SELECT
   column1
  FROM 
   myTable
  WHERE
   column2 = a
   AND column3 = b
   AND (column4 = c AND column5 = d )
   FOR READ ONLY WITH UR; 
  
 OPEN cursor1;
END P1

Is this huge return difference correct? If wrong, Is there something wrong in my procedure that justifies this return time difference? Or could be something wrong in the DB configuration or the server that justifies this difference, something like few resources in the DB server or something like the proc not using the index( I don't know if procs in the DB2 use the index by default like the queries )?

I am new in DB2 and new in procedures creation. Thank you in advance.

Best regards, Luis

Answer 1

I don't know if is the best way, but I solved the problem using a solution that I read for SQL Server. The solution is create local variables that will receive the parameters values, and use the variable in the queries, to guarantee always the best execution plan. I didn't knew if this was valid to DB2, but my proc now has almost the same response time compared to query. Worked!

Link of SQL Server post: SQL Server: Query fast, but slow from procedure

In this link above a user called @Jake give this explanation: "The reason this happens is because the procedures query plan is being cached, along with the parameters that were passed to it. On subsequent calls, this query plan generated will be reused with new parameters. This can cause problems because if the data is unevenly distributed, one parameter can generate a sub-optimal plan vs. another. Using local variables essentially does the same as OPTIMIZE FOR UNKNOWN because local variables cannot be sniffed."

I think that is the same for DB2, because worked. After I change these old procedures to use local variables my execution plan begun to use the indexes recently created