Database Tab

Use the Database tab to manage database activity during Insert, Restore, and Delete Processes.

Product Options dialog - Database tab

Maximum Commit Frequency

Specify the maximum number of rows (1 - 999999) inserted, restored, or deleted before changes are committed to the database. If you do not specify a limit, the default value is 200. An Insert, Restore, or Delete Request can require more frequent commits.

Sybase Unchained Mode

Optim™ normally runs in chained mode. When a trigger in a Sybase ASE table will be fired as a result of an Insert or Delete Process, and the trigger calls a stored procedure that must run in unchained mode, the connection must be in unchained mode for the procedure to work.

Select an option for running an Insert or Delete Process, as follows:

Active
Require Insert, Restore, and Delete Processes to run in unchained mode; the Run in Unchained Mode check box in Personal Options (see Sybase Unchained Mode) is unavailable.
Inactive
Set all processes to run in normal mode; the Run in Unchained Mode check box in Personal Options is unavailable.
Default Active
The Run in Unchained Mode check box in Personal Options is available and selected, by default. Insert, Restore, and Delete Processes run in unchained mode unless the Run in Unchained Mode check box in Personal Options is cleared.
Default Inactive
The Run in Unchained Mode check box in Personal Options is available and cleared, by default. Insert, Restore, and Delete Processes run in normal mode unless the Run in Unchained Mode check box in Personal Options is selected.

Allow User to Lock Tables

Select this check box to enable the Lock Tables option in the Insert, Restore, and Delete Request Editors. Users can then use the option to lock tables during an Insert, Restore, or Delete Process. Locking tables ensures that other database activity does not interfere with the process and prevents other users from accessing tables involved in the process.

Allow Insert Action Delete Option

Select this check box to enable the Delete Options box in the Insert Request Editor. If you clear this check box, the Delete Options box is unavailable, and the Delete option is set to No Tables, by default.

Enforce DBMS Rel. Name Lengths

Select this check box to enforce DBMS restrictions for relationship name length when creating or importing relationships. This option is selected by default. Clear this check box to override DBMS naming restrictions and create or import relationships with names (constraints) up to 64 characters. After changing this option, you must exit Optim before the change takes effect. You cannot create a DBMS relationship based on an Optim relationship with a name that exceeds the DBMS restrictions.

Allow Optim PK/Rels on DB Views

Select this check box to allow users to define Optim Primary Keys and Relationships for database views.

Extract using Uncommitted Read

Set an option to enable extracting of uncommitted rows from the database during an Archive or Extract Process. You can extract uncommitted rows from specific tables in the Access Definition or all tables. Selecting this option for tables with known performance problems may increase the speed of your Archive or Extract processes. This option is available only if your Optim license key includes support for a DB2® LUW or DB2 z/OS® database.

Active
Automatically extract uncommitted rows from each table in the Access Definition during all Archive or Extract Processes. The Uncommitted Read check box on the Access Definition Editor is unavailable.
Inactive
Automatically extract only committed rows from each table in the Access Definition during all Archive or Extract Processes. The Uncommitted Read check box on the Access Definition Editor is unavailable.
Default Active
The Uncommitted Read option on the Access Definition Editor is available and selected by default. Uncommitted rows are extracted from the table unless the Uncommitted Read check box is cleared.
Default Inactive
The Uncommitted Read option on the Access Definition Editor is available and cleared by default. Uncommitted rows are not extracted from the table unless the Uncommitted Read check box is cleared.
Notes:
  • If you choose to extract uncommitted rows, the relational integrity of the data in the Archive or Extract File may be compromised. Use caution if inserting data from any Archive or Extract File with uncommitted rows.
  • Optim disables the extracting of uncommitted rows if the DBMS or version does not support it, regardless of the setting.

Parallel Processing

Set options to determine the maximum number of concurrent database connections allowed. Increasing database connections improves performance when processing large quantities of data by allowing multiple threads to process rows in parallel.

Allow Parallel Processing
Select this check box to enable Maximum Database Connections.
Maximum Database Connections
Increase the maximum number of concurrent database connections for an Archive, Extract, or Delete Process. You can select an even number of maximum database connections, from 2 through 32, or Maximum. Your selection enables Database Connections on the Archive, Extract, and Delete Request Editors and Maximum Database Connections in Personal Options (see Parallel Processing).

MBCS Roundtrip Processing

Options for handling characters that could cause round-trip conversion issues in a multi-byte Optim Directory or DB Alias.

Optim uses the Unicode character set in dialogs and to process data. In some multi-byte character sets (such as Oracle JA16SJIS), multiple characters are mapped to the same Unicode character. When these characters are converted from Unicode back to multi-byte (a round trip), the original character may not be returned.

Archive/Extract Requests
Select an option for handling round-trip conversion issues during Archive or Extract processing:
Stop Processing
Stop processing when a multi-byte character is encountered that could cause an incorrect round-trip conversion. Each row of data is checked for characters that could cause an incorrect round-trip conversion.
Ignore all round-trip Failures
Continue processing when a multi-byte character is encountered that could cause an incorrect round-trip conversion. (Default.)
Use Value from Personal Options
Use the round-trip processing setting from the Database tab in Personal Options.
Insert/Load/ Convert Requests
Select an option for handling round-trip conversion issues during Insert, Load, or Convert processing:
Stop Processing
Stop processing when a multi-byte character is encountered that could cause an incorrect round-trip conversion. Each row of data is checked for characters that could cause an incorrect round-trip conversion.
Ignore all round-trip Failures
Continue processing when a multi-byte character is encountered that could cause an incorrect round-trip conversion. (Default.)
Use Value from Personal Options
Use the round-trip processing setting from the Database tab in Personal Options.

