Re: IBM Object naming rules

On 30-Jul-2014 16:06 -0500, Mark Murphy wrote:

CRPence on 06/13/2014 11:01AM wrote:

On 13-Jun-2014 08:29 -0500, Mark Murphy wrote:

There are valid characters, and unfriendly characters. If you
plan to SQL, the period (.) is one of those unfriendly characters
as it interferes with the standard SQL name delimiter (in *SQL
naming). But even if you are using *SYS naming, SQL cannot access
stored procedures or user defined functions in libraries
containing a period (.).

To be sure, the SQL *can access* the objects in libraries where
that library name contains a period.

I haven't tried with a period in the object name itself, but I
suspect that it interferes there as well.

The same issue and resolution exist for any /object name/ as with
the /library object name/; as well the other /names/ such as
columns, routines, constraints, etc. can contain periods and be
referenced.

The SQL provides support for any SQL identifier that includes a
period. A requirement to effect the reference to such an identifier
is that the SQL identifier must be delimited. The delimiter is the
double-quote character. An example referencing some identifiers
with embedded periods; using the new support for SQL naming within
System naming to show both qualifier characters [period and slash],
and a qualified column as well:

SELECT "ORD.LIB"."ORD.UDF"(T1."ORD.FIELD")
FROM "ORD.LIB"/"ORD.TABLE" AS T1

Note: the column name "ORD.FIELD" is a bit different [than the
other object names] in that statement, because the "ORD.FIELD" is
the actual name as stored and visually presented, whereas the other
names as stored and visually presented would generally be without
delimiters.

Sorry for the late reply on this, but I said that SQL could not
access Stored Procedures and User Defined Functions stored in a
library with a (.) in the name. Even if you properly escape
everything in the statement as you describe below. The problem is
that while SQL can indeed find the definition of the stored
procedure in the catalog, if the real qualified name of the object
contains a (.), SQL gets confused because it does not properly escape
the names in the catalog. So SELECT "MY.LIB".MYUDF() from ... reports
"Can't find object LIB.MYUDF~000 in library MY" because the real name
of the object in the catalog is MY.LIB.MYUDF~000. This may only
affect JDBC and ODBC, that is where I was having trouble with it.
Unfortunately I don't have a clean example right now.

The effect seems suspect, and likely IMO, that the same results would be seen when the requests are performed local to the server.

But I do agree that MOST SQL objects can reside in a library with a
(.) in the name without issue. You just have to escape the name.

As I had noted, the DB2 for i "SQL provides support for any SQL identifier that includes a period." So I meant to imply that if the SQL can *not* access the system objects [that the SQL created for the routines] with that naming while properly using the documented delimiter [contrary to my claims that should be possible], then the failure would seem likely be a defect. There is nothing in the documentation suggesting that the SQL would legitimately get /confused/ about the properly qualified and _delimited identifiers_, aside from the warning about effectively the /confusion/ that might arise with /variant characters/ in the identifiers; the period is an invariant character.

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzch2iden.htm>
_Identifiers_
"An identifier is a token used to form a name. An identifier in an SQL statement is an SQL identifier, a system identifier, or a host identifier.

Note: $, @, #, and all other variant characters should not be used in identifiers because the code points used to represent them vary depending on the CCSID of the string in which they are contained. If they are used, unpredictable results may occur. ..."

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzsqlidts.htm>
_SQL identifiers_
"There are two types of SQL identifiers: ordinary identifiers and delimited identifiers.

• An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character. Note that ordinary identifiers are converted to uppercase. An ordinary identifier should not be a reserved word. See Reserved schema names and reserved words for a list of reserved words. If a reserved word is used as an identifier in SQL, it should be specified in uppercase and should be a delimited identifier or specified in a variable.

• A delimited identifier is a sequence of one or more characters enclosed within SQL escape characters. The sequence must consist of one or more characters. Leading blanks in the sequence are significant. Trailing blanks in the sequence are not significant. The length of a delimited identifier does not include the two SQL escape characters. Note that delimited identifiers are not converted to uppercase. The escape character is the quotation mark (") ..."

<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzch2sysident.htm>
_System identifiers_
"A system identifier is used to form the name of system objects in the IBM® i operating system. There are two types of system identifiers: ordinary identifiers and delimited identifiers.

• The rules for forming a system ordinary identifier are identical to the rules for forming an SQL ordinary identifier.

• The rules for forming a system delimited identifier are identical to those for forming SQL delimited identifiers, except:

• The following special characters are not allowed in a delimited system identifier:

• A blank (X'40')
• An asterisk (X'5C')
• An apostrophe (X'7D')
• A question mark (X'6F')
• A quotation mark (X'7F')

..."

Notably missing from the above list of disallowed characters, is the period (x'4B').