Why db hints doesn't work?

[BergOff]

Hi, experts.
Why hints doesn't work? It's just ignored.
i've created a simple program and try to use ' %_hints db2 '&max_in_blocking_factor 100&', 
but it;s just ignored.

data : lt_tab type table of mara,
       lt_Tab2 type SORTED TABLE OF makt WITH UNIQUE key matnr spras.

select * from mara into CORRESPONDING FIELDS OF TABLE lt_tab.

select * from makt APPENDING CORRESPONDING FIELDS OF TABLE lt_tab2
  FOR ALL ENTRIES IN lt_tab
  where matnr = lt_tab-matnr
  and   spras = 'E'
  %_hints db2 '&max_in_blocking_factor 100&'.

As i understood 'FOR ALL ENTRIES' takes for 30 records and when i try to set more it's just ignored.

[Sandra_Rossi]

There's no evidence in your question that ABAP should consider your hint. Why do you think it should consider it? Are you just expecting from FAE that it would always improve the performance. That's wrong, you can't know. By default, consider that a join is better. FAE may be really slow sometimes, especially for big volumes. You should always analyze what's best for performance.

[BergOff]

I know that join is better in performance

I want to understand why hints doesn't work.

As u can see at screen. 

It goes around 30000 times for makt and insert 30 record in each of them.

I want to set more than 30.

[Sandra_Rossi]

You don't understand. Your hint may not apply. Please do what ShaikAzmathulla has suggested and show the native SQL statement generated by the kernel.

[BergOff]

 Statement 'Explain' is not defined.

Mine hint not 'may not applied' it's does not applied, and i want to understand why

[Sandra_Rossi]

Thank you, I appreciate. Now it's clear that it's using 30. You have chosen the "IN" FAE hint but your plan is neither "=" nor "IN" (for Oracle or HANA, one can see clearly either "column = A0 or column = A1 or ..." or "column IN (A0, A1, ...)"), it's a join with a "temporary table" containing A0, A1, ... I don't know which FAE hint the ABAP kernel is choosing for DB2/390. Could you try the other hint (max_blocking_factor) and share all FAE profile parameter values? (see the note 48230 for the exact list). As I said, I don't know DB2/390, so you may also contact the SAP support.

[ShaikAzmathulla]

Hi ,

You can run an EXPLAIN on your query:

sql
Copy code
EXPLAIN SELECT * FROM makt
APPENDING CORRESPONDING FIELDS OF TABLE lt_tab2
FOR ALL ENTRIES IN lt_tab
WHERE matnr = lt_tab-matnr
AND spras = 'E'
%_hints db2 '&max_in_blocking_factor 100&'.

This will provide you with details on how DB2 plans to execute your query, allowing you to see if your hint is being utilized.

Regards, 

[raymond_giuseppi]

Are you actually running on an IBM DB2/390 (db2) databse? 

(Check SYST-DBSYS or menu system/status)

[Sandra_Rossi]

That must be the issue! (DB2 for DB2/390, DB6 for DB2/UDB).

[BergOff]

Sorry for misleading.

It's db6.

The solution was to change 

%_hints db2 '&max_in_blocking_factor 100&' to 

%_hints db6 '&max_blocking_factor 100&'.

Thanks for your help

[Sandra_Rossi]

Could you please clarify if you're using DB2/UDB or DB2/390?