Db2

Hi,

we are moving many workflow from DB2 iSeries to DB2 LUW (with BLU) and we have some queries that dont finish on DB2 LUW

We have noticed that DB2 BLU some time is not able to execute complex queries with many subqueries that include DISTINCT, query take hours without finishing or fill all the temps TBS. 

changing the DISTINCT by a GROUP BY solve the probleme.

is distinct and group by produce differents execution plan ?

are there any special setup (db2set) so that DB2 execute distinct like a group by.

When queries with multiple subqueries and DISTINCT clauses cause performance issues in Db2 for z/OS, it often points to inefficiencies in how the query is being executed or how the underlying database is structured. Here's an approach to diagnose and address the problem:

1. Understand the Query Plan

• Use EXPLAIN to generate the access path for the query. It shows how Db2 plans to retrieve the data.
• Check for:
  • Excessive table scans.
  • Nested loop joins with large datasets.
  • High-cost operations like sorting due to DISTINCT.

2. Optimize Subqueries

• Review subqueries to ensure they aren't repeating unnecessary calculations.
• Where possible, replace correlated subqueries with joins, as joins are often more efficient.

3. Index Optimization

• Ensure appropriate indexes exist for columns involved in DISTINCT, WHERE, and JOIN clauses.
• Use clustered indexes if sorting is frequently needed.

4. Reduce Intermediate Results

• Use common table expressions (CTEs) or temporary tables to break down the query into smaller, manageable parts.
• Materialize intermediate results that can be reused to avoid recomputation.

5. Memory and Temp Tablespace Considerations

• Ensure your temporary tablespaces are adequately sized for the workload. Review TEMP tablespace usage and resize if necessary.
• Adjust sort and bufferpool settings to optimize memory usage for large queries.

6. Refactor the Query

• Sometimes, rewriting the query can significantly improve performance. Simplify where possible:
  • Minimize the use of DISTINCT if it's redundant due to primary keys or unique indexes.
  • Aggregate early (e.g., use GROUP BY instead of DISTINCT in subqueries).

7. Parallelism and Workload Management

• Db2 can process queries in parallel. Ensure the system is configured to take advantage of this feature if the workload justifies it.
• Use workload management (WLM) to allocate sufficient resources to your query without impacting other processes.

Example Optimization Scenario

Imagine a query:

SELECT DISTINCT a.column1, b.column2FROM table_a aJOIN table_b b ON a.key = b.keyWHERE a.condition = 'X'  AND b.condition = 'Y';

Optimizations:

• Ensure indexes exist on table_a.key, table_b.key, a.condition, and b.condition.
• Use EXPLAIN to verify Db2 is using indexes.
• Break the query:
  1. Extract distinct values into a temp table.
  2. Join on the reduced dataset.

Tools and References

• Use Db2 Optimizer guides and query tuning reference materials
• Employ batch job tools or utilities like JCL for managing large query executions
• Use ISPF for interactive query and dataset management
• Subqueries in Db2: IBM Documentation on Subqueries
• Using the DISTINCT Keyword: Db2 Tutorial on DISTINCT
• Writing Efficient Subqueries: IBM Documentation on Efficient Subqueries
• Index Optimization: IBM Community Blog on Optimizing Query Performance
• Query Tuning for Optimal Performance: IBM Documentation on Query Tuning

Would you like specific examples or assistance with a particular part of the query?

Hi,

we are moving many workflow from DB2 iSeries to DB2 LUW (with BLU) and we have some queries that dont finish on DB2 LUW

We have noticed that DB2 BLU some time is not able to execute complex queries with many subqueries that include DISTINCT, query take hours without finishing or fill all the temps TBS. 

changing the DISTINCT by a GROUP BY solve the probleme.

is distinct and group by produce differents execution plan ?

are there any special setup (db2set) so that DB2 execute distinct like a group by.

Hi Raydo,

Thank you for your detailed response, here some more informations:

