?
Solved

SQL names :vs: SYSTEM names

Posted on 2008-10-22
15
Medium Priority
?
1,371 Views
Last Modified: 2012-05-05
According to DOCs for AS400 iSeries SQL R5V3 - when creating an object (like a Proc, Table, View, etc) ...

          If SQL names are used, procedures are created with the system authority of
          *EXCLUDE on *PUBLIC. If system names are used, procedures are created
          with the authority to *PUBLIC as determined by the create authority
          (CRTAUT) parameter of the schema.

Here is a CREATE PROC sample which "apparently" uses "SQL NAMES" ... I know this because ... it's created with *EXCLUDE on *PUBLIC
--  Generate SQL
--  Version:                        V5R3M0 040528
--  Generated on:                   10/22/08 11:30:50
--  Relational Database:            STEEL
--  Standards Option:               DB2 UDB iSeries
SET PATH "QSYS","QSYS2","CISFGV" ;
CREATE PROCEDURE MYSCHEMA.MYPROC ( )
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
     SPECIFIC MYSCHEMA.MYPROC
     NOT DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     P1 : BEGIN
          DECLARE C1 CURSOR FOR SELECT * FROM MYSCHEMA.MYTABLE
          OPEN C1 ;
     END P1  ;

============================================================
So can somone give an example where SYSTEM NAMES are used?


0
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22777864
maybe

SET PATH "QSYS","QSYS2","CISFGV" ;
CREATE PROCEDURE QSYS.MYPROC ( )
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
     SPECIFIC MYSCHEMA.MYPROC
     NOT DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     P1 : BEGIN
          DECLARE C1 CURSOR FOR SELECT * FROM MYSCHEMA.MYTABLE
          OPEN C1 ;
     END P1  ;

 ?
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 2000 total points
ID: 22777954

The naming convention is usually set in the SQL connection.

The only difference in the proc-code for "system" naming convention is that you use a slash instead of a dot to separate the schema and the table.

Therefore, you're proc becomes this:
SET PATH "QSYS","QSYS2","CISFGV" ; 
CREATE PROCEDURE MYSCHEMA/MYPROC ( ) 
     DYNAMIC RESULT SETS 1 
     LANGUAGE SQL 
     SPECIFIC MYSCHEMA.MYPROC 
     NOT DETERMINISTIC 
     MODIFIES SQL DATA 
     CALLED ON NULL INPUT 
     P1 : BEGIN 
          DECLARE C1 CURSOR FOR SELECT * FROM MYSCHEMA/MYTABLE
          OPEN C1 ; 
     END P1  ;

Open in new window

0
 
LVL 5

Author Comment

by:volking
ID: 22779207
@DaveSlash
Still can't get it to set *PUBLIC to match (CRTAUT) parameter of the schema
It's still getting set to *EXCLUDE

hmmmm ... reading your example carefully  ... I noticed

You changed
CREATE PROCEDURE MYSCHEMA.MYPROC ()
to
CREATE PROCEDURE MYSCHEMA/MYPROC ()

You did NOT change
SPECIFIC MYSCHEMA.MYPROC

You did change
DECLARE C1 CURSOR FOR SELECT * FROM MYSCHEMA.MYTABLE
to
DECLARE C1 CURSOR FOR SELECT * FROM MYSCHEMA/MYTABLE

Is your second "NOT" changed as intended?
and
Is the third change ... addressing all tables via SYSTEM names ... intended?


0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 18

Expert Comment

by:Dave Ford
ID: 22779278

> Is your second "NOT" changed as intended?

I missed that one. It should also change to a slash.

0
 
LVL 5

Author Comment

by:volking
ID: 22779578
@DaveSlash
> Is the third change ... addressing all tables via SYSTEM names ... intended?

????
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 22779649

Your question asked "can somone give an example where SYSTEM NAMES are used?"

Therefore, it was absolutely intentional to address all tables via SYSTEM names.
0
 
LVL 5

Author Comment

by:volking
ID: 22779869
@DaveSlash

Still ... "No Joy"

Here is exact code ...
===============================================================
--  Generate SQL
--  Version:                        V5R3M0 040528
--  Generated on:                   10/22/08 14:36:51
--  Relational Database:            STEEL
--  Standards Option:               DB2 UDB iSeries
SET PATH "QSYS","QSYS2","CISFGV" ;
CREATE PROCEDURE CSIDEV/TEST22 ( )
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
     SPECIFIC CSIDEV/TEST22
     NOT DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
     P1 : BEGIN
     DECLARE C1 CURSOR FOR SELECT * FROM CSIDEV/YMD;
     OPEN C1 ;
END P1  ;
===============================================================
Error is
SQL State: 42833
Vendor Code: -5016
Message: [SQL5016] Qualified object name TEST22 not valid. Cause . . . . . :   One of the following has occurred: -- The syntax used for the qualified object name is not valid for the naming option specified.  With system naming, the qualified form of an object name is schema-name/object-name.  With SQL naming the qualified form of an object name is authorization-name.object-name. -- The syntax used for the qualified object name is not allowed. User-defined types cannot be qualified with the schema in the system naming convention on parameters and SQL variables of an SQL procedure or function. Recovery  . . . :   Do one of the following and try the request again: -- If you want to use the SQL naming convention, verify the SQL naming option in the appropriate SQL command and qualify the object names in the form authorization-id.object-name. -- If you want to use the system naming convention, specify the system naming option in the appropriate SQL command and qualify the object names in the form schema-name/object-name. -- With the system naming convention, ensure the user-defined types specified for parameters and variables in an SQL routine can be found in the current path.

