My problem is with unqualified names (*LIBL is used to facilitate testing). I'm looking at forcing *SYS naming.
Thanks
-----Original Message-----
From: Birgitta Hauser [mailto:Hauser@xxxxxxxxxxxxxxx]
Sent: Sunday, March 15, 2020 12:25 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Determine SQL or System naming?
Hmmm, I think it is not as easy.
You can set and use the naming conventions in your environment. ... as far as good!
But if you call a stored procedure or a user defined function the SQL routine is executed with the naming convention which was used when the routine was generated.
I.e. you can call a stored procedure with SQL naming in an environment where system naming conventions are used (and vice versa!)
The difference between SQL and System Naming is much more than separating the Schema and object with a dash (/) or a period (.). BTW this rule is relaxed for system naming conventions with Release 7.1 TR 6, i.e. when using system naming conventions you can use either dash OR period as separator between schema and object.
Much more important is, what happens when database objects are unqualified used?
When using System Naming conventions, the (current) library list is searched.
When using SQL Naming conventions, tables, views and indexes are searched within the DEFAULT/CURRENT SCHEMA. If a current schema in not explicitly set, unqualified specified tables, views and indexes are searched in a schema with the same name as the (current) user profile. Library List is ignored!
Unqualified specified routines (SQL Stored Procedures, User Defined Functions, User Defined Table Functions) are not searched within the Default schema but within the SQL Path. The SQL Path can be set by executing the SET PATH statement. Within the SQL Path multiple schemas can be listed (like in a library list) and even *LIBL is a special value that can be specified in the SQL Path. If SQL Routines are located within the data library (CURRENT SCHEMA), the data library must also be specified within the SQL PATH, even though it is set as CURRENT SCHEMA.
BTW Current Schema (SQL) and Current Library (CL) are not identical.
Also when creating a database object with System Naming conventions ... everything works as we expect it on the I, i.e. owner is the user that creates the object, except it belongs to a group profile and the group profile becomes the owner of the generated objects. *PUBLIC authority depends on the System Value QCRTAUT (default *CHANGE).
When creating a database object with SQL Naming conventions, the owner is either the user profile or the group profile, except there is a user profile with the same name as the library. In this case the user profile with the same name as the library in which the object is generated gets the owner of the database object. Can be even a user profile with only *USE authority.
When creating a database object with SQL Naming conventions, *PUBLIC authority is always *EXCLUDE.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) "If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Bryan Dietz
Sent: Samstag, 14. März 2020 19:58
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Determine SQL or System naming?
Would be nice for this :
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqudfgetjobinfo.htm <
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqudfgetjobinfo.htm>
To include, maybe if needed a RFE is in order.
I do recall a PTF(cannot find it) that “allowed” you to use SQL naming even if your session was set for SYSTEM naming.
So I’ve been using SQL naming and not using SYSTEM at all.
Bryan
On Mar 14, 2020, at 1:08 PM, David G via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
On Fri, Mar 13, 2020 at 2:29 PM Justin Taylor <JUSTIN@xxxxxxxxxxxxx> wrote:
How can I check a running job to determine if it's using SQL or
System naming?
From inside the job or outside?
--
IBM i on Power Systems: For when you can't afford to be out of business!
I'm riding in the American Diabetes Association's Tour de Cure to
raise money for diabetes research, education, advocacy, and awareness.
You can make a tax-deductible donation to my ride by visiting
https://mideml.diabetessucks.net.
You can see where my donations come from by visiting my interactive
donation map ... https://mideml.diabetessucks.net/map (it's a geeky thing).
I may have diabetes, but diabetes doesn't have me!
--
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
midrange.com
