Naming Conventions for DB2 under z/OS

Overview

For general information, see SAS Names and Support for DBMS Names.

DB2 objects include tables, views, columns, and indexes. They follow these naming conventions.

• These objects must have names of the following length in characters: column (1–30), index (1–128), table (1–128), view (1–128), alias (1–128), synonym (1–128), correlation (1–128). However, SAS limits table names to 32 bytes. This limitation prevents database table objects that are specified through a DATA step—for example, to have names that are longer than 32.

  These objects must have names from 1–8 characters long: authorization ID, referential constraint, database, tablespace, storage group, package, or plan.

  A location name can be 1–16 characters long.

• A name must begin with a letter. If the name is in quotation marks, it can start with and contain any character. Depending on how your string delimiter is specified, quoted strings can contain quotation marks (for example, “O'Malley”).
• A name can contain the letters A–Z, numbers from 0–9, number or pound sign (#), dollar sign ($), or at symbol (@).
• Names are not case sensitive. For example, CUSTOMER and Customer are the same. However, if the name of the object is in double quotation marks, it is case sensitive.
• A name cannot be a reserved word in DB2.
• A name cannot be the same as another DB2 object. For example, each column name within the same table must be unique.

Case Sensitivity and Special Characters in Object Names

The PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options determine how this interface handles case sensitivity, spaces, and special characters. (For information about these options, see LIBNAME Statement for Relational Databases.) Although DB2 is case sensitive, it converts table and column names to uppercase by default. To preserve the case of the table and column names that you send to DB2, enclose them in double quotation marks.

Using Table Aliases or Synonyms

DB2 under z/OS supports the use of table aliases or table synonyms. If you use aliases or synonyms in your database, then you must specify the qualifying value to use when accessing the alias or synonym name, even if the qualifying value is NULL. The AUTHID= and SCHEMA= LIBNAME options specify qualifying values for tables.

To specify a NULL value for the AUTHID= LIBNAME option, submit this code:

libname mylib2 db2 authid=''; /* no space between quotation marks */

To specify a NULL value for the SCHEMA= LIBNAME option, submit this code:

libname mylib db2 schema=''; /* no space between quotation marks */

You can also specify NULL values for the AUTHID= and SCHEMA= data set options.