Profile
Talented Database & J2EE/WebSphere Solution Developer with solid background in programming and scripting. Worked well alone and as part of a team. Have strong communication and documentation skills. Have designed databases and schemas since 2006. Application Integration and Data Integration focused.
Skills
• Database design
• Data Modeling
• Programming and design skills
• Java / J2EE / WebSphere (WAS/WMQ/WMB)
• Structured query language (SQL) expert
• Shell scripting ( win & *nix) expert
• Excellent problem-solving abilities
• Strong analytical skills
• Hands-on and proactive
• Windows and Linux
• Comfortable with change
• Fostering teamwork
• Database server optimization
• Resource/capacity planning
• System backups
• Excel in Shell Scripting
• Data backup/retrieval
• Working Knowledge of JavaScript, CSS, HTML
• Excellent communication skills
• Patch management
• Excellent diagnostic skills
Work Experience
Database Infrastructure Specialist FT Contract
Oct 2014 – Present (31-Dec-2015)
IBM Canada/Markham – Client BMO Bank
SQL 2008 2012/Oracle 11g 12c RAC Data Guard/ DB2 8.2
o SQL Server 70% – Oracle 20% (as needed) – DB2 10% production support. Most activities in MSSQL server, least in DB2.
o Built new always on cluster with MSSQL 2012 for critical financial OLTP transactions. Built new Active/Passive cluster with MSSQL Server 2008 R2. Developed Archive/Purge (3 stage) Stored procedure for purge job in PRS. Performed 6 DR test runs with SQL Servers 2008/12.
o MSSSQL: Developed over 12 DMV/DMF monitoring scripts for integration with company’s monitoring and ticketing infrastructure. This allowed better page outs to on-call resources.
o Enhanced MSSQL Server 2008 R2 log shipping and ROS. Added more application groups to use ROS for load-balancing purpose. This has eased up the load on OLTP upto 15%.
o SQL Server SQL implementations – Enhanced existing ETL process with 2008 R2 SSIS. Enhanced SQL server monitoring with new VBS scripts, currently working on re-write in Powershell. Developed performance log trail for proactive monitoring using VBS/Batch scripting.
o Enhanced DB2, SQL Server and Oracle RAC/Data Guard Databases.
o Oracle 11g R2 health-check scripting. Oracle DG Monitoring.
o Performance Tuning for DB2 and Oracle SQL, Applications.
o DB2 HADR and Enterprise Security. HADR Monitoring scripts in KSH/Windows.
o Production Support – Circulation as Primary and Backup DBA Resource.
o Rolling upgrades and Security patches.
o DR Testing SQL Server and DB2.
o Analyzing Locks and performance.
o Real time problem resolutions (MIRT).
o Created SBG’s, Implementation Guides and New Employee Onboarding Documents.
DB2 Database Specialist FT Contract Aug 2014 – Oct 2014
IBM Canada/Markham – Client Shoppers Drug Mart
SUSE 11 Enterprise/DB2 10.1
o Informix to DB2 transformation review before rollout.
o Performance Tuning. Python scripting for DB2 Performance test.
o DB2 Optimal Configuration Recommendations.
o Schema consolidation (Informix to DB2).
o Migration Scripting KSH Shell/SQL.
o UAT testing.
o Bash/KSH scripting, Data migration fixes.
o Migration Planning & Testing
o Analyzing Locks and performance.
o DB2 benchmarking and load-testing.
IBM Websphere/MQ Administrator PT Contract, L2, ON-CALL
July 2014 – Aug 2015
Nova Networks Inc., Ottawa, ON – Client MSC Direct USA
AIX 6.1, DB2, ECOMM, WAS/MQ
Websphere Commerce, WAS 7, MQ 7, DB2 v9.7, HADR TSA
Weekly deployments.
DB2 HADR (TSA) administration.
One Fixpack (9.7.9a) upgrade.
IBM Data Studio, Control Center, db2top.
DDL, DML and DCL implementations.
Online and Offline Reorgs.
PL/SQL batch processes, PL/SQL programs – Packages, Functions, Stored Procedures, Collections, Cursors, and Triggers.
Installed EARs, WARs and configured application specific JVM settings, Web container parameters using the Admin Console and WSADMIN.
Updated EARs for both Portal and Commerce servers.
Wrote monitoring scripts, email and pager alerts.
L2 DBA Support on Exercise DR Environment Restore.
Handling SQL scripts run, LOAD scripts and Grant scripts requested via OTRS tickets.
Created New Table Spaces for Table Backups. Used db2look for DDLs.
Configured WebSphere resources like JVM heap settings, JDBC providers, JDBC data sources, connection pooling, Virtual Hosts,
Performance Analysis using IBM Tool-Kit.
Analyzing Locks and performance using db2top and db2advis.
AIX v6.1, DB2 UDB Enterprise Server Edition 9.7 (HADR).
DB2 Database Administrator (lead) PT Contract, L2, ON-CALL
Sep 2013 – Aug 2015
Nova Networks Inc., Ottawa, ON – IBM Partner – Client Farmers Insurance LA
AIX 7.1, InfoSphere 9.7.8, ISAS
o VLDB : 49 partitions, 65TB, 8 Physical Servers DB2 UDB database warehouse(InfoSphere)
o One major version upgrade ( 9.1 to 9.7).
o One Fixpack (9.7.8 ) upgrade.
o Two Disaster Recovery Exercises (lead one).
o One Real Disaster Recovery (IBM, Farmers and Nova teams)-disk failure.
o Work Load Manager (WLM), switching workload priorities.
o DB2 HADR (TSA) administration.
o IBM Data Studio, Control Center, db2top.
o DDL, DML and DCL implementations.
o Online and Offline Reorgs.
o PL/SQL batch processes, PL/SQL programs – Packages, Functions, Stored Procedures, Collections, Cursors, and Triggers.
o Data Exports, custom export formats.
o Wrote monitoring scripts, email and pager alerts.
o Backup Weekly Prod and Non-Prod. Redirected Restore database and tablespace.
o Lead DBA Team on Exercise DR Environment Restore.
o Handling SQL scripts run, LOAD scripts and Grant scripts requested via OTRS tickets.
o Creating New Tablespaces for Table Backups. Used db2look for DDLs.
o Analyzing Locks and performance using db2top and db2advis.
o AIX v6.1, DB2 UDB InfoSphere Server 9.7 (DPF)
DB2 DBA/Developer z/OS FT
Jun 2013 – Aug 2013
TD Wealth, Toronto, ON
o DB2 UDB z/OS version 8 and 10.1, CA RC toolkit , QMF and ICETOOL
o Supported FATCA Development team
o Data Pre-Validation for initial loads
o COBOL Stored Procedures, SQL Triggers and JCL maintenance jobs
o Data validation for various development phases
o Handling data mapping for data load on data spec changes
o Analyze and implement data conversion strategies in DEV and PAT environments
Sr. DB2 Database Administrator FT
Dec 2012 – Jun 2013
Nova Networks Inc., Ottawa, ON – IBM Partner – Client Farmers Insurance LA
AIX, InfoSphere, ISAS
o VLDB : 49 partitions, 54TB, 8 Physical Servers DB2 UDB database warehouse (InfoSphere)
o Work Load Manager (WLM)
o DB2 UDB HADR database setup
o Data Pre-Validation for Solution Design
o Data validation for various development phases
o Data transformation handling through multi staged environments
o Extensively experience of PL/SQL batch processes, PL/SQL programs – Packages, Functions, Stored Procedures, Collections, Cursors, and Triggers.
o Extensively worked on the data validations and profiling
o Experience of interfacing extensively with the Business Teams for requirements mapping, cause analysis or defect resolution
o Handling data mapping for data load on data spec changes
o Wrote scripts to transfer data from database to excel or flat files and used SQL Loader to load data from excel or flat files to data base.
o Performed quality check/ Data analysis on data coming from different sources.
o Analyze and implement data conversion strategies in DEV and PAT environments
o Analyzed the Business Requirements and Rules and worked with Business Analysts and Business Users
o Closely went through and worked on all the stages of the project and executed Functional, Integration, Regression, and System (End to End), Backend (Database) from a Data perspective
o AIX v6.1, DB2 UDB InfoSphere Server 9.5 (DPF)
o Supported LBAC access control on warehouse server
o Using DB2 UDB 9.7 new index compress features to save disk space on large tables
o DB2 UDB porting from AIX to Centos
o DB2 UDB development and production support on LINUX/AIX/Windows
o DB2 UDB database cloning using REDIRECT restore and DB2 UDB relocatedb
o Production databases troubleshooting production issues
o Used DB2 UDB Health Monitor and testing IBM Data studio for DB2 UDB database monitoring
o Using IBM InfoSphere Architect (IDA) for data model and generating DDL
o DB2 UDB V9.1 upgrade to DB2 UDB V9.7
o Supported ORACLE 11g servers for City Of Ottawa
o Managed DB2 Security using OS group and user ID, DB Authentication, DB Authorization, and database object privileges
o Managed DB2 LBAC security policies.
Principal Consultant (DBMS) FT
Jul 2011 – Jul 2012
NTT DATA Inc., TORONTO, ON CLIENT: Shoppers Drug Mart, Toronto
Informix and MSSQL Server
• Informix 60% – MSSQL Server 40% development support – OLTP and OLAP.
• Supported Shoppers Drug Mart development teams on OLTP transaction monitoring.
• Development Support – HealthWatch Data Management and CRx upgrades
• Data Modeling Support
o Developed over 35 stored procedures for various OLTP and OLAP requirements.
o Entity Design & optimization, SQL optimization support for Java Programming Team. Used Erwin Data Modeler and InfoSphere Data Architect for modeling data for new add-ins to existing databases. Re-evaluated about 120 data units.
o Understood the end-to-end scope of work and designed architectural solutions accordingly. Used database design guidelines and database modeling concepts to ensure data accessibility and security. Develop plans, strategies and standards for data within the enterprise.
o Perform defect Tracking and reporting with strong emphasis on root-cause analysis to determine where and why defects are being introduced in the process.
o Identified Entities, attributes and relationships and designed the conceptual, logical and physical model using ERwin Data Modeler & IBM Rational/InfoSphere Data Architect tool. Designed star & snowflake schemas.
o Involved in meetings with Business Analyst’s & BI’s at Shoppers Drug Mart and made proper database modeling decisions accordingly. Partner with all other teams (database management team, software development team ) and UAT testers to provide best solutions by maintaining the best standards of practice.
o Ensured that all information requirements in the Business Architecture were met at the time of delivery and produced good quality of work products and delivered database models as per schedule.
o Planned and migrated ERwin data models to IBM Rational modeling tool (IDA) and resolved many incompatibilities between the two tools.
o Followed-up with team members on the installation, connectivity and access issues for software’s with Shoppers Managers and gained required access on time.
o Conducted status calls with resources in Alberta (Calgary) and Nova Scotia (Halifax) and maintained smooth communication within the team.
o Provided guidelines to resolve queries and issues faced by team members.
• ETL/EDW support
o Implemented OLTP, ODS, EDW concepts
o Designed Extraction Transformation Staging Loading workflows
o Resolved workflow slowness issues, provided various development routines.
o Used Infomatica PowerCentre 8 & 9 for workflow development.
o Assisted the team with persistence and business logic development
• SP/Trigger and SQL tune-up. Facilitated SQL optimization for 30 Informix Databases.
• IDS versions 7, 9 and 10.
DBA Remediation Project 6 months (Jul 2011 – Jan 2012)
• Assess and devise Pro-Active Monitoring
o DB2 UDB and Informix Database Monitor Development
o Review Current practices – Recommend improvements
o 24 DB2 UDB v9.1 instances on 17 physical servers
o One DB2 UDB warehouse with 16+1 partitions
o Devise and implement Capacity Management Procedures
o VLDB – Max size 1.6 TB – DPF (16 partitions on 3 physical servers) – Dimensional
o Programmed in pure Korn Shell scripts. Developed full Pro-Active Monitoring Model for DB2 UDB and Informix Databases.
o Developed DB2 UDB korn shell Performance and Capacity Management application.
o Developed Delayed Alert system using Open View and Remedy interfaces.
o Informix IDS (versions 7.3 – 10) korn shell script application was developed on the same business logic.
• Data Model Assessments:
o Identified Entities, attributes and relationships in physical model using Erwin Data Modeler & Toad Data Modeler and prepared recommendations based on best practices of data modeling.
o Reverse engineered existing production databases (some that involved in development projects) using MS Visio.
o Analyzed data type usages for various indexing strategies and performed an expert check on performance per data model.
Database Administrator FT
Sep 2008 – Aug 2011
Net Present Services Ltd, TORONTO, ON
SQL Server 2008, DB2 9.1, 9.5, Oracle 10g
Database Design – New database design on BSD v8 Linux platform 6 months (Mar 2011-Aug 2011)
o Team Lead – team of 5 – New Databases designed – 3 databases – 14 data model sets (schemas).
o Tools used ER/Studio, AQUA Data Studio, PowerDesigner, MS Visio and Toad Data Modeler.
o Upgraded 5 Windows servers to BSD Linux v8. Initial loads and regression tested. ETL via custom scripts – From Windows DB2 UDB databases to newly developed DB2 Databases on BSD Linux v8.
o Performance Tuned SQL Server engine and SQL Queries.
o Developed Stored Procedures, Triggers and batch scripts.
o Scripted new implementations for Customized Health Monitoring Alerts.
DB2 UDB Data Modeling– 70%
o OLTP performance monitoring using windows scripting was implemented using 5 scripts and 3 datafiles.
o Performed Logical Design to Physical Design (Oracle and DB2 UDB Databases), Database Entities Performance testing/tuning, PL/SQL programming and SQL Tune up.
o Setup and maintained Q-Replication (QREP).
o Reverse engineered existing production databases (some that involved in development projects) using MS Visio.
o Generated detailed ERD layouts of existing Data Models with Aqua Data Studio.
o Identified Entities, attributes and relationships and designed the conceptual, logical and physical model using ERWin Data Modeler, PowerDesigner & Toad Data Modeler. Designed star & snowflake schemas.
o Involved in meetings with Business Analysts’ and Java Development Team and made proper database modeling decisions accordingly. Partner with all other teams (database management team, software development team, designers) and users to provide best solutions by maintaining the best standards of practice.
o Documented source and target data mappings and validated the models to ensure it supported the principles, objectives and constraints.
o Reviewed the qualitative criteria, providing all the measurable criteria (e.g. minimum tolerable data losses, maximum data volumes at peak times).
o Ensured that all information requirements in the Business Architecture were met at the time of delivery and produced good quality of work products and delivered database models as per schedule.
o Implemented efficient databases, including specifying physical characteristics, designing efficient indexes and mapping database objects to physical storage devices.
o Performed traditional physical DBA tasks such as problem determination and resolution, performance monitoring and tuning using snapshot functions, identifying problem SQL and performance bottlenecks, schema changes, configuration changes, working with developers and explaining analysis.
DB2 UDB Database Administration – 30%
o Infosphere 8.7 upgraded to 9.1 – DataStage and QualityStage servers on CentOS Linux Enterprise version.
o Warehouse size 2.6TB.
o Managed access to the system by users, other administrators and designer/developers and managed the contents of the file system and databases.
o VLDB – Max size 2.6 TB – non-Dimensional
o Extract Transform and Load (ETL) among DB2 UDB and MySQL databases.
o Supported Java Development Team for jdbc, swing, hibernate and struts development.
o Development Support – SQL optimization support for Java Programming Team
o Developed DB2 UDB database schemas for new game development projects.
o Responsible for managing Mirror Nightly Restore system scripts for all production databases.
o Developed, installed and managed DB2 UDB stored procedures for various purposes including daily reporting & batch prune large tables.
o Opened PMR with IBM Software support and assisted with new issues troubleshooting.
o Developed monitoring & preventive scripts for DB issues on various problems (Held Logs Monitor, Table Lock Monitor, Force Bad Apps).
o Developed regular DBA monitoring scripts (TableSpace Watch, Diaglog Monitor Alerts, Server Disk and Process Monitor).
o Tested and recommended DB2 UDB implementations w.r.t encryption devices.
o Tested and reported 3+ bugs to IBM support, obtained hot fixes and applied to production databases.
o Provided 24*7 support via pager, cell phone, VPN.
o Developed trigger and SProc fine tuning strategies.
o Prepared and implemented audit routines as per industry and organization standards.
o Applied various fix packs and patches for DB2 UDB.
o Developed and implemented DB2 UDB backup & recovery processes.
Lead Database Administrator FT Jul 2005 – Sep 2008
Computer Window, Brampton ON
SQL server 2005 2008, Oracle 10g and DB2 8.2 9.1
Database Design/Architect/Data Modeler (70%)
o New design and experiment existing for performance. Used Erwin, Toad Data Modeler, PowerDesigner MS Visio for Data Modeling Support.
o Developed (designed) databases for Linux platform, written required scripts for maintenance. MySQL ver 5.0 on CentOS 4.8.
o Developed databases in DB2 UDB & MSSQL. Also all scripts and scheduled jobs were written. Windows environment.
o Closely worked with software development teams and database administrators at various sites reviewing logical/physical data models. Worked on Data Models and trained client development teams on understanding data models.
• Database Administration (DB2 UDB, SQL Server, ORACLE & MySQL) : mixed environment
o Responsible for managing over 150+ servers, 600+ application clients (Windows, AIX, Solaris, Linux), 450+ instances, 650+ databases and over 10,000 end users
o Develop, install and manage relational Database Management Systems utilizing DB2 UDB, Oracle and SQL Server on various versions, editions and operating system environments
o Provide 24*7 support via on-call (pager, cell phone)
o Prepare and implement audit routine for all the RDBMS as per industry and organization standard
o Apply various fix packs and patches for DB2 UDB.
o Develop and implement DB2 UDB backup, Oracle RMAN backup and SQL Server backup and recovery processes
o Improve database performance via. fine-tuning queries, change database parameters, change operating system parameters
o Create various database objects e.g. buffer pools, table spaces, tables, indexes, functions, federated objects – wrappers, servers, user mappings, nickname etc.
o Create and tune MQT’s and MDC’s for analytical purposes
o Diagnose database log and event log files on daily basis to identify/fix errors
o Setup backup and recovery processes for all the databases using TSM
o Participate in DR testing by creating the process documentation and performing on-site recovery
o Cleanup old servers from DB2 UDB, Oracle and SQL Servers components e.g. code, instances, databases, file systems and ID’s
o Develop customized scripts for data refresh, replication and other solution for specific business solutions
o Developed various customized scripts e.g. to track database activity, track transaction logging, tablespace usage, cleanup old database images and logs from TSM etc.
o Create and implement best practices for DB2 UDB HADR, backup/recovery, performance tuning etc.
o Open PMR with DB2 UDB support and assist with new issues troubleshooting
o Administer and tune database on daily basis, running utilities like – Reorg, Runstats, event analyzer, event monitor, import, export, load, analyze etc. on various RDBMS’s
Database Administrator FT
Jul 2002 – Jul 2005
Pabla Consulting Services Ltd., Mississauga, ON
SQL server 2000 2005, Oracle 9i 10g and DB2 8.2 9.1
o SQL Server, DB2 UDB, ORACLE, & MySQL: mixed environment.
o Performed different tasks in UNIX related to DB2 UDB i.e. user creation/update, file system update, Organizing files and directories, installing software
o Developed, installed and managed relational Database Management System, utilizing DB2 UDB v7 and v8 in Windows environment
o Managed databases with size in excess of 300 GB. Create processes to take bi-weekly database backups, restore, import/export etc.
o Improved database performance via. fine-tuning queries, change database parameters, change operating system parameters
o Diagnosed database log and event log files on daily basis to identify/fix errors
o Administered and tuned database on daily basis, running utilities like – reorg, event analyzer, event monitor, import, export, load
o Provided 24*7 support
o Interfaced with business analysts to perform needs analysis and define user specifications
o Analyzed user/business requirements and translate into functional specifications
o Design storyboard prototype designs for database applications
o Created and implemented SQL-based procedures, including writing stored procedures, scripts, User defined functions and Data Types
o Designed Instances, databases and schemas
o Designed, created and implemented primary database storage structures (table spaces) and primary objects (tables, views, indexes, triggers, constraints)
o Documented daily jobs performed on the database, troubleshooting connectivity issues
Oracle Database Administrator FT
Jan 2000 – Jul 2002
M & M Computers, Concord, ON
Oracle 9i
o Used Explain Plan and Oracle optimizer hints to enhance performance of SQL statements.
o Performed the user requirements and troubleshooting activities and resolved connectivity issues.
o Implemented, administrated, and maintained daily backup and archived data on tape.
o Created user accounts and system-level and user-level database objects.
o Daily Database Monitoring – database disk and memory usage.
o Stored Procedures, Triggers and SQL/Shell scripts.
Accomplishments
Database Design/Architect:
• Designed database data interfaces for new and existing databases e.g. HealthWatch & CRx
• Re-designed data models for Informix, DB2 UDB and Oracle Databases
• Programmed Triggers and Stored Procedures
• Tested the model for performance
• Improved indexes for performance
Database Monitoring:
• Developed performance monitoring interface (korn shell)
• Developed capacity monitoring interface
• Coded alert system for Open View/Remedy and email (timed & buffered alerts)
• Tested and deployed the solution (korn shell version)
Database Administration:
• Maintained production high transaction database
• Operated on line tools as reorgs and runstats
• Prepared ETL plans and scripts
• Monitored and tuned overnight backups
• Coded triggers and stored procedures
• Scripted for task center jobs
• Created access plans
• Upgraded DB2 UDB, Informix & MS SQL servers
Database Performance Tuning:
• Tuned DB2 UDB, Oracle, MySQL database engines
• Evaluated SQL queries for performance on daily basis
• Planned indexes, primary keys for performance
• Evaluated and recreated indexes
• Rebounded the packages
Shell Scripting:
• Designed and coded Mirror Nightly Restore scripts
• Programmed remote control scripts (windows)
• Coded replication scripts
• Developed daily monitoring scripts (dba)
Qualifications
Information Technology – RDBMS
• 2013 IBM, Toronto, Ontario – Certified Database Administrator DB2 UDB 10 for z/OS
• 2013 IBM, Toronto, Ontario – InfoSphere Warehouse Solution Designer v9.5
• 2012 IBM, Toronto, Ontario – InfoSphere DataStage Solution Designer v8.5
• 2012 IBM, Toronto, Ontario – DB2 UDB V10.1 Database Administrator
• 2010 Oracle, Toronto, Ontario – OCE, Oracle SQL Expert
• 2009 IBM, Toronto, Ontario – DB2 UDB V9 Application Developer
• 2008 IBM, Toronto, Ontario – DB2 UDB V9 Stored Procedure Developer
• 2007 IBM, Toronto, Ontario – DB2 UDB V9 Database Administrator
• 1998 Novell Education(USA), Toronto, Ontario – CNE Novell IntranetWare 4.11
• 1989 – 1992 National Institute of Computer Education (NICE), New Delhi, India
PG Diploma in Computer Software including 1 year practicum
Academic
• 1982 – 1985 Punjab State Board of Technical Education, Chandigarh, India
Diploma in Electrical Engineering
• 1982 Guru Nanak Dev University, Amritsar, India
Pre-Engineering (2nd Year University with Math and Science)
• 1981 Guru Nanak Dev University, Amritsar, India
Pre-University (1st Year University with Math and Science)
• 1979 Punjab School Education Board, Chandigarh, India
Matriculation (School Graduation)
