Db2 (On Premises and Cloud)

An address which will live in infamy

By Martin Hubel posted Thu September 12, 2019 01:59 PM


Well, it certainly wasn’t a date, and it wasn’t Pearl Harbor. But this data error has stuck with me for a long time, hence the paraphrase of FDR.


The problem started innocently as a test that went awry. All address line 1s in the Websphere Commerce database got updated to the same value. It was time to simply reload the table, fix the code and try again.


But wait! The developer didn’t realize that he was pointed to production! All address line 1s were changed to:


3396 Moss Lane


This became a very famous address. Before, it was likely just the address of the developer.


There were 362,000 rows out of the 600.000 rows in the table to fix, and we had several possible approaches for this recovery. The most important factor was that this was a production table in a 24x7 application that was responsible for 70% of the company’s revenue. An outage was unacceptable, and even as we discussed our recovery plan, new customers had added their information and existing customers had fixed their address.


Our approach had to keep the application up, and while it was embarrassing to have the wrong address, customers could correct it themselves so no business was being lost. While we wanted to implement a solution quickly, it was more important to do it right.


Of course, this is now a production data problem, so it becomes a DBA problem. In this case, it became my problem. This makes perfect sense, because DBAs are often data janitors. Also, as this was looking like the solution would involve writing SQL, who better to write SQL? (Yes, my tongue is firmly in my cheek.)


I did a restore of a recent copy of the table, and started to work on the SQL. Over the next two weeks or so, I found some SQL to modify that merged the correct addresses into the production table.


Besides performing a critical recovery, we learned a couple of valuable lessons. The most important lesson is that a production Db2 system may only have a character or two is its name from a test system. If you are using PuTTY or another terminal emulator, consider a different color scheme to help you easily distinguish test from production.


And lastly, the person who created this problem was one of four developers. To their credit, the developers never told us which one was responsible for this problem. Also to their credit, management didn’t look too hard either. In hindsight, I suspect it was the developer who had a relationship with people at 3396 Moss Lane.



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.