OVERVIEW OF DATABAE UPDATES

You can update databases either using ABAP's Open SQL commands, or with the database-specific commands of your database's Native SQL command set.

You can access ABAP cluster databases using special ABAP commands.

You can access the data in database tables using the Open SQL commands. The command set includes operations of the Data Manipulation Language (DML). The Data Definition Language (DDL) operations are not available in Open SQL, as these functions are performed by the ABAP Dictionary.

Native SQL commands allow you to carry out both DML and DDL operations.

The commands for ABAP cluster databases enable operations to be carried out on the data in the cluster databases. The tables themselves are created in the ABAP Dictionary as transparent tables.
For general information on cluster tables, refer to the course appendix.

For further information on Native and Open SQL, see the ABAP Editor Keyword documentation for the term SQL.

Each time you access the database using Open SQL, the database interface of each work process (application server) converts this to a database-specific command. For this reason, the ABAP programs themselves are independent of the database used and can be transferred to other system platforms (with a different database system) without additional programming requirements.

SAP database tables can be buffered at the application server level. The aims of buffering are to

Reduce the time needed to access data with read accesses. Data on the application server can be accessed more quickly than data on the database.

Reduce the load on the database. Reading the data from application server buffers reduces the number of database accesses.

The buffered tables are accessed exclusively via database interface mechanisms.

Database accesses with Native SQL enable database-specific commands to be used. This requires a detailed knowledge of the syntax in question. Programs that use Native SQL commands need additional programming after they are transported to different system environments (different database systems), since the syntax of the SQL commands generally varies from one database to the next.

The target quantity can be limited on the database using all the Open SQL commands discussed here.

One or more rows can be processed with a SQL command. Each command also provides the option of specifying the table name dynamically.

In addition to this, each type of operation has a syntax variant, which can be used to change individual fields in a row.

With masked field selections (WHERE LIKE ''), note that '_' masks an individual character and '%' masks a character string of any length (in line with the SQL standard).

For all Open SQL commands, you can edit data in the current client (standard). To do so, you do not specify any command additions and leave the client field non valuated.

If you want to edit data from other clients explicitly, use the SQL command with the addition CLIENT SPECIFIED and enter the number of the client in which the SQL operation is to be carried out in the WHERE clause of the command.

All Open SQL commands return confirmation of the success or failure of the database operation in the form of a return code. This is always returned by the database interface in the sy-subrc system field. The return code '0' (zero) always means that the operation has been completed successfully. All other values mean that errors have occurred. For further details, please refer to the keyword documentation for the command in question.

In addition, the sy-dbcnt system field displays the number of records for which the desired database operation was actually carried out.

Note that Open SQL commands do not perform any automatic authorization checks. You need to carry these out separately (see unit Authorization Checks).

To insert a new row in a database table, enter the command INSERT INTO VALUES . To do so, you must specify the data to be written to the database in the structure (key and non-key fields) before the command.

The structure must be typed according to the row structure of the database table to be updated (DATA TYPE ).

Rows can also be inserted for views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The INSERT command has the two return codes '0' (row could be inserted) and '4' (row could not be inserted, as a row with the same key already exists).

The following ABAP short forms exist:

Short form 1: INSERT [CLIENT SPECIFIED] FROM .
Short form 2: INSERT [CLIENT SPECIFIED].

The second short form requires that the data, which is to be added to the database, be available in a table work area called . This table work area must be declared in the program with TABLES: .

The second short form is forbidden using ABAP Objects.
a database table. The internal table contains the data in the rows that are to be inserted.
The internal table must be typed to row type .

If the operation can be carried on all rows, the return code sy-subrc returns the value zero. If even one data record cannot be created, a runtime error is triggered. This means that no data record is inserted by the command.

You can prevent the runtime error occurring with the addition ACCEPTING DUPLICATE KEYS.
In the event of an error, the addition sets return code 4 instead of the runtime error. The data records that were successfully inserted are not rejected (no DB ROLLBACK).

The sy-dbcnt system field contains the number of rows that were successfully inserted in the database.

