Schema reconciliation messages in the job log

When a job with a Dynamic RDBMS stage is converted to the job with a DRS Connector stage, the new job can report schema reconciliation messages (of warning or informational severity) which have not been reported in the original job.

This will typically be the case for DRS Connector stages configured for the IBM® DB2® or ODBC database types. For the DRS Connector stages configured for Oracle database type, the schema reconciliation messages will less likely be reported because in this case the DRS Connector will defer most of the schema reconciliation logic to the Oracle database.

A DRS Connector stage configured for DB2 or ODBC database type will report schema reconciliation messages if it determines that the definition of the columns on the link does not match the definition of the corresponding columns in the database table.

There are generally three approaches for eliminating the schema reconciliation messages:

Modify column definitions on the link to match (as close as possible) the corresponding column definitions in the database table.
Modify column definitions in the database table to match (as close as possible) the corresponding column definitions on the link.
Define a message handler that demotes or suppresses the schema reconciliation messages. The message handler definition can be applied to all the jobs in the project or to an individual job.

Example 1

For example, if a parallel InfoSphere® DataStage® job contains a DRS Connector stage configured for IBM DB2 database type and the stage is used to insert data to a DB2 database table, the input link of the stage contains the following columns:

Table 1. Example 1: Input link contains the following columns
Name	Type	Length	Scale	Extended
C1	Decimal	8	4	(not set)
C2	NVarChar	10	(not set)	(not set)
C3	VarChar	10	(not set)	(not set)
C4	Float	(not set)	(not set)	(not set)
C5	Timestamp	(not set)	(not set)	Microseconds

The target table in the database contains the following columns:

Table 2. Example 1: Target table contains the following columns
Name	Type	Length	Scale
C1	DECIMAL	6	2
C2	VARCHAR	10	(n/a)
C3	VARCHAR	5	(n/a)
C4	INTEGER	(n/a)	(n/a)
C5	TIMESTAMP	(n/a)	3

The job runs and the following messages are reported in the job log:

#1
Type: Warning
Message Id: IIS-CONN-DAAPI-00398
Message: DRS_Connector_1: Schema reconciliation detected a
size mismatch for column C1. When writing column DECIMAL(8,4) into
database column DECIMAL(6,2), truncation, loss of precision or data
corruption can occur.
#2
Type: Warning
Message Id: IIS-CONN-DAAPI-00396
Message: DRS_Connector_1: Writing the WVARCHAR column C2 into
a VARCHAR database column can cause data loss or corruption due to
character set conversions.
#3
Type: Warning
Message Id: IIS-CONN-DAAPI-00393
Message: DRS_Connector_1: The length of WVARCHAR column C2
cannot be validated because the database column is VARCHAR and character
set conversion is involved. Inadequate column lengths can lead to
data truncation or unexpected errors.
#4
Type: Warning
Message Id: IIS-CONN-DAAPI-00398
Message: DRS_Connector_1: Schema reconciliation detected a
size mismatch for column C3. When writing column VARCHAR(min=0,max=10)
into database column VARCHAR(min=0,max=5), truncation, loss of precision
or data corruption can occur.
#5
Type: Warning
Message Id: IIS-CONN-DAAPI-00398
Message: DRS_Connector_1: Schema reconciliation detected a
size mismatch for column C4. When writing column SFLOAT into database
column INT32, truncation, loss of precision or data corruption can
occur.
#6
Type: Warning
Message Id: IIS-CONN-DAAPI-00398
Message: DRS_Connector_1: Schema reconciliation detected a
size mismatch for column C5. When writing column DATETIME(fraction=6)
into database column DATETIME(fraction=3), truncation, loss of precision
or data corruption can occur.

The job log can report additional warnings or fatal errors, depending on the actual data on the link and the type of the stage on the other side of the link.

Message #1 is reported because the Decimal(8,4) column C1 on the link has larger precision and scale than the corresponding DECIMAL(6,2) column C1 in the target table. To eliminate this warning, the Length and Scale attributes for the column C1 on the link should be set to values 6 and 2 (or smaller) so that they do not exceed the precision and scale of the column C1 in the target table.

Messages #2 and #3 are reported because the Unicode column C2 on the link is used to write to a VARCHAR column in the database. To eliminate the warnings the type of column C2 on the link should be changed from NVarChar to VarChar.

Message #4 is reported because the VarChar column C3 on the link has larger length than the corresponding VARCHAR column C3 in the target table. To eliminate this warning, the Length attribute for the column C3 on the link should be set to value 5 or smaller so that it doesn't exceed the length of the column C3 in the target table.

Message #5 is reported because the Float column C4 on the link is used to write to an INTEGER column C4 in the target table. The range of allowed values for the Float InfoSphere DataStage type is larger than the range of allowed values for the INTEGER DB2 type. To eliminate this warning, the type of the column C4 on the link should be changed from Float to Integer (or to SmallInt or TinyInt).

Message #6 is reported because the Timestamp column C5 on the link has larger fractional second precision (6) than the TIMESTAMP(3) column C5 in the target table. To eliminate this warning, the Extended attribute for the column C5 on the link should cleared. The Scale attribute should be cleared as well or set to the value 3 or smaller so that it doesn't exceed the fractional second precision of the column C5 in the target table.

An alternative approach for eliminating messages #1 to #6 would be to modify column definitions in the target table so that they are in agreement with the column definitions on the link.

