Data Stage ETL Developer

Executive Summary:

• Over 6 years of Experience in client/server and Business systems with Data Warehouse environments.
• Worked throughout the Software Development Lifecycle including requirements gathering, analysis, development, testing, implementation and post implementation support.
• Technical experience includes working with IBM InfoSphere 8.7/8.5/8.1 clients DataStage, Information Server, QualityStage, Information Analyzer , Ascential DataStage 7.5.
• Have experience in scripting XML, UNIX Shell Scripting, SQL, PL/SQL and hands on in scheduling the ETL jobs using scheduling tools like Control-M, UC -4 etc.
• Worked on databases Oracle, Teradata, DB2, SQL Server as backend in testing the data loads and implementing extraction of data using PreSQL and Post SQL queries.
• Good understanding in datawarehouse concepts and worked on SCD Type 2.
• Worked with most of the parallel stages applying different partitioning techniques.
• Worked with Job Sequencers in supporting the control flow of execution of ETL Jobs.
• Worked on Load Utilities of Teradata in handling huge volumes of data.
• Experience in creating Technical Design documents, STMs and Offshore co-ordination.

TECHNICAL SKILLS:

PROFESSIONAL EXPERIENCE

Sr. Data Stage Developer

CONFIDENTIAL a leading financial security company and specializes in Life insurance and CONFIDENTIAL Insurance. CONFIDENTIAL project was aimed at implementing enterprise wide data warehouse to cater to all the reporting needs of the organization. The main goal of the EDW Project is to extract the data from source system GIF , standardized and loads in to Enterprise Data Ware house EDW .

Responsibilities:

• Involved in the Analysis of the functional side of the project by interacting with functional experts to design and write technical specifications.
• Worked on the Architecture of ETL batch framework.
• Developed DataStage jobs for Data acquisition, staging to ODS and then to the Data warehouse constantly from different OLTP sources.
• Extracted data from sources like Oracle, DB2, Teradata and Flat Files.
• Successfully implemented pipeline and partitioning parallelism techniques and ensured load balancing of data.
• Debug, test and fix the transformation logic applied in the parallel jobs
• Provided estimates for ETL development effort and regularly updated senior management regarding the progress.
• Designed and developed parallel jobs, server and sequence jobs using DataStage Designer.
• Experience in using different types of stages like Transformer, Aggregator, Merge, Join, Lookup, and Sort, Remove duplicate, Funnel, Filter, Pivot, change capture, change apply, and Shared containers for developing jobs.

Environment: Infosphere DataStage 8.7 and 8.5 Designer, Director and Administrator , IBM DB2,Teradata, Autosys Scheduling tool, Oracle 11g/10g/9i, Shell Scripts, AIX.

CONFIDENTIAL

Sr. Data Stage Developer

CONFIDENTIAL is world's sixth-largest consumer health company, world's largest and most diverse medical devices and diagnostics company, world's largest and most diverse medical devices and diagnostics company And the world's sixth-largest pharmaceuticals company. The major job involved in cleansing the data and transforming the data to the staging area then loading the data in the Data Marts. This Data mart enables an insight into the current and future sales situation/needs based on the information received from the Data Warehouse.

Responsibilities:

• Involved in requirements gathering, Analysis, Design and Develop and maintenance Phases.
• Worked closely with Business Analysts and Architects to gather the requirements and business rules.
• Developed/Created source to target mapping documents and technical specification documents.
• Involved in the performance tuning of the existing parallel jobs without affecting the current functionality
• Review the code and suggest the modifications/changes.
• Obtained detailed understanding of data sources, Flat files and Complex Data Schema.
• Resolving Defects and issues in production environment and bug fixing.
• Worked on standing up the Performance Environment by creating Value Files, Parameter Sets, running one time DataStage jobs for inserting/updating values in the various tables
• Project maintenance and support for the end users and business analysts to ensure the application is working as expected.
• Implementing enhancements to the project modules and applications.
• Designed parallel jobs using various stages like Join, Transformer, Sort, Merge, Filter and Lookup, Sequence, Modify, Peek etc. stages.
• Broadly involved in Data Extraction, Transformation and Loading ETL process from Source to target systems using DataStage PX.
• Extensively used DataStage Manager, Designer, Administrator, and Director for creating and implementing jobs.
• Developed UNIX shell scripts to manipulate the data.
• Successfully Integrated data across multiple and high volumes of data sources and target applications.
• Automation of ETL processes using DataStage Job Sequencer and CA7 scheduler tool to schedule the jobs.

