topic Re: Reg Open Cursor Concept in Application Development and Automation Discussions

Reg Open Cursor Concept

Former Member — Thu, 29 Sep 2011 07:14:21 GMT

Friends,

Please kindly help me to analyse this dump.

In BI end routine select * query has been written to fetch values from active dso . They are using non primary key in the where condition.

More than ~~2 crore~~ 20 million records were avaialble in that DSo for that condtion. While executing this query its going to dump.

If i move ahead with Open cursor set Hold will this query work fine and using open cursor set hold can i fetch ~~2 crore~~ 20 million records.

In dump its shwoing to check the parameters : ztta/roll_area ,ztta/roll_extension and abap/heap_area_total . Even i checked those parametrs in RZ11. The curent val is sufficient.

Please kindly advice me for this dump and will open cursor set hold avoid the dump for ~~2 crore~~ 20 million records.

Thanks

Edited by: Suhas Saha on Sep 29, 2011 1:06 PM

Re: Reg Open Cursor Concept

yuri_ziryukin — Thu, 29 Sep 2011 11:54:25 GMT

Hello,

your dump shows that SAP work process does not have enough memory to store the complete result of your query.

Your first question should be "why do I need 20 mln records in memory"? What are you going to do with all of them?

"Open cursor" and the subsequent "fetch ... package size ..." may help you reading data in packages thus reducing the amount of memory needed. This is working if you do not need all data at once and can process it in sequence.

Regards,

Yuri

Re: Reg Open Cursor Concept

Former Member — Thu, 29 Sep 2011 13:24:40 GMT

The OPEN CURSOR construct is used to enable you to have two or more different cursors open on a table at the same time. It doesn't look like that this is what you want here.

Look at the PACKAGE SIZE option of the SELECT statement.

Rob

Re: Reg Open Cursor Concept

yuri_ziryukin — Fri, 30 Sep 2011 06:55:20 GMT


The OPEN CURSOR construct is used to enable you to have two or more different cursors open on a table at the same time. It doesn't look like that this is what you want here.
> 
> Look at the PACKAGE SIZE option of the SELECT statement.
> 
> Rob

Indeed, SELECT ... PACKAGE SIZE ... + ENDSELECT is an easier alternative to the OPEN CURSOR + FETCH ... PACKAGE SIZE + CLOSE CURSOR.

Re: Reg Open Cursor Concept

Former Member — Fri, 30 Sep 2011 13:11:35 GMT

Not only easier, correct.

Rob

Re: Reg Open Cursor Concept

Former Member — Wed, 05 Oct 2011 08:45:17 GMT

I am not completely convinced: the difference depends on the task which has to be done with the records of a package.

If the records are processed and the result must be written to the database into another table, then it will be necessary to COMMIT the changes, otherwise the 20.000.000 records will cause an overflow in the redo-logs of the database.

=> Only the OPEN CURSOR WITH HOLD will survive the DB-Commit.

The SELECT ... PACKAGE SIZE is simpler and can be used, if the result is further processed and reduced in size, which makes it possible to keep all data in memory (no intermediate DB-COMMITs are necessary) .

I would assume that case is the less frequent use case.

Siegfried