IBM Support

JR31657: 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:
       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 number

    JR31657

  • Reported component name

    MSSQL WRAPPER

  • Reported component ID

    5724N9705

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-01-13

  • Closed date

    2009-10-07

  • Last modified date

    2009-10-07

  • 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

[{"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":"910","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
07 October 2009