Create DB Aliases

The next step in the process involves creating a DB alias for each database, and packages, plans, or procedures to access database tables. Your Optim solution can access several databases during processing; however, each database must have a unique alias that is stored in the current Optim directory. A DB alias is used as a high-level qualifier for a table name. The qualifier provides a single-name association for parameters that are required to connect to the database.

The configuration program prompts you to

Create an alias for the database that hosts the Optim directory.
Create Optim primary keys for tables that have unique indexes, if no DBMS primary keys exist.
Create and load the sample tables, if wanted.
Create and load the data privacy data tables, if you are licensed for them.

The configuration program then provides an option to create aliases for other databases that are processed by your Optim solution and repeats the process.

If you have several SQL Server, Sybase ASE, or Informix® instances on one server, you can use the Create Multiple option. That option creates an alias for every database instance at one time.

Create DB Alias?

After you create the Optim directory, you are prompted to create an alias for the database that hosts it. After you create that alias, you are prompted to create aliases for any additional databases that you will process.

Example of the Create/Update DB Alias for this Database dialog.

If you plan to access tables in a database in addition to directory tables, select the Create/Select DB Alias for this Database check box. (After you create the alias, you are prompted to share connection information for the directory and the other tables.) If you want to create an alias for a database other than that hosting the directory, clear the check box.

Click Proceed to continue.

Create/Select DB Alias

Use the Create/Select DB Alias dialog to

Create and name a single new DB alias.
Create multiple new DB aliases for a single server.
Modify an existing DB alias.

If you are configuring a workstation that is to function as an Optim server (server), you also must provide information for each DB alias. To provide the needed information, use the Connection tab on the Optim Server Settings applet. See Connection tab .

Example of the Create/Select DB Alias dialog, described as follows.

The Create/Select DB Alias dialog includes the following options:

Create New: Select this option to create a DB alias. You must provide a name for the DB alias (1 - 12 characters, no embedded blanks). The Name entry is blank when the dialog opens.
Create/Select Multiple: Select this option to create or select an alias for each of multiple database instances on a Sybase ASE, SQL Server, or Informix database server. For more information, see Create Multiple DB Aliases.
Use Existing: Select this option to use a previously created DB alias. Use this option to modify the name of a stored procedure for an existing DB alias. The Name entry is available if Use Existing is selected. To select from a list, click the down arrow.

Create New or Use Existing

When you select an option to create a new or modify an existing DB alias, you are prompted for the necessary information.

Specify DB Alias DBMS

You can provide a description to distinguish a new DB alias from others. The Configuration program displays the Specify DB Alias DBMS dialog.

If you are creating an additional DB Alias, you must select a DBMS Type and Version. (If the DBMS Type is PostgreSQL or Hive, the only valid entry for Version is Not Applicable.)

Specify Character Set of DB Alias Data

For a DBMS that supports both single-byte and Unicode tables, you must select a data format for the data. The configuration process displays the Specify Character Set of DB Alias Data dialog.

Connect to Database

The Configuration program requires certain information to connect to the database. Use the Connect to Database dialog to provide the connection information that allows the configuration program to connect to the database and configure the directory tables.

The Connect to Database dialog provides the previously entered directory name and prompts for the following database connection parameters:

DB Alias: Name of the alias you are creating.

Database Connection Parameters

User ID

Enter a user ID (up to 30 characters) that the DBMS requires to allow access to the database or database instance.

Password

Enter the password that corresponds to the specified user ID.

Note: Starting with Release 11.7.0.3, support for passwords up to 64 characters has been introduced for DBMS connectivity. (Previously, passwords up to 30 characters were supported.)

Connection String

Enter the name or string that is needed to access the database. The label that is displayed for this item varies based on the DBMS. The syntax is described in the documentation for the DBMS.

The following table shows examples of the type of Connection Strings you can specify for each DBMS. If you can put the Optim Directory in the DBMS, Yes is displayed in the Optim Directory column. If not, No is displayed in that column.

