2.4 Installing and Configuring Database Objects

Install and configure database objects (for example, tables, triggers, and indexes) for synchronization with the sample driver configuration. If you don’t configure database objects, the sample configuration file won’t work.

2.4.1 SQL Script Conventions

The following table lists default locations for SQL scripts:

Table 2-4 Default Locations for SQL Scripts

Platform

Default Location

Windows

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

UNIX or Linux

/usr/lib/dirxml/rules/jdbc/database-abbreviation

For example, when installed on a SuSE Linux Enterprise Server with eDirectory, the DB2 scripts are found in /usr/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 meaning:

Table 2-5 Identifier Abbreviations and Meanings

Abbreviation

Interpretation

proc_1

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)

1 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 2-6 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:

  • All database identifiers are lowercase.

    This is the most commonly used case convention between databases.

  • String field lengths are 64 characters.

    Fields of this length can hold most eDirectory™ attribute values. You might want to refine field lengths to enhance storage efficiency.

  • For performance reasons, primary key columns use native, scalar numeric types whenever possible (such as BIGINT as opposed to NUMERIC).

  • The record_id column in event log tables has the maximum numeric precision permitted by each database to avoid overflow.

  • Identity columns and sequence objects do not cache values. Some databases throw away cached values when a rollback occurs. This action can cause large gaps in identity column or sequence values.

2.4.2 Installing IBM DB2 Universal Database (UDB)

IMPORTANT:For IBM* DB2, you must manually create operating system user accounts before running the provided SQL scripts.

Because the process to create user accounts differs between operating systems, Step 1 below is OS-specific. These instructions are for a Windows NT operating environment. If you rerun the SQL scripts, repeat only Steps 2 through 5.

The directory context for DB2 is install-dir\jdbc\sql\db2_udb\install

  1. Create user accounts for users idm, indirect and direct.

    Use novell as the password in User Manager for Domains.

    Remember to deselect User Must Change Password at Next Login for this account.

    You might want to also select Password Never Expires.

    NOTE:The remaining instructions are OS-independent.

  2. Adjust the file path to idm_db2.jar in the 1_install.sql installation script. The file path to idm_db2.jar should reflect the location of this file on your client machine.

  3. Execute the 1_install.sql script from the Command Line Processor (CLP.)

    For example: db2 -f 1_install.sql

    IMPORTANT:The scripts won’t execute in the Command Center interface beyond version 7. The scripts use the ‘\' line continuation character. Later versions of the Command Center don’t recognize this character.

  4. For versions 8 or later, execute the 2_install_8.sql script.

    For example: db2 -f 2_install_8.sql

2.4.3 Installing Informix Dynamic Server (IDS)

IMPORTANT:For Informix* Dynamic Server, you must manually create an operating system user account before running the provided SQL scripts.

Because the process of creating user accounts differs between operating systems, Step 1 below is OS-specific. These instructions are for a Windows NT operating environment. If you rerun the SQL scripts, you should repeat only Steps 2 through 4.

The directory context for Informix SQL scripts is install-dir\jdbc\sql\informix_ids\install.

  1. In Windows NT, create a user account for user idm.

    Use novell as the password in User Manager for Domains.

    Remember to deselect User Must Change Password at Next Login for this account.

    You might want to also select Password Never Expires.

    NOTE:The remaining instructions are OS-independent.

  2. Start a client such as SQL Editor or DBAccess.

  3. Log in to your server as the informix user or another user with DBA (database administrator) privileges.

    By default, the password for the informix user is informix.

    NOTE:If you execute scripts as a user other than informix, change all references to informix in the scripts prior to execution.

  4. Open and execute 1_install_9.sql from either the ansi (transactional, ANSI-compliant), log (transactional, non-ANSI-compliant), or no_log (non-transactional, non-ANSI-compliant) subdirectory, depending upon which type of database you want to create.

  5. For version 10 or later, open and execute 2_install_10.sql from either the ansi (transactional, ANSI-compliant), log (transactional, non-ANSI-compliant), or no_log (non-transactional, non-ANSI-compliant) subdirectory, depending upon which type of database you want to create.

2.4.4 Installing Microsoft SQL Server

The directory context for Microsoft SQL Server scripts is install-dir\jdbc\sql\mssql\install.

  1. Start a client such as Query Analyzer (7, 2000) or Microsoft SQL Server Management Studio (2005).

  2. Log in to your database server as the sa user.

    By default, the sa user has no password.

  3. Execute the installation script.

    For version 7, execute 1_install_7.sql.

    For version 2000 (8), execute 1_install_2k.sql.

    For version 2005 (9), execute 1_install_2005.sql.

    NOTE:The execute hotkey in Query Analyzer is F5.

