Create Tab

Use the options on the Create tab to set defaults for creating objects. Note that you can establish as many as three layers of default settings for the creation of database objects, in addition to target system defaults. The default settings determine the values displayed in the Object List for the Create Utility and can be overridden at the object level by editing the list.

At the broadest level, DB Alias settings establish defaults for creating objects in the associated database. If desired, you can provide Personal Options settings, as described in the following text, for a user or group of users that override some or all DB Alias settings. A third level of optional defaults apply at the processing level to override Personal Options and DB Alias settings. Use the options on the Create tab to set second-level defaults for the Create Utility.

personal options create tab, explained below

DB Alias

Specify the DB Alias that identifies the database in which you want to create database objects. To select from a list, click the down arrow. Specify default options for creating different types of database objects on each corresponding tab.

Compile error Drop

Select this check box to automatically drop any Oracle object that causes a compile error during the Create Process. If you clear this check box and compile errors occur, you must interrupt the Create Process to drop the object before continuing.

This feature applies to Oracle compile errors that may occur on certain database objects: functions, packages, package bodies, procedures, triggers, and views. (The Create Process can create these objects, but they may not be functional.) Select this check box to correct possible problems in the Review SQL dialog before performing the Create Process.

Replace UDTs

Select this check box to replace table-type column references to User Defined [data] Types with base column data types in any generated DDL. When you clear this check box, references to UDTs are preserved in generated DDL. (This check box is available only when you select a DB Alias for a DB2®, Sybase ASE, or SQL Server database.)

Note: Clear this check box if you want UDT references in the generated DDL.

DB2 OS/390 Current Rules: DB2

Select this check box to require the user (i.e., Create and DDL) to create and delete LOB tablespaces, AUX tables, and unique Indexes. When you clear this check box, DB2 OS/390® automatically creates and deletes LOB tablespaces, AUX tables, and unique Indexes. This check box is selected by default.

Object Name Highlighting

Select a font color to highlight object name changes in the SQL statements shown on the Review SQL dialog before creating those objects in the target database. During the Create Process, object names (specified in the Table Map) are translated to be appropriate for the target database. This feature applies to creating text type database objects: functions, packages, package bodies, procedures, triggers, and views.

Limit
When creating a large number of objects, highlighting object names in color can affect the speed of the process. Specify the maximum number of created objects to highlight in color (i.e., if the number of objects to create exceeds the limit you specify, colorization is not used). The default is 1000.
Confident
Select a font color to highlight object name changes that are reasonably confident. Accept the default color (blue) or click the down arrow to select a different color.
Uncertain
Select a font color to highlight object name changes that may require verification because of the way different DBMSs use object names. Accept the default color (yellow) or click the down arrow to select a different color.

Create — Tables Tab

Use the Tables tab to specify the default database (for DB2 MVS™). Specify a default tablespace (segment, filegroup, or dbspace) for creating database objects. Specify an allocation percent to adjust SQL storage related parameters (for Oracle and DB2 MVS).

Default Database

Enter the name of the default database for creating tables. To select from a list, click the down arrow. This option is available only if you are using DB2 MVS. A single DB Alias in Optim can identify more than one database in DB2 MVS.

Use default database

Select this check box to use the default database for creating tables. If you clear this check box, the Create Utility attempts to use the source database from the Source File. However, if the source database does not exist on the target system, the Create Utility uses the default database.

Default Tablespace

Enter the name of the default tablespace (segment, filegroup, or dbspace) for creating tables. To select from a list, click the down arrow. If you select <Default>, the default set in the database is used.

Use default tablespace

Select this check box to use the default tablespace (segment, filegroup, or dbspace) for creating tables. If you clear this check box, the Create Utility attempts to use the tablespace (segment, filegroup, or dbspace) in the Source File. However, if the source does not exist on the target system, the Create Utility uses the default.

Allocation Percent

Enter a percent (0 to 999) to adjust SQL storage-related parameters for the Create Utility. The default is 100. Allocation percent is available for creating tables and indexes in Oracle and creating indexes in DB2 MVS.

Target SQL is generated based on the values of the objects in a Source File. If you specify zero (0), the storage-related clause in the SQL statement is omitted. Using a value other than zero results in a percentage of the source value being used in the target clause.

Create — Indexes Tab