DBMS	Connect String Example	Optim Directory?
DB2®	Database name or alias.	Yes
Oracle	Connection string or host string.	Yes
SQL Server	Data source name (DSN) or server.	Yes
Hive (for Hadoop)	Name of the configured ODBC data source.	No
Informix	Server name.	No
Netezza®	Name of the configured ODBC data source.	No
PostgreSQL	Name of the configured ODBC data source.	No
Sybase ASE	Server name.	No
Teradata	TDP ID for client connection to server system.	No

DB Name

If the DB alias is for a Sybase ASE, SQL Server, or Informix database, enter the name of the database instance that is referenced by the alias.

Account ID

This optional prompt is displayed for a Teradata database only. If appropriate, type the Account ID associated with the specified user ID.

Create/Drop Packages

In most cases, access to database tables requires plans, packages, or procedures, which the configuration process creates automatically:

If you are configuring a DB2 for Linux®, UNIX, and Windows database, the Bind/Drop Plans dialog is displayed, see Create Optim Directory.
If you are using Oracle, the Create/Drop Packages dialog opens. Similarly, the Create/Drop Stored Procedures dialog is shown for Informix, Netezza, SQL Server, and Sybase ASE.

Use the Create/Drop Packages dialog or the Create/Drop Stored Procedures dialog to specify the identifier for new or existing packages (plans) or procedures, when available. You can use common stored procedures for Sybase ASE.

Example of the Create/Drop Packages dialog, described as follows.

The Create/Drop Packages dialog includes the following items:

DB Alias

The previously entered name for the DB alias for which packages, plans, or procedures are being created.

Tables

Type of tables (Data Dictionary, Catalog Tables, or System Tables, depending on the DBMS) for which packages, plans, or procedures are being created.

Stored Procedure Specifications

Create/Refresh

Select this option to create new or refresh existing packages, plans, or procedures. This option is always available when you are creating a DB alias, and it is the default selection when the dialog opens.

Use Existing

Select this option to use existing packages (plans) or procedures. The Configuration program creates a DB alias that references the packages, plans, or procedures, but does not verify that they exist.

Drop

Drop existing packages, plans, or procedures. This option is available only if packages, plans, or procedures exist and can be dropped.

Qualifier/Prefix

When enabled, enter the high-level qualifier for packages, plans, or procedures. The name that is displayed for this prompt varies by database, as follows:

Qualifier/Prefix Name	To Access
Collection Name	DB2 catalog tables
Schema Name	Oracle data dictionary
Owner ID	Informix, Netezza, SQL Server, or Sybase ASE system tables

Note: For Sybase ASE and SQL Server, the sp_ prefix is displayed when stored procedures are shared and Use One Copy for all Databases on this Server is selected.

Grant Auth ID

Enter an identifier for authorized users as a single user ID, a group name, or public.

Use One Copy for all Databases on this Server

This check box is displayed only for Sybase ASE and SQL Server. Select the check box to use common stored procedures for databases on a single server. For Sybase ASE, stored procedures are stored in the special Sybase ASE database sybsysprocs. For SQL Server, stored procedures are stored in the MASTER database.

Note: See Microsoft SQL Server documentation for information about the creation of stored procedures in the MASTER database. Consider the implications of sharing stored procedures for SQL Server before proceeding.

Display SQL

Select this check box to display SQL statements before you create or dropping packages, plans, or procedures.

Always Require a Password

You can choose to require a password on the initial connection to the database for each session.

If you select this option, a user must provide a password at the beginning of each session. After you select this option, you cannot change it. To continue, click Proceed.

Share Connection Information

When the new DB alias represents the database for the directory, you can use one connection to access both the directory and data in the database. For this reason, the Configuration program displays the Share Connection Information for Current Database dialog after it creates the packages, plans, or procedures.

