When you select the Service properties (right-click on the server name, select Properties and select the Subscription Wizard Tab). The options selected are saved and affect future subscriptions or future changes to existing subscriptions; modifying the values does not affect existing subscriptions. Some options (noted below) are available only in this view.
When you select Mapping Options on the Columns tab of the Subscription Wizard. In this case, the options selected are used only when rebuilding the column mappings of the current subscription; select Rebuild Columns to view the results of using these options.
The Character data destination precision heuristic of the Service Properties dialog is useful if you wish to customize the handling of CHAR and VARCHAR columns to avoid loss of data (truncation) when the database encoding of the destination database differs from the source. Some examples are replication from an EBCDIC SBCS (single byte character set) database to a Unicode database (UTF-8 encoding may require up to three bytes for a single character), or replication to or from a DBCS (double-byte) database configured to support Chinese, Japanese, or Korean ANSI code pages (code pages 932, 936, 949, and 950).
For instance, when replicating from a non-Unicode Db2 for i source to a Db2 for LUW UTF-8 destination, you may encounter the following error for certain character data:
SQLSTATE 22001, native error -302, The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.
This error occurs when some characters are converted into multi-byte characters and the size of the target table's character column is inadequate. The solution to this issue is to edit the column mappings in the affected subscription and increase the size of the affected columns on the target table.
The options in this tab allow you to automatically increase the default Precision value for destination columns of data type CHAR or VARCHAR.
Choose one of the following options for Character data destination precision heuristic:
Use source precision (no Inflation) This is the default and appropriate if your data contains only single-byte character sets (SBCS), or if you wish to manually adjust the size of destination character fields.
Inflate VARCHAR type
Inflate both VARCHAR and CHAR types
If you select an Inflation option, use the Inflation Percentage field to specify the increase in size (in percentage) of the destination column. For example, specifying 10% will result in a CHAR(10) source field mapping to CHAR(11) destination. The inflation percentage is rounded to the nearest integer value. If the result is the same as the original precision, a value of 1 is added. The maximum value is 200%, which increases the destination field by three times; this is sufficient to handle all worst-case expansion scenarios.
The inflation is limited by the maximum precision allowed for a data type on the destination. For instance, if a source column is defined as CHAR(255) and 255 is the maximum for a CHAR on the destination, the size will not be changed.
Inflation will not occur if the Promote CHAR types to WCHAR equivalents is selected.
Inflation will not change the destination type; i.e. CHAR will not be converted to VARCHAR if inflation would exceed the CHAR maximum. If type conversion is acceptable, consider using the Promote CHAR to VARCHAR option.
Data types such as NCHAR and NVARCHAR will map 1-1 to GRAPHIC and VARGRAPHIC types with respect to character precision - i.e. NCHAR(10) -> GRAPHIC(10); both will use 2 bytes of storage. If support for these data types is reported by the destination ODBC driver, SQDR will map to these types and no inflation is required.
Mapping to other national character types, such as nchar and nvarchar (used by SQL Server) will not require inflation, but may over-allocate storage when replicating from UTF-8 to UCS2.
Option |
Description |
Create LOB columns as not logged |
Certain target systems (e.g. Db2 for Linux, UNIX, & Windows 9.5 and earlier) require that LOB columns > 2mb be created as "NOT LOGGED". Enabling this option will instruct the SQDR subscription wizard to generate a CREATE TABLE statement with LOB columns defined as "NOT LOGGED". This option is global and will affect subscriptions being created for other targets, where this syntax may be illegal. We recommend enabling this option when needed (creating a subscription containing LOB columns to a Db2 UDB LUW database target) and reverting to the default (disabled) after creating the subscription. Default: Disabled Service Properties Subscription Wizard Tab only
|
Default CHAR and VARCHAR as Nullable |
This option causes the "Nullable" check box to be automatically selected for any newly-added CHAR and VARCHAR columns. This facilitates replication from DBMS systems that permit an non-NULL empty character string to Oracle and other DBMS systems that differ from the ANSI 92 standard and consider an empty string as a NULL value. Default: Disabled
|
Promote CHAR to VARCHAR |
This option eliminates the use of a fixed precision character column type in favor of a variable character type. Default: Disabled |
This option instructs the Column wizard to promote CHAR types to Unicode equivalents (WCHAR) when selecting a destination column type. See also the runtime option Bind Character Types as Wide (Unicode) on the Replication Tab.
|
|
Quote SQL Identifiers |
Certain character combinations and words are normally forbidden or have reserved meanings in SQL statements. For instance, SQL does not normally allow creating a table named TABLE or including a space in a column name. If you want to use words or character combinations that are reserved or forbidden by SQL in one or more subscriptions, make sure that the Quote SQL Identifiers check box is selected before you save the subscription. To have subscriptions comply with the regular SQL restrictions, clear the Quote SQL Identifiers check box before you create and save subscriptions. Default: Enabled Service Properties Subscription Wizard Tab only |
This option controls the behavior of the Subscription Wizard when creating new subscriptions or when editing the properties of an existing subscription. SQDR will fold the destination identifier case to match the preferred case of the destination DBMS, when that DBMS specifies either SQL_IC_UPPER or SQL_IC_LOWER. In other cases (SQL_IC_MIXED or SQL_IC_SENSITIVE), no folding ever occurs. Disabling the Fold option will preserve the case of the identifiers on the destination as specified on the source table in all cases. Enabling the Fold option preserves the customary behavior. This setting does not change existing subscriptions unless a column rebuild occurs. This option appears in several places:
For instance, unchecking Fold Identifiers permits a subscription from a lower case source such as informix to create objects with lower case identifiers on a destination whose preferred case is Upper (e.g. Exasol). Note that Quoted Identifiers must also be enabled in such as case (default behavior).
|
|
Map NUMERIC(p,0) to INTEGER types |
Select this option to map decimal(p,0) or numeric(p,0) to binary equivalents (int, bigint), which are typically more efficient in terms of storage and processing. Large precisions (>9) are mapped to BIGINT and very large precisions (> 18) are not converted. Examples: DECIMAL(9,0)->INTEGER DECIMAL(10,0)->BIGINT NUMERIC(19,0)->NUMERIC(19,0) Note: this may not be desirable when mapping to a destination such as Db2 that has natural support for DECIMAL and NUMERIC. Default: Disabled |
Enable deprecated types |
Many ODBC drivers report support for deprecated data types. For example, Microsoft SQL Server reports a type of datetime which has significant restrictions (range and precision) as compared to the newer type datetime2. Similarly, text, ntext and image have been replaced by varchar(max), nvarchar(max) and varbinary(max). To avoid conversion issues, using the modern data types is recommended. The default column mapping behavior of the SQDR subscription wizard is to display only the newer data types. Currently, the cloaking of deprecated types is implemented for SQL Server only. Select this checkbox to allow mapping to the deprecated types. Default: Disabled Service Properties Subscription Wizard Tab only
|