Alternative for Open , Fetch cursor command

Former Member · ‎2009 Feb 19

Hi Experts,

I am populating data from glpca table using Open Cursor statement with hold as shown below:

OPEN CURSOR WITH HOLD cur1 FOR

Select data from the purchase order header table.

SELECT runit rprctr sprctr

racct rfarea hsl msl kostl aufnr ps_psp_pnr

FROM glpca

WHERE docnr IN s_docnr

AND rbukrs = p_rbukrs

AND rprctr IN s_rprctr

AND sprctr IN s_sprctr

AND rfarea IN s_rfarea

AND budat IN s_budat

AND kostl IN s_kostl

AND aufnr IN s_aufnr.

Now when i try to Fetch the cursor even restricting it with PACKAGE SIZE command ( value 998), my program get struck over here for 20-30 mins or more den that & session gets out for the user at client end coz of large data. I checked the value of cursor , it was around 670-700 approx.

The query is shown below:

DO.

fetch rows from cursor depending on package size

FETCH NEXT CURSOR cur1 INTO TABLE t_glpca

PACKAGE SIZE p_size.

IF sy-subrc NE 0.

CLOSE CURSOR cur1.

EXIT.

ENDIF.

..................

.................

The program get struck at above fetch next cursor command.

Kindly suggest me some other way to handle or populate the data or any alternative for Cursor command.

Thanks for your consideration.

Regards

Mudit

ThomasZloch · ‎2009 Feb 19

Does not have so much to do with Cursor and Fetch, but rather how your selection ranges are filled when the statement is executed.

Have a look at primary and all secondary indexes of GLPCA and make sure you provide a selection that allows narrowing down the selected values using index support. Having a narrow range in S_DOCNR would help a lot, for example.

Why did you choose Open Cursor with Hold and Fetch over a normal Select with package size?

Thomas

Former Member · ‎2009 Feb 19

thanks Thomas but i have already increase the selection criteria.. the query is shown below..

SELECT runit rprctr sprctr "CR0777

racct rfarea hsl msl kostl aufnr ps_psp_pnr
FROM glpca
WHERE kokrs = p_kokrs
AND rldnr = p_rldnr
AND rvers = p_rvers
AND rrcty = p_rrcty
AND rbukrs = p_rbukrs
AND docnr IN s_docnr
AND rprctr IN s_rprctr
AND sprctr IN s_sprctr "CR0777
AND rfarea IN s_rfarea
AND budat IN s_budat
AND kostl IN s_kostl
AND aufnr IN s_aufnr.

20-40 records have been filtered .stil getting around 650 records in a cursor but the problem is still same & i cnt change the docnr to single value or less value. it depends on the user..

Former Member · ‎2009 Feb 20

Maybe a stupid question, but why do you use a cursor at all, and not a SELECT with INTO TABLE?

former_member194613 · ‎2009 Feb 20

there is a reason why OPEN CURSOR WITH HOLD is usually used.

If you don't understand that and you don't know the reason, then better leave it as it is.

Concerning performance, same comment is in 98% of all other questions, performance is not determined by commands, it is determined by your SELECT Statement and the conditions.

Siegfried

Former Member · ‎2009 Feb 25


> there is a reason why OPEN CURSOR WITH HOLD is usually used.
> 
> Siegfried

Sorry for hijacking this thread and asking here. I am here in the forums on daily basis just reading and learning.

This got me curious and I googled and read SAP tutorial about cursors:

http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3b23358411d1829f0000e829fbfe/content.htm

also read an answer from Thomas after searching forum:


The combination with OPEN CURSOR ... WITH HOLD allows package processing of tasks that require a database commit inside the loop. A normal SELECT loop without holding the cursor would be interrupted by a commit -> short dump.

MY TWO QUESTIONs

1. In SAP tutorial it states that

"WITH HOLD addition in the OPEN CURSOR statement allows you to prevent a cursor from being closed when a database commit occurs in Native SQL"

If I don't specify any commit statements in my abap code and user cursor without the addition with hold.

