Db2 (On Premises and Cloud)

 View Only

Data Integrity – The Hidden Requirement

By Martin Hubel posted Sat February 08, 2020 11:10 AM


This story applies directly to databases. How often do we review our bank statements to ensure that our transactions are correct? We look for correct amounts and for erroneous transactions charged to our account. This is our personal check to ensure that our finances are correct, or in database language, they have integrity.

Without integrity, people lose faith in the applications they use. To ensure integrity, there are a number of topics that must be addressed: data validation, recoverability, security, concurrency and the safe operation of the database. Applications must be designed and program such that integrity is maintained. If data is lost or inaccurate, other topics like performance become irrelevant.

Maintaining integrity takes work. In the same way as JR commented, database environments can be easier to build and faster if short cuts are taken and integrity is ignored. However, when an integrity problem is found later, the costs and exposure can be huge.

A good example of a shortcut that affects integrity is one I have seen at a few customer sites. They turn off database logging during large data loads to improve performance. But now there is a gap in the log, and without a complete log, Db2 can only recover to the last full backup. Any recovery activity involves reloading data after the time of the last full backup, and this can be slow and problematic.

Another example of where integrity is critical involves business transactions, particularly where a business transaction requires more than one database transaction to complete (initial display, validate until correct, process). While the DBMS provides locking, most business transactions are designed to terminate during user input, so it takes multiple invocations of a DBMS transaction to complete one business transaction. Proper design is required to ensure integrity during transaction execution.

While teaching a Db2 design class a few years ago in Edinburgh, one of the students said that many topics in the class were “DBA issues” and as such they were not important to them as developers. Their DBA replied: “They might seem like DBA issues, until we DBAs tell you to change your code!” Everyone must understand and embrace their role in ensuring integrity.

DBAs must help developers understand data integrity and ensure that applications are designed properly. Data maintenance activities must be done in a way that ensures data integrity is maintained.   

About Martin Hubel

I am an IBM Champion and Gold Consultant. My Db2 industry activities include being the host of the Db2Night Show, a member of the IDUG Volunteer Hall of Fame, an active IDUG speaker of over 50 presentations over the past 30 years, a panelist, an author, and a member on the executive of the Central Canada Db2-IMS Users Group.