Naming Conventions

 <  Day Day Up  >  

Before issuing any DDL, standard names must be identified for all objects that will be created. There are rules that DB2 will enforce for database object naming. For example:

  • Object names must start with an alphabetic character or one of the national characters ( @ , # , $ ).

  • Numeric characters are permissible in the object name, just not in the initial character of the name .

  • The underscore (_) is used as a separator character.

  • Special characters and blanks can be used in a DB2 object name, but the name must then be enclosed in double quotes ( " " ).

  • STOGROUP , DATABASE , and TABLESPACE names must not contain any special characters.

Qualification of object names is required whenever two objects share the same name. When coding DDL, a fully qualified table space name is required for the IN database-name.tablespace-name clause when creating a table. Otherwise, the default database ( DSNDB04 ) is used if a simple table space name is coded.

The next section offers DB2 naming conventions that conform to these rules.

Develop and Enforce DB2 Naming Conventions

The first step in creating an optimal DB2 environment is the development of rigorous naming standards for all DB2 objects. This standard should be used with all other IT naming standards in your shop. Where possible, the DB2 naming conventions should be developed to peacefully coexist with your other standards, but not at the expense of impairing the DB2 environment. In all cases, naming standards should be approved by the corporate data administration (DA) department, or the corporate database administration department if a DA group does not exist.

Do not impose unnecessary restrictions on the names of objects accessed by end users. DB2 is supposed to be a user -friendly database management system. Strict, limiting naming conventions, if not developed logically, can be the antithesis of what you are striving to achieve with DB2.

For example, many shops impose an eight-character encoded table-naming convention on their environment. DB2 provides for 18-character table names, and there is no reason to restrict your table names to eight characters. There is even less reason for these names to be encoded. A reasonable table-naming convention is a two- or three-character application identifier prefix, followed by an underscore, and then a clear, user-friendly name.

For example, consider the customer name and address table in a customer maintenance system. The name of this table could be

 

 CMS_CUST_NAME_ADDR 

The application identifier is CMS (for Customer Maintenance System), followed by an underscore and a clear table name, CUST_NAME_ADDR . If this table were named following an eight-character encoded name convention, it might appear as TCMSNMAD . This clearly is not a user-friendly name, and should be avoided.

In general, a standard naming convention should allow the use of all characters provided by DB2. (See Appendix D, "DB2 Limits," for a listing of DB2 size limitations for each type of object.) By using all available characters, the DB2 environment is easier to use and understand. All information pertaining to which indexes are defined for which tables, which tables are in which table spaces, which table spaces are in which databases, and so on can be found by querying the DB2 Catalog.

The only valid exception to using all available characters is when naming indexes. An index name can be 18 characters, but there are advantages to limiting it to eight characters. Indexes are unknown to most end users, so a limiting index name is not as great a blow to user friendliness as a limiting table name.

The problem with 18-character index names is the result of the strict data set naming convention required by DB2. This convention is

 

  vcat  .DSNDB  x.dddddddd.ssssssss.t  0001.A  nnn  

where:

vcat

High-level qualifier, indicating an ICF catalog

x

C if VSAM cluster component

D if VSAM data component

dddddddd

Database name

ssssssss

Table space name or index name

t

I or J, depending on last fast switch

nnn

Partition number or the data set number


NOTE

A non-partitioned index can cover 32 2GB data sets. The first data set ends with 001 , the second data set ends with 002 , and so on.


If you use more than eight characters to name an index defined using a STOGROUP , or storage group, DB2 creates a unique, eight-character string to be used when defining the underlying data set for the index. If the index is created using native VSAM, the first eight characters of the name must be unique and must be used when defining the underlying VSAM data set. These two constraints can make the task of correlating indexes to data set names an administrative nightmare when indexes have names greater than 8 bytes.

Be sure to create and publish naming standards for all DB2 objects. A comprehensive list of objects follows :

STOGROUP

PLAN and PACKAGE

DATABASE

STORED PROCEDURE

TABLESPACE

PROGRAM

LOB TABLESPACE

TRIGGER

STORED PROCEDURE

USER-DEFINED FUNCTION

TABLE

DBRM

AUXILIARY TABLE

GLOBAL TEMPORARY TABLE

REFERENTIAL CONSTRAINT

CHECK CONSTRAINT

VIEW

UTILITY ID

ALIAS

INDEX

SYNONYM

COLUMN

COLLECTION

VERSION

USER-DEFINED DISTINCT TYPE

 

You might also consider creating naming standards for other related objects such as FIELDPROC s, EDITPROC s, image copy data set names, PDS library names, and so on. Creating a naming standard for cursors inside of DB2 programs is also recommended.

Sample DB2 Naming Conventions

Sample DB2 naming standards follow. These standards are only suggestions. Your shop standards are likely to vary from these standards. However, if you have yet to adopt a naming standard at your shop, consider using these. Valid characters are all alphabetic characters, the underscore, and numbers .

DB2 Database Names

Format:

Daaadddd

aaa

Application identifier

dddd

Unique description


DB2 Table Space Names

Format:

Saaadddd

aaa

Application identifier

dddd

Unique description


DB2 LOB Table Space Names

Format:

Laaadddd

aaa

Application identifier

dddd

Unique description


Table, View, and Alias Names

Format:

aaa_d...d

Limit:

128 total bytes (18 for V7 and previous)

aaa

Application identifier

d...d

Unique description up to 124 characters long


Synonym Names

Format:

aaa_dddddddddddddd

Limit:

18 total bytes

aaa

Application identifier

dddddddddddddd

Unique description up to 14 characters long


Auxiliary Table Names

Format:

Xaaa_d...d

graphics/v8_icon.gif Limit:

128 total bytes (18 for V7 and previous)

aaa

Application identifier

d...d

Unique description up to 123 characters long


Temporary Table Names

Format:

TMP_d...d

Limit:

128 total bytes (18 for V7 and previous)

TMP

Constant temporary indicator (consider an alternate shop standard if you already use TMP as an application identifier)

d...d

Unique description up to 124 characters long


DB2 Index Names

Format:

Xaaadddd

aaa

Application identifier

dddd

Unique description


Index names should be limited to 8 characters even though DB2 allows up to 18 character index names. This is important because you can explicitly name DB2 indexes, but you cannot explicitly name DB2 index spaces. Yet, every DB2 index requires an index space name. The index space name is implicitly generated by DB2 from the index name. If the index name is 8 characters or less in length, then the "index space" name will be named the same as the index itself. If the index name is greater than 8 characters long, DB2 will use an internal, proprietary algorithm to generate a unique, 8-byte index space name. It is difficult to match indexes to index spaces when the names do not match, so it is wise to limit the length of index names to 8 characters.

STOGROUP Names

Format:

Gaaadddd

aaa

Application identifier

dddd

Unique description


Referential Constraint Names (Foreign Keys)

Format:

Raaadddd

aaa

Application identifier

dddd

Unique description


Check Constraint Names

Format:

Caaadddd

aaa

Application identifier

dddd

Unique description (for example, first four characters of column name)


DB2 Trigger Names

Format:

G aaadddd

aaa

Application identifier

dddd

Unique description (for example, characters that tie back to the table on which the triggers are defined, if possible)


DB2 Stored Procedure Names

Format:

Up to 18 characters


DB2 stored procedure names should be as descriptive as possible to define the purpose of the stored procedure. Use as many of the 18 characters as needed to help identify the functionality of the stored procedure. For example, RETURN_ALL CUSTS is a better name for a stored procedure than is RTALLCST .

DB2 User-Defined Function Names

Format:

Up to 8 characters; should be as descriptive as possible to define the purpose of the function


DB2 Column Names

graphics/v8_icon.gif Format:

Up to 128 characters


DB2 column names should be as descriptive as possible to provide documentation. Version 8 changes the limit for column size from 18 bytes to 128. In general, use as many characters as necessary to make the column name understandable. When you use abbreviations to limit the size of a column, use the standard Data Management abbreviations. This ensures a consistent and effective database environment.

Even though DB2 supports column names of up to 128 bytes, use caution before taking advantage of all available characters. Be sure to consider issues such as storage space and interface display limits used by your applications before naming columns .

Columns that define the same attribute should be named the same. Additionally, the same name should never be used for different attributes. In other words, a column used as a primary key in one table should be named identically when used as a foreign key in other tables. The only valid exception is when the same attribute exists in one table multiple times. In this case, specify a substitute column name; you usually can use the attribute name with a descriptive suffix or prefix. For code supplied by vendors , you might have to make exceptions to this guideline of singular column names per attribute.

DB2 Distinct Type Names

Format:

Up to 18 characters


DB2 distinct types should be defined with a similar mindset as defining DB2 columns. The distinct type name should be as descriptive as possible within the 18-character limitation.

DB2 Plan Names

Format:

Up to eight characters


The convention is that the name of the plan should be the same as the name of the application program to which it applies. If multiple program DBRMs (Data Base Request Modules) are bound to a single large plan, or if one plan is composed of many packages, the name should be assigned by the database administration department such that the name successfully identifies the application, is not an actual program name, and is unique in the DB2 subsystem.

DB2 Package Names

Format:

Up to eight characters

Packages are named the same as the DBRM.


DBRM Names

Format:

Up to eight characters


DBRMs generally are named the same as the program. If a single program is used to create multiple DBRMs, consult with the database administration department for an acceptable name.

Collection Names

Format:

aaa_dddddddd_eeeee

aaa

Application identifier

dddddddd

Unique description

eeeee

Environment ( BATCH , CAF , CICS , DLI , IMSDC , BMP , TSO , and so on)


Explicit Version Names

Format:

uuuuuuuu_date_tttt_s

uuuuuuuu

authid (of person performing precompile)

date

Date of precompile (ISO format)

tttt

Type of program ( TEST , TEMP , PROD , QUAL , and so on)

s

Sequence number (if required)


The explicit version name should be used when the programmer is to specify the version instead of having DB2 supply the version automatically at precompile time. An example of an explicit version name would be DBAPCSM_2004-01-01_TEMP_3 , indicating that on New Year's Day user DBAPCSM precompiled this version as a temporary fix (at least) three times.

Automatic Version Names

The automatic version name must be permitted when DB2 is to assign the version name automatically at precompile time. In this case, the version name is a 26-byte ISO timestamp. For example, 2004-07-21-15.04.26.546405 .

Utility ID

DB2 utility IDs should be unique for each utility to be executed. No two utilities can be run concurrently with the same ID.

The utility ID for all regularly scheduled DB2 utilities should be allowed to default to the name of the job. Because MVS does not permit two identically named jobs to execute at the same time, DB2 utility IDs will be forced to be unique.

DCLGEN Declare Members

Format:

oaaadddd

o

Object identifier:

 

T

table

 

V

view

 

A

alias

 

S

synonym

aaa

Application identifier

dddd

Unique description


The unique description, dddd , should be the same as the table space to which the table has been defined. If more than one of any object type exists per table space, the database administration department should assign a unique name and provide that name to the appropriate application development staff.

Compliance

The database administration department should assign all DB2 object names. It is also the database administration department's responsibility to enforce DB2 naming conventions.

Database administration should work in conjunction with the corporate data administration group to ensure that naming conventions are consistent throughout the organization.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net