Summary

• Over 8 years of experience in ETL Design, Development, Testing, implementation, documentation, maintenance and Data Migration life cycle using IBM DataStage 9.x/8.x.
• Extensive experience with Retail, Insurance and Banking domains.
• Proficient in developing strategies for Extraction, Transformation and Loading ETL mechanism.
• Excellent Experience in Designing, Developing, Documenting, Testing of ETL jobs and mappings in Server and Parallel jobs using DataStage to populate tables in Data Warehouse and Data marts.
• Expert in designing Parallel jobs using various stages like Join, Merge, Lookup, Remove duplicates, Filter, Dataset, Lookup file set, Complex flat file, Modify, Aggregator, transformer etc.
• Expert in designing Server jobs using various types of stages like Sequential file, ODBC, Hashed file, Aggregator, Transformer, Sort, Link Partitioner and Link Collector.
• Expert in working with DataStage Manager, Designer and Director.
• Experienced in integration of various data sources DB2-UDB, SQL Server, PL/SQL, Oracle and MS-Access into data staging area.
• Strong experience in troubleshooting DataStage jobs and addressing production issues like performance tuning and enhancement.
• Have performed various testing activities like Unit Testing, System Integration Testing, Regression Testing, User Acceptance Testing and ensured that the code is ideal.
• Extensive experience in developing Stored Procedures, Functions, Views and Triggers, Complex SQL queries using SQL and Oracle PL/SQL.
• Involved in Performance/ Query tuning. Generation /interpretation of explain plans and tuning SQL to improve performance.

IBM DB2 certified by IBM Corporation.

• Extensive experience in UNIX work environment, file transfers, job scheduling and shell scripting.
• Experience in using Automation Scheduling tools like Maestro and Autosys.
• Maintained outstanding relationship with Business Analysts and Business Users to gather information as per business requirements.
• Experience in support and knowledge transfer to the production team.
• Experience of working in onsite-offshore structure and effectively coordinated tasks between onsite and offshore teams as a technical and onsite lead.
• Had Basic knowledge on Informatica Power Center.
• Reliable, responsible, hardworking and good team player.

Technical Skills:

• ETL Tools : IBM Information Server V8.x DataStage, Quality Stage, Information Analyzer , Ascential DataStage V7.5 Designer, Director, Manager, Parallel Extender
• Database : Vertica, DB2, Oracle
• Testing Tools : HP Quality Center
• Scheduling Tools : Autosys, Maestro
• Operating System : Windows 9x/XP, Windows 7, UNIX
• Programming Languages : C, UNIX shell Scripting, SQL and PL/SQL

Professional Experience

Confidential

ETL Designer/ DataStage Senior Developer, Technical Lead

The objective of this project is to develop and implement a centralized database that integrates the various systems in order to create an effective linkage between promotions to either source code and/or associate bank. This project also involves data quality where in Quality checks are imposed on the extracts that are sent to downstream. Execute various checks on the data elements and generate the report of elements which was not in compliance.

Environment: IBM DataStage 8.7 Designer and Manager, Director , Oracle 10g, DB2 UDB, Flat files, Sequential files, Autosys, Maestro, TOAD 9.6, UNIX Scripting.

Responsibilities:

• Analyzed the business requirements and functional specifications.
• Involved in creating specifications for ETL processes, finalized requirements and prepared specification document.
• Used DataStage Designer to create the table definitions for the CSV and flat files, import the table definitions into the repository, import and export the projects.
• Developed ETL processes to extract the source data and load it into the enterprise-wide data warehouse after cleansing, transforming and integrating.
• Imported metadata from repository, created new job categories, routines and data elements.
• Designed and developed jobs using DataStage Designer as per the mapping specifications using appropriate stages.
• Worked with Metadata Definitions, Import and Export of DataStage jobs using DataStage Manager.
• Used DataStage stages namely Hash file, Sequential file, Transformer, Aggregate, Sort, Datasets, Join, Lookup, Change Capture, Funnel, Peek, Row Generator stages in accomplishing the ETL Coding.
• Create parameter set to assign a value to job at run time.
• Used Perl for file parsing for loading into destination.
• Created JIL files for Datastage jobs entry in Autosys.
• Worked extensively With AutoSys for scheduling Jobs, Provided Production support for AutoSys.
• Used HP Quality Center to work on the defects raised.
• Used Unix Script to copy files from Development Server to Production Server for testing Datastage jobs.
• Worked with Unix admin to resolved datastage bug related error.
• Developed job sequences to execute a set of jobs with restart ability, check points and implemented proper failure actions.
• Wrote UNIX shell scripts to read parameters from files for invoking DataStage jobs. Created source to target mapping and job design documents from staging area to Data Warehouse.
• Used DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on an ad hoc or scheduled basis .
• Worked on troubleshooting, performance tuning, performance monitoring and enhancement of DataStage jobs.
• Involved in performance tuning and optimization of DataStage mappings using features like Pipeline and Partition Parallelism and data/index cache to manage very large volume of data.
• Wrote several complex SQL, VSQL queries to extensively test the ETL process. Provided production support and performed enhancement on existing multiple projects.
• Worked with Autosys, Maestro for setting up production job cycles for daily, weekly monthly loads with proper dependencies.
• Performed unit testing and system integration testing in dev and UAT environments.
• Participated in Disaster Recovery exercise and took ownership of coordinating with other teams to complete DR successfully and meeting the RTO's
• As a technical lead, coordinated/administered all onsite and offshore work with team members.
• Confidential
• ETL Designer/ DataStage Developer, Technical Lead
• The purpose of this Project is to deliver the missing historical data elements, for the bank's Retail exposures from systems of record or data warehouses, into the repository to achieve at least the minimum requirement of five years of account level history, including a period of economic downturn.
• The main aim is to provide variety of services like Data storage, Data Analysis, Data warehouse, Data mart etc. which can adopt consistent tailored processes in order to strive and fulfill promise of commitment and reliability to Customers.