To access the directory and the DB alias with a single connection, select the check box. If you clear the check box, the Connect to Database dialog opens and you can specify a user ID and password for the new DB alias.

Note: Changes to stored procedures for a shared connection (for example, dropped procedures or failure to apply maintenance after installation) might prevent a directory connection.

Define Character Format

You must indicate the character format of the DB alias if the directory is in Unicode format and the DB alias is for a database (except SQL Server). If the DB alias uses a single connection with the directory, the directory and DB alias must use the same character format.

A directory in multibyte format supports multibyte DB aliases only. If the directory is multibyte, the DB alias is set to multibyte format; however, you must respond to the Round Trip Issues with Multi-byte Format for a DB Alias dialog. If the DB alias connects to the directory, the Keep Character Data in Unicode Format and Specify Character Set of DB Alias Data dialogs are displayed. See Share Connection Information.

Keep Character Data in Unicode Format

If your Optim solution supports Unicode on the database for the Optim directory, you must indicate whether the data is kept in Unicode format.

Specify Character Set of DB Alias Data

If the Optim solution supports Unicode for the DBMS, you are prompted to indicate the format in which data is stored. The character sets of the DBMS client and the database server must match your selection.

Information on Unicode Format (UTF-8)

If you specify Unicode on the Specify Character Set of DB Alias Data dialog, the character sets for client and database must be Unicode.

Information on Multi-byte Format (MBCS) for a DB Alias

If you select multibyte format on the Specify Character Set of DB Alias Data dialog:

Both the DBMS client and server must have the same supported multi-byte character set.
A directory in multi-byte format supports multi-byte DB aliases only.

Example of the Information on Multi-byte Format (MBCS) for a DBA dialog.

Round Trip Issues with Multi-byte Format for a DB Alias

If the directory is in multibyte format, you must acknowledge round-trip conversion issues.

Your Optim solution uses the Unicode character set in dialogs and to process data. In some multibyte character sets (such as Oracle JA16SJIS), multiple characters are mapped to one Unicode character, multiple Unicode characters are mapped to one multibyte character, or both mappings occur. When these characters are converted from Unicode to multibyte and back or multibyte to Unicode and back, the original character might not be returned. This two-way conversion is considered a round trip and identifies this situation. To avoid round-trip issues with multibyte data, ensure that your source data does not include multibyte characters that can result in ambiguous conversions.

Personal and product options determine how round-trip conversion issues are handled when processing data in a multibyte database.

Example of the Round Trip Issues with Multi-byte Format dialog.

Register DB Alias

The Configuration program creates a configuration file entry for subsequent access to the database. Unless the connection is shared with the Optim directory, you must provide the information that is needed for this configuration file entry on the Connect to Database dialog.

Connect to Database

When the Connect to Database dialog opens, User ID, Password, and other prompts are populated with any previously entered values.

Database Connection Parameters

User ID: Enter the user ID (up to 30 characters) that the DBMS requires to allow access to the database or database instance. For security and other reasons, a user ID with privileges different from the privileges that are required to configure the server might be desirable.
Password: Enter the password that corresponds to the specified user ID.
Note: Starting with Release 11.7.0.3, support for passwords up to 64 characters has been introduced for DBMS connectivity. (Previously, passwords up to 30 characters were supported.)
Connection String: String or name that allows the workstation to access the Optim directory database. The DBMS uses this connection string to recognize the database. This value is entered earlier in the process and cannot be edited.
DB Name: Name that identifies the Sybase ASE, SQL Server, or Informix instance for the DB alias. This name is assigned when the database is created.
Account ID: For Teradata only, the account ID associated with the specified user ID.
Always Ask for Password: Select this check box to require a password each time you connect to the database. This option is selected and unavailable if you selected the Always Require a Password for this Database option.

After you create the DB aliases, you create Optim Primary Keys for databases you want to use with your Optim solution.

Create Primary Keys

