× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Think I'll stick with *SQL and just run
set path = *LIBL;

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Thursday, March 28, 2019 1:19 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Library lists vs Run SQL Scripts

Basically "Default SQL Schema: Use library list of server job" says SET current schema = select schema_name from qsys2.library_list_info where type='CURRENT'

And "Library list:" says 'what libraries would you like to replace ROB in this current list: "QSYS","QSYS2","SYSPROC","SYSIBMADM","ROB" derived from VALUES CURRENT PATH'. I changed blank to ROUTINES and got "QSYS","QSYS2","SYSPROC","SYSIBMADM","ROUTINES". Trying to use a value of *LIBL in "Library list:" results in the system ignoring that and going back to using ROB.

Format, naming convention. Changing from *SQL to *SYS will change the library list from "QSYS","QSYS2","SYSPROC","SYSIBMADM","ROB" to *LIBL, but the help makes no mention of this.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon Hamberg
Sent: Thursday, March 28, 2019 12:58 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Library lists vs Run SQL Scripts

Configuration of this is in the Connection menu, the Edit JDBC Configuration - Default (the last word will be the name of a configuration you have saved).

The setup for libraries (schemas) is on the System tab of the dialog - there's a blue-circle with question mark on the lower right - click on it to get help for setting up. The language is SQL-ish, so they say "job schema list" instead of "job library list" - things like that.

Naming convention is on the Format tab.

Cheers
Vern

On 3/28/2019 11:00 AM, Rob Berendt wrote:
Trying to translate: If the SQL path is not explicitly specified, the SQL path is the system path followed by the run-time authorization ID of the statement.

values current path;
return
"QSYS","QSYS2","SYSPROC","SYSIBMADM","ROB"

select * from qsys2.library_list_info; returns
QSYS SYSTEM
QSYS2 SYSTEM
QHLPSYS SYSTEM
QUSRSYS SYSTEM
QIWS PRODUCT
ROB CURRENT
QTEMP USER
QGPL USER
ROUTINES USER

There is no
Values system path
To match
Values current path
So it leaves me to deduce that system path is current path minus the "run-time authorization ID of the statement" or ROB.
And, system path is unrelated to the system portion of the library list.

It would be nice if the blue text for "see SQL path" was actually a
hot link at
https://www.ibm.com/support/knowledgecenter/search/Unqualified%20funct
ion%20resolution?scope=ssw_ibm_i_73
So I searched for "SQL path" and got this hit at:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzcurp
ath.htm
Otherwise,
For SQL naming, "QSYS", "QSYS2", "SYSPROC", "SYSIBMADM", "the value of the run-time authorization ID of the statement" .
For system naming, "*LIBL".

You did not indicate:
1 - whether or not you are using system or sql naming.
2 - whether or not you are using STRSQL or Run SQL Scripts STRSQL sets
current path to *LIBL either way. Run SQL Scripts does not. This may be a recent change to STRSQL. I think I read this somewhere.

I failed to see anything in the settings for Run SQL Scripts to set this.
Running this
set path = *LIBL;
Seems to resolve it.
Not sure what issues missing SYSPROC and SYSIBMADM from the path may cause.







-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Thursday, March 28, 2019 11:28 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Library lists vs Run SQL Scripts

FTFM...

Unqualified function resolution: When a function is invoked with only a function name, the database manager needs to search more than one schema to resolve the function instance to execute. The SQL path contains the list of schemas to search. For each schema in the SQL path (see “SQL path” on page 64), the database manager selects candidate functions.

If the SQL path is not explicitly specified, the SQL path is the system path followed by the run-time authorization ID of the statement.

On my system,
select current_path from sysibm.sysdummy1;

Returns '*LIBL'.

When connected using *SYS naming and "Default SQL schema = Use library list from server job"

select * from qsys2.library_list_info; returns the library list I
expect

select * from table(mylib.myudtf()) as X; works but this fails if
mylib in not in the *LIBL select * from table(myudtf()) as X;

But if I add MYLIB to my library list, then it works just fine.

Charles



On Thu, Mar 28, 2019 at 9:03 AM Rob Berendt <rob@xxxxxxxxx> wrote:

BTW, many functions,
when ran in Run SQL Scripts,
do not seem to respect library lists for finding the function themselves.

cl: addlible routines;
select * from qsys2.library_list_info; values MYFUNCTION(...);

But this works
values ROUTINES.MYFUNCTION(...);

Why is that?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.