Detailed Description of Options

The utility connects to the Oracle database identified by oserver using the supplied credentials, then examines tables specified in one of three ways.

By using the:

  • -tables option
  • -tableFile option
  • If neither the -tables nor -tableFile option is specified, the utility examines all tables owned by ouser.

-tables and -tableFile options can be used separately or together, and each can appear multiple times on the command line.

A table specification tablespec has the following format: 
[owner.]name[:attribute]...

Owner and/or attributes are optional. A table name of * (which may need to be quoted if used on the command line) is expanded to represent all tables belonging to the owner. Any database identifier, such as a schema owner, table name, or user name, must be properly quoted if it does not conform to standard database-identifier rules.

Attributes are colon-separated key or value pairs. Most control aspects of parallel data loading; see the Parallel Data Loading section for details. The supported attributes are:

Attributes Description
rows=n Specifies the row count for the table. n must be an integer from 0 through 9223372036854775807. The utility determines a table’s row count during analysis. Specify a row count to avoid a table scan when determining the row count is the only reason to analyze the table. A table’s row count is required for compression evaluation, table-size estimation, and parallel data loading. There is no default value.
ttwriters=w Specifies the maximum number of TimesTen threads, per reader, to be used when performing intra-table parallel load for this table. w is an integer value between 1 and 16. The default value is determined by the ttthreads value (minus 1) specified in the -dataLoadParallelism option.
oradop=n Specifies the degree of parallelism to be used for each Oracle reader in the Oracle database. n is an integer value between 0 and 16. 0 or 1 means no parallel query in Oracle. The default value is determined by the oradop value specified in the -dataLoadParallelism option.

Note that whether the value specified is honoured or not depends on the configuration of the Oracle database.

