Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 28, 2015 at 04:12 PM

    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' ?

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Oct 28, 2015 at 02:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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 :-)