PROFESSIONAL SUMMARY:

• Datastage/ETL Developer with over 8 years of experience and high performance in Information technology, especially in Business Intelligence, SQL and Client/server business systems.
• Involved in complete Software Development life - cycle (SDLC) of various projects, including Business Requirements, Analysis, Data modeling, Application Design, Application development, Performance tuning, Test Support, Implementation, Production support and maintenance.
• Worked extensively on designing and administrating Data stage jobs in V 9.1,8.7,8.1,8.x,7.5 worked in server and parallel jobs using different processing stages like Transformer, Lookup, Merge, Join, Remove Duplicate, Funnel, Aggregator, Filter, Sort, Pivot and successfully ran jobs from medium to high complexity levels.
• Extensive experience in Designing, Developing and maintaining applications in data warehouse for Healthcare, Retail, pharmaceuticals and Insurance sectors.
• Worked with Dimensional modeling, Data migration, Data cleansing, ETL Processes for data warehouses.
• Created local and shared containers to facilitate ease and reuse of jobs.
• Knowledge in dimensional Star Schema, Snowflakes Schema methodologies to design mappings between sources to operational staging targets, physical database designing (OFA standard) of Oracle databases and implemented logic for Slowly Changing Dimensions (SCD).
• Expert in Enterprise wide Data warehouse Architecture and good in Database programming for Data Warehouses, Dimensional Modeling, OLTP and Dimensions and Facts tables.
• Excellent knowledge in creating and managing Conceptual, Logical and Physical Data Models.
• Expertise in building Operational Data Store (ODS), Data Marts, and Decision Support Systems (DSS) using Multidimensional Model (Kimball and Inmon), Star and Snowflake schema design.
• Experience in analyzing the data generated by the business process, defining the granularity, source to target mapping of the data elements, creating Indexes and Aggregate tables for the data warehouse design and development.
• Experience in Mapping Server/Parallel Jobs in Data Stage to populate tables in Data warehouse and Data marts.
• Data profiling experience on IBM Information Analyzer 8.0.1/ Profile stage (validates data values and column/table relationships, source to target field mappings, source system profiling and analysis ) join with SME & Data modeler.
• Experience in Scheduling and running Jobs using DataStage Director, External tools like Control-M, Autosys and Unix Crontab Utility, Tivoli Scheduler.
• Experience in analyzing the data generated by the business process, defining the granularity, source to target mapping of the data elements, creating Indexes and Aggregate tables for the data warehouse design and development.
• Experienced in scheduling sequence, parallel and server jobs using Data Stage Director, UNIX scripts (C, Bourne and K) and scheduling tools.
• Good at running Data stage director, viewing job logs, finding errors and fixing them in given time.
• Excellent experience in TERADATA Utilities: BTEQ, Fast load, Multiload, Fastexport.
• Proven track record in addressing production issues like performance tuning, system integration testing, implementation and maintenance of databases jobs enhancement and memory issues.
• Worked on UNIX shell scripts using K-shell for the automation of processes and scheduling the Data Stage jobs using wrappers .
• Worked with various databases like SQL Server, Oracle, Teradata and DB2 including database development.
• Proficient in writing, implementation and testing of triggers, stored procedures and functions in PL/SQL and has indexes in Teradata database.
• Used Enterprise Edition/Parallel stages like Datasets, Change Data Capture, Row Generator and many other stages in accomplishing the ETL Coding.
• Participated in discussions with Project Manager, Business Analysts and Team Members on any technical and Business Requirement issues
• Experienced with Quality stage for data profiling, standardization, matching and survivorship.

TECHNICAL SUMMARY:

ETL: IBM Information Server, Data Stage 9.1/8.7/8.5/8. x/7.5,Quality stage 8.1/7.1

Operating Systems: IBM AIX, DG UNIX. Windows XP/Windows 7, Linux

Database: Oracle 10g/9i/8i, Teradata, SQL Server 2005/2008,DB2

Database Tools: Toad 8.6.1,Teradata SQL Assistant, BTEQ

Scheduling Tools: Tivoli workload Scheduler 8.5.1, Corn tab utility, Autosys 11.3,Control-M,ESP

