Canada Boarders Services Agency, Ottawa, Canada
March 2012 to January 2018
- Performed advance database administration, troubleshooting and development tasks on the CBSA Enterprise Data Warehouse (EDW) project.
- Coded PL SQL stored procedures to performing ETL operations from staging to ADW tables in EDW.
- Took part in the Oversight Committee and provided significant contribution to the documentation of the “as is state” of EDW processes.
- Performed DB2 V9.7 upgrade/maintenance of the development and production databases (from DB2 LUW V9.1).
- Performed a complete analysis of DB2 Shared Memory configuration for all EDW databases and tuned the bufferpools for optimum performance.
- Setup automatic database stats collection and processing using UNIX shell scripts and Java applications (DWSTAT) to capture database configuration, structure and performance data.
- Developed DWSTAT scripts and stored procedures to produce daily reports on various database metrics and usage for all prod and non-prod databases.
- Developed scripts using database federation, stored procedures, UNIX shell scripts and Java to perform live structure compares between non-homogenous environments, such as DB2 on Z/OS to DB2 LUW to Netezza (PureData).
- Pioneered code versioning using SVN for all application DBA development work and managing database structures.
- Pioneered build script technology to setup databases with full structure at a press of a button.
- Developed advanced multi-threaded shell scripts and Java procedures to copy data between non-homogenous environments (FedCopy/JDBC Copy). The JDBC Copy was primarily used to copy BLOB, CLOB and XML data types from mainframe to DB2 LUW environment.
- Developed advanced multi-threaded shell scripts for performing DB2 Backup, RUNSTATS and REORG to achieve optimum throughput.
- Lead the DBA team in many maintenance releases and notably in the successful migration of EDW from DB2 LUW SMP platform to DB2 LUW MPP.
- Successfully implemented DB2 Audit for highly sensitive data.
- Successfully implemented Trusted Context for Cognos reports.
- Lead the DBA team in successful launch of CBSA’s first Netezza (PureData) project (D2A). Developed a significant part of the ETL process using shell scripts.
- Conducted training to small and large audience.
- Optimized ETL for XML Shredding by reviewing complex xPaths and xQueries. Built a benchmark process for a repeatable performance testing cycle of the xQueries. Identified the root cause of xQueries and resolved the issue by moving the source XML table to DB2 coordinating node.
- Performed complete restore of production EDW in a major incident from TSM. Performed routine redirected restores on development and UA databases.
- Used DB2 built-in tools such as db2top and internal DBMS logs for various troubleshooting and problem diagnosis.
- Used db2look for capturing DDLs.
- Exported all the CDC subscripts (prod and non-prod) into XML format and developed a script and necessary database structure to load them in DWSTAT. Analyzed the xPaths in these XML files and used xQueries to produce a CDC Landscape report at the organization level.