|
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
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
