John Hancock, Toronto, Canada
October 2004 to December 2009
Accomplishments
- The project involved design, development and support of JHRPS eComm and Data Warehouse like databases running on DB2 LUW.
- Provided 3rd level production support on all the DB2 LUW environments. Was the ultimate escalation point for problem management during production crisis. Used live OS monitoring tools such as vmstat and nmon plus live DB2 monitoring and diagnostics tools such as db2top and db2diag to solve production crisis in timely manner.
- Was promoted to the DBA Team Lead position to manage a team of Nine DBAs. The team was composed of both full time and contract staff.
- Was responsible for reviewing project estimates and planning resources accordingly.
- Led the DBA team in successful upgrade/maintenance of DB2 environments from Version 7.1 to Version 8.2, and subsequently to Version 9.5 on time and on budget.
- Led the DBA team in successful implementation of HADR and used HADR to perform live maintenances via TSM.
- Was the Release DBA on majority of Manulife ECOMM projects. The responsibilities included change grid planning, execution of release steps and post-release monitoring, and managing the use of data by various project groups.
- Introduced a new database standard to make the support of database structure changes for all the test and production environments much more straight forward. Furthermore, scripted the database build process which eliminated many environmental issues during the QA cycles.
- Updated the data dictionary after each major release to reflect database structure changes.
- Setup historical production stats collection at the O/S and DB2 level. Used these stats for capacity planning, database profiling and database performance measurements.
- Was responsible to conduct database performance testing for all releases. Caught and fixed poorly written SQLs prior to going live in production. Setup automatic RUNSTATS and REORGs to ensure optimum performance.
- Performed day to day hands on Application DBA tasks such as developing ETL scripts, writing Java stored procedures and making database object changes. Used CVS as the code repository system to manage database structure and script changes.
- Provided QA support such as running the ETLs, investigating bugs or applying database fixes on the QA environment.
- Addressed the capacity and stability issues of the test environments by building and configuring three additional in-house database servers on AIX and Windows platform. Introduced the concept of Virtual PCs.
- Setup a new process for extracting data from production while adhering to SOX requirements in terms of data masking and encryption. Used this process for updating HomeState and creating masked production-sized databases for special performance testing requirements.
- Designed, coded and tuned many Java stored procedures for the Data Access Layers for all web-based projects such as Plan Sponsor, Account Balance Offload, Broker Dealer 2.0.
- Mentored junior DBAs and interviewed candidates for full time and contract positions.
- Produced physical database structures containing more advanced DB2 data types such as Binary VARCHAR, BLOBs and CLOBs.
- Used advanced XML xQuery features of DB2 in stored procedures during the Broker Dealer Phase-2 project. The stored procedures used complex XML Aggregate functions to produce pre-computed lists in each result set row and automatically updated the list based on filtering and sorting criteria passed to the stored procedure.
