RE: How to tell it to NOT reuse an ODP

Rob, I think this is a bug, but don't have time right now to look for the
right PTF.  I know you're not PMR adverse, so give IBM a ping :)

Elvis

-----Original Message-----
Subject: How to tell it to NOT reuse an ODP

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