Visit the SAP Forum

Languages : English | Dutch | French | Portugese | Italian

Get 30,000 Interview Questions & Answers in an eBook.

Interview Success Kit - Get Success in Job Interviews

Send your Resume to 6000 Companies
India's Best Web Hosting Company

Logical Sequence of Database Operations & Buffering

Home »SQL Trace Tool »Logical Sequence of Database Operations & Buffering


S A P - R E S O U R C E S

Get Free SAP Resources:

Are you looking for:
  • SAP Jobs
  • Free SAP eBooks
  • SAP Softwares
  • SAP Tutorials
  • ERP Implementation Examples
Enter Email Address:
(Enter your email address above and we will instantly send you the download link to you, when you confirm your email.)
Search SAP Resources:

Logical Sequence of Database Operations

Database requests are interconnected and always occur in the same logical sequence.

The DECLARE function defines and numbers the cursor. DECLARE precedes the PREPARE function.

Use PREPARE to prepare a specific SQL statement, such as:

select * from sflight where carrid eq 'LH'.

and define the access method before the system can transfer the request to the database. During this preparation, the system is concerned only with the structure of the SQL statement and not with the values it contains.

The OPEN function takes the prepared SELECT statement and completes it with the correct values. In the above example, OPEN would issue the field carrid the value LH.
FETCH passes the entries from the database to the database interface of the SAP System All of the database operations required to execute an SQL statement are linked by the same cursor ID.
If the SQL statement makes changes in the database (INSERT, UPDATE, DELETE), PREPARE is followed by EXEC, which executes the statement

If the system can refer back to an SQL statement that has already been prepared, there is no PREPARE operation, and the statement is executed using REOPEN or REEXEC as appropriate


The SAP System ensures that data transfer between the SAP System and the database system is as efficient as possible. To do this, it uses the following techniques:

  • Table buffering. The program accesses data from the buffer of the application server.
  • Database request buffering. Individual database entries are not read or passed to the database until required by an OPEN SQL statement.
When you analyze trace records, you should also examine the system's buffering mechanisms.

Table Buffering

For tables can be either partially or fully buffered (refer to Buffering Database Tables.), an OPEN SQL statement only accesses the database if the results of the statement are not already in the buffer Consequently, the SQL Trace does not contain a command or command sequence for every OPEN SQL statement. On the other hand, every SQL statement in the trace file has been sent to the database and executed.

Buffering of Database Requests

To keep the number of runtime-consuming PREPARE calls small, each an application's work processes hold a certain number of already translated SQL statements in a special buffer. By default, a process holds up to 250 statements.

If the system must execute a specific OPEN SQL, the system first checks whether this statement is stored in the "Statement cache". If the statement is in the cache, the system executes it immediately using a REOPEN (SELECT) or a REEXEC (INSERT, UPDATE, DELETE).

If the statement is not buffered, a PREPARE operation prepares it for the subsequent OPEN/EXEC. The system administers the buffer according to the LRU algorithm ("least recently used"). When space is needed for new statements, the statements that are rarely used are deleted. As a result of the LRU algorithm, the statement must prepare frequently used statements usually only once.

An application server buffers the DECLARE, PREPARE, OPEN, and EXEC requests within the cursor cache of one work process. As a result, once the system opens a cursor for a DECLARE operation, it can use this cursor over and over again within the same work process.

Analyzing a Sample SQL Data File

When you create an SQL trace file for an application, you can see exactly how the system handles database operations. In a sample application, a report reads, and later changes, records on the ABAP Dictionary table SFLIGHT using ABAP Open SQL statements. Since the table SFLIGHT is not buffered, the system first needs to access the database to retrieve the records. In the sections below, the data file from the sample application is analyzed.

Read Access

The first screen of the SQL trace file displays each measured database request the application made. The trace file records when the request occurred and its duration. The ABAP Dictionary table involved in the request is also listed.

A trace file for a read access of the table SFLIGHT might look like this:

Read Access
The system measured several database operations involved in retrieving records from SFLIGHT:

Operation Function

Prepares the OPEN statement for use and determines the access method. Since an active cursor with the number 18 is available in the work process's cursor cache, the system does not perform a DECLARE operation. However, the system must prepare the SELECT statement that is used to read the table SFLIGHT.

The system does enter any value into the fields 'MANDT' and 'CARRID' in the SELECT statement at this point, but instead gives them a database-specific marker.

Opens the cursor and specifies the selection result by filling the selection fields with concrete values. In this example, the field 'MANDT' receives the value '000' and the field 'CARRID' receives the value 'LH'. The OPEN operation then creates a set of retrieved records.


