Database Update Techniques Database Update Techniques OBJECTIVE Transactions

Database Update Techniques

Database Update Techniques OBJECTIVE ( ( ( Transactions in SAP system Update Bundling Techniques Programming Bundling Updates. Unbundled Updates Local Updates

Database Update Techniques PROGRAMMING DATABASE UPDATES To program database updates effectively, programmers are mainly concerned with: ( ( Maintaining database Correctness. Optimizing response times for users. For a quick introduction before programming updates, see: ( ( ( Transaction in the SAP system. Introduction to Update Bundling. Introduction to SAP Locking

Database Update Techniques TRANSACTIONS IN THE SAP SYSTEM ( If the transaction runs successfully, all changes should be carried out. ( If the transaction encounters an error, no changes should be carried out, not even partially. In the database world, an "all-or-nothing" transaction is called an LUW (Logical Unit of Work). There are three types of LUW’s. ( ( ( Database LUW SAP LUW ABAP/4 Transaction

Database Update Techniques DATABASE LUW The SAP System triggers database commit operations automatically at every screen change. Database LUW lasts (at longest) from one screen change to the next.

Database Update Techniques SAP LUW As a logical unit, update transactions should be executed entirely, or not at all. In general, an update transaction usually spans several database LUWs and is closed at the ABAP/4 level with a COMMIT WORK command.

Database Update Techniques ABAP/4 TRANSACTION An ABAP/4 or SAP transaction is an application program that you start using a transaction code. It may contain one or more SAP LUWs. Whenever the system reaches a COMMIT WORK or ROLLBACK WORK statement that is not at the end of the last dialog step of the SAP transaction, it opens a new SAP LUW. ( With one or more SAP LUWs. Transactions in this form consist entirely of processing blocks (dialog modules, event blocks, function module calls, and subroutines). You should be careful to ensure that external subroutines or function modules do not lead to COMMIT WORK or ROLLBACK WORK statements accidentally being executed.

Database Update Techniques ( By inserting an SAP LUW The ABAP statements CALL TRANSACTION (start a new transaction), SUBMIT (start an executable program), and CALL FUNCTION. . . DESTINATION (call a function module using RFC) open a new SAP LUW. When you call a program, it always opens its own SAP LUW. However, it does not end the LUW of the SAP transaction that called it. This means that a COMMIT WORK or ROLLBACK WORK statement only applies to the SAP LUW of the called program. When the new LUW is complete, the system carries on processing the first SAP LUW.

Database Update Techniques ( By running two SAP LUWs in parallel The CALL FUNCTION. . . STARTING NEW TASK statement calls a function module asynchronously in a new session. Unlike normal function module calls, the calling transaction carries on with its own processing as soon as the function module has started, and does not wait for it to finish processing. The function call is asynchronous. The called function module can now call its own screens and interact with the user.

Database Update Techniques INTRODUCTION TO UPDATE BUNDLING With Update Bundling you can execute updates at the end of the update transaction, rather than at every screen change. ABAP/4 provides commands for bundling updates in special update routines. ( You can avoid your updates being committed at each screen change. ( You can lock the objects to be updated across multiple screens.

Database Update Techniques SUMMARY OF BUNDLING TECHNIQUES With update bundling, you package your updates in special routines that run only when your program issues a ABAP/4 commit/rollback. To do this, you use: ( PERFORM ON COMMIT ( CALL FUNCTION IN UPDATE TASK ( CALL FUNCTION IN BACKGROUND TASK These statements specify that a given FORM routine or function module be executed not immediately, but rather at the next ABAP/4 commit /rollback. An ABAP/4 commit is an ABAP/4 statement that triggers a database commit, but also perform other functions. The ABAP/4 statements for performing these commits and roll backs are: ( COMMIT WORK ( ROLL BACK

Database Update Techniques BUNDLED UPDATING IN THE DIALOG TASK The PERFORM ON COMMIT statement calls a form routine in the dialog task, but delays its execution until the system encounters the next COMMIT WORK statement. Using PERFORM ON COMMIT: You can bundle updates for dialog-task processing and have them execute in the last LUW for the transaction. To do this: 1. Place all update statements (insert, update, Modify, Delete) in a FORM routine. 2. Call the transaction with PERFROM <form> ON COMMIT>

Database Update Techniques The effect of using PERFORM ON COMMIT, Instead of simple perform, is that the system delays execution of the form routine until it encounters the COMMIT WORK statement. You should position the commit WORK at the end of the statement or wherever user can select the save condition. ASSIGNING RUN PRIORITIES TO FORM ROUTINES: You can assign a run priority to each form routine by adding the level parameter to the PREFORM ON COMMIT statement. For Ex. PERFORM update_table 1 ON COMMIT LEVEL 2. …. PERFORM update_table 2 ON COMMIT LEVEL 3. …. PERFORM update_table 3 ON COMMIT LEVEL 1.

Database Update Techniques When this reaches COMMIT WORK statement, the FORM routines will run in this order. First : Update_table 3. Second : Update_table 1. Third : Update_table 2.

Database Update Techniques BUNDLED UPDATING IN THE UPDATE TASK The CALL FUNCTION IN UPDATE TASK statement logs a function module for execution in the update task. The subsequent COMMIT WORK statement triggers actual execution. You can perform updates in the dialog task as well as in the update task. Dialog task updates are synchronous updates. Update task updates are asynchronous. (An exception is You can trigger an update function with COMMIT WORK AND WAIT: this is synchronous. )

Database Update Techniques The following diagram illustrates the Synchronous Update in Update Task

Database Update Techniques CREATING UPDATE FUNCTION MODULES To create a function module, you first need to start the Function Builder. Choose Tools - ABAP Workbench, Function Builder. To be able to call a function module in an update work process, you must flag it in the Function Builder. When you create the function module, set the Process Type attribute to one of the following values: · Update with immediate start Set this option for high priority ("V 1") functions that run in a shared (SAP LUW). These functions can be restarted by the update task in case of errors. · Update w. imm. start, no restart Set this option for high priority ("V 1") functions that run in a shared (SAP LUW). These functions may not be restarted by the update task.

Database Update Techniques · Update with delayed start Set this option for low priority ("V 2") functions that run in their own update transactions. These functions can be restarted by the update task in case of errors. To display the attributes screen in the Function Builder, choose Goto - Administration. DEFINING THE INTERFACE Function modules that run in the update task have a limited interface: · Result parameters or exceptions are not allowed since task function modules cannot report on their results. · You must specify input parameters and tables with reference fields or reference structures defined in the ABAP Dictionary. update-

Database Update Techniques CALLING UPDATE TASK FUNCTION MODULES: Function modules that run in update task can run synchronously or asynchronously. ( COMMIT WORK: Your program does not wait for the requested functions to finish processing, which specifies asynchronous processing. ( COMMIT WORK AND WAIT Your program will wait for the requested functions to finish processing. Control returns to your program after function module run successfully. Which specifies synchronous processing.

Database Update Techniques CALLING UPDATE FUNCTIONS DIRECTLY To call a function module directly, use CALL FUNCTION IN UPDATE TASK directly in your code. CALL FUNCTION 'FUNCTMOD' IN UPDATE TASK EXPORTING. . . For EX: a = 1. CALL FUNCTION 'UPD_FM' IN UPDATE TASK EXPORTING PAR = A. . . a = 2. CALL FUNCTION 'UPD_FM' IN UPDATE TASK EXPORTING PAR = A. . . a = 3. COMMIT WORK. Here, the function module UPD_FM is performed twice in the update task: the first time, with value 1 in PAR, the second time with value 2 in PAR.

Database Update Techniques ADDING UPDATE TASK CALLS TO A SUBROUTINE You can also put the CALL FUNCTION IN UPDATE TASK into a subroutine and call the subroutine with: PERFORM SUBROUT ON COMMIT. If you choose this method, the subroutine is executed at the commit. Thus the request to run the function in the update task is also logged during commit processing. As a result, the parameter values logged with the request are those current at the time of the commit.

Database Update Techniques a = 1. PERFORM F ON COMMIT. a = 2. PERFORM F ON COMMIT. a = 3. COMMIT WORK. FORM f. CALL FUNCTION 'UPD_FM' IN UPDATE TASK EXPORTING PAR = A. ENDFORM. In this example, the function module UPD_FM is carried out with the value 3 in PAR. The update task executes the function module only once, despite the two PERFORM ON COMMIT statements. This is because a given function module, logged with the same parameter values, can never be executed more than once in the update task.

Database Update Techniques TRANSACTION THAT CALL UPDATE TASK FUNCTIONS If your program calls another program that itself calls an update function module, you should be aware of the following: When the new program is called, a new SAP LUW begins, and a new update key is generated. This key is used to identify all update-task operations requested during the called program. When returning from the program, the LUW of the calling program is restored together with the old update key. If the called program does not contain its own COMMIT WORK, the database update requests are not processed, and the update function modules are not called. In the following example, F 1, F 2, and F 3 are update function modules:

Database Update Techniques Here, F 1 and F 3 are executed in the update task, because the COMMIT WORK for the main program triggers their execution. However, since transaction ZABC contains no COMMIT WORK statement, the function F 2 is never executed by the update task.

Database Update Techniques BUNDLED UPDATING IN A BACKGROUND TASK The CALL FUNCTION IN BACKGROUND TASK statement logs a function module to run in a background task. Normally, this statement is used to execute functions on remote hosts (by specifying an additional DESTINATION parameter). For Example if you are maintaining a replicated data in two databases. While making the primary updates in the update task of the local system, you would also execute the same updates on a remote database. This system uses RFC to send request to the remote system. To execute updates on a remote host, use the statement: CALL Function <fctmode> IN BACKGROUNG TASK DESTINATION <dest>

Database Update Techniques This statement requests execution of an update function in an external system. The destination specifies the remote system. On encountering this statement, the system writes your request to the log table and then executes it at the next commit statement. You can also use this statement without the DESTINATION parameter. In this case , the function runs in a separate work process, but on the local host. All remote host background task functions triggered with same COMMIT WORK run together in a common LUW.

Database Update Techniques ERROR HANDLING FOR BUNDLED UPDATES Runtime errors can occur during execution of bundled updates. How are they handled? In general, COMMIT WORK processing occurs in the following order: All dialog-task FORM routines logged with PERFORM ON COMMIT are executed. All high-priority (V 1) update-task function modules are executed. The end of V 1 -update processing marks the end of the. If you used COMMIT WORK AND WAIT to trigger commit processing, control returns to the dialog-task program. All low-priority (V 2) update-task function modules are triggered. All background-task function modules are triggered.

Database Update Techniques Runtime errors can occur either in the system itself, or because your program issues an termination message (MESSAGE type ‘A’). Also, the ROLLBACK WORK statement automatically signals a runtime error. The system handles errors according to where they occur: In a FORM routine (called with PERFORM ON COMMIT) - Updates already executed for the current update transaction are rolled back. - No other FORM routines will be started. - No further update-task or background-task functions will be started. - An error message appears on the screen.

Database Update Techniques In a V 1 update-task function module (requested IN UPDATE TASK) - Updates already executed for V 1 functions are rolled back. - All further update-task requests (V 1 or V 2) are thrown away. - All background-task requests are thrown away. - Updates already executed for FORM routines called with PERFORM ON COMMIT are not rolled back. - An error message appears on the screen, if your system is set up to send them

Database Update Techniques In a V 2 update-task function module (requested IN UPDATE TASK) - Updates already executed for the current V 2 function are rolled back. - All update-task requests (V 2) still to be executed are carried out. - All background-task requests still to be executed are carried out. - No updates for previously executed V 1 or V 2 function are rolled back. - No updates previously executed for FORM routines (called with ON COMMIT) are rolled back. - An error message appears on the screen, if your system is set up to send them

Database Update Techniques In a background-task function module (requested IN BACKGROUND TASK DESTINATION) - Background-task updates already executed for the current DESTINATION are not rolled back. - All further background-task requests for the same DESTINATION are thrown away. - No other previously-executed updates are not rolled back. - No error message appears on the screen. If your program detects that an error in remote processing has occurred, it can decide whether to resubmit the requests at a later time.

Database Update Techniques UNBUNDLED UPDATES You can make database updates in ABAP/4 without these bundling Here You can place update statements (insert, Modify, DELETE) directly in your code. These are inline updates without using any bundling techniques. Even if you code No COMMIT WORK statement the database commit at the next screen change will commit the updates to the database. This inline update is only suitable for the single screen transactions. With Multiple screen transactions, if any error occurs in later screens, you can't roll back the data committed in earlier screens.

Database Update Techniques Advantages and Disadvantages: ( Inline updating may feel simpler and more natural. ( PERFORM ON COMMIT may make more efficient use of data resources, since all accesses are performed at one time.

Database Update Techniques LOCAL UPDATES In ABAP/4 You can tell the system to perform updates locally, rather than in the update task. To do this, use the statement SET UPDATE TASK LOCAL whenever your program runs in background. You can check the task from your code by querying the SY-BATCH system variable. IF SY-BATCH NE SPACE. SET UPDATE TASK LOCAL. ENDIF. … CALL FUNCTION UPDATE_TABLE 1 IN UPDATE TASK. … CALL FUNCTION UPDATE_TABLE 2 IN UPDATE TASK. … COMMIT WORK. LOCKING IN THE SAP SYSTEM Please refer the Locking concepts in DATA DICTIONARY.

Database Update Techniques SUMMARY ( Transaction is an operation that lets the user make changes to the database. The operation must be carried out in “all or nothing fashion”. ( The different transactions in SAP are Database LUW, SAP LUW and ABAP Transactions. ( The ABAP/4 bundling techniques let you distribute your updates to different work processes. ( We have seen the bundling techniques in different updates like dialog task, update task and Background task. ( ABAP/4 provides commands for bundling updates in special routines like PERFORM ON COMMIT, CALL FUNCTION IN UPDATE TASK and CALL FUNCTION IN BACKGROUND TASK. Execution of these routine is delayed until your program issues an explicit “SAP COMMIT” ( In a local update, the update program is run by the same work process that processed the request.

Database Update Techniques EXERCISE: 1. All or Nothing transaction is called a ______ 2. Different types of transaction in SAP are _____________ 3. A logical unit consisting of dialog steps, whose changes are written to the database in a single database LUW is called_______ 4. The ________ statement calls a form routine in the dialog task. 5. ABAP/4 bundling techniques routine is triggered by ______ statement. 6. Update task function module is triggered with ______ and _______ Statements. 7. Response time is longer with ________ processing. The different types of functions in update task processing are________________ 8. In update task COMMIT WORK specifies __________ Processing. 9. To Execute updates on a remote host the statement used is____________________.

Database Update Techniques SOLUTION: 1. Database LUW. 2. Database LUW, SAP LUW, ABAP transaction 3. SAP LUW 4. PERFORM ON COMMIT 5. COMMIT WORK 6. COMMIT WORK and COMMIT WORK AND WAIT. 7. Synchronous 8. Asynchronous 9. CALL Function <fctmode> IN BACKGROUNG TASK DESTINATION <dest>