Canada Boarders Services Agency

Canada Boarders Services Agency, Ottawa, Canada

March 2012 to January 2018

Accomplishments

  • 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.

Comments are closed.