Re: How to tell it to NOT reuse an ODP

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

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