On 05-Jun-2014 17:07 -0500, Buck Calabro wrote:
When I create an SQL function using SQL naming, it appears to
remember the CURRENT SCHEMA that was in effect when the function was
created rather than the one that is in effect when the function
Inside the function is an unqualified <ed: table-reference in a>
SELECT statement. My reading of the manual indicates that an
unqualified table reference in SQL naming would use the CURRENT
SCHEMA. <<SNIP re-create>>
I know that if I change my CREATE FUNCTION time naming to *SYS that
the function will use *LIBL to find the table (as expected)
Only if the CURRENT SCHEMA is set to DEFAULT; i.e. set to *LIBL, for
but I am surprised that the function does not use the CURRENT SCHEMA
at runtime to locate the table.
The run-time value should be used for any dynamic SQL.
I must be overlooking something but my hours in the manual haven't
revealed it and searching the internet has a high noise to signal
The qualification is bound at compile-time rather than being
established at run-time; the side effect, for static SQL.
For the SQL Naming, the qualifier is either defaulted to the
authorization identifier [SET CURRENT SCHEMA DEFAULT] or to the
specified SCHEMA. For the System Naming, the qualifier is either
defaulted to the *LIBL [library list; SET CURRENT SCHEMA DEFAULT] or to
the specified SCHEMA. Notice that for both naming options, "or to the
The effect is visible with Print SQL Information (PRTSQLINF) against
the Service Program (*SRVPGM) for the User Defined Function (UDF). The
CURRENT SCHEMA value appears in the Default RDB Collection (DFTRDBCOL)
parameter information [presented by the PRTSQLINF] and that value is
described in the help text for the parameter of the same name found on
the Run SQL Statement (RUNSQLSTM) command:
" Default collection (DFTRDBCOL) - Help
Specifies the name of the schema identifier used for the unqualified
names of the tables, views, indexes, SQL packages, aliases, constraints,
external programs, node groups, and triggers. This parameter applies
only to static SQL statements.
The naming convention specified for the Naming convention (NAMING)
parameter is used.
Specify the name of the schema identifier to be used instead of the
naming convention specified for the NAMING parameter.