Use the Indexes tab to select a default identifier for creating new indexes. Specify an allocation percent to adjust SQL storage-related parameters (for Oracle and DB2 MVS). Specify the default tablespace (segment, filegroup, or dbspace) for creating indexes.

personal options create indexes tab, explained below

Identifier

Specify the default identifier for new indexes based on the identifier from one of the following:

Table
Use the identifier from a corresponding target table as the default for new indexes.
Source
Use the identifier from the source index as the default for new indexes.
Current ID
Use the current SQLID (User ID) as the default for new indexes.
Explicit
Use an explicit identifier as the default for new indexes. If you select this option, you must specify an explicit identifier (1 to 64 characters). To select from a list, click the browse button.

Allocation

Enter a percent (0 to 999) to adjust SQL storage-related parameters for the Create Utility. The default is 100. Allocation percent is available for creating tables and indexes in Oracle and creating indexes in DB2 MVS.

Target SQL is generated based on the values of the objects in a Source File. If you specify zero (0), the storage-related clause in the SQL statement is omitted. Using any value, other than zero, results in a percentage of the source value being used in the target clause.

Default Tablespace

Specify a default tablespace (segment, filegroup, or dbspace) for creating new indexes, based on one of the following:

Table
Create an index in the same tablespace (segment, filegroup, or dbspace) as the owning table.
Source
Create an index in the same tablespace (segment, filegroup, or dbspace) as the index referenced in the Source File.
Explicit
Create an index in a particular tablespace (segment, filegroup, or dbspace). If you select this option, you must specify the appropriate default. If you select <Default>, the default set in the database is used.

Buffer Pool

The buffer pool (e.g., BP1) that is to be used when creating an Index. You can enter a specific value for the buffer pool or select a value from the list. (Buffer Pool is displayed only for DB2 MVS.)

The list displays any index buffer pools already specified for this DB Alias (i.e., on the Index Defaults tab of the DB Alias Editor), as well as the following:

<DEFAULT>
Select to use the default buffer pool specified by DB2 MVS. When creating an index, Optim does not generate a BUFFERPOOL clause in the Create statement.
<SOURCE>
Select to use the same buffer pool as the index for the source Archive or Extract File.

Create — Aliases Tab

Use the Aliases tab to select default options for creating new aliases. You can specify a default alias when you use DB2 CS, DB2 UDB, or DB2 MVS.

personal options create aliases tab, explained below

Identifier

Specify the default identifier for new aliases based on the identifier from one of the following:

Object
Use the identifier from the corresponding target object as the default for new aliases. For aliases, the corresponding target object is the table, view, or alias referenced in the alias.
Source
Use the identifier from the source alias as the default for new aliases.
Current ID
Use the current SQLID (User ID) as the default for new aliases.
Explicit
Use an explicit identifier as the default for new aliases. If you select this option, you must specify an explicit identifier (1 to 64 characters). To select from a list, click the browse button.

Create — Synonyms Tab

Use the Synonyms tab to select default options for creating new synonyms. You can specify a default synonym when you use Oracle or Informix®.

personal options create synonyms tab, explained below

Identifier

Specify the default identifier for new synonyms based on the identifier from one of the following:

Object
Use the identifier from a corresponding target object as the default for new synonyms. For synonyms, the corresponding target object is the table, synonym, function, package, package body, procedure, sequence, trigger, or view referenced in the synonym.
Source
Use the identifier from the source synonym as the default for new synonyms.
Current ID
Use the current SQLID (User ID) as the default for new synonyms.
Explicit
Use an explicit identifier as the default for new synonyms. If you select this option, you must specify an explicit identifier (1 to 64 characters). To select from a list, click the browse button.

Create — Triggers Tab

Use the Triggers tab to select default options for creating new triggers. You can specify a default trigger when you use DB2 UDB, Oracle, Sybase ASE, SQL Server, or Informix.

personal options create triggers tab, explained below

Identifier

Specify the default identifier for new triggers based on the identifier from one of the following:

Object
Use the identifier from a corresponding target object as the default for new triggers. For triggers, the corresponding target object is the table referenced in the trigger.
Source
Use the identifier from the source trigger as the default for new triggers.
Current ID
Use the current SQLID (User ID) as the default for new triggers.
Explicit
Use an explicit identifier as the default for new triggers. If you select this option, you must specify an explicit identifier (1 to 64 characters). To select from a list, click the browse button.