Table Buffering in Application Server

In this blog, i will be explaining how table buffering actually works in the SAP Application Server practically.

Let’s get started.

Every one knows about what is table buffering and how it reduces the time required to access the data from the data base table by buffering the frequently accessed data, but have you ever given a thought about following questions:

How system decides that i need to fetch modified fresh data from the table, instead of fetching from buffer ( in case of any buffered data modification happened recently)?

How system synchronize the different application server buffer and the related operations performed on the corresponding buffered data?

When the buffered table data get invalidated? and what is the significance of where clause in Insert,Delete,Modify/Update operation in terms of dealing with buffered data?

How to view the buffered table data?

if not, don’t worry this blog here aims to answer above questions and related concerns.

First of all lets summarize the basic points which is needed to proceed further with the table buffering:

1. We can choose buffering to be activated or not for the database table in the technical settings of the table,
2. Once buffering is activated we can also choose specific type for buffering (Single,Full and Generic area buffering),
3. Every SAP application server in a system holds it’s own separate table buffer.

Buffer Synchronization:

Now since each Application Server(AS) hold the separate buffer, for synchronization of the buffer across the AS, system uses central table called DDLOG, which is central for all the AS and holds entries for all the AS buffer regarding the type of operation performed to corresponding AS buffered table data.

for example : say you have table Tab1 with buffering is activated, for this table whenever you insert an entry or delete/modify the buffered table data, an entry will be inserted into the DDLOG table saying that a record or group of records have been changed or inserted to the table from a particular AS. You can view this data from Tcode AL12 as shown in following snapshot with menu path AL12->Monitor->Synchronization–>DDLOG records.

After some time(default 120 seconds) which we call as synchronization time, all the AS checks these entry in DDLOG table and modify their buffer accordingly for the change.

In the above snapshot highlighted the synchronization time whihc i 120 sec, and we can also see different type of buffers we have in bottom left corner, we will be concentrating in this blog only “Table Buffer”.

In the above snapshot we can see the navigation path for seeing DDLOG table , once you navigate to the above path you will get a screen to choose synchronization class that is single record buffering , generic area buffering etc.., select single and generic as i have interested in table buffering and choose Continue.

and following is the snapshot of DDLOG records saved for each change of buffer from different AS, you can see different mode of operation(highlighted) performed on the table like “DEL”->Delete, INS-Insert, etc.

Now We will do Insert,Delete and Read operation on table with buffering activated and observe the changes in the DDLOG table , buffer content, and flags used to maintain synchronization between different AS buffers.

INSERT:

We first create table called ZBTEST with single buffering activated and insert few entries directly from the SE11.

now DDLOG table contains entries as follows:

Note : We have SE38 RSTUNE60 report also, which can be used to read/display DDLOG table records, upon executing the report–> Select server as None–> then choose execute

In the above snapshot select TABL and TABLP which are single record and full/generic buffering for the table, then choose “Read DDLOG” option which show the DDLOG records as follows:

In the above snapshots you can see inserting 5 entries into the table created 5 entries in DDLOG table with KEY, and related the application server (AS) name.

Now why DDLOG table has to hold record for inserting into table?, what is the impact of these records on buffering of table?

To answer the above question, we must understand that whenever a request is made for reading the non-existent key/entry from the table, that key combination will be marked as non-existent in Buffering object list of the respective AS buffer, so that if the request is made again for the same key combination, system will directly decided by seeing this non-existent flag in buffer instead of going into table, then search, then inform that this key/entry not present in the table.

Consider the below example where i have tried to access/read the non-existent record that is EMP_ID = 11 from the table ZBTEST, now to see where the flag set for this non-existent record in buffer, you can navigate to single object display in “AL12->Monitor->Buffer->Table Buffer->Single Object Display”

once you navigate to the above options, you will see below screen:

You can observe in the above snapshot, for the field Non-Ex.. value ‘1’ is kept indicating there is one non-existent record has been accessed. I am not sure how to see the key which is marked as not exist in table in AL12, any one can comment on this for the same here.

Now I will insert a record to table ZBTEST with key EMP_ID = ’11’ from SE11

and now if you see the “Single Object Display” in the below snapshot that the value initially kept to indicate that there is non-existent record is now cleared out.

So conclusion, is inserting the record into the table will never update the buffer of any AS, only DDLOG table will get update from the current AS(from which you inserted the data,) for the purpose of marking non existing key as existing in buffer( “Single Object Display”). After buffer synchronization time , all the other AS buffer will synchronize for the non-existent flag of one AS buffer.

2. Read :

Whenever we access the records of the table(with buffering activated), the table data will get buffered in current Application Server buffer. Buffering the data depends on the type of buffering you have chosen in technical settings of the table whether it’s Single , Full or Generic buffering.In case of Single only those record which has been accessed will in buffer, in case of Full buffering accessing any of the record make the whole table data gets buffered and with Generic Area buffering it depends on number key you have mentioned.

We now Read the table records/data from the simple select query from SE38 report, then check the buffer content, and Single object display of the buffer.

–> First we will see the buffer content before reading just to confirm that buffer is not holding any data. you can see the buffer content by following the path show in following snpashot:

After selecting the above options, you need to give the table name for which you want to see the buffer data in the following dialog and have to choose “Continue”.

Now the buffer content can be viewed as in the following snapshot:

So it is confirmed that there is no data buffered yet for the table ZBTEST.

–> Now we will read the key EMP_ID = 1 from table ZBTEST(with Single buffering activated) by following query from SE38 report

SELECT SINGLE *

FROM zbtest

INTO @DATA(lw_emp)

WHERE emp_id = 1

After successful reading of the record, now if we check the buffer content you can see one record in the buffer as follows :

Please note that as i have activated single record buffering for the table only one record got buffered which is accessed, but if you would have used Full buffering then whole table records would have been buffered.

To conclude, reading the table record will always do buffering of the table data/record in buffer considering the type of buffering currently being used. Just to be clear here , only current AS buffer will get updated from which you tried to read the data , and all the other AS buffer will still be empty even after buffer synchronization time, and other buffers will separately hold the buffered data when read access will be made for for the table from the corresponding AS. Reading the data from the table will never update DDLOG table.

3. Delete/Update:

Here comes deleting the buffered data. Let’s see how deleting of buffered data behaves.

First of all let’s make our both AS buffer contains some data in their buffer ,in my case it should be same data say its key EMP_ID = ‘1’, by reading those entry via select statement from respective AS and then will try to delete from one AS and see how it gets synchronized in other AS buffers.

1.Read same content that is key ‘1’ from table ZBTEST from both the AS ‘201’ and ‘202’ by running report with following query:

SELECT SINGLE *

FROM zbtest

INTO @DATA(lw_emp)

WHERE emp_id = 1.

Note: you can switch to other AS by tcode – SM51-> select AS you want to enter into to by clicking on link of the name of AS as shown in below snapshot, my system has only two AS 201 and 202, the number of Application servers depends on configuration settings from BASIS.

2.buffer content of AS 201 and 202 after query execution from the AS 202.

Just to remind you here again, the AS 201 will never hold any buffered data(until unless you read data from 201)just because of AS 202 have some data in its buffer,

So this time we read data from from AS 201, below is the snapshot of buffer content of 201 after query execution from 201:

So now our both Application server 201 and 202 buffer is having common data that is record with key ‘1’.

3. Now let’s do deleting of this ‘1’ record from table from 201 first from report with following statement.

Delete from ZBTEST where emp_id = 1.

After execution of above statement, entry got deleted in table as shown in belwo snapshot:

Single object displays below. you can see state pending (switched from state ‘Valid’) indicating that some action is pending. The below snapshot is from AS 201.

DDLOG display from RSTUNE60 report:

In the above snapshot you can see that there an entry from AS 201 with ‘INV’ Funtion for the table ZBTEST, where INV means invalidated, in above snapshot the whole buffer is invalidated, that is the reason of not having any particular buffer key INV entry.

Note : this invalidating of the buffered data happened only in AS 201, we need to wait for buffer sync time to get other AS buffers gets invalidated by reading the DDLOG table entries.

Now one interesting and curious question comes into mind here is why the whole buffered data gets invalidated though i have tried to delete only one entry ( with where EMP_ID= ‘1’ in select query), and also just to note here my table ZBTEST is having “Single Record Buffering” activated? So  what to do you think?, i also stumbled upon this point, but later i have found the below link from help.sap, which answered the above question.

If changes are made with WHERE conditions (UPDATE dbtab WHERE …, DELETE FROM dbtab WHERE …) to the table having buffering activated , the entire table is invalidated in the buffer of the local server (server on which the command was submitted) and on all other servers at the time of the synchronization.

How do you verify the statement “and on all other servers at the time of the synchronization.”?

Answer is you wont get any extra record in DDLOG for AS 202 as we got for AS 201(because from there you had executed delete statement).

so how to verify, it is by observing the Single object display of each  AS, let’s check that!!

Initially i have shown you that 202 buffer Single Object Display switched from Valid to Pending when you deleted the entry from the table. After waiting for synchronization time of 120 seconds i can see the same change in AS 201 as well as shown below:

Note : the state field in the above snapshot can have following values :

Important Note:

Invalidation of table buffer means that next access to the table should load fresh content directly from DB table for the specific key.

In my case i have just deleted EMP_ID = 1  entry with where condition in delete statement, from the table, because of that the entire table buffer gets invalidated, now i will insert the same key entry again from SE11 to table ZBTEST, this time I will try to read it from program with the same query as before, since buffer is already invalidated because of delete, it should now hit the db for accessing the record, which I will be showing with SQL and Buffer trace from ST05.

After activating the trace, i tried to read the key ‘1’ from table, then deactivating and display trace.

i can see the below is the trace generated.

DB connection R/3 means, database access has done. If DB connection is empty, then its buffer access.

Lets take another trace now for the reading of key ‘1’ again second time.

No ‘R/3’ in DB connection and No DB cursor found., so this time it read from buffer and observe time is also less.

When I double click on above line I got the following

So to conclude, for delete operation: when you delete the buffered data/entry from the table, the current application server buffer metadata state will go to ‘Pending’ state from ‘valid’, and in DDLOG table entry will made with current AS and with function mode as ‘INV’(invalidate) without key (if your delete statement used contain where clause, then entire buffer will be invalidated, else if your delete statement executed has no where clause,  and using index if you are trying to delete then, only that single record will get invalidated and DDLOG table will be having record with ‘INV’ with key for single record), and at the time of synchronization all the other AS read DDLOG table entry , their buffer will get invalidated.