Correct Application description of DB2 V9.7 statement Concentrator

The following articles mainly describe the correct use of the DB2 V9.7 statement concentrator. We all know that in the OLTP environment, a large number of SQL statements are running every second. Before the DB2 V9.7 database version, DB2 is required to compile each dynamic SQL statement.

DB2 compilation of SQL statements is a very CPU-consuming behavior. In DB2 V9.7, the statement concentrator is proposed to transform similar and unequal dynamic SQL statements so that these similar SQL statements can be shared in the Package Buffer, this greatly reduces the number of compilations to eliminate compilation overhead.

Introduction

There are two types of SQL statements in the DB2 database: dynamic SQL and static SQL. The execution plan of static SQL is determined when the bind package is sent to the database. You only need to call the execution plan for execution. The execution plan of dynamic SQL needs to be compiled during each execution, if the next execution plan is no longer in the package cache, re-compile the statement.

In the OLTP environment, a large number of SQL statements are executed every second. If these SQL statements are dynamic statements, a large amount of CPU time is required for compilation. DB2 uses the HASH algorithm to determine whether the execution plan of a dynamic SQL statement is used in the package cache. The algorithm performs HASH Based on the SQL statement text, even if only one letter has different big and lowercase letters, the HASH value may be different. If the HASH value is different, it is considered to be two different SQL statements.

DB2 considers the following two statements as different SQL statements.

 
 

  
  
select firstnme,lastname from employee where empno='000020'   
  
  
select firstnme,lastname from employee where empno='000070'  
 
 

Although the preceding two statements have only one numerical difference at the Where condition, the DB2 HASH algorithm considers this as two different SQL statements. However, DB2 generates the same execution plan for them. We use the db2expln tool to obtain the execution plan as follows. The execution plan shows that the above two SQL statements use the index PK_EMPLOYEE to first obtain the RID, and then read the specific data according to the RID.

 
 

  
  
Rows   
  
  
RETURN   
  
  
( 1)   
  
  
Cost   
  
  
I/O   
  
  
|   
  
  
1   
  
  
FETCH   
  
  
( 2)   
  
  
7.58163   
  
  
1   
  
  
/----+----\   
  
  
1 42   
  
  
IXSCAN TABLE: DB2INST1   
  
  
( 3) EMPLOYEE   
  
  
0.0165581 Q1   
  
  
0   
  
  
|   
  
  
42   
  
  
INDEX: DB2INST1   
  
  
PK_EMPLOYEE   
  
  
Q1  
 
 

Although the execution plans of the two SQL statements are the same, DB2 needs to compile both SQL statements to obtain the execution plan, consuming the CPU. In the OLTP environment where thousands of SQL statements are executed per second, the performance is greatly affected.

Enable statement Concentrator

DB2V9.7 introduces the statement concentrator function. The statement concentrator modifies dynamic SQL statements on the database server so that similar and different SQL statements can share the same execution plan. If you have started the DB2 V9.7 statement concentrator, you only need to compile the two SQL statements once.

In the online transaction processing (OLTP) system, simple statements containing different denominations may be generated repeatedly. In such workloads, the cost of re-compiling statements will greatly increase. Statement concentrator eliminates this overhead by allowing repeated use of compiled statements without considering the literal value.

By default, the statement concentrator is disabled. To enable statement Concentrator for all the dynamic statements in the database, set the stmt_conc database configuration parameter to LITERALS. However, DB2 will only Replace the first 100000 word denominations; the remaining word denominations remain unchanged, which generally meets our requirements.

 
 

  
  
db2 get db cfg for sample |grep "CON"  
 
 

Statement concentrator (STMT_CONC) = OFF

If you do not want to enable statement Concentrator for all SQL statements, but want to specify a connection to execute a statement set when dynamic SQL statements are executed, you can start the statement concentrator on the client, which needs to be in db2cli. settings in the ini configuration file:

 
 

  
  
StmtConcentrator = WITHLITERALS  
 
 

By default, the configuration of the Server determines whether the connection statement concentrator is enabled. If StmtConcentrator is set to OFF, it indicates that the concentrator of the connected DB2 V9.7 statement is disabled; If StmtConcentrator is WITHLITERALS, it indicates that the statement concentrator is started. When the statement concentrator is started, all the statements that support the centralized Server will share the execution plan. The StmtConcentrator parameter in db2cli. ini affects the SQL _ATTR_STMT_CONCENTRATOR attribute of the connection. You can also set this attribute in ODBC and JDBC programs.

We should give priority to enabling statement concentrator at the client level. First, it allows control of statement concentrator at the finest level. Second, it is the only consistent method for enabling statement concentrator throughout the DB2 product series.

If a dynamic statement is modified in a statement set, both the original statement and the modified statement are displayed in the description output. If the statement concentrator has modified the original statement text, the output of the event monitor logical monitoring element and the MON_GET_ACTIVITY_DETAILS table function will display the original statement. Only the modified statement text is displayed on other monitor interfaces.

Modify the Data configuration parameter STMT_CONC to start the DB2 V9.7 statement Concentrator for all connections.

 
 

  
  
db2 update db cfg for sample using STMT_CONC LITERALS  
 
 

Then run the following commands:

 
 

  
  
select firstnme,lastname from employee where empno='000020'   
  
  
select firstnme,lastname from employee where empno='000070'  
 
 

Use the following statement to obtain the compilation and execution of SQL statements:

 
 

  
  
db2 get snapshot for dynamic sql on sample  
 
 

Execution COUNT = 0

Compilation COUNT = 0

Worst pre-Compilation Time in milliseconds) = 0

Optimum pre-Compilation Time in milliseconds) = 0

---------------------------------- Omitted ------------------------------

Statement text = select firstnme, lastname from employee where empno = '20140901'

Execution COUNT = 0

Compilation COUNT = 0

Worst pre-Compilation Time in milliseconds) = 0

Optimum pre-Compilation Time in milliseconds) = 0

---------------------------------- Omitted ------------------------------

Statement text = select firstnme, lastname from employee where empno = '20140901'

Executions = 2

Compilation COUNT = 1

Worst pre-Compilation Time in milliseconds) = 218

Optimal pre-Compilation Time in milliseconds) = 218

---------------------------------- Omitted ------------------------------

Statement text = select firstnme, lastname from employee where empno =: L0

We can see that the compilation times, execution times, and Compilation Time of the two original SQL statements are both 0. At the same time, the number of statements identified by the ": L0" parameter is 1, and the number of executions is 2, the Compilation Time is 218 milliseconds. The above content is an introduction to the DB2 V9.7 statement concentrator, and I hope you will get something.

For details, visit:

Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0907luohq3/