Optimization profile and explain mode? The other day I noticed something strange that perhaps someone have an explanation for. The problem I was facing was a query that due to a skewed distribution of data generated a poor plan when a parameter marker was used. The execution time was prox 10 times higher compared to a hardcoded value concatenated into the query.

I have never tried optimization profiles before, so I thought it would give it a shot. I installed a profile like:

&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;OPTPROFILE VERSION="10.5.8.0"&gt;
&lt;STMTPROFILE ID="Guideline for update of process_state_id"&gt;
    &lt;STMTKEY&gt;
       &lt;![CDATA[UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90 WHERE EXISTS ( SELECT     1 FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID AND SUBSTR(P.PNR, 9, 1) &lt;&gt; 'F' ) AND NOT EXISTS ( SELECT    1 FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID ) AND PCO.ADMISSIONROUND_ID = ? AND PCO.RESULT = 20]]&gt;
    &lt;/STMTKEY&gt;
    &lt;OPTGUIDELINES&gt; 
      &lt;REOPT VALUE='ALWAYS'/&gt; 
    &lt;/OPTGUIDELINES&gt;
&lt;/STMTPROFILE&gt;




This gave me what I wished for, i.e. the query where recompiled every time it got a new parameter and benefitted from distribution statistics. So far so good.

I wanted to take a closer look at how the plan changed due to the optimization profile, so I turned on explain mode and fired of the query:

]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql
SQL0217W  The statement was not executed as only Explain information requests are being processed.  SQLSTATE=01604
]$ db2exfmt -d ...


Much to my surprise, I get an error doing the same thing with the optimization profile enabled:

]$ db2 "set current optimization profile test.optprof"
DB20000I  The SQL command completed successfully.
]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql 
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0313N  The number of variables in the EXECUTE statement, the number of 
variables in the OPEN statement, or the number of arguments in an OPEN 
statement for a parameterized cursor is not equal to the number of values required.  SQLSTATE=07004


If I run the query from python or java everything works well, so it is more a question of the convenience to be able to explain statements from command line. 

In addition, it also works to explain the plan like:

]$ db2 "explain plan for UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90 
&gt; WHERE EXISTS (
&gt;      SELECT     1     
&gt;         FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID     
&gt;         AND SUBSTR(P.PNR, 9, 1) &lt;&gt; 'F' 
&gt; ) AND NOT EXISTS (
&gt;     SELECT    1    
&gt;         FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL    
&gt;         WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID     
&gt;         AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID    
&gt;         AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID 
&gt; ) AND PCO.ADMISSIONROUND_ID = ?
&gt; AND PCO.RESULT = 20"
DB20000I  The SQL command completed successfully.


This, however, becomes a bit unpractical for large queries since it requires either to change the query in the file, keep two files, one for explain and one without, or as above executing the query directly. 

Does the optimization profile somehow invalidate the explain mode? It does not matter in what order I set explain mode and optimization profile, I cannot execute a query with a parameter marker either way. Any thoughts anyone?
The REOPT(ALWAYS) option conflicts with the explain facility behaviour, because in this case the statement compilation is delayed until the bind variable value is known -- which is the whole point of reoptimization, isn't it. The manual says in particular:


  The REOPT bind option defers statement compilation from PREPARE to OPEN or EXECUTE time, when the input variable values are known. The values are passed to the SQL compiler so that the optimizer can use the values to compute a more accurate selectivity estimate. 


You are not providing the expected value, so compilation cannot proceed.

The EXPLAIN statement succeds I believe because it doesn't get to the re-optimization phase therefore not expecting a value for the bind variable. As a result, the plan it generates will probably differ from the reoptimized one.

You can address this by creating a global variable: CREATE VARIABLE myparm INT DEFAULT 5 (or whatever data type and value you need) and then referencing myparm in the statement where you have the parameter marker now. You will also need to create a new profile that matches the new statement text.

Tested and works on v11.1 but I assume the behaviour would be the same in 10.5.