Select the Ignore all round trip Failures option if the database does not contain data with characters that could cause an incorrect round-trip conversion, or if columns used to manipulate data in a Column Map (for example, a function is used) and columns for which selection criteria are defined do not contain characters that could cause an incorrect round-trip conversion.

DBMS Buffer Limits

Set the buffer size to use when fetching or deleting rows from the database. Optim multiplies the specification for Fetch Buffer Size or Delete Buffer Size by the value for Maximum Database Connections. You can select a value from 64K to 1024K, in increments of 32. Use the default value unless you are processing tables larger than 10,000 rows.

Fetch Buffer Size
Size of the buffer used when rows are fetched from the database. The default is 512K.

For Teradata users, this entry controls the maximum response buffer size (response parcel) used by CLIv2 (Call-Level Interface v2) when communicating with Teradata. The maximum buffer size for Teradata is 1 MB.

Delete Buffer Size
Size of the buffer used when rows are deleted from the database. The default is 512K.

For Teradata users, this entry controls the maximum request buffer size (response parcel) when communicating with Teradata. The maximum buffer size for Teradata is 1 MB.

Oracle

For an Oracle database, you can set options to control archive, extract and delete processing. Click the Oracle button to display the Oracle Settings panel:

Oracle settings panel, fields explained below
Use Oracle Array Delete

Select Use Oracle Array Delete to use the Oracle array delete feature during a delete process.

Note: If your site audits delete processing, be aware that Array Delete may report rows as being successfully deleted that do not exist in the database and, therefore, were not actually deleted by the process.

The Oracle array delete feature is performed during a delete process only when the following are true:

  • Compare Row Contents is not selected in the Delete Request Editor.
  • Both the table in the source file and database have a unique primary key.
  • The table has no file attachments to be deleted.
Allow DML Operations by RowID
Controls whether Optim extracts the Oracle internal row ID during an extract or archive process. Specify:
Never
Row IDs are not extracted during an Extract or Archive process. This is the default. If there are any Extract RowID specifications in the Access Definition they are overridden by this Product Option. Any row ID extracted previously is never used in an Extract or Archive request.
Dynamic
Optim evaluates the row to be extracted or archived. Optim extracts the row ID only if there is no unique index for the row.
User-defined
Optim uses the value specified by the user in the Access Definition for Extract RowID at the table level.
Always
Optim extracts or archives the row IDs for every row and every table.
Undo
Unselects any selections for this dialog.
Defaults
Restores the default values: unselects Use Oracle Array Delete and sets Allow DML Operations by RowID to Never.

Teradata

If you use a Teradata database, click the Teradata button to specify the appropriate Teradata settings on the Teradata Parallel Transporter (TPT) Settings dialog.

Example of the Teradata Parallel Transporter dialog for TPT Settings

Allow Temp Table usage
This option determines whether temporary tables are created during an extract or archive process to improve the performance of archives and extracts of related Teradata tables. If you enable this feature, the solution will create temporary tables in Teradata during an archive or extract process. The temporary tables are used to store key values to join with related tables. Select:
  • Never to not allow the solution to create temporary tables during an extract or archive process. This is the default value.
  • When Possible to allow the solution to create temporary tables when possible during an extract or archive process. This setting will improve performance during an extract or archive process.
Temp Database
If appropriate, type the name of the database in which you want temporary tables created. If a database name is not specified, the tables will be created in the same database as the reference tables. (The user must have CREATE TABLE, INSERT, and DROP TABLE privileges for the database to create temporary tables.)
Allow Teradata Parallel Transporter (TPT) usage
This option determines whether the Teradata Parallel Transporter (TPT) is available for extract and archive requests. TPT is a parallel multi-function load environment. TPT provides scalable access to data sources from a single interface using a single SQL-like scripting language or an open API, and provides a completely parallel environment. TPT utilizes system resources efficiently by allowing distribution of the workload across CPUs, eliminating bottlenecks in data loading process. Select:
  • Never to never allow the use of TPT for extract and archive requests. This is the default value.
  • When Possible to use TPT when possible for extract and archive requests, otherwise allow the driver to "step down" to CLIv2 (Call-Level Interface v2).
    Optim will step down to CLIv2 if:
    • a table contains LOBs, or
    • a table includes a Point-and-Shoot list, or
    • the table access strategy is Key Lookup.
Note: TPT is used to scan start tables and reference tables, whether the scan is a full-table scan or a scan by subquery.

TPT Limit Settings

Maximum Sessions
Type the maximum number of sessions to log on. The default is one session per available AMP. The maximum value cannot be more than the number of AMPS available.
Minimum Sessions
Type the minimum number of sessions required for a TPT job to continue. The default is one session. This entry must be greater than or equal to 1 and less than or equal to the maximum number of TPT sessions.
Sleep
Type the number of minutes the TPT should pause before attempting to log on when the maximum number of load and export operations are already running on the Teradata database. The default is six minutes.
Tenacity
Type the number of hours the TPT should attempt to log on if the maximum number of load and export operations are already running on the Teradata database. This entry must be greater than zero to enable the Tenacity feature. Type zero to disable the Tenacity feature.


Feedback