Blocking Data UpdatesOfficial Content

When throughput is an issue and you need to update, insert or delete a large number of records, reducing the number of roundtrips to the DBMS may be the answer.

Blocking database update operations (generic for insert/update/delete) entails storing them in memory and sending them in groups to the DBMS. Instead of interacting with the DBMS on every database update operation, interaction takes place only every N update operations, where N is a number specified by you.

Say, for example, that you have to print all the invoices that have not yet been printed, and that the InvoicePrintedFlag is used to store printing status. The corresponding code should look something like the one shown below. This code reads matching records and updates them one at a time. Every time the EndFor is reached (one iteration) a record is sent to the DBMS to be updated.

For each
     where InvoicePrintedFlag = 'N'
       PrintInvoice.Call( InvoiceNumber )
       InvoicePrinterFlag = 'Y'

Adding the Blocking clause, like in the code below, reduces the number of roundtrips to the server. In this case, a set of database updates are sent to the DBMS only every 100 times the EndFor is reached. This is usually faster than the previous code.

For each
     where InvoicePrintedFlag = 'N'
     Blocking 100
         PrintInvoice.Call(InvoiceNumber )
         InvoicePrinterFlag = 'Y'

The same happens when inserting a large number of records in a repetitive code such as the following:

For &i = 1 to &j
       New Blocking 100
             Att1 = ...
             Att2 = ...


This clause is not implemented for DB2



Subcribe to this category's changes
Sub CategoriesAdd a new subcategory in this category
PagesAdd a new page in this category
Was this page helpful?
What Is This?
Your feedback about this content is important. Let us know what you think.