"For All Entries IN" causes short dump when reading from HANA external view

[rafal_matuszewski2]

Hi Experts,

in a nutshell:

In ABAP report I use "For All Entries IN" to read data from HANA external view, and FAE causes short dump with DBSQL_SQL_ERROR exception. I have to use ABAP hint: %_HINTS HDB '&max_in_blocking_factor 5&'. Why?

Details:

In HANA I created several calculated view. I exposed these views using external views which I'm consuming in my ABAP report.

Everything was working as I expected until yesterday. From yesterday my report finishes with ABAP short dump like this:

Category           
Installation Errors


Runtime Errors     
DBSQL_SQL_ERROR


Except.            
CX_SY_OPEN_SQL_DB


Date and Time      
28.10.2015 09:07:44






 Short Text



 
SQL error "SQL code: 2048" occurred while accessing table "Z079FI_CURR_PO2".






 What happened?



 
Database error text: "SQL message: column store error: search table error:


  
[2999] general error (no further information available);Failed to translate


  
queryEntries for node CLTTABPROJ_Z079FI_CURR_IZ079FI_CURR_I_ISUB_CAV, reason:


  
general error (no further information available"

As far as I know nothing was changed in the database.

In HANA Studio all my views are consistent and Data Preview is returning values.

Even in SAP I can see data from z079fi_curr_po2 in SE16N without problems!

In the ABAP report my SQL statement looks like this:

SELECT *
       FROM z079fi_curr_po2
       APPENDING CORRESPONDING FIELDS OF TABLE lt_tra_isub
       FOR ALL ENTRIES IN ct_data_tra
       WHERE trans_num = ct_data_tra-trans_num.

As you see I use "For All Entries In".

Because short dump gives no information about the cause of error, I started to modify the SQL statement.

Using trial and error method I got the answer. The FAE is the reason of the dump. \

I found an ABAP hint for HANA: &max_in_blocking_factor n& which limits the length of IN lists in the context of FOR ALL ENTRIES to <n>:

SELECT *
       FROM z079fi_curr_po2
       APPENDING CORRESPONDING FIELDS OF TABLE lt_tra_isub
       FOR ALL ENTRIES IN ct_data_tra
       WHERE trans_num = ct_data_tra-trans_num
       %_HINTS HDB '&max_in_blocking_factor 5&'.

Setting the <n> factor to 5 resolved the problem.

Could you please tell my what is wrong or what to check? HANA memory problems or what?

Best regards

Rafal

[jasmin_gruschke]

Hi Rafal,
did you check the consistency of the external view and the corresponding DB view? Saw similar thing in SAP Note 2163449 (http://service.sap.com/sap/support/notes/2163449).
Best,
  Jasmin

[rafal_matuszewski2]

Hi Jasmin,

I saw already this SAP Note and I checked my calculation view and external view. Everything is consistent and external view is synchronized with calculation view. I'm able to read data from this external view in SE16N and even in my program using normal SELECT without "for all entries".

Best regards

BTW I like your "ABAP Development for SAP HANA" course in Open SAP 🙂

[christian_seitel]

Hi,

you need to visualize what's going on with transaction ST05 (SQL Trace).

Clause FAE is translated by ABAP into a series of DB-selects.

E.g. I traced

SELECT FROM snwd_so_inv_item

FIELDS node_key

  INTO TABLE @DATA(lt_keys).

DATA lv_count_1 TYPE i.

SELECT COUNT(*) FROM snwd_so_inv_item INTO lv_count_1

  FOR ALL ENTRIES IN lt_keys

  WHERE node_key = lt_keys-node_key

  .

DATA lv_count_2 TYPE i.

SELECT COUNT(*) FROM snwd_so_inv_item INTO lv_count_2

  FOR ALL ENTRIES IN lt_keys

  WHERE node_key = lt_keys-node_key

  %_HINTS HDB '&max_in_blocking_factor 100&'.

  .

This results in numerous DB-selects (assuming more than 1000 entries in table snwd_so_inv_item)

Then DB-selects look like

SELECT

  "CLIENT" , "NODE_KEY"

FROM

  "SNWD_SO_INV_ITEM"

WHERE

  "CLIENT" = ? AND "NODE_KEY" IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?

  , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,

  ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?

  , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,

  ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?

  , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )  WITH RANGE_RESTRICTION('CURRENT')

where the number of '?' equals 100 in case the hint is used (when the hint is not used the number is larger and possibly breaks the database; in my case it is 1024)

see also profile parameter 'rsdb/max_in_blocking_factor'

(Max. split factor for FOR ALL ENTRIES queries (IN opt))

How many entries has your table 'ct_data_tra' and what is the field size of 'trans_num' ?

[rafal_matuszewski2]

Hi Christian,

How many entries has your table 'ct_data_tra' and what is the field size of 'trans_num' ?

That's good question, but my program is still on development system and ct_data_tra is small. It contains only 15 rows, trans_num field is 10 characters long. So I don't expect memory problems.

I activated the trace in ST05, and we see that DB-select has only 15 question marks for trans_num:

SELECT

  DISTINCT  "MANDT" , "DOC_TYPE" , "TRANS_NUM" , "ZZ_CV_NETDT_COR" ,

  "WEEK_NUM_ZZ_CV_NETDT_COR" , "ZZ_CV_NETDT" , "NETDT_PLAN" , "VBELN" , "POSNR" ,

  "LFDAT" , "EBELN" , "EBELP" , "NETWR" , "WAERS" , "BRWTR" , "ZZ_CV_FREF" ,

  "LIFNR" , "SORTL" , "ZTERM" , "TEXT1" , "BUKRS" , "BUTXT" , "WERKS" , "LOEKZ" ,

  "POSNR_LIPS" , "EBELP_EKPO" , "ZZ_CV_CUR_STAT"

FROM

  "Z079FI_CURR_PO2"

WHERE

  "MANDT" = ? AND "TRANS_NUM" IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?

  , ? , ? , ? )

