Re: Using library name as a variable in embedded SQL

Hi Pete-

Thanks for your response.  It's my understanding that the SQE "remembers"
plans (10,000-20,000 plans on V5R4) and shares them as required, so the same
SQL statement run five times with five different libraries should generate
five separate access plans.  But it's possible that embedded SQL isn't
designed to resolve schema at execution time and I'll have to decide if the
hard-coded library is a Wilbury or not.

On 5/20/06, Pete Hall <pbhall@xxxxxxxxxxxxx> wrote:

Different library means different object and different access path.
AFAIK, there's no choice there. In order to open a new table with the
same statement you need to close the original first, one way or another,
then open the new. The few times I've needed to do that in the past,
I've built a dynamic statement and used PREPARE and EXECUTE/DECLARE or
EXECUTE IMMEDIATE.

--

Pete Hall
pbhall@xxxxxxxxxxxxx
http://www.pbhall.us/



Reeve wrote:
> I'm trying to work with tables in a library not in my library list and
am
> trying to figure out a way to soft-code the library in my embedded SQL
> statement.  I don't plan on touching multiple libraries and I don't like
> hard coding.   I'm trying to do something like this:
>
> C/exec SQL
> C+        Update :library/file set name = :new_name, address =
:new_address
> where key = :new_key with NC
> C/end-exec
>
> I know I can grab CURRENT SCHEMA from the special register, SET CURRENT
> SCHEMA to the new library, and SET CURRENT SCHEMA back to the original
> library list, or I can run the 'system' procedure to  OVRDBF and
DLTOVR.  I
> can use dynamic SQL and eliminate the library problem but I don't know
if
> the SQE is smart enough to reuse the access plan (I'm still on V5R3; I
know
> V5R4 does some cooler stuff).  I've considered abandoning SQL and
setting up
> logical files in my library list with hard-coded qualified PFILE's.
>
> I'm using *SYS naming and "key" is the file's primary key.
>
> Is there a way to handle this using embedded SQL or do I need to look at
> another approach?
>
> Thanks,
> reeve
>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.