StarQuest Technical Documents

Hints for using Derived Columns

Last Update: 4 August 2021
Product: StarQuest Data Replicator
Version: SQDR 4.6 or later
Article ID: SQV00DR036

Abstract

Any subscription that replicates tables can contain derived columns. Each derived column must be based on a SQL expression that is valid for the source DBMS, as well as the DB2 for LUW staging database (if you are performing incremental replication). A valid SQL expression can be a constant, function, or any combination of source column names, constants, and functions connected by operators.

The Defining Derived Columns topic of the help file for SQDR contains several examples of using derived columns; this tech note contains additional examples and tips.

• Example: Converting a Julian Date to a DATE data type
• Additional Date/Time Conversions
  
• Hint: Avoiding Data Conversion Errors
  
• Hint: Using the coalesce() Function

Example: Converting a Julian Date to a DATE data type

In this example, a Julian date is stored on the source system in a five-digit numeric column in the format of YYDDD, where DDD represents the day of the year. This example contains legacy data that is not Y2k-compliant.

Expression:
DATE('19'||DIGITS(column-name))
or
DATE(DIGITS(DECIMAL(column-name + 1900000,7,0)))

Data Type: date

Additional Date/Time Conversions

The following conversions were developed for replicating legacy data from DB2 for i to SQL Server.

• For a column named d1 with format decimal(7,0), where the first digit represents the century (1 = 20, 0 = 19), the other six are YYMMDD. e.g. 10603312210 is converted to 2006-03-31 22:10.

case
when left(DIGITS(d1),1)=0 then timestamp_format('19' || right(DIGITS(d1),6),'YYYYMMDD')
else timestamp_format('20' || right(DIGITS(d1),6),'YYYYMMDD')
end

• For a column t1 with format decimal(6,0)

timestamp_format(t1,'HH24MISS',6)

• For a column named dt1 with format decimal(16,0)
  where the first digit represents the century (1 = 20, 0 = 19), the other 15 digits are YYMMDDHH24MISSFF3

case
when left(DIGITS(dt1),1)=0 then timestamp_format('19' || right(DIGITS(dt1),15),'YYYYMMDDHH24MISSFF3')
else timestamp_format('20' || right(DIGITS(dt1),15),'YYYYMMDDHH24MISSFF3')
end

Hint: Avoiding Data Conversion Errors

When defining derived columns, you should be aware of how both the source system and the DB2 for LUW staging database handles invalid data.

Problem: A customer using the expression in the previous example encountered a problem because some rows contained invalid data for the date column. In this case, the customer was replicating from DB2 for i to SQL Server, and the numeric column contained 0 in some of the rows. When doing a snapshot or a baseline replication, no error occurred because the DATE() function on DB2 for i returned NULL when it encountered the value 0.

However, when incremental replication was started, the customer received the following error from the staging database, because the DATE() function on DB2 for LUW behaves differently than that of DB2 for i:

ODBC message: SQLSTATE 22007, native error -181, [StarSQL][StarSQL ODBC Driver][DB2 Universal DataBase]The string representation of a datetime value is out of range.

Solution: Check the validity of the data.

CASE WHEN column-name = 0 THEN NULL(or a valid default value) ELSE MONTH(DATE(DIGITS(DECIMAL(column-name + 1900000,7,0)))) END

Hint: Using the coalesce() Function

The coalesce() function is a syntactic shortcut for the CASE expression and provides a handy method of supplying a default value in the case of a NULL value or expression on the source system.

coalesce(cast(<column-name> as char(10)), '1900-01-01')