3.1 SQL Script Conventions

The following table lists default locations for SQL scripts:

Table 3-1 Default Locations for SQL Scripts

Platform

Default Location

Windows*

c:\novell\NDS\DirXMLUtilities\jdbc\sql\database-abbreviation

UNIX* or Linux*

/opt/novell/eDirectory/lib/dirxml/rules/jdbc/sql/database-abbreviation

For example, when the scripts are installed on a SUSE Linux Enterprise Server with eDirectory, the DB2 scripts are found in opt/novell/eDirectory/lib/dirxml/rules/jdbc/db2/*.

All SQL scripts use the same conventions, regardless of the database.

The maximum size of a DB2 identifier is 18 characters. This least common denominator length defines the upper bound of database identifier length across all SQL scripts. Because of this restricted length, abbreviations are used. The following table summarizes identifier abbreviations and their meanings:

Table 3-2 Identifier Abbreviations and Meanings

Abbreviation

Interpretation

proc_

stored procedure/function

idx_

index

trg_

trigger

_i

on insert trigger

_u

on update trigger

_d

on delete trigger

chk_

check constraint

pk_

view primary key constraint

fk_

view foreign key constraint

mv_

view multi-valued column

sv_

view single-valued column (implicit default)

Instead of proc_, the more common abbreviation is sp_. This prefix is reserved for system-stored procedures on Microsoft* SQL Server. Also, this prefix forces lookup of a procedure first in the master database before evaluating any qualifiers (for example, database or owner). To maximize procedure lookup efficiency, this prefix has been deliberately avoided.

The following table indicates identifier naming conventions for indexes, triggers, stored procedures, functions, and constraints:

Table 3-3 Identifier Naming Conventions

Database Object

Naming Convention

Examples

stored procedure/function

proc_procedure-or-function-name

proc_idu

index

idx_unqualified-table-name_sequence-number

idx_indirectlog_1

trigger

tgr_unqualified-table-name_triggering-statement-type_sequence-number

tgr_usr_i_1

primary key constraint

pk_unqualified-table-name_column-name

pk_usr_idu

foreign key constraint

fk_unqualified-table-name_column-name

fk_usr_idu

check constraint

chk_unqualified-table-name_column-name

chk_usr_idu

Other conventions: