Caremark RX, Bannockburn IL USA
October 1999 to October 2000
- Provided DBA production maintenance support for Caremark’s Data Warehouse environment – 500 GB DB2/EEE system running on twelve SP nodes.
- Participated in the Data Warehouse Expansion task - adding six new SP nodes, and re-distributing the data.
- Set up DB2 Governor to control the Data Warehouse usage and reduce the number of database crashes. This initiative resulted in much higher database availability for all users of data.
- Identified and corrected the bottlenecks with the Data Warehouse and brought the CPU utilization down from 100% to 50%. Collected and monitored OS metrics such as vmstats on a continuous basis to make sure CPU usage remained healthy.
- Designed and built an in-house monitoring tool using C, embedded SQL, stored procedures and Brio reports to capture the behavior of various database objects and identify and shut down problem-queries before they caused Data Warehouse outages. This initiative saved the company over $150,000 in expected software expenses (third party monitoring tools).
- Through four months of collecting and processing data from the DB2 Event Monitor, created a Data Warehouse profile. Used this information to identify the non-utilized database objects such as tables and columns, and removed them from the Data Warehouse. This initiative saved the company over $250,000 dollars in expected hardware expenses (buying an additional SP node).
- Built and maintained a new test environment - 40 GB DB2/EEE system running on two SP nodes. Built ETL scripts to populate the test environment from the production Data Warehouse.
- Helped Data Warehouse users with SQL and performance tuning requests. Some solutions required application re-design complemented by database changes.
- Investigated query performance problems on Mainframe. Reduced the execution of monthly reports from 30 to 3 hours.
- Handled the opening and closing of the PMRs with IBM Canada – Toronto Lab.
- Produced a complete set of documentation to explain all the required fine-tuning on the Data Warehouse.