Manipulating bulk data using simple Stored Procedures

Being a developer of DB2 LUW, I had written many Migration scripts where I performed data manipulation on bulk data. Most known problem for such operations on bulk data is that most of the time we face transaction log full problem:

“SQL0964C The transaction log for the database is full”

This problem occurs frequently when the amount of data is huge. For example, consider a performance database or production database table having millions of customers and we need to manipulate all the customer data for some value.

To resolve this problem, DBAs usually configure the database parameters such as LOGBUFSZ, LOGFILSIZ, LOGPRIMARY and LOGSECOND. This approach is trail and error approach where we need to set the particular values and try for the operations. Understanding these parameters is not in the scope of this post.

You can get a brief overview of Managing DB2 Transaction Log Files in Ember Crook’s post. You can understand different parameters, settings and the potential issues from this post.

I will discuss the ways to perform such bulky operations using the PL/SQL Stored Procedures.

Continue reading