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
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.
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.

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.

The Create/Select DB Alias dialog includes the following options:
When you select an option to create a new or modify an existing DB alias, you are prompted for the necessary information.
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.)
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.

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:
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 |
In most cases, access to database tables requires plans, packages, or procedures, which the configuration process creates automatically:
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.

The Create/Drop Packages dialog includes the following items:
| 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 |
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.
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.
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.
If your Optim solution supports Unicode on the database for the Optim directory, you must indicate whether the data is kept in Unicode format.

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.

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

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

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.

The Configuration program creates a registry 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 registry entry on the Connect to Database dialog.
When the Connect to Database dialog opens, User ID, Password, and other prompts are populated with any previously entered values.

After you create the DB aliases, you create Optim Primary Keys for databases you want to use with your Optim solution.
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.

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

The Select Tables dialog includes the following items:
When the process completes, you are prompted to load the sample database 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.
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:
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.

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.
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:
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.

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.
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.

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.

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.

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

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

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.
Use the Databases tab on the Create Multiple DB Aliases dialog to provide explicit information for each DB alias.
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.
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.
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 registry 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.
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 registry 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.