IT39055: FEDERATION CREATE SERVER TYPE OPTIMIZATION FOR ODBC WRAPPER

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

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 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