Moves the cursor through the dataset created by the OPEN operation. The array size displayed beside the fetch data means that the system can transfer a maximum package size of 392 records at one time into the buffered area. The system allocates this space on the application server for the SFLIGHT table.

In the above example, the first FETCH retrieves the maximum number of records from the dataset. Then, the these records are transferred to the program interface.

Write Access

An sample SQL file analyzing a request that changes data in the Table D010SINF might look like this:

Write Access

The example shows the system inserting new records into the table (INSERT). As in the first example, where the system carried out a read-only access, the system needs to prepare the database operations (PREPARE) that change the database records. The PREPARE operation precedes the other operations


discussionDiscussion Center


Yahoo Groups
Y! Group
Sirfdosti Groups
Contact Us
Sign in

User Name:
Forget password? | Register


Get 30,000 Interview Questions & Answers in an eBook.

Interview Success Kit - Get Success in Job Interviews

Send your Resume to 6000 Companies

SAP Resources

  • SAP Articles
  • SAP Books
  • SAP Certifications
  • SAP Companies
  • SAP Study Materials
  • SAP Events
  • SAP Faqs
  • SAP Jobs
  • SAP Job Consultants
  • SAP Links
  • SAP News
  • SAP Sample Papers
  • SAP Interview Questions
  • SAP Training Institutes
    SAP Tutorial

  • Introduction
  • SAP History
  • SAP Facts
  • SAP Tables
  • SAP Function
  • SAP Report
  • Reasons To Use SAP
  • SAP Landscape
  • SAP Transaction Code
  • SAP Lotus Notes Integration
  • Business Connector and XML
  • Enterprise Release 4.7

  • SAP Training Institutes

  • SAP Training Institutes
  • Job Consultants Address
  • SAP Companies Address

  • Business Process Cycle

  • Analyze
  • Design
  • Implement
  • Operate
  • Optimize
  • Skills And Education

  • Logistics Module

  • Sales and Distribution
  • Quality Management
  • Materail Management
  • Plant Maintenance
  • Financial Modules (FI/CO)
  • Project system
  • Production Planning
  • Service Management

  • SAP NetWeaver

  • Introduction
  • Object Model
  • Interoperability
  • Creating Web Apps
  • Java Connector
  • Advantages & Conclusion
  • Life Cycle Management
  • Composite Application Framework

  • SAP Tools

  • SQL Trace Tool
  • SAP BW

  • SAP Modules

  • SAP FI Module
  • SAP CO Module

  • SAP Technical

  • SAP ITS Architecture
  • Financial Management
  • Manufacturing Planning and Execution
  • Customer Order Management
  • Lackbox Processing
  • SD and Financial Accounting Integration
  • SAP - HR (Human Resource)

  • SAP Miscellanous

  • Web Application Server(WAS)
  • Data Archiving
  • Mobile Engine
  • SAP Script
  • SAP Transactions
  • SAP Business Warehouse(BW)

  • SAP Reference

  • SAP Facts
  • SAP Tables
  • SAP Function
  • SAP Report
  • SAP Landscape

  • SAP Others

  • SAP Treasury
  • SAP Finance
  • SAP Data Archiving
  • SAP Exchange
  • SAP Applications Components
  • SAP Software
  • Dunning
  • SAP Payroll Basics
  • SAP BASIS and Security Authorization
  • SAP Backup
  • SAP Router

  • ABAP Tutorials

  • Introduction
  • ABAP Transaction
  • ABAP Function
  • ABAP File Processing
  • ABAP Objects
  • ABAP Syntax
  • ABAP Queries
  • SAPMail Using ABAP
  • ABAP Programming Hints

  • SAP Projects & Codes

  • Finding the user-exits
  • Purchase Order Display
  • Batch Input
  • Dynamic Open SQL
  • Creating Extract Dataset
  • Reading database tables
  • Load table from a UNIX file
  • Create subscreen

  • SAP Resources

  • SAP Books
  • SAP Links

  • Interview Question

  • Common Interview Que.
  • ABAP Interview Que.
  • ERP Interview Que.
  • HR Interview Que.
  • MM Interview Que.
  • PP Interview Que.
  • ALE Interview Que.
  • EDI Interview Que.
  • Basis Interview Questions
  • SAP-SD Interview Questions
  • FI&CO Interview Questions
  • BW Interview Questions
  • CRM Interview Questions
  • Interview Questions

  • Careers at SAP

  • Careers in SAP (SAP Jobs)
  • Advertise