SAP Note 868888 - DB6: Optimization Guidelines

Component : DB2 Universal Database for Unix / NT -

Solution : https://service.sap.com/sap/support/notes/868888 (SAP Service marketplace login required)

Summary :
In an SAP system on IBM DB2 for Linux, UNIX, and Windows, if default optimization by DB2's cost-based optimizer does not yield desired results, explicit optimization guidelines in XML format can be employed. These guidelines allow for direct influence over DB2's execution plans. Guidelines must adhere to specifications cited in IBM documentation, accessible via their Info Center. For ABAP OpenSQL and NativeSQL, guidelines are integrated into SQL through syntactical embeddings specific to each language, governed by the SAP database interface which also enforces double-quoting syntax norms for OpenSQL to ensure accurate transmission of guidelines. Systems must run on DB2 V8.2.2 or later, with "DB2_WORKLOAD=SAP" set, and include relevant SAP kernel patches. Optimization procedures differ between OpenSQL, using embedded hints, and NativeSQL, where XML guidelines are appended as comments in SQL.

Key words :
choose database fundamentals -> performance tuning ->  tuning database application performance -> query access plans -> optimizing query access plans -> optimization guidelines, tabspace%_hints db6 '<nljoin>'        db6   '<access table=''iadb6'' />'        db6   '<access table=''tadb6'' />'        db6 '</nljoin>', tabspace%_hints db6 '<nljoin>'        db6   '<ixscan table=''iadb6'' />'        db6   '<ixscan table=''tadb6'' />'        db6 '</nljoin>', tabspace%_hints db6 '<join>'        db6   '<ixscan table=''iadb6'' sap_index=''0'' />'        db6   '<access table=''tadb6'' />'        db6 '</join>', ~tabart%_hints db6 '<nljoin><ixscan table=''iadb6'' />'        db6 '<ixscan table=''tadb6'' /></nljoin>', svers  %_hints db6 '<ixscan table=''svers'' sap_index=''0'' />', svers%_hints db6 '<ixscan table=''svers'' index='', terms db6 udb luw db2udb db2luw reason, <ixscan table='table_name' sap_index='sap_index_name'/>, <join_type>    access_type outer_table    access_type inner_table  </join_type>

Related Notes :

1484907DB6: Conditional hints
1352458DB6: Substitution of table names in OPTGUIDELINES
1294443DB6: OPTGUIDELINES on UNION ALL statements
1292796DB6: Indirect hints in Open SQL (DB2 for LUW)
876321DB6: REOPT and Hints in the EXPLAIN
150037Database hints in Open SQL for DB6 (DB2 for LUW)