Re: How to tell it to NOT reuse an ODP

hmmmmm...

well, what about QAQQINI?
Take a look at OPEN_CURSOR_CLOSE_COUNT and OPEN_CURSOR_THRESHOLD

another one that _might_ apply... REOPTIMIZE_ACCESS_PLAN

Note that this file is read for each SQL statement, so you can update
the values on the fly in a program...



On Thu, 2006-08-24 at 10:48 -0400, rob@xxxxxxxxx wrote:

We have a program that does something like
do...
  exec sql drop alias a1;
  exec sql create alias a1 as ...;
  exec sql execute immediate :stmt; // perform an insert into a file based 
on using alias a1 in a subselect
enddo;

The problem is that it doesn't use the recreated alias that is now based 
on a file in a new library.  It's reusing the odp from the first time the 
alias was created.  If we run the program in debug, copy the subselect to 
the clipboard, F21 for command line, prompt RUNSQL and paste the subselect 
in there it uses a new odp and retrieves the right data.  Close cursor is 
set to endmod.  Not that we're using any cursors, but just in case you 
need to know that.

Alternatives we're kicking around:
- Using RCLRSC before the drop alias.
- Instead of execute immediate use QCMDEXC and RUNSQL.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com