Environment: IBM DataStage 8.5, Teradata, Oracle 10g, DB2, AIX.

CONFIDENTIAL

DataStage Developer

CONFIDENTIAL is a premier wholesale grocer's product company. This project involves building a Data Warehouse for C S with Orders related data. The primary aim of this project is to integrate the Orders, Invoice and Shipping to provide a better decision support system of sales volume and revenue.

Responsibilities:

• Worked with the business analysts and the DBA for requirements gathering, business analysis, testing, and project coordination.
• Documented data sources and transformation rules required populating and maintaining data warehouse. Developed DataStage parallel jobs to load data from sequential files, flat files, XML files and Oracle.
• Used DataStage Designer to design and develop jobs for extracting, cleansing, transforming, integrating, and loading data into different Data Marts like orders, invoice, shipping and Quotes.
• Used several stages like sequential file, Aggregator, Funnel, Change Capture, Change Apply, Row Generator, Peek, External source, Remove Duplicates, Copy, Lookup and RDBMS during the development process of the DataStage jobs.
• Used multiple invocation ids to run multiple instances of jobs at the same time.
• Designed job sequencers to automate the process and document all the job dependencies, predecessor jobs frequencies to help the production support people better understand the job runs.
• Resolved data issues during the testing processes and reloaded the data with all the necessary modifications.
• Extensively written UNIX scripts in AIX environment.
• Used the DataStage Director and its run-time engine to schedule and run the parallel jobs, testing and debugging its components and monitoring the resulting executable versions on an ad hoc or scheduled basis.
• Used SQL Developer and Teradata SQL assistant for analyzing data and writing SQL, PL/SQL scripts to perform DDL operations.

Environment: IBM WebSphere information server 8.1, Oracle 10g, Teradata, Flat files, XML Files, SQL Developer, PL/SQL.

CONFIDENTIAL

Datastage Developer

CONFIDENTIAL

is a provider of software solutions and consulting services to organizations, assisting clients in the entire process of planning, selecting, implementing, and supporting the system to address enterprise-wide information management goals.

Responsibilities:

• Used DataStage as an ETL tool to extract data from source systems and load into the Target Database tables.
• Used DataStage Designer to develop server jobs for extracting, transforming, integrating and loading data to targets.
• Designed complex DataStage mappings between sources to Operational staging targets.
• Used DataStage Director to Validate, Run and Schedule the jobs.
• Used DataStage Manager for importing Metadata Table Definition.
• Used Lookup transformations to manipulate the information.
• Used Annotation Stage for documenting on the Job designer window.
• Taking the Backups of the developed Jobs using DataStage Manager Export/Import utility.
• Worked extensively in performance tuning by removing performance bottlenecks that occurs in the target and source mapping session.
• Used Before/After Job-Subroutines in Job Properties.
• Defined Constraints and Stage Variables.
• Creating small data sets, loading to excel sheets and forwarding to business people for verifying the data.
• Created and Monitored sequences using DataStage sequencer.
• Tested if the data is validated after loading the data.

Environment: IBM DataStage 8.1, DB2, Oracle, AIX.

CONFIDENTIAL

DataStage Developer

• Solutions is a global leader in Providing software solution development and support services. CONFIDENTIAL
• Technology Solutions has made inroads in the field of Application Development, ERP Implementation and Support, IT consulting and E-Commerce Solutions. Ascential DataStage is used to load the data from sources to target. The statistical analysis and reports generated from this data warehouse helps the marketing group to make the right decisions.

RESPONSIBILITIES:

• Worked on Data stage tool to develop processes for extracting, cleaning, transforming, integrating, and loading data into data warehouse database and ODS.
• Designed DataStage Jobs and migrated to operational staging targets using Oracle as the target database in Data Warehouse.
• Used Data Stage Designer to develop jobs for Extracting, Cleansing, Transforming, Integrating and loading data into data warehouse database.
• Developed user defined Routines and Transform by using Universe Basic.
• Used DataStage Manager for importing metadata from repository, and new job categories.
• Extensively wrote user SQL coding for overriding for generated SQL query in Data Stage.
• Execute and scheduling the jobs using DataStage Director.
• Created Job Sequencer to execute the jobs in batches.
• Used the partitioning techniques in most accurate way for increasing the speed of job execution.
• Developed data conversion, integration, loading and verification specifications

ENVIRONMENT: ASCENTIAL DataStage 7.0, Oracle 9i, DB2 UDB, PL/SQL, SQL Loader, UNIX, Shell Scripting.