In some cases, primary keys are required to extract and insert data. Certain tables in the database that do not have primary keys, might have unique indexes. You can use the Configuration program to create primary keys for these tables in the Optim directory. These primary keys supplement primary keys that are defined to the database.

Use the Create Primary Keys dialog to confirm the DB alias for the database tables that require primary keys.

Example of the Create Primary Keys dialog.

Select Tables

Use the Select Tables dialog to select tables for primary keys.

The Select Tables dialog includes the following items:

DB Alias

DB alias that is associated with the list of tables.

Selected Tables

List of tables that are selected for creating primary keys. To remove a table from the list, drag the table name to Excluded Tables.

Schema (or other identifier, depending on the DBMS): Identifier for the table.
Table Name: Name of the table.
Index Name: Name of the unique index.

Excluded Tables

List of excluded tables available for creating primary keys. To select an excluded table, drag the table name to Selected Tables.

Schema (or other identifier, depending on the DBMS): Identifier for the table.
Table Name: Name of the table.
Index Name: Name of the unique index.

When the process completes, you are prompted to load the sample database tables.

Load Sample Tables

This software is distributed with sample data tables. You can use these tables for training and to experiment with sample data before you apply the software to your own database tables. Generally, the sample data is loaded when you configure the first workstation, but you also can load or refresh that data by selecting Load/Drop Sample Data from the Tasks menu.

Load/Drop Sample Tables

If available, use the Load/Drop Sample Tables dialog to provide the identifier and table space for the sample tables before they are loaded.

The Load/Drop Sample Tables dialog includes the following items:

DB Alias

DB alias for sample tables. If you do not want to load sample tables for this DB alias, click Skip.

Load/Refresh Sample Tables

Select this option to load or refresh sample tables. This option is available and selected when the dialog opens.

Drop Sample Tables

Select this option to drop previously loaded sample tables. This option is unavailable when not applicable, such as when you are initially loading those tables.

Sample Table Specifications

Creator ID (or other identifier, depending on the DBMS): An identifier for the sample tables.
Tablespace: Select a table space to store the tables. To select from a list, click the down arrow. Table space is not available if you select Drop Sample Tables, and it is not used for some databases.

Display SQL

Select this check box to display SQL statements before you load or drop the tables.

Note: If you are loading or refreshing the sample tables, click Proceed to open the Drop Tables dialog.

Drop Tables

Use the Drop Tables dialog to review a list of the sample tables that are to be dropped in the selected DB Alias.

Use the Drop Tables dialog to ensure that the names of the sample tables do not conflict with your other table names. If there are conflicts, click Cancel; otherwise, click OK.

During the process that drops sample tables and loads or refreshes sample tables, the Configuration program displays the Sample Tables Insert Request Progress dialog.

Example of the Sample Tables Insert Request Progress dialog.

Load Data Privacy Data Tables

Tables of data masking data are available with some Optim solutions. The tables support masking of sensitive data, such as employee names, customer names, social security numbers, credit card numbers, and email addresses. Generally, these tables are loaded when you configure the first workstation, but you also can load or refresh them by selecting Load/Drop Data Privacy Data from the Tasks menu.

Load/Drop Data Privacy Tables

If available, use the Load/Drop Data Privacy Tables dialog to provide the identifier and table space for the tables before they are loaded.

The Load/Drop Data Privacy Tables dialog includes the following items:

DB Alias

DB alias for the database in which to load the tables. If you do not want to load data privacy tables for this database, click Skip.

Load/Refresh DP Tables

Select this option to load or refresh data privacy tables. This option is available and selected when the dialog opens.

Drop DP Tables

Select this option to drop previously loaded data privacy tables. This option is unavailable when not applicable, such as when you are initially loading those tables.

Table Specifications

Tables Type

Select privacy tables, as follows:

Single-byte English (AU, UK, US)
Multibyte Latin-1 (AU, CA, DE, ES, FR, IT, UK, US)
Multibyte Japanese (JP)

