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:
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.
Note: I use the sample database while providing the example. These examples are just for reference and can be used in different scenarios. For SAMPLE database, we don’t need to use these methods since the data is very low there.
1) Update n records at a time using GET DIAGNOSTICS:
We can write a stored procedure where inside while Loop we can use the update statement in little different way as follows:
UPDATE (SELECT * FROM Trnsct_tab WHERE isUpdated = 0 FETCH FIRST 10000 ROWS ONLY) SET Transactky = pmtlineitemky;
GET DIAGNOSTICS nCount = ROW_COUNT;
In above example, After every 10000 records COMMIT operation will be performed and the transaction log will get cleared. I used 10000 records at a time. We can use any number depending on our environment and parameter values.
2) Update/Delete n records at a time using commit-count method:
We can design a stored procedure and provide the input parameter to determine after how many records the commit operation should be performed. This is the preferred way I follow in almost all of my migration scripts even if I have a small doubt about the transaction log full. This is the most trusted way for me.
IN v_CommitInterval INTEGER
DECLARE v_cntcommit INTEGER DEFAULT 0;
DECLARE v_empNo INTEGER;
DECLARE SQLCODE INTEGER DEFAULT 0000;
DECLARE C1 CURSOR WITH HOLD FOR
SELECT empNo from EMPLOYEE;
FETCH C1 INTO v_empNo;
WHILE (SQLCODE <> 100)
SET workDept = ‘F004’
WHERE empNo = v_empNo;
SET v_cntCommit = v_cntCommit + 1;
— commit the database after certain number of transactions.
IF v_cntCommit > v_commitInterval THEN
SET v_cntCommit = 0;
FETCH C1 INTO v_empNo;
While Calling this stored Procedure user can specify the appropriate number after which he wish to commit the current transaction.
CALL PPANDE.BulkUpdateSP(500) — To perform the commit operations after every 500 transactions.