Variables

A0(CH,3)        = '200'

A1(CH,10)       = '0000000001'

A2(CH,10)       = '0000000003'

A3(CH,10)       = '0000000004'

A4(CH,10)       = '0000000005'

A5(CH,10)       = '0000000006'

A6(CH,10)       = '0000000007'

A7(CH,10)       = '0000000008'

A8(CH,10)       = '0000000013'

A9(CH,10)       = '0000000014'

A10(CH,10)      = '0000000015'

A11(CH,10)      = '0000000016'

A12(CH,10)      = '0000000017'

A13(CH,10)      = '0000000018'

A14(CH,10)      = '0000000019'

A15(CH,10)      = '0000000023'

I used this kind of select statement in my program and it worked - no short dump occured:

SELECT *
       FROM z079fi_curr_po2
       APPENDING CORRESPONDING FIELDS OF TABLE lt_tra_isub
       WHERE trans_num in (
0000000001',
'0000000003',
'0000000004',
'0000000005',
'0000000006',
'0000000007',
'0000000008',
'0000000013',
'0000000014',
'0000000015',
'0000000016',
'0000000017',
'0000000018',
'0000000019' ).

Because I can't rely on FAE (I don't want to use hint &max_in_blocking_factor n& because I'm not sure if n = 5 will not stop working some day), I decided to use:

SELECT *
         APPENDING CORRESPONDING FIELDS OF TABLE @lt_tra_isub
         FROM z079fi_curr_po2
         WHERE trans_num IN ( SELECT DISTINCT trans_num
                                FROM z079fi_curr_h_i
                                WHERE bukrs        IN @ltr_bukrs
                                  AND status_h     IN @s_stath
                                  AND payment_date IN @s_paydat
                                  AND week_num     IN @s_weekno
                                  AND waers        IN @s_waers
                                  AND werks        IN @s_werks ).

But the problem with FAE still exist.

Best regards

Rafal

[christian_seitel]

Hi,

I think the problem with FAE has to be considered as bug (i.e. should work, but does not).

Any chance to test this on another HDB version/release ?

[rafal_matuszewski2]

Hi,

I will ask my colleagues if we have another HANA environment available for my test. My calculation view is based on many other calculation views, but I think I will be able to transport it using delivery unit.

Thank you for help and best regards

Rafal