ABAP Developmentadvanced

Open SQL Part 7: PACKAGE SIZE and Large Data Processing

Key takeaway

Process millions of rows without memory dumps or timeouts: SELECT … PACKAGE SIZE, OPEN CURSOR / FETCH, commit handling in long jobs, restartability and parallel processing basics.

Explanation

📌 KEY POINTS
✅ SELECT … INTO TABLE … PACKAGE SIZE n … ENDSELECT processes n rows at a time — only one package in memory.
✅ OPEN CURSOR / FETCH NEXT CURSOR … PACKAGE SIZE gives more control (e.g. across method calls).
⚠️ COMMIT WORK inside a SELECT … ENDSELECT loop closes the cursor — use OPEN CURSOR WITH HOLD if you must commit.
💡 Design mass jobs to be restartable: mark processed records, log progress.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–6 read manageable volumes. Interfaces, archiving preparation and data migrations read millions of rows — this part shows the classic tools.\n\n━━━━━━━━━━━━━━━━━━━━\n▶ 1. SELECT … PACKAGE SIZE\nSELECT … FROM sbook INTO TABLE gt_pkg PACKAGE SIZE 10000 WHERE …
process gt_pkg (write file, post, aggregate)

ENDSELECT.

Each pass replaces gt_pkg with the next package (APPENDING TABLE accumulates — usually not what you want).
sy-dbcnt counts all rows read so far.

━━━━━━━━━━━━━━━━━━━━
▶ 2. OPEN CURSOR / FETCH
OPEN CURSOR WITH HOLD lv_cursor FOR SELECT … FROM sbook WHERE …
DO.
FETCH NEXT CURSOR lv_cursor INTO TABLE gt_pkg PACKAGE SIZE 10000.
IF sy-subrc <> 0. EXIT. ENDIF.
process; COMMIT WORK (allowed with WITH HOLD)

ENDDO.

CLOSE CURSOR lv_cursor.

Useful when processing is split across methods or needs commits.
WITH HOLD keeps the cursor open across database commits.

━━━━━━━━━━━━━━━━━━━━
▶ 3. COMMITS IN LONG JOBS

Large updates in one LUW lock many rows and fill the rollback segments — commit per package.
Never COMMIT inside SELECT … ENDSELECT (cursor is lost → runtime error on next fetch).
Commit only when each package is consistent on its own.

━━━━━━━━━━━━━━━━━━━━
▶ 4. RESTARTABILITY

Keep a status per record (processed flag, log table) so a restarted job skips done work.
Log package progress (application log / job log).
Use selection criteria so a failed range can be re-run.

━━━━━━━━━━━━━━━━━━━━
▶ 5. PARALLEL PROCESSING (OVERVIEW)
For very large volumes, split the work (e.g. by airline or key range) into parallel tasks: background jobs per range, or aRFC with SPTA framework. Mind locks and system capacity.

━━━━━━━━━━━━━━━━━━━━
▶ 6. BUT FIRST: DO YOU NEED THE ROWS?
If only totals are needed, aggregate in SQL (Part 6). If data moves table-to-table, consider INSERT … FROM SELECT (newer releases) or database-side logic.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP
Run the code example: package processing with totals, and the cursor pattern with commits. Watch memory in the debugger — it stays constant.

✅ CHECKPOINT: your mass programs run with constant memory and can be restarted.

Code example

ABAP Code

REPORT zosql_part7. TYPES: BEGIN OF ty_book,         carrid    TYPE s_carr_id,         connid    TYPE s_conn_id,         fldate    TYPE s_date,         bookid    TYPE s_book_id,         forcuram  TYPE s_f_cur_pr,         forcurkey TYPE s_curr,       END OF ty_book. DATA: gt_pkg    TYPE STANDARD TABLE OF ty_book,      gv_cursor TYPE cursor,      gv_total  TYPE i,      gv_pkgs   TYPE i,      gv_lines  TYPE i. START-OF-SELECTION.* ===== 1. SELECT ... PACKAGE SIZE: one package in memory =====  SELECT carrid connid fldate bookid forcuram forcurkey    FROM sbook    INTO TABLE gt_pkg    PACKAGE SIZE 10000    WHERE cancelled = space.     gv_pkgs  = gv_pkgs + 1.    DESCRIBE TABLE gt_pkg LINES gv_lines.    gv_total = gv_total + gv_lines.*   ... process gt_pkg: write file lines, build totals, call APIs ...*   NO COMMIT WORK here - it would close the SELECT cursor!  ENDSELECT.  WRITE: / 'packages:', gv_pkgs, 'rows:', gv_total. * ===== 2. OPEN CURSOR WITH HOLD: commits per package allowed =====  CLEAR: gv_total, gv_pkgs.  OPEN CURSOR WITH HOLD gv_cursor FOR    SELECT carrid connid fldate bookid forcuram forcurkey      FROM sbook      WHERE cancelled = space.   DO.    FETCH NEXT CURSOR gv_cursor INTO TABLE gt_pkg PACKAGE SIZE 10000.    IF sy-subrc <> 0.      EXIT.                                      " no more rows    ENDIF.    gv_pkgs = gv_pkgs + 1.*   ... update own status table for this package (restartability) ...*   UPDATE zbook_status FROM TABLE ...    COMMIT WORK.                                 " OK because of WITH HOLD  ENDDO.  CLOSE CURSOR gv_cursor.  WRITE: / 'cursor packages:', gv_pkgs.   FREE gt_pkg.

Real project scenario

A migration program copied 25 million booking records into a new table with one INSERT per row and a single COMMIT at the end. After six hours it dumped because the database's rollback space was exhausted, and the work was lost.
The redesign read with OPEN CURSOR WITH HOLD in packages of 20,000, inserted each package with INSERT … FROM TABLE, committed per package and stored the last processed key in a status table. A restart after a network interruption continued from the last package, and the run finished in 50 minutes.

Common mistakes

Loading everything into one internal table.
COMMIT WORK inside SELECT … ENDSELECT.
One giant LUW for millions of updates.
APPENDING TABLE in package loops (memory grows again).
No restart concept for long jobs.

Best practices

PACKAGE SIZE or cursors for mass reads.
WITH HOLD cursors when committing per package.
Mass DML (INSERT/UPDATE FROM TABLE) per package.
Status tracking and logging for restartability.
Aggregate in SQL when only totals are needed.

Interview angle

Questions: "How do you process millions of rows in ABAP?", "What happens if you COMMIT inside SELECT … ENDSELECT?", "What does OPEN CURSOR WITH HOLD do?"
Strong answer: use SELECT … PACKAGE SIZE or OPEN CURSOR/FETCH … PACKAGE SIZE so only one package is in memory; a COMMIT inside SELECT loops closes the cursor and causes a runtime error; WITH HOLD keeps the cursor open across commits; commit per package, use mass DML, make jobs restartable with status tracking, and consider parallelization.

Practice this lesson

Quick check

1. Which construct keeps a database cursor open across COMMIT WORK?

2. What happens if COMMIT WORK is executed inside a SELECT ... ENDSELECT loop?

3. Best practice for large result sets in ABAP is to:

Interview questions

1. A migration report loads millions of accounting rows at once and dumps. How do you redesign it?

Answer it in your own words first, then compare.

2. A background job frequently fails with cursor-related dumps or lock-timeout symptoms during large Open SQL processing. How would you diagnose and redesign it safely?