Solved

SQL names :vs: SYSTEM names

Posted on 2008-10-22
15
1,256 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
  • 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:daveslash
daveslash earned 500 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
 
LVL 18

Expert Comment

by:daveslash
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:daveslash
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Expert Comment

by:daveslash
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:daveslash
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now