Environment: IBM DataStage 8.0 Designer, Director, Manager, Parallel Extender , Oracle 10g, DB2 UDB, Flat files, Sequential files, Autosys, Maestro TOAD 9.6, UNIX

Responsibilities:

• Involved as primary on-site ETL Developer during the analysis, planning, design, development, and implementation stages of projects using IBM Web Sphere Datastage.
• Prepared Data Mapping Documents and Design the ETL jobs based on the DMD with required Tables in the Dev Environment.
• Active participation in decision making, QA meetings and regularly interacted with the Business Analysts and SMEs to gain a better understanding of the Business Process, Requirements Design.
• Used DataStage as an ETL tool to extract data from sources systems, loaded the data into the Oracle/DB2 database.
• Designed and Developed DataStage Jobs to Extract data from heterogeneous sources, Applied transform logics to extracted data and Loaded into Data Warehouse Databases.
• Created DataStage jobs using different stages like Transformer, Aggregator, Sort, Join, Merge, Lookup, Data Set, Funnel, Remove Duplicates, Copy, Modify, Filter, Change Data Capture, Change Apply, Sample, Surrogate Key, Column Generator, Row Generator, Etc.
• Extensively worked with Join, Look up Normal and Sparse and Merge stages.
• Extensively worked with sequential file, dataset, file set and look up file set stages.
• Extensively used Parallel Stages like Row Generator, Column Generator, Head, and Peek for development and de-bugging purposes.
• Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on ad hoc or scheduled basis.
• Developed complex stored procedures using input/output parameters, cursors, views, triggers and complex queries using temp tables and joins.
• Converted complex job designs to different job segments and executed through job sequencer for better performance and easy maintenance.
• Created shell script to run datastage jobs from UNIX and then schedule this script to run datastage jobs through scheduling tool.
• Coordinate with team members and administer all onsite and offshore work packages.
• Performed performance tuning of the jobs by interpreting performance statistics of the jobs developed.
• Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, prepared test data for testing, error handling and analysis.
• Participated in weekly status meetings.
• Developed Test Plan that included the scope of the release, entrance and exit criteria and overall test strategy. Created detailed Test Cases and Test sets and executed them manually.

As a technical lead, coordinated/administered all onsite and offshore work with team members.

Confidential

ETL Designer/ DataStage Developer

AXA Insurance provides a wide range of insurance policies such as Life Insurance, Health Insurance, General Insurance etc. The project was to design, develop and maintain a data warehouse for their vendor's data, internal reference data and work with their DBA to ensure that the physical build adheres to the model blueprint.

Environment: IBM DataStage 8.0 Designer, Director, Manager, Parallel Extender , Oracle 10g, DB2 UDB, Flat files, Sequential files, Autosys, TOAD 9.6, UNIX

Responsibilities:

• Involved in understanding of business processes and coordinated with business analysts to get specific user requirements.
• Defined granularity, aggregation and partition required at target database.
• Used DataStage as an ETL tool to extract data from sources systems, loaded the data into the database.
• Imported table/file definitions into the Datastage repository.
• Performed ETL coding using Hash file, Sequential file, Transformer, Sort, Merge, Aggregator stages compiled, debugged and tested. Extensively used stages available to redesign DataStage jobs for performing the required integration.
• Extensively used DataStage Tools like Infosphere DataStage Designer, Infosphere DataStage Director for developing jobs and to view log files for execution errors.
• Controlled jobs execution using sequencer, used notification activity to send email alerts.
• Scheduled job runs using DataStage director, and used DataStage director for debugging and testing.
• Created shared containers to simplify job design.
• Performed performance tuning of the jobs by interpreting performance statistics of the jobs developed.
• Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, regression testing, prepared test data for testing, error handling and analysis.
• Created queries to compare data between two databases to make sure data is matched.
• Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions on an ad hoc or scheduled basis.
• Created shared container to incorporate complex business logic in job.
• Written UNIX shell script to monitor the Datastage job on daily basis and to send an email whenever the job fails.
• Created and modified batch scripts to ftp files from different server to datastage server.
• Extensively used slowly changing dimension Type 2 approach to maintain history in database.
• Worked on logical data model creation.
• Used PL/SQL programming to develop Stored Procedures/Functions and Database triggers.

Confidential

ETL Designer/ DataStage Developer

The project objective was to collect, organize and store data from different operational data sources to provide a single source of integrated and historical data for the purpose of reporting, analysis and decision support to improve the client services.

Environment : IBM / Ascential DataStage 7.5, Quality Stage 7.5, Autosys, Windows NT, UNIX, Oracle 9i, Sequential files, .csv files

Responsibilities

• Used IBM DataStage Designer to develop jobs for extracting, cleaning, transforming and loading data into data marts/data warehouse.
• Worked closely with data warehouse architect and business intelligence analyst in developing solutions.
• Used different stages of Datastage Designer like Lookup, Join, Merge, Funnel, Filter, Copy, Aggregator, and Sort etc.
• Sequential File, Aggregator, DB2, Transformer, Hashed-File etc were extensively used to develop the server jobs.
• Converted complex job designs to different job segments and executed through job sequencer for better performance and easy maintenance.
• Developed several jobs to improve performance by reducing runtime using different partitioning techniques.
• Designed DataStage jobs using Quality Stage stages in 7.5 for data cleansing data standardization Process
• Staged the data coming from various environments in staging area before loading into DataMarts.
• Involved in writing Test Plans, Test Scenarios, Test Cases and Test Scripts and performed the Unit, Integration, system testing and User Acceptance Testing.
• Used stage variables for source validations, to capture rejects and used Job Parameters for Automation of jobs.
• Created stored procedures, functions, sequences, triggers.
• Wrote complex SQL queries using joins, sub queries and correlated sub queries.
• Performed debugging and unit testing and System Integrated testing of the jobs.
• Wrote customized server/parallel routines according to complexity of the business requirements.
• Created shell scripts to perform validations and run jobs on different instances DEV, TEST and PROD .
• Created mapping documents for all the ETL Jobs interfaces, Data Warehouse and Data Conversion activities .

Confidential

ETL Designer/ DataStage Developer

Johnson Controls is a global diversified technology and industrial leader serving customers in over 150 countries. It has 142,000 global employees today create quality products, services and solutions to optimize energy and operational efficiencies of buildings lead-acid automotive batteries and advanced batteries for hybrid and electric vehicles and interior systems for automobiles. From design to manufacturing to delivery, these products, services and solutions incorporate sustainability features and more energy efficient

Environment : IBM Datastage, Windows NT, UNIX, Oracle 9i, Sequential files, .csv files, TOAD.

Responsibilities

• Involved in full development cycle of Planning, Analysis, Design, Development, Testing and Implementation.
• Used DataStage as an ETL tool to extract data from sources systems, loaded the data into the database.
• Imported table/file definitions into the Datastage repository.
• Extensively used DataStage Tools like Infosphere DataStage Designer, Infosphere DataStage Director for developing jobs and to view log files for execution errors.
• Used different stages of Datastage Designer like Lookup, Join, Merge, Funnel, Filter, Copy, Aggregator, and Sort etc.
• Designed DataStage jobs using Quality Stage stages in 7.5 for data cleansing data standardization Process
• Involved in writing Test Plans, Test Scenarios, Test Cases and Test Scripts and performed the Unit, Integration, system testing and User Acceptance Testing.
• Used stage variables for source validations, to capture rejects and used Job Parameters for Automation of jobs.
• Developed and modified triggers, functions and stored procedures for data conversions.
• Wrote complex SQL queries using joins, sub queries and correlated sub queries.
• Created some Custom reports and Forms for the end users to check the details and errors.
• Performed debugging and unit testing and System Integrated testing of the jobs.
• Wrote customized server/parallel routines according to complexity of the business requirements.
• Created mapping documents for all the ETL Jobs interfaces, Data Warehouse and Data Conversion activities .