0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 22779907

You changed the code to use SYSTEM naming, but you didn't change your SQL connection to use it. Your connection seems to be still set to SQL, so it's still looking for a dot separator instead of the slash.
0
 
LVL 5

Author Comment

by:volking
ID: 22780431
hmmmmm ... I'm using iSeries Navigator and (what I "thought") is a standard connection.

I'll go hunt around the iSeries Navigator Connection node and see if I can find a setting for SQL naming or SYSTEM naming.
0
 
LVL 5

Author Comment

by:volking
ID: 22780609
Well .... I can't find anything in iSeries Navigator to change the way my connection is handled.

So I'm at a deadend.
0
 
LVL 5

Accepted Solution

by:
volking earned 0 total points
ID: 22781005
Holy Smokes! It was hidden ... but I think I found it!
But it's not simple ...

When I use iSeries Navigator and I right click on an object (like a Proc, Table, UDF, View) and select [GENERATE SQL] a window opens titled [GENERATE SQL] The window has three tabs [OUTPUT] [OPTIONS] [FORMAT]

The [FORMAT] tab contains a [NAMING CONVENTION] drop down with two choices
     [SQL naming convention (*SQL)]
     [System naming convention (*SYS)]

STEP #1 is to select the option [System naming convention (*SYS)]

Then the generated SQL will contain "/" seperators between schema and objects (like MYSCHEMA/MYTABLE) rather than SQL naming (like MYSCHEMA.MYTABLE). But this is only STEP #1 ...

Then, when the generated SQL opens, in a window titled [Run SQL Scripts] ....

STEP #2 on the menu select [Connection] + [JDBC Setup] - Open the tab titled [FORMAT] and set the drop down [NAMING CONVENTION] to [System (*SYS)]

Once both steps are done, when you [RUN] the SQL, the CRTAUT parameter of your schema library will be inherited by the new object.

And this kind of SQL format will be accepted .....
 
CREATE PROCEDURE MYSCHEMA/TEST22 ( )
     DYNAMIC RESULT SETS 1
     LANGUAGE SQL
     SPECIFIC MYSCHEMA/TEST22
     NOT DETERMINISTIC
     MODIFIES SQL DATA
     CALLED ON NULL INPUT
P1 : BEGIN
          DECLARE C1 CURSOR FOR SELECT * FROM MYSCHEMA/MYTABLE ;
          OPEN C1 ;
END P1;

---------------------------> Enjoy!
0
 
LVL 5

Author Comment

by:volking
ID: 22786734
BE WARNED ... Once changed, it PERSISTS until you change it back.

Apparently changing [Connection] + [JDBC Setup] & [FORMAT] + [NAMING CONVENTION] = [System (*SYS)] changes an internal setting which PERSISITS after closing & re-opening iSeries Navigator. So, once changed to use "/" instead of "." you must use that convention everywhere. (unless you change it back)

Volking
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22789255
Minor note... SQL "naming convention" also implies SQL "names".

Normally, we might think SQL "naming convention" as only meaning the use of the .dot [.] separator in qualified names, rather than the slash [/] separator. But a SQL name for an object can be totally different from a system name for the same object. A SQL TABLE might be named OUREMPLOYEEMASTER, and the system name might be PREMPMST, for example.

In addition, the choice between *SQL and *SYS can affect the SQL path, the way authorities work and other details.

Be aware that the choice of naming _should_ be reasonably consistent and even persistent, for individual developers as well as for the entire site.

Switching should be an exception that's done for a conscious purpose.

But the warning that it is persistent by default is indeed important.

Tom
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 22789474

I could be wrong here, but I don't think the naming convention has anything to do with which "name" to use for the table. In fact, I can use the long-name (a.k.a. the "SQL name") in either *SYS or *SQL naming convention.

Simlarly, I can use the short-name (the AS/400 object name) in either *SYS or *SQL naming convention.

... see example below ...

Initially, I was in *SYS naming convention.
 
> create table MySchema/rltn (
    aVarChar20 varchar(20) 
  )
 
> RENAME TABLE MySchema/rltn TO ReallyLongTableName FOR SYSTEM NAME rltn
 
> insert into MySchema/ReallyLongTableName
  values ('some stuff')
 
> select *         
  from   MySchema/rltn
 
AVARCHAR20
some stuff
 
> select *
  from   MySchema/ReallyLongTableName
 
AVARCHAR20
some stuff
 
Then, Change from *SYS naming convention to *SQL
 
> select *         
  from   MySchema.rltn
 
AVARCHAR20
some stuff
 
> select *
  from   MySchema.ReallyLongTableName
 
AVARCHAR20
some stuff

Open in new window

0
 
LVL 27

Expert Comment

by:tliotta
ID: 22790942
daveslash:

You're probably right that it can be interchangeable for most/all names. Hmmm...

Still, I'd be concerned over whether different clients or newer releases of DB2 would automatically set convention based on characteristics of statements.

The FTP server, for example, will set NAMEFMT to 0 or 1 based on the first subcommand that uses either convention. It's not that I don't "trust" IBM. It's more that the enforcement of 'standards' tends to come in phases. What worked yesterday might quit working tomorrow.

Anyway, you're probably correct here. I'd swear I've seen contradictory behavior, but that could also have been an old DB2 bug fixed by PTF and I remember wrong.

In any case, elements such as authorization and others do get affected. When authorities are affected, it should be a consideration for the site overall.

IMO, of course.

Tom
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question