Royal Bank of Canada, Toronto, Canada
September 1998 to September 1999
- Worked with business analysts and project managers to put together the solution architecture and obtained sign off.
- Produced, maintained and certified the data model for the new Royal Bank Merchant Risk Management System (MRMS) using BACHMAN and ERWin.
- Forward engineered the data model to MRMS physical database. Designed the table spaces, buffer pools, and indexes in accordance to database initial size and growth factor; set up triggers for audit trails.
- Produced data mapping rules between legacy source systems and new MRMS database and ensured that mainframe developers coded the extract logic in accordance to my specifications. Maintained the mainframe jobs after developers’ contracts were expired.
- Designed data transformation and load scripts using C, embedded SQL, stored procedures and shell scripts to support data updates in a near real-time fashion. Designed and coded near real-time summarization jobs to populate OLAP cubes.
- Used DB2 Visual Explain, Application Snapshots and Event Monitors to identify and resolve performance bottlenecks. Participated in the AION/DS code walkthrough and helped identify application bottlenecks. Regularly monitored db2diag for database errors and investigated the errors in timely manner.
- Validated fraudulent transactions identified through AION/DS code through Brio reports. Used Brio as a data-mining tool to identify potential fraudulent cases that were not identified by AION/DS.
- Installed, configured and maintained DB2/LUW Enterprise Edition (Version 5.2) on RS/6000. Lead the infrastructure group in installation, configuration and troubleshooting of MRMS production database (90 GB).
- Lead the developers and testers in the system integration and acceptance testing. Ensured that all bugs were fixed in a proper priority sequence on time prior to the release.
- Produced documentation for the near real-time ETL process, backup and recovery strategy, and system growth/capacity. Conducted training sessions to full-time staff and ensured that proper hand-off was completed.