Creator ID (or other identifier, depending upon the DBMS)

Type an identifier for the tables.

Tablespace

Select a table space to store the tables. To select from a list, click the down arrow. Table space is not available if you select Drop DP Tables, and it is not used for some databases.

Display SQL

Select this check box to display SQL statements before you create or drop the tables. If you are loading or refreshing the tables, click Proceed to open the Drop Tables dialog.

Drop Tables

Use the Drop Tables dialog to review the list of tables that are to be dropped.

Review the table names to ensure that they do not conflict with your other table names. If there are conflicts, click Cancel and specify a different schema name for the tables; otherwise, click OK.

During the process that drops data privacy tables and loads or refreshes tables, the Configuration program displays the privacy Tables Insert Request Progress dialog.

Example of the privacy Tables Insert Request Progress dialog.

Create/Update Another DB Alias

The steps for creating a DB alias are complete. To use your Optim solution with additional databases, you must create corresponding DB aliases. After you load the sample tables and the data privacy tables, the Configuration program prompts you to create another DB alias. Your positive response opens the Create/Select DB Alias dialog to repeat the configuration process for another database.

If the DB alias you created is for a DB2 for z/OS database, the Create Copies of DB2 MVS Relationships dialog is displayed. Otherwise, the prompt to create another DB alias is displayed. Use that dialog to copy the DB2 relationships into the Optim directory to reduce the run time when you are accessing DB2 tables. See Create Copies of DB2 z/OS Relationships. After that task is completed, the Configuration program will prompt you to create another DB alias.

After all DB aliases are created, you can configure Personal and Product Options. See Configure Options.

Create Multiple DB Aliases

If you are using SQL Server, Sybase ASE, or Informix, you might have several database instances on one server. You can create multiple DB aliases, one for each database, in a single operation.

Example of the Create/Select DB Alias dialog.

You can select the Create New option and follow the steps for each database. Alternatively, you can use the Create/Select Multiple option set specifications for all databases at the same time.

On the Create/Select DB Alias dialog, select the Create/Select Multiple option and click Proceed.

The Configuration program opens the Specify DB Alias DBMS dialog.

Select the DBMS type and version and click Proceed to display the Connect to Database dialog.

Example of the Connect to Database dialog.

Specify a user ID and Password with authority to connect to the master database on the server. Next, click proceed to open the Create Multiple DB Aliases dialog. For SQL Server, the user ID must have database owner (dbo) privileges to create or select multiple DB aliases.

For Sybase ASE and SQL Server, you are prompted to share a single copy of the stored procedures.

Example of the Store Procedures in master? dialog.

If you elect to share stored procedures, you are prompted to convert existing DB aliases to use the shared procedures.

Example of the Convert Existing DB Aliases? dialog.

Click proceed to open the Create Multiple DB Aliases dialog and enter the database information.

Example of the Create Multiple DB Aliases Databases tab, described as follows.

The Create Multiple DB Aliases dialog includes the following tabs. The default values apply to all database instances, unless otherwise specified on the Databases tab.

Databases: A list of all database instances that are hosted on the server. Enter explicit information for each instance for which you want to create a DB alias.
Logon Defaults: Enter the default user ID and password that is required to create or refresh stored procedures for each DB alias. In some cases, this logon might have greater privileges than the Saved Logon Defaults.
Saved Logon Defaults: Enter the user ID and password that is required to access the database. This information is saved to the configuration file for the workstation that is being configured.
Stored Procedure Defaults: Enter the default Procedure Qualifier and Grant Authorization ID required to create or refresh stored procedures.

Databases

Use the Databases tab on the Create Multiple DB Aliases dialog to provide explicit information for each DB alias.

Sel

Select the check box to create a DB alias for the database. If you do not want to create a DB alias, clear the check box. This grid column is locked in position, so you can scroll to the left or right and still see the selections for the databases. If a DB alias for a database exists, the grid row is protected and shaded.

