out of memory while calling a procedure from xs job

[Vinothkumar1]

Hello HANA Experts,

We have a procedure which fetches data from HANA calculation view and inserts into a table. Calling a procedure from studio sql editor works fine. However calling a procedure via xs job results out of memory issue. Does the procedure gets executed in different mode when called from xs job?  is there any hint we can use to trigger the way it gets triggered from sql editor?

Below is the exception log

search table error: cannot allocate enough memory:  [9] Memory allocation failed;exception 1000002: Allocation failed ; $failure_type$=STATEMENT_MEMORY_LIMIT_FROM_GLOBAL_CONFIG; $failure_flag$=; $size$=2627173632; $name$=Results; $type$=pool; $inuse_count$=263; $allocated_size$=35515423384; $alignment$=8
,Exception in executor plan11765910@xxx while executing pop 9 (SQLScript function CA_BOE:CA.APPS.COMMON::CA_UNIQUE_REALM_TBF, line 10; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::D_PROJ_INFO_TF, line 28; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::D_TIME_TF, line 29; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::D_UNSPSC_TF, line 99; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::D_USER_DATA_TF, line 67; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::F_CUR_CNF_TF, line 32; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::F_CUR_VAR_TF, line 15; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::F_GEN_CON_W_TF, line 187; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::F_SRC_PROJ_TF, line 323; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::F_USR_ABLTY_TF, line 37; SQLScript function CA_BOE:CA.SDI.APPS.TABLE_FUNCTIONS::J_OWNRS_PRJ_TF, line 13) at SQLScript/Executor/Evaluator/SeEvaluatorImpl.cpp:725

regards,

Vinoth

[HakanHaslaman · Accepted solution]

The error message indicates that the procedure is trying to allocate more memory than is available. This could be due to the fact that the XS job and the SQL editor have different memory limits. When you run a procedure from the SQL editor, it is executed in the context of your user session, which might have a higher memory limit. When the procedure is run from an XS job, it is executed in the context of the XS engine, which might have a lower memory limit.

Try to reduce the amount of data that your procedure needs to process at once. This could involve breaking down the procedure into smaller parts, or using more efficient SQL queries but always test any changes in a controlled environment before applying them to your production system.