topic DBSQL_NO_PERM_MEMORY in Application Development and Automation Discussions

DBSQL_NO_PERM_MEMORY

Former Member — Fri, 20 Nov 2015 13:05:49 GMT

Hi,

I have used the below select query in my program.

SELECT knumh kappl kschl pltyp datbi datab matnr

INTO CORRESPONDING FIELDS OF TABLE gt_dattrn_price

FROM a607

FOR ALL ENTRIES IN lit_cdhdr

WHERE knumh = lit_cdhdr-objectid

AND kappl = p_kappl

AND kschl IN s_kschl

AND pltyp IN r_pltyp

AND matnr IN s_matnr

AND datbi GE sy-datum

AND datab LE sy-datum.

There are some 25,000 records which are fetched from cdhdr table. On running the same program in background we received a dump

Internal session terminated with a runtime error DBSQL_ALLOCATION_FAILED (see ST22)

Runtime Errors DBSQL_NO_PERM_MEMORY

Could you please give a solution to this.

Re: DBSQL_NO_PERM_MEMORY

Former Member — Fri, 20 Nov 2015 14:09:43 GMT

This would be the place where you use the PACKAGE SIZE addition to the SELECT statement. You will be able to process your data in smaller chunks and should be able to get around this error.

Rob

Re: DBSQL_NO_PERM_MEMORY

Former Member — Mon, 23 Nov 2015 08:41:16 GMT

have you tried to use a more complex SQL Statement, including a join instead of the "FOR ALL ENTRIES" ?

depending on the DB you are using this might solve the problem, as "FOR ALL ENTIRES" esentially creates a loop, and with a size of 25000 elements this is no easy task

Re: DBSQL_NO_PERM_MEMORY

Former Member — Wed, 25 Nov 2015 07:26:26 GMT

You mean I should use another select query than the above one. I am not sure if this would be the issue with the basis setting or the code

Re: DBSQL_NO_PERM_MEMORY

Former Member — Wed, 25 Nov 2015 07:28:02 GMT

So you mean if I remove 'for all entries" and place a join the number of entries in CDHDR table would not matter, As I would also be receiving 1 lac entries in CDHDR table

Re: DBSQL_NO_PERM_MEMORY

Former Member — Wed, 25 Nov 2015 14:16:55 GMT

It's doubtful if you will get Basis to change their settings so that a single program can run. You can use the PACKAGE SIZE option of the SELECT statement to retrieve and process smaller chunks of data at one time. Press F1 on SELECT and you'll find the documentation.

Rob