Use the Database tab to manage database
activity during Insert, Restore, and Delete Processes.
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:
- 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.

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