orapar={none|auto(n)|hash(n,colexpr)| partition(n)} Specifies how intra-table parallel data load is executed for tables matching this specification. The meaning of the different values is as follows:
  • none - Table is not loaded in parallel.
  • auto[(n) - If the table is partitioned, the utility loads it using partition(n); otherwise, it uses hash(n).
  • hash(n,colexpr) - Table is loaded using n readers. Rows are distributed across the readers based on ORA_HASH(colexpr,n-1). If colexpr is not specified then ROWID is used.

    If colexpr is specified as =PK= then a concatenation of the table's primary key columns are used. In this case, it is an error if the table does not have a defined primary key. If n is not specified, the utility uses the default value of the -dataLoadParallelism option, or 8 if no default is specified.

The default is auto. Tables are considered for inter-table parallel loading unless parallel data loading is disabled. If specified, the orapar attribute must be last in the attribute list.

The -dataLoadParallelism option lets you override built-in limits and defaults that control parallel data loading. You should rarely need to change these values, but if necessary, use the following syntax: -dataLoadParallelism disabled.

Parallel data load is disabled. Tables are loaded sequentially with no parallelism. Any parallel data load attributes specified in table specifications are ignored.

You can set parameters to control parallel data loading defaults:

   
minrows Specifies the minimum number of rows a table must contain before the system considers it for intra-table parallel loading. A value of 0 means all tables are considered. The default is 500,000.
ttthreads The maximum number of concurrent threads allowed in TimesTen during parallel data loading. Each reader and each writer runs as a separate thread. It must be greater than or equal to 0. A value of 0 means no limit. The default is 36.
tttabwriters The default maximum number of writers per reader when performing an intra-table parallel load on a TimesTen table. It must be between 1 and 16. The default is 3.
orareaders The maximum number of concurrent readers allowed in the Oracle database during parallel data loading. It must be greater than or equal to 0. A value of 0, the default, means no limit.

Both ttthreads and orareaders cannot be set to 0.

consistency Specifies what data consistency is required when loading data. Allowed values are:
  • 0 - No consistency enforcement. If the table data in Oracle is modified in any way while the load is running then the loaded data may be inconsistent.
  • 1 (default) - All data loads are performed using Oracle flashback queries based on the time when the load script starts running.
  • 2 - All data loads are performed using Oracle flashback queries based on the current SCN when the load script starts running. In order to use this option, your Oracle database user must be granted EXECUTE privilege on the DBMS_FLASHBACK package.
oradop The default degree of parallelism used for each reader in the Oracle database during parallel data loading. It must be between 0 and 16. 0 and 1 both mean no parallel query. The default is 1.
orapar Specifies the default mechanism for intra-table parallel load. Allowed values are none, auto[(n)], partition[(n)] or hash[(n)].

The default is auto(8).

For details, see the table above.

Parallel data loading is not supported on Windows. When you run the utility on Windows, you can generate a parallel data load script for Unix or Linux by specifying -target unix. If the target platform is Windows, the utility behaves as if you specified -dataLoadParallelism disabled.

Parallel data loading works as follows:
  • For each table with intra-table parallel loading enabled, the utility assigns Oracle Database readers based on the explicit configuration or applicable defaults. Each reader processes a subset of the table’s rows using one of the mechanisms partitioning or hashing.
  • When querying the Oracle database to retrieve rows, each reader uses parallel query with the specified dop value. A dop value of 0 or 1 uses a nonparallel query. Each reader has a set of writers that inserts data into the TimesTen table.
  • The number of writers per reader is specified by the ttwriters attribute or the tttabwriters default. When loading a table, the utility always check the limits for total Oracle readers and total TimesTen threads specified by the -dataLoadParallelism option.

For inter-table parallelism, the utility groups tables into sets to load together based on their intra-table parallelism settings and the overall limits for Oracle Database readers and TimesTen threads specified by the -dataLoadParallelism option.

Some examples of valid table specifications are:

sometable - table ouser.sometable

scott.tab2 - table scott.tab2

customer:rows=125000 - table ouser.customer with row count 125,000

* - all tables owned by ouser

scott.* - all tables owned by scott

scott.*:rows=1000 - all tables owned by scott applying a row count of 1000 to all of them

For examination and analysis purposes, materialized views are treated as tables. You can control this behavior with the -importMviews option. Regular views are not imported.

For each table examined, the utility generates a TimesTen compatible definition. This definition includes some or all primary key and foreign key definitions, unique and nonunique indexes, bitmap indexes, recommended data type mappings, and compression clauses. Use the available options to control what the utility includes in the generated DDL.

The utility maps source data types to TimesTen data types to optimize space usage in the TimesTen database. The following types of mappings are available:
  • Numeric: Standard mapping converts NUMBER columns with a scale of zero to an appropriate TimesTen native integer type. This safe mapping does not risk data truncation and does not require source data analysis. Aggressive mapping analyzes the source tables to identify NUMBER columns whose stored values use less precision or scale than the column definition allows. For those columns, the generated TimesTen definition uses a smaller precision or scale. Aggressive mapping is safe only for the data present when the analysis runs. Later inserts or updates can exceed the mapped precision or scale and may no longer meet the criteria used for the mapping.
  • Varlen: The utility analyzes source tables to identify VARCHAR2 and NVARCHAR2 columns whose stored data is shorter than the column’s defined size. It then defines smaller columns in TimesTen to reduce space usage. This mapping is safe only for the data present when the analysis runs. Later inserts or updates can exceed the mapped column size and may no longer meet the mapping criteria.
  • JSON: A native Oracle JSON column is mapped directly to a TimesTen JSON column, and the generated CreateTables.sql file defines the target column as JSON. No conversion to a character or binary data type is performed.

    The var field of the -typeMap option controls JSON maximum-length analysis. When var=1, the utility analyzes native JSON values and verifies that they fit within the TimesTen JSON limit. Values that exceed the limit are handled according to the -onBadType option. When var=0, the utility does not perform this validation during analysis. As a result, an oversized value can cause data loading to fail later.

  • RAW/LONG/LONG RAW: TimesTen does not support the RAW, LONG, or LONG RAW data types. This mapping converts source columns of these types to the following compatible TimesTen data types:
    • RAW(n) -> VARBINARY(n)
    • LONG -> VARCHAR2(4194304)
    • LONG RAW -> VARBINARY(4194304)

    Oracle Database allows LONG and LONG RAW values up to 2 GB, but TimesTen limits VARCHAR2 and VARBINARY values to 4 MB. Therefore, some source values cannot fit in TimesTen. After this mapping, the utility also applies varlen mapping to any VARBINARY columns resulting from RAW(n) columns if you specify varlen in the type-mapping options.

  • LOB: TimesTen supports CLOB, NCLOB, and BLOB data types. However, the ttLoadFromOracle() built-in cannot currently load data into LOB columns. Map LOBs to the following compatible TimesTen types to enable data loading:
    • CLOB -> VARCHAR2(4194304)
    • NCLOB -> NVARCHAR2(2097152)
    • BLOB -> VARBINARY(4194304)

    Oracle Database LOBs can be very large, while TimesTen variable-length types have much smaller maximum sizes. As a result, some values may be too large to fit. When LOB columns are mapped to VARCHAR2 or VARBINARY, the utility also applies varlen mapping if you specify it in the type-mapping options.

Aggressive type mappings (num=2, var=1) and compression evaluation require a full scan of the source tables to analyze their data.

When evaluating a table for compression, the utility considers three factors that the -compression option parameters control:
  • The minrows parameter specifies the minimum number of rows a table must contain before the utility considers compressing it. Tables with fewer rows are never compressed. You can specify a value from 1 to 9223372036854775807. A value of 1 considers every table for compression. The default value is 1024.
  • The padding factor (padding option). Once the number of distinct values in a column is determined by data analysis, it is increased by padding percent to allow for future changes in the source data.
  • The cfactor parameter specifies the minimum required compression factor. The utility first estimates how much storage is saved by compressing each table column. If the estimated compression factor (estimated compressed size divided by the estimated uncompressed size) is lower than cfactor (lower values mean higher compression), then the column is a candidate for compression. After evaluating all candidate columns, the utility estimates the compression savings for the entire table if it compresses those columns. The utility compresses the table when the estimated compression factor is less than or equal to cfactor. You can specify cfactor from 1% (100x compression) to 100% (no compression). The default is 70%.

Consider the following information about source data analysis and error handling:

  • If the utility scans a table for multiple purposes, such as row counts, type mapping, or compression evaluation, it performs all required analysis in a single scan. It does not scan the same table multiple times.
  • The utility scans any tables without specified row counts to determine their row counts. It writes the results to the generated tableList.txt file.
  • By default, the utility uses parallel query at the source database’s default degree of parallelism when it analyzes data. Specify -parallel 0 to disable parallel query, or specify -parallel with a value from 2 through 128 to request an explicit degree of parallelism.

    The source database configuration determines whether it can use the requested degree of parallelism. The -parallel option affects only data analysis. The -dataLoadParallelism option and table specification attributes separately control parallel query during data loading.

  • Scanning and analyzing source data can be resource-intensive and time-consuming. The required time depends on factors such as the number of rows, the number and data types of analyzed columns, and source database performance. In general, determining row counts and optimizing VARCHAR2/NVARCHAR2 storage (-typeMap x,1) are faster and less expensive than evaluating compression (-compression 1 or 2) and optimizing NUMBER data types (-typeMap 2).
    TimesTen supports only certain source data types. If the utility encounters a column with an unsupported or unrecognized data type, it reports the issue, skips the column, and continues processing by default. Use the -onBadType option to change this behavior:
    • 0: Stop processing
    • 1: Skip the table
    • 2: Skip the column (default)
The following files are created in the output directory (the default is your current directory):
  • [fnprefix]TableList.txt - lists all tables evaluated during the run in a format suitable for use with the -tableFile option.
  • [fnprefix]CreateUsers.sql - creates the TimesTen database user.
  • [fnprefix]DropTables.sql - drops all the tables.
  • [fnprefix]CreateTables.sql - creates all the tables.
  • [fnprefix]DropIndexes.sql - drops all the indexes and foreign keys.
  • [fnprefix]CreateIndexes.sql - creates all the indexes, primary keys and foreign keys.
  • [fnprefix]UpdateStats.sql - updates TimesTen optimizer statistics for each table.
  • [fnprefix]LoadData.sql - loads data into TimesTen tables from the source tables; tables with LOB columns are not included in this script.
  • [fnprefix]ttPDL.sh - loads table data in parallel. Use it instead of the LoadData.sql script to take advantage of parallel data loading. This script is not generated when the target platform is Windows.
After you connect to the TimesTen database as an appropriate user, you can run the SQL scripts from the ttIsql command prompt as follows: 
Command> @CreateUsers;

Alternatively, you can run the scripts in batch mode from the operating system command line as follows: 

$ ttIsql -f CreateUsers.sql MyDSN

Run the SQL scripts as the TimesTen instance administrator (the user who installed TimesTen) or as a user with appropriate permissions on all the tables. Before creating tables, create the users in the TimesTen database. You can use the CreateUsers.sql script to create users. Only the TimesTen instance administrator can create users.

Run the UpdateStats.sql script as the instance administrator or as the user who owns the affected tables. To run the script as any other user, grant that user the ALTER ANY TABLE system privilege.

Run the LoadData.sql script as a TimesTen database user who also exists in the backend Oracle Database. You must specify the TimesTen and Oracle user credentials in the connection string. For example: 
ttIsql -f LoadData.sql "DSN=MyDSN;UID=scott;PWD=test;OraclePWD=test"

In addition, configure the TimesTen DSN to connect to the source Oracle Database. You must specify an appropriate value for the OracleNetServiceName attribute either in the DSN definition or as part of the connection string.

Provide the ttPDL.sh script with a TimesTen connection string that includes a DSN and UID. If you omit PWD and OraclePWD, the script prompts you for their values, which is more secure than specifying them on the command line. For example:
ttPDL.sh "DSN=MyDSN;UID=scott"