COOL NEW (to me) TOYS FROM IBM - XML INDEX ADVISOR!

So after bouncing around the interwebs for a bit, I bumped into this cool little utility. This is off of IBM's alphaworks site and looks pretty nifty. I haven't worked much with DB2 and XML but i did attend some IDUG sessions with Fred Sobotka, and got a little one on one training at IDUG with Roger Sanders. This nifty little tool works similarly to the db2advise tool, only for XML and XML query's. One caveat! You need >= DB2 9.5.

Wow... what a day! Locks and Devs....

So after working with one of our Java devs to sort out a beasty that goes by the name of STAGEPROP on a IBM WAS Commerce environment; I have come to a few conclusions.

1. DBA's and Devs need to spend time teaching each other.

2. Devs need to walk in a DBA's shoes.

3. Devs and DBA's need to understand Locking and its effects of database processes and impacts to the Data Model on your databases.

So what brought on this philosophical perlustration?

Let me start with some explanation of the facts and challenges. Stageprop is a process of loading data at scheduled times from a staging DB to the production DB for the OLTP Commerce environment. The purpose is to schedule updates, insert and deletes at a time that is less stressful to the production database. The purpose for this is to reduce the lock contentions, reduce the chance of loading bad data into the production DB for testing catalog data and shopping process commands before release to production. Since Commerce sites are up 24x7x365 this is a very useful utility. More information about stagprop can be found here http://tinyurl.com/2325nab .

So, why are we here? Well the stagprop that was running started listing referential integrity errors on the update and insert processes in the logs. Basically, When the stagprop tried to update the production DB the update/insert was failing on the stage prop tables due to triggers not firing after a update to the stage prop tables on the staglog table which controls the order of updates, data to be updated, and a record of the type, columns and values updated. The reason for the lock? Several different issues. No indices on the staglog table. While stagprop utility was running, other updates were being propagated to stagprop tables, (stagprop utility causes a table lock type of IX and X for row, we'll talk about this in a minute).

So this all boils down to shoddy performance, locks and all sorts of misfiring triggers. How did we find the issue? Well, that's for later along with how we fixed the misfired triggers and eliminated the issue from happing again! So, this post will be a 3-5 parter. Stay tuned for the rest!

DB2 The List!

So after starting this blog, I decided to make a list. This list will change from time to time. The list is a table of contents of sort. As my skill and expertise increases with DB2, I will on occasion move up and down the tasks, challenges, and insight i will have learned from the gurus, my own research and peers.

1. DB2 installation for the newbie

2. DB2 configuration for the newbie

3. Database Theory - a crash course

4. Database Design - a crash course

5. DB2 Disaster planning - hope its not recovery...

6. DB2 High Availability and Disaster Recovery or H.A.D.R

7. DB2 Performance

8. Indexes

9. Disk IO

10. Query Optimization

11. Locking (what us normal people need to understand about it!)

DB2, How I made the Leap....

So after working in IT for several years, both in Linux, and primarily in windows... I decided that I needed to specialize in a field. Tired of being the jack-of-all-trades-master-of-none, I made it a point while working with an Ohio Non-profit that I would try to specialize as a DBA. At that time I had some exposure to PostGre, MySQL, MSSQL, and DB2 version 8 for Windows.

"So, how do I start this?" I asked myself, well after leaving the non-profit and working at a small civil engineering firm, I decided to take a leap of faith and talk to a company I had interviewed with previously. They had mentioned a year earlier that there were some big changes coming down the IT pipeline; SAP integration, E-commerce website, Tivoli, just to name a few and they had a real shortage of talent. So I took the leap. I was offered a position as a DB2 DBA, for a restaurant supply company.

Great! Now where do I get information on DB2, I had only the basics, DB theory, and about 5 scattered years working both directly and indirectly supporting different database applications, and a very informative yet challenging stint where I upgraded a IBM content management server on windows from version 7 to version 8.3.

So I started reading, and reading and reading some more. I bought books at Amazon, checked out the library. What I learned, was interesting, to say the least. I came to the determination that DB2 was a dark art. DB2 was and is similar to learning how to hand make samurai swords (I saw a PBS special on this). So now after reading this you are probably asking "What the frack is this clown talking about?" Simple. You can read all the books in the world about DB2, but you will never have the expertise to be a seasoned VET in DB2 until you find a mentor. The documentation, books and materials for learning DB2 are really only a spark to get you started in the right direction, just a gentle nudge. In order to know DB2 you have to have disasters, learn from mistakes and hope you have a shoulder to lean on when you are just about to fall flat on your face.

Once you get a mentor, don't stop there. Find another mentor, because even though you have decide to specialize in DB2, there are specialties of the DB2 Master. Security, performance tuning, I/O, HADR, SAP, z/OS, installation and configuration, ETL, Data Studio... If you can do it in DB2, it most certainly can be specialized in. So, now this all sounds incredible overwhelming. DB2 Experts are like pokemon.... gotta catch them all! And if all the money you have budgeted for training only amounts to $3k-$5k... spend it on IDUG... I learned and networked at IDUG more in 1 week than in my whole career. This place is the place to rub elbows with the geniuses of the industry, and to find some DB2 Pokemon!