DB2 - Problem description
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) |