Changes to data type mapping in release 2.3.17

In order to be able to compare data types across all data sources, Data Virtuality Server maps the data source's native data types to a common set of logical data types. This is commonly referred to as data type mapping. In our release 2.3.17, we introduced changes to this mapping for numeric data types.

This article describes the impact of these changes and how to deal with them when upgrading to version 2.3.17 or later.

Failing Replications

A replication fails after the upgrade because data types in the source and target are no longer aligned. In this example, we are looking at a batch replication:

exec SYSADMIN.BatchUpdate('"dwh.target_table_data_types"', '"snow.DATA_TYPE_TEST"', NULL);;

Which fails with this exception:

[...]
Caused by: org.teiid.core.TeiidProcessingException: TEIID30128 Cannot convert insert query expression projected symbol 'snow.DATA_TYPE_TEST.colbyte' of type java.math.BigInteger to insert column 'dwh.target_table_data_types.colbyte' of type java.lang.Short
at org.teiid.query.resolver.command.InsertResolver.resolveTypes(InsertResolver.java:202)
at org.teiid.query.resolver.command.InsertResolver.resolveProceduralCommand(InsertResolver.java:125)
at org.teiid.query.resolver.ProcedureContainerResolver.resolveCommand(ProcedureContainerResolver.java:146)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:284)
at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:125)
at org.teiid.query.processor.proc.ExecDynamicSqlInstruction.process(ExecDynamicSqlInstruction.java:250)
... 22 more

In order to re-align the source and target tables, please run this statement with the corresponding table names. It will issue ALTER COLUMN statements against the target table to again align it with the source table: 

CALL "SYSADMIN.getReplicatedTableDiff"(
  "targetTable" => '"dwh.target_table_data_types"',
  "sourceTable" => '"snow.DATA_TYPE_TEST"',
);;

Virtual Views with static type definitions

Virtual views can contain static type definitions, like in this example:

CREATE VIEW views.data_type_tests(
  colbyte BYTE,
  colshort SHORT,
  colinteger INTEGER,
  collong LONG,
  colfloat FLOAT,
  coldouble DOUBLE,
  colbigdecimal BIGDECIMAL(2147483647, 2147483647),
  coltime TIME,
  colate DATE,
  colchar CHAR(1),
  colstringlimited STRING(10),
  colstring STRING(4000),
  colbool BOOLEAN
)
AS
SELECT "colbyte", "colshort", "colinteger", "collong", "colfloat", "coldouble", "colbigdecimal", "coltime", "coldate", "colchar", "colstringlimited", "colstring", "colbool"
FROM "snow.DATA_TYPE_TEST"

After an upgrade, a view can be in a failed state with an error message similar to this one:

The definition for views.data_type_tests has the wrong type for column 1. Expected byte, but was biginteger.

Please update your view definition accordingly until all type errors are resolved, e.g. in the above example:

CREATE VIEW views.data_type_tests(
  colbyte BIGINTEGER,
[...]

Virtual Procedures with return values

Similar to the example above with views, you may have procedures that read from a data source and specify return data types:

CREATE VIRTUAL PROCEDURE views.simple_procedure() RETURNS(
  colbyte BYTE,
  colshort SHORT,
  colinteger INTEGER,
  collong LONG,
  colfloat FLOAT,
  coldouble DOUBLE,
  colbigdecimal BIGDECIMAL(2147483647, 2147483647),
  coltime TIME,
  colate DATE,
  colchar CHAR(1),
  colstringlimited STRING(10),
  colstring STRING(4000),
  colbool BOOLEAN
) AS
BEGIN
  SELECT "colbyte", "colshort", "colinteger", "collong", "colfloat", "coldouble", "colbigdecimal", "coltime", "coldate", "colchar", "colstringlimited", "colstring", "colbool"
  FROM "snow.DATA_TYPE_TEST";
END

After an upgrade, a procedure can be in a failed state with an error message similar to this one:

The expected result set of the procedure views.simple_procedure does not match the result set from returnable statement SELECT snow.DATA_TYPE_TEST.colbyte, snow.DATA_TYPE_TEST.colshort, [...] FROM snow.DATA_TYPE_TEST; use WITHOUT RETURN to indicate the statement should not be returned - The definition for views.simple_procedure has the wrong type for column 1. Expected byte, but was biginteger.

Please update your procedure's return data types accordingly until all type errors are resolved, e.g. in the above example:

CREATE VIRTUAL PROCEDURE views.simple_procedure() RETURNS(
  colbyte BIGINTEGER,
[...]