DB2 performance optimization design DB2 configuration database is very useful!

Here we will mainly tell you the correct operation steps for designing the DB2 database for performance optimization, in practice, there are many database design and configuration options that directly affect the performance of querying the DB2 database. For more suggestions on Database Design, see the "Planning your Physical Database Design" Best Practices article.

Use constraints to improve query optimization

Consider the uniqueness of definitions, check and refer to consistency constraints. These constraints provide semantic information that allows the DB2 optimizer to override queries to evaluate connections and reduce aggregation and fetch first n rows through connections, remove unnecessary DISTINCT options and some other DB2 performance optimizations.

When an application can ensure its own relationship, Information constraints can also be used to check and reference consistency constraints. The same optimization is also possible. When updating (inserting or deleting) rows, mandatory constraints from the Database Manager may cause high system overhead, especially when updating many rows with consistency constraints. If an application has verified the information before updating a row, the use of information constraints is more effective than normal constraints.

For example, consider two tables DAILY_SALES and CUSTOMER. Each row in the CUSTOMER table has a unique CUSTOMER key value (CUST_KEY ). DAILY_SALES contains a CUST_KEY column and each row references the CUSTOMER key in a CUSTOMER table. You can create a reference consistency constraint to prevent a 1: N relationship between CUSTOMER and DAILY_SALES.

If the application needs to force this relationship, you can create an information constraint. The following query avoids the connection between CUSTOMER and DAILY_SALES, because no column is obtained from CUSTOMER, and each row from DAILY_SALES can find matching rows in CUSTOMER, therefore, the query optimizer automatically deletes the connection.

 
 

  
  
SELECT AMT_SOLD, SALE PRICE, PROD_DESC   
  
  
FROM DAILY_SALES, PRODUCT, CUSTOMER   
  
  
WHERE   
  
  
DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY AND   
  
  
DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY   
 
 

The application must execute Information constraints, otherwise the query may return incorrect results. In the preceding example, if the row exists in DAILY_SALES and the corresponding CUSTOMER key cannot be found in the CUSTOMER table, the rows returned by the preceding query may be incorrect.

Use REOPT to bind options and input variables in complex queries

In an online transaction processing (OLTP) environment, it is critical that the input variable has a good statement preparation time. In such an environment, the statement is often relatively simple and the query plan is also very easy to choose. Using different input variables to run the same statement multiple times can reuse the access fragments compiled in the dynamic statement cache, this avoids expensive SQL statement compilation overhead due to changing input values at any time.

However, input variables also cause problems for complex query loads, and their query plan selection is very complicated. Therefore, the optimizer needs more information to make a good decision. In addition, the statement Compilation Time is usually a small part of the total running time. Because BI queries do not usually repeat, it does not benefit from dynamic statement caching.

If you need to use input variables in a complex query workload, consider using the REOPT (ALWAYS) BIND option. When the input variable value is known, the reopt bind option delays statement compilation from PREPARE to OPEN or during execution. The variable value is passed to the SQL Compiler, so that the optimizer can use these benefits to calculate a more accurate selection evaluation. REOPT (ALWAYS) indicates that all execution statements should be precompiled.

REOPT (ALWAYS) can also be used for complex queries involving special registers, such as "WHERE TRANS_DATE = current date-30 DAYS ". If the input variable causes poor access Plan Selection for the OLTP workload and the REOPT (ALWAYS) option causes excessive overhead due to statement compilation, use REOPT (ONCE) for the selected query ). REOPT (ONCE) Delays statement compilation until the first data variable is bound.

Use the first input variable value to compile and optimize the SQL statement. Subsequent statements run with different values will reuse the query segment compiled based on the first input. This is a good method. If the first input variable represents the subsequent input value and the input value is unknown, different values are used for evaluation than the optimizer, it provides a better query access plan.

There are many ways to specify REOPT:

Use the reopt bind option for Embedded SQL in C/C ++ applications. This BIND option affects the performance optimization of static and dynamic SQL statements in DB2.

