JR31657: FUNCTION MAPPINGS FOR LABELED DURATION CAN'T BE PUSHED DOWN

DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• The problem exists in all platforms and affects all data sources
  except Informix and Teradata.
  Usually Federation server doesn't pushdown a labeled durations
  (A labeled duration represents a specific unit of time as
  expressed by a number followed by one of the seven duration
  keywords: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or
  MICROSECONDS.) because there're no function mappings for them.
  To improve the performance, we should create function mappings
  for labeled durations. But Federation server doesn't push down
  such function mappings as the server option
  db2_labeled_duration_style is set to 'D' by default except
  Informix wrapper and Teradata wrapper. It's right for drda
  wrapper. But other wrappers can't take the advantage of function
  mappings.
  

Local fix

•   We find a workaround to pushdown for the second kind of
  predicate like
  (Q10.DT_ID = TIMESTAMP(CURRENT DATE, '00.00.00')).
   need to replace the
  <SERVER_NAME> and <remote_schema_name> to their own.
  
  Here are the SQL statements,:
  set passthru <SERVER_NAME>;
  create function
  "<remote_schema_name>".date_varchar_to_datetime(@cdate
  datetime, @ctime varchar(8))
  returns datetime
  with execute as caller
  as
  begin
  declare @ret datetime
  set @ret = convert(datetime, convert(varchar, @cdate, 102) + ' '
  +
  replace(@ctime, '.', ':') + '.000', 111)
  return @ret
  end;
  create function
  "<remote_schema_name>"."date_time_to_datetime"(@cdate
  datetime, @ctime datetime)
  returns datetime
  with execute as caller
  as
  begin
  declare @ret datetime
  set @ret = convert(datetime, convert(varchar, @cdate, 102) + ' '
  +
  replace(convert(varchar, @ctime, 108), '.', ':') + '.000', 111)
  return @ret
  end;
  set passthru reset;
  CREATE FUNCTION MAPPING MAPTIMESTAMP1 FOR
  SYSIBM.TIMESTAMP(SYSIBM.DATE,
  SYSIBM.VARCHAR()) SERVER <SERVER_NAME> OPTIONS (REMOTE_NAME
  '<remote_schema_name>.date_varchar_to_datetime');
  CREATE FUNCTION MAPPING MAPTIMESTAMP2 FOR
  SYSIBM.TIMESTAMP(SYSIBM.DATE,
  SYSIBM.TIME) SERVER <SERVER_NAME> OPTIONS (REMOTE_NAME
  '<remote_schema_name>.date_time_to_datetime');
  CREATE FUNCTION MAPPING MAPTIMESTAMP3 FOR
  SYSIBM.TIMESTAMP(SYSIBM.VARCHAR(), SYSIBM.TIME) SERVER
  <SERVER_NAME>
  OPTIONS (REMOTE_NAME
  '<remote_schema_name>.date_time_to_datetime');
  

Problem summary

• Users affected:
     Users of the DB2 for LUW Homogeneous Federation Feature or
  InfoSphere Federation Server
  Problem description and summary:
     See error description.
  

Problem conclusion

• Problem was first fixed in Version 9.1 FixPak 8 (s090823). This
  fix should be applied on the federation server.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  JR31669

Fix information

• Fixed component name

  MSSQL WRAPPER

• Fixed component ID

  5724N9705

Applicable component levels

• R910 PSN

     UP

• R911 PSN

     UP