Languages: Shell Scripts(C,K and Bourne), SQL,PL/SQL,C/C++,Java

Other Tools: Microsoft Visio 2007, Microsoft Office 2010

PROFESSIONAL EXPERIENCE:

Confidential, Somers, NY

Sr. DataStage developer

Responsibilities:

• Involved in understanding of business processes and coordinated with business analysts to transform it into technical requirements and responsible for deliverables.
• Worked with Autosys for setting up production job cycles for daily, weekly, monthly loads with proper dependencies.
• Validated the report generated using Business Objects using PL/SQL queries and Data stage ETL jobs.
• Worked extensively with Data Stage 9.1 V - Designer and Director to load data from source extract files to warehouse.
• Proven track record in troubleshooting of Data Stage jobs and addressing production issues like performance tuning and enhancement.
• Designed and developed ETL processes using Data Stage designer to load data from Oracle, MS SQL, Flat Files and XML files to staging database and from staging to the target Data Warehouse database.
• Used Quality Stage to ensure consistency, removing data anomalies and spelling errors of the source information before being delivered for further processing .
• Used various stages of Quality stage like standardize stage, survive stage, format convert, transfer and unjoin stages.
• Used DataStage stages namely Hash file, Sequential file, Transformer, Aggregate, Sort, Datasets, Join, Lookup, Change Capture, Funnel, Row Generator stages in accomplishing the ETL Coding.
• Excellent knowledge of studying the data dependencies using metadata stored in the repository and prepared batches for the existing sessions to facilitate scheduling of multiple sessions.
• Data base design & data model, Logical & Physical design with hands on experience on DDL and DML SQL operations.
• Enhanced the reusability of the jobs by making and deploying shared containers which is used in multiple instances of the jobs .
• Extensively involved in the data migration team to build the Re-usable Data stage job templates, common parameter sets, common Data stage job containers, SQL extract procedures and common re-usable shell scripts.
• Tuned Data Stage jobs for better performance by creating DataStage Hash files for staging the data and lookups.
• Scheduled the server jobs using DataStage Director, which are controlled by DataStage engine and also for monitoring and performance statistics of each stage.
• Created Parameters and Parameter sets where necessary.
• Used Fast load, Multi load scripts to load data into Oracle from flat files and COBOL copybooks.
• Performed debugging on these jobs using Peek Stage by outputting the data to Job Log or a Stage.
• Developed Job Sequencer to execute jobs in proper sequence. Also automated email messaging was implemented using Sequencer to notify the operations team of any data load issues such as job failure, dropped rows, rejected rows etc.
• Experienced in Scheduling and running Jobs using DataStage Director, External tools like Control-M, Autosys and Unix Crontab.
• Used Quality Stage to ensure consistency, removing data anomalies and spelling errors of the source information before being delivered for further processing.
• 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.
• 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.
• Executed Pre and Post session commands on Source and Target database using UNIX Shell scripting (Bourne and C).
• Participated in weekly status meetings. Worked with Developers to troubleshoot and resolve issues in job logic as well as performance.
• Reporting was done using Cognos Reporting tools.

Environment: IBM Info Sphere Data Stage 9.1/8.7 (Administrator, Designer, Director, Manager), Quality Stage 8.1, Control-M, Autosys, Toad, SQL Server, PL/SQL, DB2, Oracle 10g/9i, UNIX/AIX 6.0/5.2 and Windows 7.

Confidential, Boston, MA

Sr. Datastage Developer

Responsibilities:

• Designed the ETL jobs using IBM Web Sphere Information Server 8.5 to Extract, Transform and load the data into Staging and then into Oracle Database.
• Designed complex Data Stage mappings (Parallel Extender and Server) between sources to the target-using Oracle as the target database.
• Wrote scripts to automate Data stage jobs on daily bases
• Used the Data stage Director to schedule running the job, testing and debugging its components, and monitoring the resulting executable versions.
• Designed Proof of Concept (POC) for certain jobs which required heavy manipulations to be performed as part of fine tuning.
• Worked on different partitioning methods (Round Robin, Entire, Hash by field, Modulus and Range) in processing sequential data.
• Involved in production automation and maintenance and also was responsible for troubleshooting issues that arose while upgrading data stage.
• Extensively used import and export utility in data stage manager to import metadata and for creating new categories and new data elements.
• Implemented local containers for same job and shared containers for multiple jobs which have the same business logic.
• Created Job Parameters and Environment variables to run the same job for different sources and targets.
• Used Datastage to transform the data to multiple stages and prepared documentation.
• Extensively used Information Analyzer Client to perform Database comparisons and OLTP analysis.
• Used Parallel Extender Development/Debugging stages like Row generator, Column Generator, Head, Tail and Peek for debugging of the jobs.
• Extracted data from source systems Oracle 10g/9i, Sequential files and Flat files to Oracle.
• Created shared and local containers to simplify and modularize server job designs by replacing complex areas of the diagram with a single container stage.
• Performed the Back-End integration testing to ensure data consistency on front-end by writing and executing SQL Queries. Provided management with metrics, reports, and schedules and was responsible for entering, tracking bugs .
• Performed debugging of some existing jobs using the Director to test and fix the errors.
• Defined data base triggers & PL/SQL stored procedures for business validations.
• Implemented and hardcoded high performance Data Stage routines. Migrated projects from development to QA to Production environments.
• Involved in Quality Assurance; Unit Testing and Integration Testing to test jobs and also the system process flow.
• Tested if the data is seen correct in the User Interface (ICM).
• Identified and documented data sources and transformation rules required to populate and maintain data warehouse.
• Excellent knowledge of studying the data dependencies using Metadata of DataStage and preparing job sequences for the existing jobs to facilitate scheduling of multiple jobs.
• Used Surrogate Keys to keep track of Slowly Changing Dimensions (SCD).
• Extensively used Pivot stage to pivot the source data to achieve required table structures like converting data from rows into a column.
• Used Autosys job scheduler for automating the monthly regular run of DW cycle in both production and UAT environments.
• Worked with Developers to troubleshoot and resolve issues in job logic as well as performance.

Environment: Information server DataStage 8.7/8.5(Designer, Director, Administrator ), Quality Stage, ESP Scheduler, AIX 5.1, Windows XP, Oracle 10g/9i, SQL, PL/SQL, DB2, Teradata, WINSCP, Parallel Extender, and Information Analyzer (IA), Windows 7.

Confidential, New York

Sr. ETL Developer

Responsibilities:

• Used IBM Data stage Designer to develop jobs for extracting, cleaning, transforming and loading data into data marts/data warehouse.
• Involved currently in analyzing the source systems and finding out the feasibility of moving the data to CRM System.
• Developed several jobs to improve performance by reducing runtime using different partitioning techniques.
• Extensively used the designer to develop various parallel jobs to extract, transform, integrate and load the data into Corporate Data warehouse (CDW).
• Interacted with Business Analysts to finalize the requirements and documented the technical design document for Coding.
• Used shared containers for server jobs & shell scripts for job sequences, for handling rejected data, handling null values and complete email reporting of data changes for production support.
• Designed parallel jobs using stages such as Join, Merge, Lookup, Remove Duplicates, Copy, Filter, Funnel, Dataset, Lookup, Pivot, and Sort, Surrogate key Generator, Change Data Capture (CDC), Modify, Row Generator and Aggregator.
• Extensively worked with all the new features in IBM information Server.
• Performed the Integration and System testing on the ETL application.
• Designed Mappings between sources to operational staging targets, using Star Schema, Implemented logic for Slowly Changing Dimensions (SCD).
• Used Built-in, Plug-in and Custom Stages for extraction, transformation and loading of the data, provided derivations over DS Links.
• Extensively wrote Custom Routines and Transformations as per the business requirements.
• Involved in Extracting, cleansing, transforming, integrating and loading data into data warehouse using Data stage Designer.
• Involved in the migration of DataStage jobs from development to QA and then to production environment.
• Working with BI team to apply the business rules for OLAP, designing the Frame Work models.
• Encompasses Database designing, ETL phases of Data Warehousing. This is achieved with an emphasis on relational data modeling and dimensional data modeling for OLTP and OLAP systems.
• Involved in the Performance Tuning of the Data Stage jobs using different methodologies like configurations of the environment variable file, designing and editing configurations, increasing the reading as well as the writing speed while fetching or loading data to files or databases.
• Created stored procedures to be used by front end report development.
• Imported and exported Repositories across Data Stage projects using Data Stage Manager.
• Used Parallel jobs for splitting bulk data into subsets and dynamically distribute to all available nodes to achieve best job performance.
• Tuned transformations and jobs for Performance Enhancement.
• Created Batches (DS job controls) and Sequence jobs to control set of jobs.
• Performed extraction, transformation and loading of data using different types of stages and by performing derivations over the links connecting these stages.
• Enhanced the Job Performance by using proper Partitioning methods and analyzing the resources utilized using Job Monitor. 
• Used Unix Script to copy files from Development Server to Production Server for testing DataStage jobs. 
• Developed Job Sequencer to execute jobs in proper sequence. Also automated email messaging was implemented using Sequencer to notify the operations team of any data load issues such as job failure, dropped rows, rejected rows etc.
• Worked with Data Stage Director to run, schedule, monitor and view logs for jobs.
• Assisted operation support team for transactional data loads in developing SQL & Unix scripts

