SAP Basis Tech Supports

Symptom
This note will list all known SQL statements that have a bad performance despite of or due to the REOPT(ONCE) feature of DB2 V8 and higher.

Reason and Prerequisites
There are 3 categories of problems:
1. REOPT(ONCE) does not find the optimal access path because not enough
frequencies (value distributions) are available.
2. REOPT(ONCE) does not find an optimal access path because the first
set of values used were not typical. For example the client was not
the.productive client etc.
3. DB2 optimizer problems.
Solution
1. Run a RUNSTATS with COLGROUP .. FREQVAL to gather the distribution
statistics.
Attention:
In UNICODE systems please ensure that IBM APAR PK59535 is applied as
otherwise RUNSTATS will generate incorrect SYSCOLDIST entries.
2. If only 1 ABAP is affected SAP DB2 Development Support can provide a
CTE hint that enforces the correct index or join sequence
If several ABAPs are affected the table attribute VOLATILE will be
used where possible.
The CTE hints to specify a specific index or join sequence should be used at the moment only after consulting SAP DB2 Development Support as this is a rather new feature which needs careful implementation.
If a new problem not documented here is encountered please open an SAP problem message on compoenent BC-DB-DB2 with reference to this note.
Known cases:
1 .Table: EDIDC
Problem category 2
ABAP: Function module ALE_CCMS_EVALUATE_GRP
This table is first accessed in client 000 before in the productive
client so DB2 might choose a wrong index
Solution: Note 989551 which is at the moment in pilot release status
Please contact SAP DB2 Development Support.
2. Table: SRRELROLES
Problem category 2
First access might be in client 000 so DB2 might choose the wrong
index.
Solution: Declare SRRELROLES as VOLATILE ( A CTE hint would also be
possible but several ABAPs will be affected. If a CTE hint is
preferred please contact SAP DB2 Development Support
3. Table: AGR_USERS
Problem category 2
ABAP: FM NAVIGATION_SELECT_AGRS_OF_USER
Solution: Declare AGR_USERS as VOLATILE or request a CTE hint
4. Table: VEPO
Problem category 1
ABAP: Function Module V51S_FIND_HU_ITEMS_DB
Solution: RUNSTATS TABLESPACE xxxxxx.VEPO
TABLE(SAPR3.”VEPO”)
COLGROUP(GENAPPT_ID)
FREQVAL COUNT 10 MOST
5. Table: SWWWIHEAD
Problem category 1
Solution: RUNSTATS TABLESPACE xxxxxx.SWWWIHEA
TABLE(SAPR3.”SWWWIHEAD”)
COLGROUP(WI_STAT)
FREQVAL COUNT 10 MOST
6. Table: SCAPPT
Problem category 1
Solution: RUNSTATS TABLESPACE xxxxxxx.SCAPPT
TABLE(SAPR3.SCAPPT)
COLGROUP(GENAPPT_ID)
FREQVAL COUNT 10 MOST
7. Table: HRP1001
ABAP: Function Module RH_READ_INFTY_1001_EXT
Problem category 2
First access to table HRP1001 might be in client 000 so DB2 might
choose a wrong access path
Solution: Note 1033877
8. Table: BDCPS
ABAP: RBDCPCLR
Problem category 2
Solution: Note 1014725 which is at the moment in pilot release
status. Please contact SAP DB2 Development Support.
9. Table: TOA01, TOA02
ABAP: CL_ALINK_CONNECTION
Problem category 2
Solution: Note 1066971.
10. Table: HRP1001
ABAP: LRHASF00
Problem category 2
Solution: Note 1075157 which is at the moment in pilot release
status. Please contact SAP DB2 Development Support.
11. Table: BCA_ORDER
ABAP: Function module BCA_DB_ORDER_SEL_MULT_REFOBJ
Problem category 2
Solution: Note 1082244 which is at the moment in pilot release
status. Please contact SAP DB2 Development Support.
12. View: VALLC
ABAP: Function module CO_BH_SUBNET_PRE_READ
Problem category 3.
Problem is under investigation by IBM. Circumvention documented in note 1090280.
13. Table: PA0105
Method: CL_HR_EXT_RELAT_A208 DO_SELECTION:
Problem category 2
Solution: Note 1116077.
14. Table: TBTCO
ABAP: LBTCHF19
Problem category 2
Solution: Note 1130954.
15. Table: PA0105
Function module: HR_GET_EMPLOYEES_FROM_USER_DB
Problem category 2
Solution: Note 1253957.
16. Table:FAGLFLEXA
Function module: FAGL_GET_LEDGER_DOCNR
Problem category 2
Solution: Note 1257587.
17. Tables NBEW , NFAL
ABAP: LNAUDF01 FORM: COUNT_PATIENTS
Problem category 2
Solution: Note 1316259.
18. Table: PA0105
ABAP: RHUSERXR
Problem category 2
Solution: Note 1169477