IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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:                                              *
    * The problem exists in all platforms and affects all data     *
    * sources except Informix and Teradata.                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * 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.)                                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version v9.5.0.5                              *
    ****************************************************************
    

Problem conclusion

  • 1. create default function mappings  for labeled durations
    2. add wrapper option "db2_labeled_duration_style"
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR31669

  • Reported component name

    MSSQL WRAPPER

  • Reported component ID

    5724N9705

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-01-13

  • Closed date

    2009-12-21

  • Last modified date

    2009-12-21

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

    JR31657

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

Fix information

  • Fixed component name

    MSSQL WRAPPER

  • Fixed component ID

    5724N9705

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPL","label":"Data Sources and Wrappers - Microsoft SQL Server"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
21 December 2009