I am not sure exactly what you are asking. REOPT is a BIND/REBIND option. This works at the package level. When a package is bound/rebound using REOPT ONCE option, the package will be optimized with the real values used on the first call to the package and that plan will be used for subsequent calls to the package. How the package is invoked, whether through JDBC or not does not matter.

HTH

Andy

Yes, reopt once is a bind/rebind options. It is also an option one can put in db2cli.ini and effectively have that behaviour for dynamic sql made from CLI application. (this is documented only in the docs of v9 but it works also with v8 (fp12 atleast)). Additionally reopt once can be enabled programatically by setting statement or connection attribute in ODBC/CLI.

So, my question is how could have reopt once behaviour for dynamic sql run from jdbc application?

The way I read the documentation, REOPT for dynamic SQL (regardless whether is is JDBC or not) only is valid if it uses host variables or parameter markers. So with JDBC that would mean that it need to be a PreparedStatement. REOPT works the same way as static SQL. REOPT ONCE will only optimize the first time the statement executes.

HTH

Andy

Right. So what package I shall rebind to make it work with prepared statements in Java?
I imagine that when a new statement is prepared, db2 adds a section into some package. I have no idea what the package name is, so I do not know what to rebind with reopt once. Additionally in order this feature to be practical the rebinding has to happen automatically (i.e. should be possible to be triggered programatically or alternatively it should be possible to be configured somewhere as default).

There is no package in the system catalog that you can BIND/REBIND from a JDBC PreparedStatement. By putting the REOPT option in db2cli.ini, you are telling DB2 how to do the REOPT of these statements dynamically. When dynamic SQL is submitted to DB2, it gets optimized and placed in the package cache of DB2. This is the "package" of the SQL from JDBC. You do not have direct access to it. REOPT ONCE (which I believe is the default behavior) means that the first time the PreparedStatement is seen by the package cache, it is optimized and an access plan is generated then. If you specify REOPT ALWAYS, then it will always generate an access plan on every call, just like dynamic SQL without parameter markers.

HTH

Andy