Reg Open Cursor Concept

[Former Member]

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

[yuri_ziryukin]

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

[Former Member]

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

[yuri_ziryukin]

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.

[Former Member]

Not only easier, correct.

Rob

[Former Member]

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