Solution : https://service.sap.com/sap/support/notes/1292796 (SAP Service marketplace login required)
Summary :
This SAP Note addresses specific techniques for enhancing the execution efficiency of dynamic OPEN SQL statements in DB2 environments where standard DB2 Optimization Guidelines (such as those referred in Note 868888) are ineffective due to statement variability (e.g., dynamic WHERE clauses or table names). It recommends the use of indirect hints provided to the SAP database interface to guide the DB2 Optimizer, including "OPT_FOR_ROWS N" to influence row optimization, and "USE_OPTLEVEL X" for specifying optimization levels. Advanced substitutions and Common Table Expressions are introduced for handling literals and 'FOR ALL ENTRIES' statements respectively, applicable in specific SAP Kernel releases. Important stipulations include ensuring compliance with correct syntax, upper case usage, and limitations for standard code application.
Key words :
sap profile parameter dbs/db6/dbsl_substitute_literals=1, werks = itab-werks %_hints db6 'convert_fae_to_cte', werks = '1100' %_hints db6 'use_optlevel 0' db6 'opt_for_rows 65', werks = '1100' %_hints db6 '&substitute literals&', db2 common table expression enables, werks = '1100' %_hints db6 'opt_for_rows 15', werks = '1100' %_hints db6 'use_optlevel 0', solve customer-specific problems, correct upper case characters, creating suitable secondary indexes
Related Notes :
| 1294443 | DB6: OPTGUIDELINES on UNION ALL statements |
| 1277235 | DB6: SUBSITUTE_VALUES causes wrong estimated statement size |
| 1237705 | FPY1: Performance problem in event module 0660 for DB6 |
| 1143736 | DB6: User-Exit for Datenbank-Hints in SAP BI 7.x |
| 1063857 | Performance improvement module FKK_COMPLETE_ACCOUNT_READ |
| 872397 | DB6: User exit for database hints |
| 868888 | DB6: Optimization Guidelines |
| 811852 | Substitute_Literals to improve access to LTAP |
| 575672 | DB6: Inverted comma in literals |
| 426036 | Long runtimes / no end for total reports with DB6 |
| 150037 | Database hints in Open SQL for DB6 (DB2 for LUW) |