Can a commit be executed anyway and I get an unwanted commit and thus an unwanted close of my cursor?

Or do they mean that "if you specify commit in native sql"?

2. Thomas says a normal select loop without the cursor holding would be interrupted. Interrupted by who? Again, it seems like there is an "unwanted" commit here? Again, can there be any commits and unwanted interrupts if I dont specify it somewhere in my code?

regards

Baran

Edited by: Baran Sölen on Feb 25, 2009 11:39 AM

ThomasZloch · ‎2009 Feb 25

> Can a commit be executed anyway and I get an unwanted commit and thus an unwanted close of my cursor?

Not if you don't use an ABAP statement that triggers a commit work (see [ABAP help|http://help.sap.com/abapdocu/en/ABAPCOMMIT.htm] for the list), or you start debugging inside the loop. Problem is, if you do many database changes inside an outer loop and never do a commit work, the redo buffers of the database will grow very much (since the original state must be remembered in case a ROLLBACK WORK comes), once they are full, your process could dump or slow down significantly.

> Thomas says a normal select loop without the cursor holding would be interrupted. Interrupted by who?

By an ABAP statement like COMMIT WORK. The database cursor is lost, and the select loop cannot be continued. Actually, COMMIT WORK will also interrupt the cursor even if opened WITH HOLD, so you need to do a native SQL database commit inside the loop, as in function module DB_COMMIT.

An example application for CURSOR WITH HOLD, PACKAGE SIZE and DB_COMMIT from my humble experience is when deleting or copying very large amounts of data (potentially > 1 GB), e.g. reorg or extraction programs.

All this from an ABAP developers point of view, I wonder what the DB gurus have to say

Thomas

krzysztof_murkowski2 · ‎2010 Nov 24

Hi Baran,


> 1. In SAP tutorial it states that 
> 
> "WITH HOLD addition in the OPEN CURSOR statement allows you to prevent a cursor from being closed when a database commit occurs in Native SQL"
> 
> If I don't specify any commit statements in my abap code and user cursor without the addition with hold. 
> 
> Can a commit be executed anyway and I get an unwanted commit and thus an unwanted close of my cursor?
> 
> Or do they mean that "if you specify commit in native sql"?

There are two possible commits:

- ABAP commit ("COMMIT WORK") and

- database commit ("CALL FUNCTION 'DB_COMMIT' " or " EXEC SQL. COMMIT WORK ENDEXEC.").

These two commits have different scope and consequences.

The addition WITH HOLD prevent cursor before database commit only.


> 2. Thomas says a normal select loop without the cursor holding would be interrupted. Interrupted by who? Again, it seems like there is an "unwanted" commit here? Again, can there be any commits and unwanted interrupts if I dont specify it somewhere in my code?

Yes, there can still an implicit ABAP commit appear (end of the dialog step, call function per rfc, etc.)

A complete example for package processing with OPEN CURSOR WITH HOLD ... FETCH NEXT CURSOR ... PACKAGE SIZE ... database COMMIT can be found here: www.kerum.pl/infodepot/00016

Regards,

Kris

Former Member · ‎2009 Feb 25

So if there's a problem with unwanted commits inside the loop, a

SELECT * INTO itab.

LOOP AT itab.
*    do the work here for each line ...
ENDLOOP.

will also have no problem with that and should be much faster?

ThomasZloch · ‎2009 Feb 25

> will also have no problem with that and should be much faster?

Only if amount of data is not too large, see answer above.

Thomas

former_member194613 · ‎2010 Nov 24

>An example application for CURSOR WITH HOLD, PACKAGE SIZE and DB_COMMIT from my humble experience is when

>deleting or copying very large amounts of data (potentially > 1 GB), e.g. reorg or extraction programs.

that is the use case for the WITH HOLD: You process a large amount of data, and you send db_commits (because of the redo functionality of the db can run into memory limits). You want to continue with work acoording to your select criteria.

I do not know whether an OPEN CURSOR is still needed otherwise, years ago it was used to program nested loops, both loop in sync. That is however not necessary, if your db has not problems with joins.

Siegfried