Create Optim Directory

To create an Optim™ directory, you must provide the name, the database that hosts the directory, and connection information for the database. After you create the directory tables, the Configuration program creates packages, plans, or procedures and a Windows registry entry that allows the workstation to access the directory. You must create the Optim Directory in one of the following databases: Oracle, SQL Server, or DB2® for Linux®, UNIX, and Windows. You can create DB Aliases for other supported databases, as well as these databases, but you cannot put the Optim Directory in them. For example, you can create DB Aliases to process data for the following data sources, but cannot put the Optim Directory in them: Informix®, Sybase ASE, DB2 for z/OS®, Teradata, Netezza®, PostgreSQL, and Hive (for Hadoop).

Specify Optim Directory

The first step in creating an Optim directory is to name it. Use the Configure First Workstation - Specify Optim Directory dialog to name or select the Optim directory.

The Specify Optim Directory dialog includes the following options. You must select an option, enter a directory name, and click Proceed to open the next dialog.

Create New Optim Directory and Registry Entry. Select this option to create the first or an additional Optim directory. This option is always available and is selected when the dialog opens.
If no registry entry for the directory exists for the workstation, the default name, OPTIMDIR, is shown. You can use this name or enter a different name for the new directory (1 - 12 characters, no embedded blanks).
Use Existing Optim Directory and Registry Entry. Select this option if you are updating or continuing the configuration of the first workstation. This option is available after registry entry are created. You must enter the name of the existing directory you want to use.

Specify Optim Directory DBMS

Before you can create directory tables, the database instance for the directory must exist; that is, it must be configured under a database management system. To create a directory, you must identify the DBMS type and version on the Specify Optim Directory DBMS dialog.

When the dialog opens, the Optim Directory Description, Type, and Version might be populated with previously entered information. The Specify Optim Directory DBMS dialog includes the following items:

DBMS Specifications

Optim Directory Description: Enter text to explain the purpose of the directory (up to 40 characters). The description is especially valuable if you have multiple directories.
Type: Select the DBMS for the directory. To select from a list, click the down arrow. The selected DBMS is displayed on the status bar of subsequent dialogs in the process. An Optim directory in a Microsoft SQL Server database is not accessible to an Optim server on Linux or UNIX.
You must create the Optim Directory in Oracle, SQL Server, or DB2 for Linux, UNIX, and Windows. You can create DB Aliases for other supported databases, as well as these databases, but you cannot put the Optim Directory in them.
Version: Select the version of the DBMS. To select from a list, click the down arrow.

Specify Character Set of Optim Directory

Character data such as object names and primary keys are stored in the Optim directory in one of these formats: ASCII, Unicode, or multibyte. Choose the option that corresponds to the character set of the database that hosts the Optim directory:

If the database is multi-byte format, the character set must be MBCS.
If the database is Unicode, the character set must be Unicode.
If the database is single byte, the character set can be either Unicode or ASCII.

Example of the Configure First Workstation - Specify Character Set of Optim Directory dialog

Select the appropriate option and click Proceed to open the next dialog.

Connect to Database

The Configuration program must connect to the database to create the directory tables and packages, plans, or procedures. To enable this connection, you must provide a valid user ID, password, and connection string.

The user ID must have the DBMS privilege to create the directory tables and to catalog the packages, plans, or procedures under the appropriate table identifier. Later, this workstation can access the directory by using the same identifier or a different identifier with a, perhaps, lower level of authorization.

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.

Example of the Configure First Workstation - Connect to Database dialog, described in following text.

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

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 a password (up to 30 characters) that corresponds to the specified user ID.

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.

DBMS	Connect String Example
DB2	Database name or alias.
Oracle	Connection string or host string.
SQL Server	Data source name (DSN) or server.

DB Name

If the directory is in a SQL Server database, enter the name of the database instance for the directory.

If you specify a user ID that is associated with another directory in the same database, you are prompted for a different ID.

Select Yes to use the existing directory and open the Create/Drop Packages dialog.
Select No to continue creating a directory with a new schema name and open the Create Optim directory Tables dialog.

Create Optim Directory Tables

After the workstation is connected to the database, you can create the directory. The configuration program names the tables automatically. You must provide the identifier (Creator ID, Owner ID, or Schema Name) and the database table space for each table, however. You can create the directory tables in a unique table space or in a table space with other database tables. You can also specify the table space for tables, individually or as a group, and browse the DDL generated to create the directory tables. The Create Optim Directory Tables dialog prompts you for the information to create these tables.

The Create Optim directory Tables dialog includes the following items:

Directory Table Specifications

Schema Name