2.4.5 Installing MySQL

The directory context for MySQL* SQL scripts is install-dir\jdbc\sql\mysql\install.

  1. From a MySQL client, such as mysql, log in as root user or another user with administrative privileges.

    For example, from the command line, execute

    mysql -u root -p

    By default, the root user has no password.

  2. Execute the installation script 1_install_innodb.sql or 1_install_myisam.sql, depending upon which table type you wish to use. For MySQL 3 or 4, use the scripts in subdirectory 3or4. For version 5 or later, use the scripts in subdirectory 5.

    For example: mysql> \. c:\1_install_innodb.sql

    HINT:Don’t use a semicolon to terminate this statement.

2.4.6 Installing Oracle

The directory context for Oracle SQL scripts is install-dir\jdbc\sql\oracle\install.

  1. From an Oracle client, such as SQL Plus, log in as the SYSTEM user.

    By default, the password for SYSTEM is MANAGER.

    NOTE:If you execute scripts as a user other than SYSTEM with password MANAGER, change all references to SYSTEM in the scripts prior to execution.

  2. Execute the installation script 1_install.sql.

    For example: SQL> @c:\1_install.sql

2.4.7 Installing PostgreSQL 7

The directory context for PostgreSQL scripts is install-dir\jdbc\sql\postgres\install. The directory context for executing Postgres commands is postgres-install-dir/pgsql/bin.

  1. Create the database idm.

    For example, from the UNIX* command line, execute the command createdb: ./createdb idm

  2. Install the plpgsql procedural language to database idm.

    For example, from the UNIX command line, execute the command createlang: ./createlang plpgsql idm

  3. From a Postgres client such as psql, log on as user postgres to the idm database.

    For example, from the UNIX command line, execute the command psql: ./psql -d idm postgres

    By default, the Postgres user has no password.

  4. From inside psql, execute the script 1_install_7.sql.

    For example: idm=# \i 1_install_7.sql

  5. Update the pg_hba.conf file.

    For example, add entries for the idm database user. Adjust the IP-ADDRESS and IP-MASK as necessary:

    # TYPE  DATABASE    USER   IP-ADDRESS        IP-MASK           METHOD# allow driver user idm to connect to database idm
host    idm         idm    255.255.255.255   255.255.255.0     password

  6. Restart the Postgres server to effect changes made to the pg_hba.conf file.

2.4.8 Installing PostgreSQL 8

The directory context for PostgreSQL scripts is install-dir\jdbc\sql\postgres\install. The directory context for executing Postgres commands is postgres-install-dir/pgsql/bin.

  1. From a Postgres client such as psql, log in as user postgres to the idm database.

    For example, from the UNIX command line, execute the command psql: ./psql -d idm postgres

    By default, the Postgres user has no password.

  2. From inside psql, execute the script 1_install_8.sql.

    For example: idm=# \i 1_install_8.sql

  3. Update the pg_hba.conf file.

    As of version 8, this can be done through pgAdminIII. After you start, go to Tools > Server > Configuration > pg_hba.conf. In the pgAdminIII pg_hba.conf editor, the IP-ADDRESS and IP-MASK columns in the file are combined into a single field: IP-Address. Place both the IP-ADDRESS and IP-MASK values in that field, separated by a single whitespace character.

    For example, add entries for the idm database user. Adjust the IP-ADDRESS and IP-MASK as necessary:

    # TYPE  DATABASE    USER   IP-ADDRESS        IP-MASK           METHOD# allow driver user idm to connect to database idm
host    idm         idm    255.255.255.255   255.255.255.0     password

  4. Restart the Postgres server to effect changes made to the pg_hba.conf file.

    If you are using pgAdminIII, from the pg_hba.conf editor press the disk icon (save file) in the toolbar. When propted, press Yes.

2.4.9 Installing Sybase Adaptive Server Enterprise (ASE)

IMPORTANT:Ensure that you have JDBC metadata support installed on the database server. This is usually an issue for versions earlier than 12.5 only.

The directory context for Sybase SQL scripts is install-dir\jdbc\sql\sybase_ase\install.

  1. From a Sybase client, such as isql, log in as the sa user and execute the 1_install.sql installation script.

    For example, from the command line, execute: isql -U sa -P -i 1_install.sql

    By default, the sa account has no password.