Re: SQL CREATE FUNCTION uses schema from create time

On 05-Jun-2014 17:07 -0500, Buck Calabro wrote:

7.1 TR7

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
executes.

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 *SYS naming.

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
ratio.

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 specified SCHEMA."

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.

*NONE
The naming convention specified for the Naming convention (NAMING) parameter is used.

name
Specify the name of the schema identifier to be used instead of the naming convention specified for the NAMING parameter.
"