Database Name

The name that is assigned to the database when it was created.

DB Alias Name

The identifier that allows the software to access the database. This name also serves as the high‑level qualifier for database table names. This entry is required, and is populated with the database name in uppercase, by default.

DB Alias Description

Optional text that describes or explains the purpose of the DB alias.

Procedure Action

Options to create or refresh procedures or use existing procedures. To select an option, click the grid cell and click the down arrow. When you are creating procedures to be used for access to all Sybase ASE or SQL Server instances on the server, this grid cell is protected. The first selected entry is displayed as Create/Refresh; other selected entries to use the shared stored procedures display Use Existing.

Procedure Qualifier

The high-level qualifier for stored procedures. If blank, the entry on the Stored Procedure Defaults tab is used. For Sybase ASE databases that share stored procedures, the entry sp_ is displayed and cannot be edited.

Grant Auth ID

An identifier for users that are authorized to maintain stored procedures. Specify a user ID, group name, or public. If blank, the entry on the Stored Procedure Defaults tab is used.

Logon User ID

The user ID (up to 30 characters) required to create or refresh stored procedures. If blank, the entry on the Logon Defaults tab is used.

Logon Password

The password required to create or refresh stored procedures. If blank, the entry on the Logon Defaults tab is used.

Note: Starting with Release 11.7.0.3, support for passwords up to 64 characters has been introduced for DBMS connectivity. (Previously, passwords up to 30 characters were supported.)

Saved User ID

The user ID (up to 30 characters) required to log on when you are using the DB alias. This identifier is saved to the configuration file of the workstation. If blank, the entry on the Saved Logon Defaults tab is used.

Saved Password

The password required to log on when you are using the DB alias. If blank, the entry on the Saved Logon Defaults tab is used.

Note: Starting with Release 11.7.0.3, support for passwords up to 64 characters has been introduced for DBMS connectivity. (Previously, passwords up to 30 characters were supported.)

Note: You can change the saved user ID and saved password when you configure options and when you set Personal Options.

Always Ask for Password

Select this check box to require a password each time you connect to the database. If you clear this check box, you do not need to supply a password on future attempts to connect to the database.

Create Primary Keys

Select this check box to create primary keys, as needed, for the database.

To create primary keys from within the Configuration program, select Create Primary Keys from the Tasks menu on the main window.

To create primary keys from within the Optim solution, select New from the File menu and select Primary Keys from the Definitions submenu on the main window.

Display SQL

Select this check box to display SQL statements before you create or drop stored procedures.

Logon Defaults

Use the Logon Defaults tab to provide the default user ID and password that is required to create or refresh stored procedures.

Use the entries on the Logon Defaults tab to connect to the database while you are configuring a workstation. You must enter the password a second time for verification. The default logon information applies to all DB aliases unless you use the Databases tab to provide explicit logon information.

Saved Logon Defaults

Use the Saved Logon Defaults tab to provide the user ID and password that is needed to access the database by using the DB alias.

Use the entries on the Saved Logon Defaults tab to save configuration file entries to access the databases. You must enter your password a second time for verification. The saved default logon information applies to all DB aliases unless you use the Databases tab to provide explicit logon information.

To modify the saved user ID and password from within the Configuration program, select Configure Options from the main window and edit Personal Options.
To modify the user ID and password from within Optim, select Personal from the Options menu on the main window and edit the Logon tab.

Stored Procedure Defaults

Use the Stored Procedure Defaults tab to provide the procedure qualifier and grant authorization ID required to create or refresh stored procedures. The default stored procedure information applies to all DB aliases unless you use the Databases tab to provide explicit stored procedure information.

On each tab, enter the necessary information and click Proceed. The Configuration program connects to the database, catalogs the stored procedures, writes the configuration file entries, and optionally creates primary keys. These four steps are repeated automatically for each selected database. When complete, the next step is to configure security.