Using the AUTOSCHEMAOPTIONS Parameter

The AUTOSCHEMAOPTIONS parameter with TYPEMAP and IGNORE_DATA_TRUNCATION options, allows you to perform the following overrides:

AUTOSCHEMAOPTIONS option_name Description Syntax Example
AUTOSCHEMAOPTIONS TYPEMAP Allows you to choose a specific data type for specific source data type name(s). 

AUTOSCHEMAOPTIONS TYPEMAP 'source type name' = 'target type name'

For example, when a source column with data type smallint needs to be converted to int as the desired data type for target columns, use the following:

AUTOSCHEMAOPTIONS TYPEMAP 'smallint' = 'int'
AUTOSCHEMAOPTIONS TYPEMAP

Allows you to choose a specific data type for specific source column name(s).

Note:

Fully qualified column names are also supported, like table_name, column_name. The name components can contain wildcards and quoted names are also supported. 

AUTOSCHEMAOPTIONS TYPEMAP source column name = 'target type name'

For example, to use int as the desired data type for target column corresponding to the source column with name EMPLOYEE_ID:

AUTOSCHEMAOPTIONS TYPEMAP EMPLOYEE_ID = 'int'
AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION

Automatic Schema Evolution allows type-widening (up-conversion), wherever feasible, to avoid possible data truncation. The option IGNORE_DATA_TRUNCATION can be used to override any kind of type-widening.

For string or binary types (key columns or non-key columns), if using fixed length data type, and the target length is small, then AUTOSCHEMA converts to its corresponding variable length data type if it can accommodate the source data otherwise it fails You can override it with AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION. 

AUTOSCHEMAOPTIONS IGNORE_DATA_TRUNCATION
 -

Syntax for Using AUTOSCHEMAOPTIONS as a Standalone Parameter

To enable the AUTOSCHEMAOPTIONS as a standalone parameter in the Replicat parameter file, use the following syntax:

Syntax:

AUTOSCHEMAOPTIONS TYPEMAP 'datatype_src' = 'datatype_tgt'

AUTOSCHEMAOPTIONS TYPEMAP 'column_name' = 'datatype_tgt'

Example:

AUTOSCHEMAOPTIONS TYPEMAP 'int'='bigint'

AUTOSCHEMAOPTIONS TYPEMAP 'smallint'='bigint'

AUTOSCHEMAOPTIONS TYPEMAP employee_no='bigint'

AUTOSCHEMAOPTIONS TYPEMAP "Employee&Name"='varchar(50)'

Map Level Syntax

To enable the AUTOSCHEMAOPTION clause at the MAP level, it needs to be enclosed within parentheses, as shown in the following syntax.

Syntax:

MAP src.table1, TARGET tgt.table1,  AUTOSCHEMAOPTIONS (TYPEMAP 'int'='smallint');

To specify multiple AUTOSCHEMAOPTIONS clauses such as TYPEMAP as part of the same MAP statement, each option should be enclosed within parentheses:

Syntax:

MAP src.table2, TARGET tgt.table2,  AUTOSCHEMAOPTIONS (TYPEMAP 'smallint'='bigint','varchar(*)'='clob', employee_no='bigint', "Employee&Name"='varchar(50)');

Examples

Example 1

To enable AUTOSCHEMAOPTIONS on all the tables mentioned in the Replicat parameter file:

AUTOSCHEMAOPTIONS TYPEMAP 'int'='bigint'

DISCARDFILE ./dirrpt/RREP.dsc

MAP west.table1, TARGET east.table1;

MAP west.table2, TARGET east.table2;

MAP west.src*, TARGET east.tgt*;

Example 2

To enable AUTOSCHEMAOPTIONS with TYPEMAP 'int' = 'bigint' for all the tables in the replicat parameter file, and to additionally use TYPEMAP 'clob'= 'varchar(2000)' only for the tables in the hr schema:

AUTOSCHEMAOPTIONS TYPEMAP 'int'= 'bigint'

MAP west.table1, TARGET east.table1;

MAP west.table2, TARGET east.table2;

MAP hr.src*, TARGET hr.*, AUTOSCHEMAOPTIONS (TYPEMAP 'varchar(2000)'='clob')

Each line of the parameter file is processed sequentially and each map that is resolved would be based on the current state of global AutoSchema parameters at that time. The AutoSchema options specified at MAP level would take higher precedence over any conflicting global AUTOSCHEMAOPTIONS.

For example, if the source tables were created as:

create table src.t111 (pkcol int not null primary key, smallintcol smallint);
create table src.t222 (pkcol int not null primary key, smallintcol smallint);
create table src.t333 (pkcol int not null primary key, smallintcol smallint);

If the Replicat parameter file contains:

REPLICAT RTEST

USERIDALIAS ggeast

DDL AUTOSCHEMA  INCLUDE ALL

discardfile ./dirrpt/irddl.dsc

AUTOSCHEMAOPTIONS TYPEMAP 'int'='bigint'

MAP src.t111, TARGET tgt.ran_t111;

MAP src.t222, TARGET tgt.ran_t222, AUTOSCHEMAOPTIONS ((TYPEMAP 'int'='smallint'));

AUTOSCHEMAOPTIONS TYPEMAP 'smallint'='bigint'

MAP src.t333, TARGET tgt.ran_t333;

Then the AUTOSCHEMA would generate the DDL for target tables as follows:

create table 'tgt.'ran_t111' ('pkcol' bigint not null, smallintcol smallint, primary key( 'pkcol'))
create table 'tgt'.'ran_t222' ('pkcol' smallint not null,`smallintcol` smallint, primary key('pkcol'))
create table 'tgt'.'ran_t333' ('pkcol' bigint not null,'smallintcol' bigint, primary key('pkcol'))