My DBA journey and database compression options

Career:

In October 2008, JAW has decided to join the blogesphere!

Back in 1985, I started out after matriculating through college (Can you say COBOL?) as a 3800 laser printer/computer operator. I thought I knew a lot about IT coming out of college but I found out that I didn't know a lot. Anyway I started to immerse myself in the technology of the time and started to get promoted a lot. I developed a love for CLIST and REXX programming.

IIRC, my first professional experience with a RDBMS (can you say VSAM,ISAM, or QSAM?) was DB2 V2.1 around July 1989 as I was what is known as a MVS (Z/OS) Systems Programmer. At various points I was a DB2 systems programmer, CICS systems programmer, VTAM/NCP/TCPIP systems programmer. Most of these types of jobs today are done by mainframe outsourcing outfits instead of in-house as the skill set is dwindling. I even have a file on the CBT mainframe shareware site at http://cbttape.org/. Around this time I started to learn as much as I could about databases as I began to see a brighter future in this. In the mid-to-late 90's I was doing DB2 System Programming and DB2 Application support which are usually separate roles in a mainframe shop along with becoming stronger in the programming languages of that platform. I have worked on DB2 all the way up to V9.1 on Z/OS and V9.7 on Windows.

In 1998 as a DB2 systems programmer I was given the opportunity to install and configure the Oracle Transparent Gateway for DB2. This simply allowed a DB2 subsystem's data to be accessed from Oracle 7.3 which had just made its way to the shop by 1998. Once you setup the gateway correctly you could go into Oracle and create a database link and a synonym and you did not know the data was in DB2 unless you SET AUTOTRACE ON or the like inside of SQLPLUS (or that you were pulling big data over the wire). The gateway on the mainframe was a DB2 application that understood Oracle and did the heavy work.

At this point I said DB2 is the king and what is this Oracle stuff?

However, I decided not to be like some peers and jumped into this thing with both feet as I was a young guy who could learn new stuff I started to buy Oracle books and pick up stuff from the contract Oracle DBA brought in to help build this UNIX based application that required data from mainframe DB2. The company also started to send me to class to learn Oracle and MQ-Series as queuing was coming into play.

During the push to Y2K (I was still primarily a mainframer) the company announced it would outsource my department because the mainframe support was too costly and that all new development would be Oracle. Since I was spending my own time and money to learn shell programming, AWK, SED and Oracle I was kept on for the new millennium and went into being an Oracle DBA full time in 1st quarter 2000! I was working on V7.3, V8, and starting to study 8i( 8.1.5 can you say shared pool contention???) which was installed on databases systems that year at my shop. I was working with some more experienced Oracle guys picking up all I could learn. I was able to quickly use what I knew about DB2, JCL, COBOL, BAL, and REXX and come up to speed on the similar Oracle/UNIX constructs. Eventually, I became the Lead Oracle DBA as fate would have it. IIRC, it was around 2003 that SQL Server 2000 made its way into the shop in a big way. Management decreed that new applications that did not require Oracle would be done on SQL Server.

They say that those who don't learn from history are doomed to repeat it. In 2004 I started to buy SQL Server books (LOL!) and began to learn SQL Server 2000. I found DTS to be pretty good for quick and dirty stuff. I started to learn VBSCRIPT, VB, and took classes on DOTNET and C development somewhere in this period as C# was to become the the new platform for the company I even dusted of some DOS SHELL programming stuff. I eventually re-wrote all of my Oracle UNIX scripts in VBscript to support Oracle on Windows and became a big proponent of RHEL Linux also. I started to play with ADO and later ADO.NET and started to have some fun at this time with that. We brought in strong SQL Server people which I picked up things from them also over time. When SQL Server 2005 came out I started learning about it and spent time comparing and contrasting the various features of the different platforms. In my spare time I am trying to learn something about SQL2008. Being a jack of all trades and master of none is tough. Some of the most fun I had in this period was using the various DOTNET providers for Oracle to access from the Visual Studio architect.

Most of my work time is spent with Oracle and DB2. Today, I am in the midst of learning Oracle 11G, Oracle Application Express (just created our first production APEX application with help from the APEX forum), SQL Developer (we are trying to get rid of TOAD) in addition to my regular work. I have found ADDM and SQL Tuning advisor to be great for my Oracle 10g applications.

I think of the big three that Oracle is the most feature rich followed by DB2 and SQL Server. As you should be able to tell I would not have said that years ago when I was a mainframe bigot!


Compression:

A concept where less is usually more! Every experienced IT person understands the concept.

In the DB2 world I first dealt with compression in V3.1 back in the mid-to-late nineties. It was a huge saver of disk. IIRC, we had a mainframe with DB2 Sort Assist to move some of the cycles off the mainframe into the firmware. The thing I liked about it was most was that the software came at no special cost as long as you had the proper hardware. This saved us reams of DASD over time and performance improved for us including our OLTP applications because more data was quickly available for processing! Basically, adding a COMPRESS operand to CREATE TABLESPACE or ALTER TABLESPACE did the trick.

In the Oracle world I noted that compression showed up around Oracle 9i. However, it was primarily of use for OLAP applications with a few restrictions that you can read about. To be fair, one aspect that Oracle had before DB2 was the ability to compress indexes which can buy you a lot with composite indexes. In Oracle 11g advanced compression came about and I would make comments to my Oracle representative about the country being in a recession and why this is not in the base stack charge of EE? Larry needs a new boat I guess. Based on my DB2 experience I can see getting the same boost for OLTP if you pay the extra money. I am still mad about that one though.

In the SQL2008 side of the house MSOFT has introduced compression. I need to read more about it and play with it when the chance presents itself. If you have tested it let me know. I would think that you would not use it in production until a few service packs go by. However, in SQL 2005 I was surprised how reliable mirroring was which is similar to Oracle Data Guard or DB2's HADR.

Next time we can talk about command line options for the big 3.

To paraphrase Nature Boy Ric Flair, "Diamonds are forever and so is database technology, WOO!".