Re: iSeries Navigator

On 27-Apr-2015 13:57 -0500, Englander, Douglas wrote:

I am trying to create a SQL Function that uses a user defined
function in a service program.

I created the CREATE FUNCTION statement in a member in QSQLSRC and
used RUNSQLSTM to compile it and create the function. The function
was created in the correct library, and iSeries Navigator lists it
when I click on the FUNCTIONS dropdown for that specific schema. The
function is in one schema and references a service program in another
schema. Both schemas are in my iSeries Navigator's schema list.

When I tried using Navigator to test the function it would not work
and always said it could not find the service program in *LIBL. I
noticed that the QZDASOINIT job that my iSeries Navigator was
connected to did NOT have either of those libraries in the library
list, even though both were in the schema list.

I went to green screen, set up my correct library list, and used
STRSQL to test my SQL statement. It worked fine the first time, and I
did not receive any errors.

Has anyone else seen this where changing the Schema List (which I
THOUGHT was the library list), does not update the library list on
the QZDASOINIT job my iSeries Navigator session is connected to? Is
there a PTF to fix this, or is there a bug in iSeries Navigator? Does
the schema list not apply to Functions?

The PATH is what determines the resolution for unqualified routine [including FUNCTION] references. Of course, library-qualifying the function reference would avoid the error about the library of the routine not being in the library-list. Otherwise establish the PATH such that the library of the routine is included.

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzcurpath.htm>
_CURRENT PATH_
"The CURRENT PATH special register specifies the SQL path used to resolve unqualified distinct type names, function names, and procedure names in dynamically prepared SQL statements.
...
The CURRENT PATH special register contains the value of the SQL path, which is a list of one or more schema names. ...
..."

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzsetpath.htm>
_SET PATH_
"The SET PATH statement changes the value of the CURRENT PATH special register.
..."

What is referred to above as the "Schema List" seems possibly to be describing the editable list of SCHEMA names that are utilized by other list presentations [i.e. list APIs such as SQLTables()] to pare those other lists. AFaIK that "Schema List" is *not*, and does not establish, the library list [of the server job]; that list is merely a logical designation of, from which libraries other listing functions should narrow what is displayed to the user.
<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzatc/rzatceditlib.htm>
_Editing the list of schemas displayed_
"By editing the list of schemas displayed, you can hide from your view those schemas that you do not use frequently. ..."

Presumably what is of interest instead of that "Schema List", is the JDBC property Librarylist:
<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzahh/jdbcliblistproperty.htm>
_JDBC Librarylist property_
"The JDBC LibraryList property specifies one or more libraries that you want to add to or replace the library list of the server job, and optionally sets the default library (default SQL schema).
..."

Perhaps also of interest are the Format property "naming", the Server property "libraries":
<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzahh/jdbcproperties.htm>
_IBM Toolbox for Java JDBC properties_
"Many properties can be specified when connecting to a server database using JDBC. All properties are optional and can be specified either as part of the URL or in a java.util.Properties object. If a property is set in both the URL and a Properties object, the value in the URL will be used.
..."