cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Expert Routine fails with SQL Error . Out of Memory

matt
Active Contributor
0 Kudos

From my earlier question here: https://answers.sap.com/questions/600389/simplifying-repetitive-sql.html I wrote an ABAP program that generates SQL for a HANA Expert Routine in a transformation.

The general form of the SQL is:

    select /* ACT 1*/
        k.f1, k.f2, k.f3,.., k.f150
        , IFNULL (l.mapped , 'ACT1') as account_name
        , ACT1 as    account_value
    from kftab k 
    left outer join lookup l
    on (k.f1, 'ACT1') = (l.f1, l.act)
    where 
        k.ACT1 != 0.0
UNION ALL
    select /* ACT 2*/
           k.f1, k.f2, k.f3..., k.f150
        , IFNULL (l.mapped , 'ACT2') as account_name
        , ACT2 as    account_value
    from kftab k 
    left outer join lookup l
    on (k.f1, 'ACT2') = (l.f1, l.act)
    where 
        k.ACT2 != 0.0

union all
...
    select /* ACT 50*/
           k.f1, k.f2, k.f3..., k.f150
        , IFNULL (l.mapped , 'ACT2') as account_name
        , ACT2 as    account_value
    from kftab k 
    left outer join lookup l
    on (k.f1, 'ACT50') = (l.f1, l.act)
    where 
        k.ACT2 != 0.0

So you can see, it's quite a large SQL statement. When I run the dtp on my development system, with 3 records in the source, it works fine.

When I run it in my test system, which has 2 million records, I get this error.

SQL error with code '2,048'. See the following SQL message:
=> column store error: search table error:[1000002] exception 1000002: ltt/impl/memory.cpp:86 Allocation failed ; $size$=1048576; $name$=VectorColumn; $type$=pool; $inuse_count$=208724; $allocated_size$=110176008192
Saving ended with errors
Execution of analysis process '/1BCAMDP/0BW:DAP:TR_5HM9SE3L061D3MDS306ZH2NE2' canceled
Exception CX_RSDHA_MSG logged


The HANA dump file shows this

[MEMORY_LIMIT_VIOLATION]  Information about current memory composite-limit violation: (2018-10-23 11:02:16 792 Local)
Composite limit violation (OUT OF MEMORY) occurred.
Composite limit=206158430208
Host: 
Executable: hdbindexserver
PID: 6153
Failed to allocate 1048576 byte.

I've tried playing with the package size - even set it to 3, and yet I still get this error. I've also unchecked the parallel checkbox. Why am I running out of memory even with a very small package size?

EDIT: I've looked at the full trace file, and it seems the memory overflow is happening at the insert into the destination ADSO.

SQL: INSERT INTO "SAPTST"."/BIC/ATGTADSO1" ("CALWEEK", "CURRENCY", "CURTYPE", "FISCPER", "FISCVARNT", "FISCYEAR", "LOGSYS", "RECORDMODE",...

Accepted Solutions (1)

Accepted Solutions (1)

matt
Active Contributor

SAP KBA 2568102

Package size is ignored when SAP HANA Execution is switched on. All the data is loaded from source. The trick is to either filter so that the data is small enough, or for initial load, unclick the flag, and put it back on again for delta.


Answers (0)