Enter an identifier for the directory tables. The label is

Creator ID when you create the directory in a DB2 database.
Schema Name for an Oracle database.
Owner ID for a SQL Server database.

For an Oracle database, the schema name must not be SYS or a name of a directory table such as, PSTDBA2, PSTPK2, PSTREL2, or PSTPT2.

Default Tablespace

Select a default table space. To select from a list of available DBMS table spaces, click the down arrow.

Tablespace grid

Directory Table: The names of the directory tables.
Tablespace: Click a cell to select from a list of available table spaces in the database. Leave the cell blank to place the table in the default table space.

Create/Drop Packages

After the directory tables are created, the configuration program automatically creates the packages, plans, or procedures that are used to access them. If you are creating the directory in an Oracle database, the Create/Drop Packages dialog is displayed. If you are using SQL Server, the same general dialog is displayed as Create/Drop Stored Procedures. However, if DB2 for Linux, UNIX, and Windows is the DBMS for the directory, the Configuration program displays the Bind/Drop Plans dialog. See Bind/Drop Plans.

The Create/Drop Packages dialog includes the following items:

Tables

Description of the tables for which packages (plans) or procedures are being created.

Package Specifications

Create/Refresh

Option to create new or refresh existing directory packages (plans) or procedures. This option is always available when you are creating a directory, and it is the default selection when the dialog opens.

Use Existing

Option to use existing packages (plans) or procedures. This option is available only if packages or procedures exist for the directory.

Drop

Option to drop existing packages (plans) or procedures. This option is available only if packages or procedures exist for the directory database.

Qualifier

Previously entered high-level qualifier that is needed to access directory tables. The label is

Collection Name for DB2.
Schema Name for Oracle.
Owner ID for SQL Server.

Grant Auth ID

Enter an identifier for authorized users. You can specify a user ID, a group name, or public. When this ID is public, all users can run the Optim solution.

Display SQL

Select this check box to display SQL statements before you create or drop packages (plans) or procedures.

Note: If you responded Yes to the prompt from the Connect to Database dialog, you are asked whether to use the existing directory.

Select Yes to use the existing directory and open the Connect to Database dialog.
Select No to not use the existing directory and terminate the directory creation process.

Bind/Drop Plans

When you create a directory in a DB2 for Linux, UNIX, and Windows database, the configuration program prompts for authorization information by using the Bind/Drop Plans dialog.

The elements in the Bind/Drop Plans dialog are similar to elements described for Create/Drop Packages with the following exception:

Always Display Output: Select this check box to open the Browse File dialog to review any errors, warnings, and information about the bind. If problems or a failure occurs during the bind, the Browse File dialog always opens.

Define Character Format

If the Optim solution supports Unicode or multibyte data for the DBMS (except Microsoft SQL Server), you must indicate the character format for the directory.

Keep Optim Directory Data in Unicode Format

If you are creating a directory in a Unicode database, you are prompted to indicate whether the directory data is kept in Unicode format. The prompt is displayed on the Keep Optim Directory in Unicode Format dialog.

Example of the Configure First Workstation - Keep Optim Directory in Unicode Format dialog, described in following text.

If the directory is in a DB2 for Linux, UNIX, Windows database, a dialog indicates that DB aliases for DB2 databases must use the same character format.

Specify Character Set of Optim Directory Data

When you create a directory, you are prompted to indicate the format in which the directory stores data: single-byte, Unicode, or multibyte. The character sets of the DBMS client and the database server must match your selection.

Example of the Configure First Workstation - Specify Character Set of Optim Directory dialog, described in following text.

Information on Unicode Format (UTF-8)

If you select Unicode format on the Specify Character Set of Optim Directory Data dialog, you are prompted to acknowledge the following character set requirements:

Character sets for both the DBMS client and server must be Unicode.
The directory must be in Unicode format if it includes DB aliases for databases that use character sets that include single byte, Unicode, or both.

Information on multi-byte format (MBCS)

If you select multi-byte format on the Specify Character Set of Optim Directory Data dialog, you are prompted to acknowledge the following character set requirements:

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 Configure First Workstation - Information on Multi-byte Format (MBCS) dialog

Round Trip Issues with Multi-byte Format

After you acknowledge the multibyte character set requirements for a directory, you are prompted to acknowledge multibyte 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 Configure First Workstation - Round Trip issues with Multi-byte Format dialog.

Register Optim Directory

The Configuration program creates a registry entry on the Windows workstation that is used to access the directory. For subsequent access to the directory from the workstation, you can use the Connect to Database dialog to specify a different user ID and password. (You can modify these entries when you configure Personal Options.)