• we are in a dataware use case
• query are generated by our ETL (OTIC, ex-GENIO )
• all tables are column organized
• there is no index
• there is only HSJOIN in the execution plan
• the CTQ operator is always on the top of the plan (there is no function or expression that force the exit from the CDE engine

that's the objectif of my question, i want to know how the CDE engine of DB2 deal with DISTINCT / GROUP BY, what are the difference, may be there is some early filtring with GROUP BY, ....

PS: we have huge queries with some time 5-10 nested datasourcesn INNER JOIN and LEFT JOIN between subqueries

Malek,

When queries with multiple subqueries and DISTINCT clauses cause performance issues in Db2 for z/OS, it often points to inefficiencies in how the query is being executed or how the underlying database is structured. Here's an approach to diagnose and address the problem:

1. Understand the Query Plan

• Use EXPLAIN to generate the access path for the query. It shows how Db2 plans to retrieve the data.
• Check for:
  • Excessive table scans.
  • Nested loop joins with large datasets.
  • High-cost operations like sorting due to DISTINCT.

2. Optimize Subqueries

• Review subqueries to ensure they aren't repeating unnecessary calculations.
• Where possible, replace correlated subqueries with joins, as joins are often more efficient.

3. Index Optimization

• Ensure appropriate indexes exist for columns involved in DISTINCT, WHERE, and JOIN clauses.
• Use clustered indexes if sorting is frequently needed.

4. Reduce Intermediate Results

• Use common table expressions (CTEs) or temporary tables to break down the query into smaller, manageable parts.
• Materialize intermediate results that can be reused to avoid recomputation.

5. Memory and Temp Tablespace Considerations

• Ensure your temporary tablespaces are adequately sized for the workload. Review TEMP tablespace usage and resize if necessary.
• Adjust sort and bufferpool settings to optimize memory usage for large queries.

6. Refactor the Query

• Sometimes, rewriting the query can significantly improve performance. Simplify where possible:
  • Minimize the use of DISTINCT if it's redundant due to primary keys or unique indexes.
  • Aggregate early (e.g., use GROUP BY instead of DISTINCT in subqueries).

7. Parallelism and Workload Management

• Db2 can process queries in parallel. Ensure the system is configured to take advantage of this feature if the workload justifies it.
• Use workload management (WLM) to allocate sufficient resources to your query without impacting other processes.

Example Optimization Scenario

Imagine a query:

SELECT DISTINCT a.column1, b.column2FROM table_a aJOIN table_b b ON a.key = b.keyWHERE a.condition = 'X'  AND b.condition = 'Y';

Optimizations:

• Ensure indexes exist on table_a.key, table_b.key, a.condition, and b.condition.
• Use EXPLAIN to verify Db2 is using indexes.
• Break the query:
  1. Extract distinct values into a temp table.
  2. Join on the reduced dataset.

Tools and References

• Use Db2 Optimizer guides and query tuning reference materials
• Employ batch job tools or utilities like JCL for managing large query executions
• Use ISPF for interactive query and dataset management
• Subqueries in Db2: IBM Documentation on Subqueries
• Using the DISTINCT Keyword: Db2 Tutorial on DISTINCT
• Writing Efficient Subqueries: IBM Documentation on Efficient Subqueries
• Index Optimization: IBM Community Blog on Optimizing Query Performance
• Query Tuning for Optimal Performance: IBM Documentation on Query Tuning

Would you like specific examples or assistance with a particular part of the query?

Hi,

we are moving many workflow from DB2 iSeries to DB2 LUW (with BLU) and we have some queries that dont finish on DB2 LUW

We have noticed that DB2 BLU some time is not able to execute complex queries with many subqueries that include DISTINCT, query take hours without finishing or fill all the temps TBS. 

changing the DISTINCT by a GROUP BY solve the probleme.

is distinct and group by produce differents execution plan ?

are there any special setup (db2set) so that DB2 execute distinct like a group by.

Hi,

we are moving many workflow from DB2 iSeries to DB2 LUW (with BLU) and we have some queries that dont finish on DB2 LUW

We have noticed that DB2 BLU some time is not able to execute complex queries with many subqueries that include DISTINCT, query take hours without finishing or fill all the temps TBS. 

changing the DISTINCT by a GROUP BY solve the probleme.

is distinct and group by produce differents execution plan ?

are there any special setup (db2set) so that DB2 execute distinct like a group by.

Your issue is related to resources.  You mentioned you moved from the "I"  System( mid range computer) to DB2 luw.

Your DB2 Luw system will need to be beef up with more CPU, Memory, etc to expect the same performance as a I system.    You may even need to use DPF.   

Also double check your predicates for the query.   Some that may have been Stage 1 on the I System may now be Stage 2 on Luw.   

Also DISTINCT with Order by on Luw may force a double sort where it did not on I.  

Hi,

we are moving many workflow from DB2 iSeries to DB2 LUW (with BLU) and we have some queries that dont finish on DB2 LUW

We have noticed that DB2 BLU some time is not able to execute complex queries with many subqueries that include DISTINCT, query take hours without finishing or fill all the temps TBS. 

changing the DISTINCT by a GROUP BY solve the probleme.

is distinct and group by produce differents execution plan ?

are there any special setup (db2set) so that DB2 execute distinct like a group by.

Hi,

we are moving many workflow from DB2 iSeries to DB2 LUW (with BLU) and we have some queries that dont finish on DB2 LUW

We have noticed that DB2 BLU some time is not able to execute complex queries with many subqueries that include DISTINCT, query take hours without finishing or fill all the temps TBS. 

changing the DISTINCT by a GROUP BY solve the probleme.

is distinct and group by produce differents execution plan ?

are there any special setup (db2set) so that DB2 execute distinct like a group by.