MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » June 2014

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

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

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.

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.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact