IBM Support

IT39055: FEDERATION CREATE SERVER TYPE OPTIMIZATION FOR ODBC WRAPPER

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • We are selecting a nickname on DB2 Federation Server pointing to
    an Aurora psql view on AWS. The query is taking a huge amount of
    time just to fetch a minimum number of rows (10 rows).
    
    The same query is running with a minimum time of less than a sec
    if we execute that directly on the AWS database.
    
    Please find some examples below:
    
    SELECT field_nm
    	FROM advgirp.vw_gcaww_trnl_clob_new where field_nm = '0024A'
    
    Query execution time => 12 s: 711 ms
    
    SELECT *
    	FROM advgirp.vw_gcaww_trnl_clob_new where field_nm = '0024A'
    
    Query execution time => 36 min: 20 s: 267 ms
    
    SELECT *
    	FROM advgirp.vw_gcaww_trnl_clob_new fetch first 10 rows only
    
    Query execution time => 41 s: 663 ms
    

Local fix

  • NA
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Db2 LUW Users                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * We are selecting a nickname on DB2 Federation Server         *
    * pointing to an Aurora psql view on AWS. The query is taking  *
    * a huge amount of time just to fetch a minimum number of rows *
    * (10 rows).                                                   *
    *                                                              *
    * The same query is running with a minimum time of less than a *
    * sec if we execute that directly on the AWS database.         *
    *                                                              *
    * Please find some examples below:                             *
    *                                                              *
    * SELECT field_nm                                              *
    *     FROM advgirp.vw_gcaww_trnl_clob_new where field_nm =     *
    * '0024A'                                                      *
    *                                                              *
    * Query execution time => 12 s: 711 ms                         *
    *                                                              *
    * SELECT *                                                     *
    *     FROM advgirp.vw_gcaww_trnl_clob_new where field_nm =     *
    * '0024A'                                                      *
    *                                                              *
    * Query execution time => 36 min: 20 s: 267 ms                 *
    *                                                              *
    * SELECT *                                                     *
    *     FROM advgirp.vw_gcaww_trnl_clob_new fetch first 10 rows  *
    * only                                                         *
    *                                                              *
    * Query execution time => 41 s: 663 ms                         *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update db2 version to db2 v11.1.4.7 and set server option    *
    * db2_maximal_pushdown ?Y?.                                    *
    ****************************************************************
    

Problem conclusion

  • When using federation query data in AWS aurora, the query
    performance might be slow as some functions could not been
    pushed down to remote aurora database.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT39055

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-11-12

  • Closed date

    2022-04-17

  • Last modified date

    2022-04-17

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022