on 10-23-2018 10:16 AM
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",...
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.