Yet another approach would be to define message handler for the job and demote the messages with the corresponding message identifiers from Warning severity to Informational severity. For the example used here, the message identifiers that would need to be demoted are: IIS-CONN-DAAPI-00398 (for messages #1, #4, #5 and #6), IIS-CONN-DAAPI-00396 (for message #2) and IIS-CONN-DAAPI-00393 (for message #3). Note that demoting or suppressing schema reconciliation messages reported by the DRS Connector stage will have no effect on any related warning and error messages logged by the InfoSphere DataStage framework and other stages in the job.

Example 2

In this example a parallel InfoSphere DataStage job contains a DRS Connector stage configured for DB2 database type. The stage is used to read data from a DB2 database table. The output link of the stage contains the following columns:

Table 3. Example 2: Output link contains the following columns
Name	Type	Length	Scale	Nullable	Extended
C1	Decimal	6	2	No	(n/a)
C2	VarChar	10	(n/a)	No	(not set)
C3	VarChar	3	(n/a)	No	(not set)
C4	Float	(n/a)	(n/a)	No	(n/a)
C5	Date	(n/a)	(n/a)	No	(n/a)

The source table in the database contains the following columns:

Table 4. Example 2: Source table contains the following columns
Name	Type	Length	Scale	Nullable
C1	DECIMAL	8	4	No
C2	VARGRAPHIC	10	(n/a)	No
C3	VARCHAR	5	(n/a)	Yes
C4	FLOAT	(n/a)	(n/a)	No
C5	TIMESTAMP	(n/a)	3	No

The job runs and the following messages are reported in the job log:

#1
Type: Warning
Message Id: IIS-CONN-DAAPI-00399
Message: DRS_Connector_0: Schema reconciliation detected a size
mismatch for column C1. When reading database column DECIMAL(8,4)
into column DECIMAL(6,2), truncation, loss of precision or data corruption
can occur.
#2
Type: Warning
Message Id: IIS-CONN-DAAPI-00397
Message: DRS_Connector_0: Reading the WVARCHAR database column
C2 into a VARCHAR column can cause data loss or corruption due to
character set conversions.
#3
Type: Warning 
Message Id: IIS-CONN-DAAPI-00393
Message: DRS_Connector_0: The length of VARCHAR column C2 cannot
be validated because the database column is WVARCHAR and character
set conversion is involved. Inadequate column lengths can lead to
data truncation or unexpected errors.
#4
Type: Warning
Message Id: IIS-CONN-DAAPI-00399
Message: DRS_Connector_0: Schema reconciliation detected a size
mismatch for column C3. When reading database column VARCHAR(min=0,max=5)
into column VARCHAR(min=0,max=3), truncation, loss of precision or
data corruption can occur.
#5
Type: Warning
Message Id: IIS-CONN-DAAPI-00399
Message: DRS_Connector_0: Schema reconciliation detected a size
mismatch for column C4. When reading database column DFLOAT into column
SFLOAT, truncation, loss of precision or data corruption can occur.
#6
Type: Warning
Message Id: IIS-CONN-DAAPI-00399
Message: DRS_Connector_0: Schema reconciliation detected a size
mismatch for column C5. When reading database column DATETIME(fraction=3)
into column DATE, truncation, loss of precision or data corruption
can occur.
#7
Type: Info
Message Id: IIS-CONN-DAAPI-00057
Message: DRS_Connector_0: Modified field: C5, attribute: IS_NULLABLE.
Design-time value: 0. External value: 1

The message #1 is reported because the DECIMAL(8,4) column C1 in the database has larger precision and scale than the corresponding Decimal(6,2) column C1 on the link. To eliminate this warning, the Length and Scale attributes for the column C1 on the link should be set to values 8 and 4 (or larger) so that they are not smaller than the precision and scale of the column C1 in the source table.

The messages #2 and #3 are reported because the VarChar column C2 on the link is used to read data from the VARGRAPHIC column C2 in the database. To eliminate the warnings the type of the column C2 on the link should be changed from VarChar to NVarChar (or Extended attribute for this column should be set to value Unicode).

The message #4 is reported because the VARCHAR(5) column C3 in the database has larger length than the corresponding VarChar(3) column C3 on the link. To eliminate this warning, the Length attribute for the column C3 on the link should be set to value 5 (or larger) so that it is not smaller than the length of the column C3 in the source table.

The message #5 is reported because the Float column C4 on the link is used to read data from the FLOAT column C4 in the target table. The range of allowed values for the FLOAT DB2 type is larger than the range of allowed values for the Float InfoSphere DataStage type. To eliminate this warning, the type of the column C4 on the link should be changed from Float to Double.

The message #6 is reported because the Date column C5 on the link does not support hours, minutes, seconds and fractional seconds and this column is used to read data from the TIMESTAMP(3) column C5 in the source table. To eliminate this warning, the type of the column C5 on the link should changed from Date to Timestamp. Also the Scale attribute should be set to value 3 or larger or the Extended attribute should be set to Microseconds. That way the fractional second precision for the column C5 on the link will not be smaller than the fractional second precision for the column C5 in the source table.

The message #7 is of informational severity and is reported because the column C5 in the source table supports NULL values, and the column C5 on the link does not. To eliminate this message, it is necessary to change the Nullable attribute for column C5 on the link from No to Yes.

An alternative approach for eliminating messages #1 to #7 would be to modify column definitions in the source table so that they are in agreement with the column definitions on the link.

Yet another approach would be to define message handler for the job and demote the messages with the corresponding message identifiers from Warning severity to Informational severity. For the example used here, the message identifiers that would need to be demoted are: IIS-CONN-DAAPI-00399 (messages #1, #4, #5 and #6), IIS-CONN-DAAPI-00397 (message #2) and IIS-CONN-DAAPI-00393 (message #3). The message #7 with message identifier IIS-CONN-DAAPI-00057 is already at the Informational severity so the message handler cannot be configured to further demote it. However it can be configured to suppress this message from the log. Note that demoting or suppressing schema reconciliation messages reported by the DRS Connector stage will have no effect on any related warning and error messages logged by the InfoSphere DataStage framework and other stages in the job.