Environment: IBM Info Sphere Data stage 8.5/8.1, SQL, PL/SQL, DB2,Oracle 9i/10g, Quality Stage, Profile Stage, TOAD, UNIX/AIX 4.2, Windows XP, MS Word, Excel, Crontab, Clear Quest.

Confidential, Minneapolis, MN

ETL/DataStage Developer

Responsibilities:

• Developed DataStage server jobs to extract, transform and load data into data Warehouse from various sources like relational databases (DB2), Oracle 9i, flat files etc.
• Worked with Business Analysts to analyze the business requirements and functional specifications.
• Used Parallelism concepts for distributing load among different processors by implementing Pipeline and partitioning of data.  Involved in Designing Parallel and server Jobs.
• Interpreted logical and physical data models for Business users to determine data definitions and establish referential integrity of the system. 
• Involved in creating the projects. Improved application performance by tuning SQL statements and fixing proper indexes, Designed data models.
• Extensively used Parallel Stages like Join, Merge, Lookup, Filter, Remove Duplicates, Funnel, Row Generator, Modify, Peek etc. for development and de-bugging purposes
• Extensively worked with Data Stage Job Sequences to Control and Execute Data Stage Jobs and Job Sequences using various Activities and Triggers.
• Used Data Stage Director and the runtime engine to schedule running the server jobs, monitoring scheduling and validating its components.
• Scheduled the parallel jobs using DataStage Director, which is controlled by DataStage engine and also for monitoring and performance statistics of each stage. 
• Created data quality standardization jobs using Web Sphere Quality Stage and also by writing PL/SQL queries to identify and analyze data anomalies, patterns, inconsistencies etc.
• Responsible for metadata management, new job categories and creating new data elements, creating shared containers for reusability .
• Worked on performance tuning to address very critical and challenging issues.
• Implemented the Surrogate Key by using Key Management functionality for newly inserted rows in Data Warehouse.
• Responsible for daily verification that all scripts, downloads, and file copies were executed as planned, troubleshooting any steps that failed, and providing both immediate and long-term problem resolution. 
• Developed UNIX scripts to automate the Data Load processes to the target Data warehouse.
• Created Error Tables containing data with discrepancies to analyze and re-process the data.
• 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 with DataStage Manager for importing metadata from repository, new job Categories and creating new data elements.
• Generations of Surrogate IDs for the dimensions in the fact table for indexed, faster data access.
• Implemented shared containers to use in multiple jobs, which have same business logic.
• Interaction with the business users to better understand the requirements and document their expectations, handling the current process, modifying and created the jobs to the updated requirements, handle the load process to data mart and eventually data warehouse .
• Involved in the design, development and testing of the PL/SQL stored procedures, packages and triggers for the ETL processes.
• Defined and implemented approaches with Metadata Definitions, Import and Export of Datastage jobs using Datastage tools functionality.

Environment: Datastage 7.5/7.1 /EE (Parallel Extender), Quality Stage, DB2-UDB, Oracle9i/8i, UNIX, IBM (AIX), Shell Scripting, TOAD, PL/SQL, SQL Server, MS Project.