DB2 Performance Tuning improve SQL query performance in SAP applications (1)

The following articles mainly show you how to improve SQL query performance in SAP applications due to DB2 performance optimization, if you are interested in improving SQL query performance in the SAP application of DB2 performance optimization, you can click to view the following articles.

SAP provides the following interfaces to communicate with databases:

Application write to ABAP

SAP Open SQL

Natural SQL is connected to the ABAP database through the ADBC Interface)

Natural SQL, starting with exec SQL statement and ending with ENDEXEC statement

JDBC for SAP Java applications

The sap abap application mainly uses SAP Open SQL to access the database. SAP Open SQL is composed of a group of ABAP statements that perform database operations. Developed to provide a common interface for all relational database platforms supported by SAP. The SAP Open SQL statement is converted into a natural SQL statement in the SAP database interface.

SAP NetWeaver BI uses the ADBC released by sap bi. The natural SQL interface ADBC is a class-based API that supports dynamic generation of SQL statements. Using adbc in the abap environment is similar to using JDBC in the Java environment.

The reports and transactions they write can use any available interface to access the database.

Use DB2 parameters in SAP Open SQL

SAP supports a batch of DB2 parameters that can be added to Open SQL statements to affect how natural SQL statements generated from Open SQL are compiled and run at the database layer. Customers can use these DB2 parameters in their own reports and transactions to improve SQL query performance.

NOTE: For the DB2 SAP parameter settings described above, you only need to use these parameters in special circumstances. They are used only when a quick solution and alternative solution are required for database DB2 performance tuning.

For Open SQL, SAP supports the following DB2 parameters:

OPT_FOR_ROWS N: If N> 0, an optimize for n rows clause is added to the SQL statement. The ABAP statement contains the up to n rows clause. optimize for n rows is automatically generated. OPT_FOR_ROWS 0 is used to prevent automatic generation of optimize for n rows.

USE_OPTLEVEL X: this parameter can be used to specify the DB2 query optimization level for statement processing. By default, SAP uses optimization level 5, which provides reasonable statement compilation and execution time in most cases. In some cases, a lower or higher optimization level may generate an access plan for a specific statement with higher DB2 performance tuning. In these cases, parameters can be used as short-term solutions or alternatives.

& Substitute literals &: for SAP kernel version 4.6, set the SAP Configuration Parameter dbs/db6/dbsl_substitute_literals to 1. This parameter makes the text in the ABAP statement text be used as the text of the SQL statement, rather than as the parameter mark ). If the text is used, the optimizer can use the distribution information in the table involving these columns. Note that this possibility exists, and no statistics on the distribution of the table are collected.

& Substitute values &: for SAP kernel version 4.6, set the SAP Configuration Parameter dbs/db6/dbsl_substitute_literals to 1. This parameter enables all input values in the ABAP statement to be written in the SQL statement text. Like & substitute literals &, the DB2 optimizer can use information about the distribution in a table. However, because the statement text changes, this statement needs to be re-prepared each time it is run.

CONVERT_FAE_TO_CTE: for sap whose kernel version is earlier than 7.0, this parameter is used to generate an SQL statement FOR the ABAP statement that controls the FOR ALL ENTIRES structure.

Use the correct SAP configuration parameters to control the generation of SQL statements for all entires Open SQL queries

For all entries (FAE) statements are a common sap abap statement structure. This parameter allows the ABAP programmer to use an ABAP internal table to connect to one or more database tables. The FAE statement always returns a unique result set. The SAP database interface deletes any duplicate data returned from the database.

For example:

 
 

  
  
SELECT … FROM <dbtable> FOR ALL ENTRIES IN <itab>   
  
  
WHERE <col> = <itab>~<col>  
 
 

SAP provides the following configuration parameters to control how many statements are converted to SQL:

Rsdb/prefer_in_itab_opt: If this parameter is set to 1 and if the FAE statement involves only one internal table column IN The WHERE clause, a natural SQL statement with an IN list is generated:

 
 

  
  
SELECT … FROM <dbtable> WHERE  
  
  
<col> IN (<itab>[1]~<col>,  
  
  
<itab>[2]~<col>, … , <itab>[N]~<col> ) 
 
 

Rsdb/prefer_join: this parameter is available in the SAP kernel version 7.0 or later. If this parameter is set to 1 and the FAE statement does not contain any connections between multiple tables, the FAE statement is implemented as a connection: SELECT

 
 

  
  
…  
  
  
FROM <dbtable> t1,  
  
  
( SELECT * FROM ( VALUES(<itab>[2]~<col>, <itab>[2]~<col>, … ,  
  
  
<itab>[N]~<col> ) )  
  
  
AS t2_tmp ( <coL> ) GROUP BY <col> )  
  
  
AS T2  
  
  
WHERE t1.<col> = t2.<col> 
 
 

Rsdb/prefer_union_all: If this parameter is set to 1, an SQL statement is generated for each value in the internal table and linked through UNION ALL: SELECT... FROM <dbtable> WHERE <col> =

 
 

  
  
<itab>[1]~<col> UNION ALL  
  
  
SELECT … FROM <dbtable> WHERE <col> = <itab>[2]~<col> UNION ALL  
  
  
…  
  
  
SELECT … FROM <dbtable> WHERE <col> = <itab>[N]~<col> 
 
 

If this parameter is set to 0, the conditions connected by OR are generated:

 
 

  
  
SELECT … FROM <dbtable> WHERE  
  
  
<col> = <itab>[1]~<col> OR  
  
  
<col> = <itab>[2]~<col> OR  
  
  
…  
  
  
<col> = <itab>[N]~<col> OR 
 
 

This parameter is not used when rsdb/prefer_join is set to 1.

If none of the preceding conversions occur, the FAE statement is converted to a natural SQL statement with OR.

The SAP configuration parameters attached below use a natural SQL statement to control the number of inputs in the internal ABAP table.

 
 

  
  
rsdb/max_blocking_factor   
  
  
rsdb/max_in_blocking_factor   
  
  
rsdb/prefer_fix_blocking   
  
  
rsdb/min_blocking_factor   
  
  
rsdb/min_in_blocking_factor  
 
 

If the internal table contains more input, some natural SQL statements are generated and the results are collected in the SAP database interface. For more details, see SAP note 48230.

The configuration parameters used by SAP to control the execution of FAE statements have a great impact on key database commands and DB2 Performance Tuning throughout the SAP system. Therefore, the default settings of these configuration parameters are overwritten only after detailed problem analysis or SAP technical support recommendations.

For Versions later than SAP kernel 7.0, the rsdb/prefer_join = 1 option is unavailable. Replace the IN list, union all statements, OR statements with OR link conditions IN the WHERE clause. The SAP parameter CONVERT_FAE_TO_CTE can be added to the abap fae statement to convert it into a DB2 Universal table expression CTE ). This improves performance because the DB2 optimizer can generate better access plans-Because duplicate values in the internal table have been deleted in the CTE.