Re: Embedded SQL and SET SCHEMA

SQL does take the library list into account for SET SCHEMA. Use the combination of *SYS naming and the implicit default or the explicitly SET SCHEMA DEFAULT.

Understand that the *LIBL is a *SYS naming concept, the CURRENT SCHEMA an SQL concept; the latter is maintained and understood only by\for the SQL, and has no effect on the OS concept of User Library List. To use the library list, use the operating system CHGCURLIB, ADDLIBLE, etc. to control the library list. Since the program "object" for embedded SQL is partially "bound" to the environment at creation time, the effect of the SET SCHEMA is seen only by the dynamic statements; and why DFTDYNCOL overrides any SET. That create-time binding may be lost by changes to the library list [due to a library change for the target VIEW\TABLE of an existing plan], and thus impacts run-time; similarly for stored\cached plans, but if memory serves, the plans for a process are all "tossed out" with the *LIBL change versus only for each validated for change.

Regards, Chuck

On 12/6/10 12:33 AM, Paul Tuohy wrote:

But am I missing something? Does this not seem a bit odd?

Considering that static embedded SQL takes the library list into
consideration - should it not do the same for SET SCHEMA?

CRPence wrote:

On 12/5/10 8:26 AM, Paul Tuohy wrote:

Is it the case that SET SCHEMA has no effect in embedded
SQL - regardless of which naming is being used?

See the docs. The CURRENT SCHEMA is applied as qualifier only for
dynamic SQL statements. Refer to the PRTSQLINF output of a program
with embedded SQL to see the DFTRDBCOL or NAMING option to know what
is the default for CURRENT SCHEMA.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstsetschema.htm


CURRENT SCHEMA: The value of the CURRENT SCHEMA special register is
used as the qualifier for unqualified names in all dynamic SQL
statements except in programs where the DYNDFTCOL has been specified.
If DYNDFTCOL is specified in a program, its schema name is used
instead of the CURRENT SCHEMA schema name.

Regards, Chuck