Department of National Defence, Ottawa, Canada
July 2011 to July 2012
- Was hired as a Senior DB2 LUW Consultant to concentrate on getting DRMIS on LUW working and performing as per DND's high standards of availability and reliability. The environment consisted of over 100 SAP installations (BI Data Warehouse, ERP, DoE, PI, Soluition Manager, ...) running on AIX 6.1 and DB2 V9.7, amounting to over 13 TB of data.
- Look at the internal database structure for Optim Repository Manager (OPM) version 220.127.116.11.
- Setup data collection templates for databases with different code pages to work around certain product limitations.
- Configured and maintained a number of databases for OPM Performance Monitoring to better understand the functionality of the dash-ports, alerts, and the canned reports.
- Investigated the OPM Console performance problems and configured the automatic maintenance policy for the OPM Repository to issue the backups and Runstats at a more appropriate interval.
- Reported OPM bugs to IBM with detailed instructions on how the bug could be re-produced. Received, applied and validated the patches from IBM.
- Calculate space requirement to turn on OPM data collection on all DRMIS databases supporting various SAP applications.
- Produced a product evaluation document for OPM and looked at the product from these perspectives: Licensing, data Collection and Configuration, Organization of Collected Data, Performance and Health Alerts, Alert Management, In-flight Dashports, Canned Reports, Database Registration, General User Interface, Data Accuracy, and Repository Database Maintenance.
- Wrote up an RFC for the installation of OPM within the DND WAN. Provided a detailed write up on the Business Case, Cost Analysis, Security Implications, Testing and Implementation Requirements.
- Performed day to day database maintenance such as re-launching failed backup and REORG jobs through the DBA Cockpit.
- Assisted two senior DBAs in the successful recovery of a crashed database on board of a Canadian Ship.
- Setup a number of korn shell script and stored procedures to collect and process DB2 Snapshots, Database Structure, Configuration and Installation information from all the DB2 LUW databases from one central location. The data was collected from a number of most relevant SYSIBMADM administrative views, SYSPROC stored procedures and SYSCAT views.
- Wrote up a System Operating Procedure (SOP) to document location, use case and detailed description of each script.
- Produced over 20 landscape-wide reports using the data collected from the korn shell scripts including: Online and Offline Maintenance Windows, Auto Reorg Policy, Backup History, Tables with no Indexes, Table Usage, Tables Needing REORG, Database Memory Usage, Active Event Monitors, Bufferpool Hit Ratio, REORG and Backup Conflict, and Database Inventory. Analyzed the xPaths used in DB2 Policies and produced user-friendly reports by extracting the relevant data using xQueries.
- Used DB2 Encryption to store and retrieve database connection credentials in a secure way.