For the CLP package, use the REOPT binding parameter to re-bind the CLP package. For example, use the CS isolation level and reopt always to re-bind the CLP package. The detailed command is as follows:

 
 

  
  
rebind nullid.SQLC2G13 reopt always;  
 
 

For CLI applications or JDBC applications that use traditional JDBC drivers, set the REOPT keyword in db2cli. ini. The options are as follows:

2-NONE

3-ONCE

4-ALWAYS

For JDBC applications that use JCC generic drivers, use one of the following methods:

Use SQLATTR_REOPT to connect to or use statement attributes.

Use the SQL _ATTR_CURRENT_PACKAGE_SET connection or statement attribute to specify the NULLID, NULLIDR1, or NULLIDRA package set. NULLIDR1 and NULLIDRA are reserved package set names. ONCE used, reopt once and reopt always are implicitly used. These package sets need to be created in the following command: db2 bind db2clipk. bnd collection NULLIDR1;

 
 

  
  
db2 bind db2clipk.bnd collection NULLIDRA;  
 
 

Use one of the following methods for SQL PL stored procedures:

Use the SET_ROUTINE_OPTS stored procedure to set binding options for the SQL PL stored procedure created in the current session. For example, call sysproc. set_routine_opts ('reopt alway ')

Use the DB2_SQLROUTINE_PREPOPTS registry variable to set the SQL PL stored procedure options at the instance level. The value is set to use the SET_ROUTINE_OPTS stored procedure to overwrite the value specified by DB2_SQLROUTINE_PREPOPTS.

You can also use optimization configurations to set REOPT for static and dynamic statements, as shown in the following example:

 
 

  
  
<STMTPROFILE ID="REOPT example "> <STMTKEY> 
  
  
<![CDATA[select acct_no from customer where name = ? ]]> 
  
  
</STMTKEY> 
  
  
<OPTGUIDELINES> 
  
  
<REOPT VALUE='ALWAYS'/> 
  
  
</OPTGUIDELINES> 
  
  
</STMTPROFILE> 
 
 

Select the optimal optimization level for your workload

Setting the optimization level can benefit from explicitly specifying the optimization technology, especially for the following reasons:

To manage very small databases or very simple query statements

To enable memory restrictions during compilation on your database server

To reduce the query Compilation Time, such as PREPARE

Most statements can be fully optimized and reasonable resources through the use of 5th-level optimization, which is also the default query optimization level. At a given optimization level, the query compilation time and resource consumption are mainly affected by the query complexity, especially the number of connections and subqueries. However, the compilation time and resource usage are also affected by execution optimization.

Query Optimization levels 1, 2, 3, 5, and 7 are applicable for general purposes. You must reduce the query optimization time and consider level 0 only when you know that SQL statements are very simple.

Tip: to analyze a query that has been running for a long time, run db2batch on the query to find out how much time is spent on compilation and running. If compilation requires more time, reduce the optimization level. If execution requires more time, a higher optimization level will be considered.

When you select an optimization level, consider the following general principles:

Level 5

To use a level other than the default level, first try Level 1, 2, or 3. Levels 0, 1 and 2 Use greedy join enumeration algorithm.

If you have many tables and a large number of join predicates on the same column, you can use DB2 performance optimization level 1 or 2 with a focus on the Compilation Time.

Use a low optimization level (0 or 1) for queries that only run for less than one second ). For example, queries often have the following features:

Access only one or a few tables

Retrieve only one or several rows

Use a unique index

Online Transaction Processing (OLTP) transactions are a good example of this type of access

Use a higher optimization level (or 7) for statements that run for a long time (more than 30 seconds ).

Optimization Level 3 and above use dynamic programming to connect enumeration algorithms. This algorithm considers more optional plans and may lead to more compilation time than, and 2, especially after the number of tables increases.

Optimization level 9 is used only when you have special optimization requirements for a query.

Complex queries require different numbers of Optimizations to select the best access plan. For queries with the following features, consider using a higher DB2 performance optimization level,

The above content is a correct introduction. I hope you will have some gains.