|
Problem IT39055
|
Status: Closed |
FEDERATION CREATE SERVER TYPE OPTIMIZATION FOR ODBC WRAPPER
|
| product: |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 |
| 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 |
| 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?. *
**************************************************************** |
| Local Fix: |
NA |
| Solution |
|
| Workaround |
****************************************************************
* 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?. *
**************************************************************** |
| Comment |
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. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 12.11.2021
17.04.2022
17.04.2022 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