The command UPDATE SET = ... = WHERE allows you to change data in one row in a database table. After the SET command, you specify the fields in the rows whose values you want to change and the key of the database row in the WHERE clause. The key must be specified completely; each individual field must be specified with the relational operator '='.

For numeric fields, the data following the SET command may be specified in the form of a "calculation rule" carried out on the database: f = g, f = f + g, f = f - g.

The command has the two return codes 0 (row could be changed) and 4 (row could not be changed).

Rows can also be changed in views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The following g short forms exist:

Short form 1: UPDATE FROM .
Short form 2: UPDATE dbtab.

With short form 1, the entire data record must have been written to the structure (key and non-key fields) before it is called up. The structure must be typed to the row type of the database table (DATA: TYPE . The short form is not field-specific, but sends the entire structure to the database interface.

The second short form requires that the data, which is to be updated in the database, be available in a table work area called . This table work area must be declared in the program with TABLES: .

The second short form is forbidden using ABAP Objects.

If identical changes are to be made to several rows in a table, use the syntax specified on the slide.
Using the WHERE clause, specify the rows for which the change is to be carried out.

The following "calculations" are also possible here for the numerical fields to be changed:
f = g, f = f + g, f = f - g.

The command has the two return codes 0 (at least one row has been changed) and 4 (no rows could be updated).

The sy-dbcnt field contains the number of updated rows in the database table.

There is a short form UPDATE SET = ... = . This requires that a table work area has been created with TABLES and changes the fields specified after SET for all rows in the current client.

The short form is forbidden using ABAP Objects.

If changes are to be made to several rows in a database table, whereby the changes for each row is determined via an internal table, use the syntax UPDATE FROM TABLE .

Here, the internal table contains the data of the rows to be changed (key and non-key fields). The internal table must have the row type .

The command has the two return codes 0 (all rows have been updated) and 4 (at least one row of the internal table was not used to update the database; the remaining rows have been updated).

The system field sy-dbcnt contains the number of rows that have been updated in the database.

The MODIFY command is SAP-specific. It includes the operations of the two commands
INSERT ... and UPDATE...:

In other words, MODIFY FROM inserts a new data record if the structure specifies a data record that does not yet exist in the database.

If the structure specifies an existing data record; the command updates the row in question.

Using the different syntax variants, you can make changes to individual rows, make similar changes to several rows, and carry out operations on sets of records.

All variants of the MODIFY... syntax have the two return codes 0 (all rows were inserted or updated) and 4 (at least one line was not inserted or updated).

The operation can also be carried out on views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The field sy-dbcnt contains the number of rows that have been changed or inserted in the database.

The command DELETE FROM WHERE enables one row to be deleted from a database table. In the WHERE clause, specify all the key fields with the relational operator '='.

The command has the two return codes 0 (row has been deleted) and 4 (row has not been deleted).

A row can also be deleted from views. However, there are two restrictions here: The view may only contain fields from one table and must be created in the ABAP Dictionary with maintenance status 'read and change'.

The following short forms exist:

Short form 1: DELETE [CLIENT SPECIFIED] FROM ,
Short form 2: DELETE [CLIENT SPECIFIED].

Short form 1 requires that the structure has been filled with the key fields of the row to be deleted before it is called up. The structure must have the row type .

Short form 2 requires that the key fields of the row to be deleted be available in a table work area called . This table work area must be declared in the program with TABLES: .

The second short form is forbidden using ABAP Objects.

The command DELETE FROM WHERE enables several rows to be deleted from a database table. Here, you can specify the rows that are to be deleted with the WHERE clause.

The command has the two return codes 0 (at least one row was deleted) and 4 (no rows were deleted).

The system field sy-dbcnt contains the number of rows that have been updated on the database.

To delete several specific rows from a database using a database operation, use the statement DELETE FROM TABLE . The internal table here contains the key fields for the rows that are to be deleted. The internal table must have the row type .

The command has the two return codes 0 (all rows have been deleted) and 4 (at least one row could not be deleted, the rest have been deleted).

There are two ways of deleting all the rows from a table in the current client:

Either DELETE FROM WHERE IN with a blank internal table

or DELETE FROM WHERE LIKE '%'.

The number of rows deleted from the database is shown in the system field sy-dbcnt.

If you receive a return code other than zero from the database interface in response to an Open SQL statement for changing data in the database, you should make sure that the database is reset to the status it had before the change attempt was made. You can do this by means of a database rollback.
The database rollback undoes any changes made to the current database LUW (see the next unit).

For return codes from DB change statements (Open SQL), the most suitable means of triggering a database rollback is to send a termination dialog message (A message or X message). This triggers a database rollback and terminates the associated program.

All other message types (E,W, I) also involve a dialog but do not trigger a database rollback.

You can also trigger a database rollback using the ABAP statement ROLLBACK WORK (without terminating the program at the same time). You should not use the ROLLBACK WORK statement directly, unless you do not want to reset the program context (unlike a termination dialog message).

ORGANIZING DATA BASE UPDATES

ORGANIZING DATA BASE UPDATES:


If your transaction executes database updates from the dialog program, you must bundle all of your database updates into a single dialog step (usually the last). This is the only way to ensure that your database changes are processed on the all-or-nothing principle.

With database changes from the dialog program, you must save the data you want to change in the global program data until the database changes are made. This data is written to the database with the status it had for the last dialog step.

With database changes from the dialog, your program must set and release SAP locks itself.

The following order is recommended:

Lock data
Read data
Update data on the database
Release locks

Note that the lock entries must be deleted by a program. To do so, you can either call up the lock modules of the object for releasing DEQUEUE_ or the function module DEQUEUE_ALL. For more detailed information, consult the function module documentation.

Database updates from the dialog can be executed in bundled form by using the special subroutine technique PERFORM ON COMMIT.

The statement PERFORM ON COMMIT registers the subroutine that has been called up. This will not be executed until the next COMMIT WORK statement is reached.

If the database updates are encapsulated in the subroutines, they can be separated from the program logic and relocated to the end of the LUW processing.

Each subroutine registered with PERFORM ON COMMIT is only executed once per LUW. calls can be made more than once (no errors); the subroutine, however, is only executed once.

From release 4.6 onward nested PERFORM ON COMMIT calls lead to a runtime error.

The COMMIT WORK statement carries out all subroutines registered to be executed and triggers a database commit (ends the DB LUW).

Unlike normal subroutines, those that you call using the ON COMMIT addition do not have an interface. They work instead with global data, that is, the values of the data objects at the point where the subroutine is actually run. This can also include Imports from memory.

The PERFORM ... ON COMMIT technique can also be used in the update. This will be discussed later.

For further information, see the ABAP Editor Keyword documentation for the term PERFORM.

Update techniques allow you to separate user dialogs from the database changes. Both are executed by different programs, which generally run in different work processes.

You work with a program that manages the user dialogs. It is referred to as a dialog program.

You use a so-called update program that updates the data received by the dialog program on the database. No dialogs run in the update programs.

Step 1: The dialog program receives the data changed by the user and writes it to a special log table.

The entries in this table function as requests. The data contained in the log table will be written to the database later by the update program.

A dialog program can write several entries to the log table.

The entries in the log table represent an LUW, in other words they will either be executed on the database together or not at all (all-or-nothing principle).

Step 2: The dialog program completes the logical data packet that was written to the log table. The SAP LUW finishes in the dialog part and informs the Basis system that a packet needs to be updated.

Step 3: A basis program reads the data associated with the LUW from the log table and supplies it to the update program.

Step 4: The update program accepts the data transferred to it and updates the database entries.

Step 5: If the update program runs successfully, a Basis program deletes all entries for the LUW from the log table.

In the event of an error, the entries remain in the log table and are flagged as errored.

The option of informing users by mail that an update action has failed can be set using the profile parameters rdisp/vb_mail_user_list and rdisp/vbmail.

The parameter rdisp/vbmail can be set to '0' (no mail is sent in the event of an error) or '1' (a mail is sent in the event of an error).

The rdisp/vb_mail_user_list parameter setting specifies who will be informed in the event of an error (rdisp/vbmail = 1) ($ACTUSER informs the user who generated the data record to be updated).

The monitor transaction for update orders is SM13.

The dialog program and the update program can be linked in various ways:

Asynchronously
Synchronously
Via a local update

Technical implementation of the update concept requires a so-called update program as well as the program that manages the user dialog. The update program tasks are carried out by special function modules called update modules.

Create an update function module by choosing the processing radio button property 'update module'.

Update modules, like other function modules, have an interface for transferring data. The interface for update function modules only includes IMPORTING and TABLES parameters. These must be typed using reference fields or structures.

Export parameters and exceptions are ignored in update modules.

The function module contains the actual database update statements.
The entries in the log file are generated from the dialog program. They are generated by calling up the associated update function module. The function module must be called using the addition IN UPDATE TASK. This ensures that the module is not executed immediately. Instead, the current data from the function module interface is written to the log table.

For every CALL FUNCTION ... IN UPDATE TASK statement in the dialog program, the system generates an entry in the log table containing the name of the update function module and the associated parameters.

All of the update requests in an SAP LUW are stored under the same update key (VB key). The update key is a unique key.

When the system reaches the next COMMIT WORK statement, a log header is generated for the corresponding log entries, concluding the set of update entries for that SAP LUW. The log header contains information on the dialog program that wrote the log entries, as well as information on the update modules to be executed.

As well as the header entry, the ABAP command COMMIT WORK ensures that the dispatcher process is informed about the availability of a further update packet.

In a dialog consisting of several steps, you can store multiple entries in the update log table that are then processed following the ABAP COMMIT WORK command.

However, you may also need to delete the update requests of the current SAP LUW using a ROLLBACK WORK statement.

In a ROLLBACK WORK statement, the system:

Deletes all form routines registered using PERFORM ON COMMIT
Deletes all database update requests from the log
Triggers a rollback on the database, followed by a database commit
Starts a new SAP LUW

With relation to database changes already completed in the dialog, the ROLLBACK WORK statement means that all changes in the current database LUW are undone.

The ROLLBACK WORK statement deletes all lock entries generated up to now from the dialog program.

The ROLLBACK WORK statement does not affect the program context, in other words all data objects (program-specific objects and objects from function groups that may be used) remain unchanged, and they are NOT reset.

You can generally only reset the data objects of your program by ending the dialog program.
Therefore, you should not use the ROLLBACK WORK statement directly. Instead, trigger an implicit rollback by sending a termination message (type A). This ensures that all of the data from the program is also reset when the program terminates.

The task of an update module is to pass the requests for database updates to the database and to evaluate their return codes.

If the database cannot successfully complete an update; the update function module must be able to react.

If you want to trigger a database rollback in the update task, you can use a termination message. This triggers an implicit database rollback.

The rollback ends the update task. The log entry belonging to the SAP LUW is flagged as containing an error. The termination message is also entered in the log.

The system sends an express mail to the relevant user, telling him or her that the update was unsuccessful. You can examine the log entry by using Transaction SM13.

You may not use the explicit ABAP statements COMMIT WORK or ROLLBACK WORK in an update module.

If your program is to run using locks, you must record the locks in the lock table. These are inherited by the update modules with the ABAP command COMMIT WORK and can then no longer be accessed by the dialog program.

To ensure that the update modules run with the protection of locks, the lock entries must not be released before the COMMIT WORK.

You do not need to release the locks explicitly in the update modules, since they are automatically released at the end of the update process by a basis program.

The locks are also released if one of the update modules triggers a database rollback by sending a termination message.

If the update modules allow failed update requests to be reprocessed (see V1 update), you should note that the data in the database tables at the point of reprocessing may be different from that at the point of the failed update attempt. Reprocessing failed update requests is only useful if the data to be updated is not dependent on the database status (e.g. writing of a document failed because of a table space overflow).

Failed update requests are reprocessed without locks.

In asynchronous update, the dialog program and update program run separately.
The dialog program writes the update requests into the log table VBLOG in the database.

You conclude the dialog part of the SAP LUW with the COMMIT WORK statement. A new SAP LUW immediately starts in the dialog program, which can carry on processing user dialogs without interruption. The dialog program does not wait for the update program to finish.

The update program is run on a special update work process. This need not be on the same Server as the corresponding dialog work process.

The SAP LUW that began in the dialog program is continued and then closed by the update Program.

The log table VBLOG can be implementing as a cluster file in your system, or be replaced with the transparent tables VBHDR, VBMOD, VBDATA, and VBERROR.

Asynchronous updates are useful in transactions where the database updates take a long time and the "perceived performance" by the shorter user dialog response time is important.

Asynchronous update is the standard technique for dialog processing.

The entries that have a HEADER can be analyzed in SM13.

In local update, the update functions are run on the same dialog process used by the dialog program containing the COMMIT WORK statement.

To do this, you must include the SET UPDATE TASK LOCAL statement in the dialog program.
The effect of this is that update requests are kept in main memory rather than being written into table VBLOG in the database.

When the system reaches the COMMIT WORK statement, the corresponding update modules are processed in the dialog work process currently being used by the dialog program. If all of the update modules run successfully, a database commit is triggered. If not, a database rollback occurs.

Once the update function modules have been processed, the dialog program resumes with a new SAP LUW.

The SET UPDATE TASK LOCAL flag can only be set if no other update requests were generated for the same LUW before the program was called up.

The SET UPDATE TASK LOCAL flag is effective until the next COMMIT WORK or ROLLBACK WORK command.

With synchronous updates, the dialog program waits for the end of the update modules. The dialog program does not begin to process the new SAP LUW until the update modules have terminated.

To switch from asynchronous to synchronous update, use the AND WAIT addition in the COMMIT WORK statement.

The entries that have a HEADER can be analyzed in SM13.

Asynchronous update is useful in transactions where subsequent user dialogs do not depend on the database updates being made immediately. Once the update task has been called, control returns directly to the user.

Local update is particularly useful for processing dialog transactions in the background. There is no contact with the database table VBLOG, and if the program is running alone on the server, local update is faster than either synchronous or asynchronous update. If, as is the usual case, several users are using the server, the speed of the program depends on the total server load.

Synchronous update is useful in transactions where you want to use the advantages of update techniques (logging, opportunity to reprocess failed update requests), but where subsequent user dialogs nevertheless do depend on the results of the update. One particular application for this technique is in "transactions within transactions" - where one transaction uses other transactions as modularization units (CALL TRANSACTION ).

When you use this method, you can determine in the call the update technique that you want the transaction to use. For further information, see the keyword documentation in the ABAP Editor for the term CALL TRANSACTION.

Update function modules can be separated into two groups. The group determines when the function module is processed: Function modules that are classified as V1 can be further divided into two subclasses: Start immediately or Start immediately, no restart. V2 function modules are processed asynchronously after all V1 update modules have finished running.

If you have used the Start immediately (V1) option, you can update any records that contained errors manually, using Transaction SM13. If you use the Start immediately, no restart (V1) option, this is not possible. V2 update function modules (Start delayed) can always be manually updated.

V1 update function modules do not normally run using the SAP lock concept. In other words, the V1 update program is executed with the protection of the locks from the dialog program.

Any lock entries are released at the end of the V1 update. V2 update function modules always run without logical locks.

You can also classify an update module using attribute 'Coll. run' (collective run). This option is used SAP internal only (special form of V2 update, asynchronously, start via program RSM13005).

The flow diagrams discussed up to now all deal with V1 updates.

Update requests for V2 update modules are also generated by the dialog program.

V1 update modules generate update requests in table VBLOG in synchronous and asynchronous update, and in main memory in local update.

V2 update modules generate entries in VBLOG and always run asynchronously.

V1 update modules are handled by the system with priority and are executed before the V2 update requests.

V1 updates can be performed synchronously, asynchronously, or locally.

V2 update function modules are not processed until all V1 update function modules have been successfully processed.

The V2 update function modules run in a separate DB LUW. They are executed in a V2 update work process. If there are no V2 update work processes set up in your system, the V2 update function modules run in a V1 update work process.

Once all of the V2 update function modules have been executed successfully, the V2 update requests are deleted from VBLOG.

If an error occurs in a V2 update function module to which the function module reacts with a termination error message, the system triggers a database rollback. All of the V2 changes in the SAP LUW are undone and an error flag is set in table VBLOG for all of the V2 update requests.

V2 update function modules run without SAP locks.

The division between V1 and V2 update function modules allows you to set 'high priority' and 'low priority' updates.

V2 update function modules are used for low-priority tasks, such as writing statistics to the database.

The locks generated in the dialog program are usually inherited by the V1 update modules when the update takes place. This is controlled by the SCOPE interface parameter of the lock modules. When SCOPE = 2, the V1 update programs inherit the locks that are set in the dialog program.

2 is the default setting for SCOPE when you call a lock module.

You do not need to release the locks explicitly in your program, since they are automatically released at the end of the V1 update process.

The locks are also released if one of the V1 update modules triggers a database rollback by sending a termination message.

An SAP LUW maps updates, which are logically related and usually involve several dialog steps, to a database LUW. The database updates are encapsulated via update modules.

SAP LUWs are supported specifically by R/3:

Locks (Scope = 2)

The CALL FUNCTION IN UPDATE TASK call mechanism

The command COMMIT WORK

Type 'A' or 'X' dialog messages.

An SAP LUW can be divided into three phases (three-phase model).

Dialogs, user entries, and their input checks take place in phase 1. Calls of update modules are not allowed here, since they might be registered more than once during an error dialog (E message).

Phase 1 ends when the first update module is called. The data to be updated must be held in global program data during phase 1.

Preparations for database updates take place in phase 2. Phase 2 begins when the first update module is called and ends with the COMMIT WORK statement. The system must now respond to any errors with a type 'A' or 'X' dialog message. The COMMIT WORK that concludes phase 2 should only be set at the top level if call hierarchies are used, since the lower-level modularization units in the hierarchy are not aware of the status of the program context.

The database updates are performed in phase 3. The system must always respond to any errors in phase 3 with a type 'A' or 'X' dialog message. This leads to a ROLLBACK of the complete database LUW as well as a termination of the update.

Local update processing is activated using the ABAP command SET UPDATE TASK LOCAL. The update type can only be changed if it is processed before the first update module is called.

With local updates, the update modules are executed in the dialog work process that is currently performing the SAP LUW.

As is the case with synchronous updates; the user must wait while the update modules are being executed.

Local updates should be used for:
Transactions that are carried out in the background (batch) (CALL TRANSACTION USING)
Exception: If UN buffered number assignments and higher parallel processing is requested at the same time.

Dialog transactions with very few database changes (3 - 5 statements) for which the dialog behavior is not critical.

Note that the fewer the number of users making changes simultaneously, the better the response time of the database.

Synchronous updates are triggered by the ABAP statement COMMIT WORK AND WAIT. With a synchronous update, the update modules are executed in an update work process.

Unlike asynchronous updates, the dialog part of the transaction is stopped while the update modules are being executed.

The success or failure of the update is displayed in system field sy-subrc once the update has been completed.

For every action on the database that prompts table updates, the record to be changed is locked physically by the database. The same applies if you are reading with SELECT ... FOR UPDATE.

Other users cannot change the same data for the duration of the lock.

To reduce the lock duration on the database, you should use the following rule to program the database updates carried out by the update modules:

First, new table entries should be created. These present the smallest 'problem' for the other users.

You should then perform table updates that are not critical to performance. As a rule, these are the tables that are accessed 'simultaneously' by as few users as possible.

Tables that are central resources in the system (which many users access at once) should always be changed as late as possible.

To lock the central tables (performance critical) for as short a time as possible, you can use PERFORM uprog ON COMMIT in the update.

For this purpose, encapsulate the changes to the central tables in FORM routines and call these up in the update using PERFORM ON COMMIT. The FORM routines are then not executed until the last update module has been processed.

After the last update module has been processed; a program executes the ABAP command COMMIT WORK, which then performs the FORM routines registered in the update.