topic Re: Why db hints doesn't work? in Application Development and Automation Discussions

Why db hints doesn't work?

BergOff — Sun, 20 Oct 2024 06:49:50 GMT

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.

Re: Why db hints doesn't work?

Sandra_Rossi — Sun, 20 Oct 2024 08:10:19 GMT

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.

Re: Why db hints doesn't work?

ShaikAzmathulla — Sun, 20 Oct 2024 08:26:34 GMT

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,

Re: Why db hints doesn't work?

BergOff — Mon, 21 Oct 2024 05:27:48 GMT

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.

Re: Why db hints doesn't work?

Sandra_Rossi — Mon, 21 Oct 2024 05:57:20 GMT

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.

Re: Why db hints doesn't work?

BergOff — Mon, 21 Oct 2024 08:27:08 GMT

Statement 'Explain' is not defined.

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

Re: Why db hints doesn't work?

Sandra_Rossi — Mon, 21 Oct 2024 12:04:41 GMT

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.

Re: Why db hints doesn't work?

RaymondGiuseppi — Mon, 21 Oct 2024 15:26:29 GMT

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

(Check SYST-DBSYS or menu system/status)

Re: Why db hints doesn't work?

Sandra_Rossi — Mon, 21 Oct 2024 16:22:07 GMT

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

Re: Why db hints doesn't work?

Sandra_Rossi — Mon, 21 Oct 2024 19:31:12 GMT

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

Re: Why db hints doesn't work?

BergOff — Tue, 22 Oct 2024 06:00:32 GMT

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