How to make DB2 re-evaluate the query plan each execution?

Question

I have the following DB2 stored procedure that allows the user to specify a variable number of parameters (specifying NULL for parameters they don't want to provide), where each parameter can be used to filter records in a WHERE clause.

CREATE PROCEDURE XX.DUMMY(IN I_FIRST_NAME VARCHAR(32),
                            I_LAST_NAME VARCHAR(100),
                            I_COMPANY_NAME VARCHAR(100))
SPECIFIC XX.DUMMY
RESULT SETS 1
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN
    DECLARE RESULTS CURSOR WITH RETURN TO CLIENT FOR
        WITH RELEVANT_RECORDS (RECORD_ID) AS (
            SELECT RECORD_ID
            FROM XX.RECORDS R
            WHERE (I_FIRST_NAME IS NULL OR R.SEARCH_FIRST_NAME = I_FIRST_NAME)
            AND (I_LAST_NAME IS NULL OR R.SEARCH_LAST_NAME = I_LAST_NAME)
            AND (I_COMPANY_NAME IS NULL OR R.SEARCH_COMPANY_NAME = I_COMPANY_NAME)
            WITH UR
        )
        SELECT RR.RECORD_ID, RD.*
        FROM RELEVANT_RECORDS RR
        JOIN XX.RECORD_DETAILS RD ON RD.RECORD_ID = RR.RECORD_ID
        FOR READ ONLY WITH UR;
    OPEN RESULTS;
END
;

It's my understanding that this procedure will perform very poorly because the procedure's query plan will only be evaluated when the procedure is bound (or ran the first time?), where if different combinations of parameters are specified in later executions the original query plan will be used, resulting in the wrong plan/indexes being used (thus making the proc very slow).

I read that SQL Server allows you to specify OPTION(RECOMPILE) in the procedure's definition to tell the server to re-evaluate query plan(s) for each execution of the procedure, however since I'm using DB2 I'm looking for a DB2 equivalent.

Is there a way in DB2 that you can tell a stored procedure to re-evaluate it's query plan each execution? Do I have to use something like SET CURRENT QUERY OPTIMIZATION inside my procedure before any statements are ran?

Answer 1

Using dynamic SQL in your routine (instead of static SQL) is one way to ensure the query gets compiled each time (while it is different from a previous instantiation, or not in the package-cache etc).

In this case in your routine, you can use EXECUTE IMMEDIATE on a query you dynamically create inside the routine, or use PREPARE and EXECUTE. In both cases the full query does not exist until run time immediately before execution. So the SQL string that you build can be different each time, including (for execute) a different number of parameter-markers (?) each time and a matching number of bind parameters. The SQL query itself is not static - it can be different every time the procedure runs - varying with the (sanitized) parameter inputs to the stored procedure.

For static-SQL, take a look at stored procedure SET_ROUTINE_OPTS , with the REOPT ALWAYS which needs to be done at build time , or you can use the registry variable DB2_SQLROUTINE_PREPOPTS to supply this instruction globally (usually this is unwise). You (or the DBA) can also arrange to rebind the static package corresponding to the sproc, with REOPT ALWAYS.

Answer 2

You may try the DMBS_SQL module.
It looks a little bit lengthy for such a simple purpose, but it does the work.
The final SELECT statement is constructed depending on each parameter nullability.
XML local variables are used to accumulate the rows fetched to return them from SP finally. You may probably use some [C|G]GTT or ARRAY variable instead of these XML variables for that.

--#SET TERMINATOR @

-- Dynamic number of parameters demo
CREATE OR REPLACE PROCEDURE TEST_DBMS_SQL
(
  P_TABSCHEMA VARCHAR(128)
, P_TABNAME   VARCHAR(128)
)
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE curid       INTEGER;
  DECLARE v_sql       VARCHAR(1000);
  DECLARE v_tabschema VARCHAR(128);
  DECLARE v_tabname   VARCHAR(128);
  DECLARE v_status    INTEGER;
  DECLARE v_doc       XML;
  DECLARE v_node      XML;
  
  DECLARE C1 CURSOR WITH RETURN FOR
  SELECT TABSCHEMA, TABNAME
  FROM XMLTABLE 
  (
    '$D/NODE' PASSING v_doc AS "D"
    COLUMNS
      TABSCHEMA VARCHAR(128) PATH 'TABSCHEMA'
    , TABNAME   VARCHAR(128) PATH 'TABNAME'
  );
  
  SET v_doc = XMLELEMENT(NAME "DOC");
  SET v_sql = 'SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE 1=1';
  IF P_TABSCHEMA IS NOT NULL THEN
    SET v_sql = v_sql || ' AND TABSCHEMA = :p_tabschema'; 
  END IF;
  IF P_TABNAME IS NOT NULL THEN 
    SET v_sql = v_sql || ' AND TABNAME   = :p_tabname'; 
  END IF;

  CALL DBMS_SQL.OPEN_CURSOR(curid);
  CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
  
  IF P_TABSCHEMA IS NOT NULL THEN 
    CALL DBMS_SQL.BIND_VARIABLE_VARCHAR(curid, ':p_tabschema', P_TABSCHEMA);
  END IF;
  IF P_TABNAME IS NOT NULL THEN 
    CALL DBMS_SQL.BIND_VARIABLE_VARCHAR(curid, ':p_tabname', P_TABNAME);
  END IF;

  CALL DBMS_SQL.DEFINE_COLUMN_VARCHAR(curid, 1, v_tabschema, 128);
  CALL DBMS_SQL.DEFINE_COLUMN_VARCHAR(curid, 2, v_tabname, 128);

  CALL DBMS_SQL.EXECUTE(curid, v_status);
  --CALL DBMS_OUTPUT.PUT_LINE('Execute: ' || v_status);
  
  FETCH_LOOP: LOOP
  
    CALL DBMS_SQL.FETCH_ROWS(curid, v_status);
    IF v_status = 0 THEN LEAVE FETCH_LOOP; END IF;
    --CALL DBMS_OUTPUT.PUT_LINE('Fetch: ' || v_status);
    CALL DBMS_SQL.COLUMN_VALUE_VARCHAR(curid, 1, v_tabschema);
    CALL DBMS_SQL.COLUMN_VALUE_VARCHAR(curid, 2, v_tabname);
    --CALL DBMS_OUTPUT.PUT_LINE('TABSHEMA: ' || coalesce(v_tabschema, '*'));
    --CALL DBMS_OUTPUT.PUT_LINE('TABNAME: ' || coalesce(v_tabname, '*'));
    SET v_node = XMLELEMENT
    (
        NAME "NODE"
      , XMLELEMENT(NAME "TABSCHEMA", v_tabschema)
      , XMLELEMENT(NAME "TABNAME", v_tabname)
    );
  
    SET v_doc = XMLQUERY
    (
      'transform copy $mydoc := $doc modify do insert $node as last into $mydoc return $mydoc'
      passing v_doc as "doc", v_node as "node"
    );
  
  END LOOP FETCH_LOOP;
  
  --CALL DBMS_OUTPUT.PUT_LINE(XMLSERIALIZE(v_doc AS CLOB(10K)));
  CALL DBMS_SQL.CLOSE_CURSOR(curid);
  
  OPEN C1;
END@  

Usage:

call test_dbms_sql(NULL, 'TABLES')@
call test_dbms_sql('SYSCAT', 'TABLES')@
call test_dbms_sql('SYSCAT', NULL)@
call test_dbms_sql(NULL, NULL)@