We provide IT Staff Augmentation Services!

Etl Designer/ Datastage Senior Developer Resume

Dallas, TX

SUMMARY

  • Over 9 years of experience in ETL Design, Development, Testing, implementation, documentation, maintenance and Data Migration life cycle using IBM DataStage9.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, Director and DQ Analyzer
  • Experienced in integration of various data sources (Teradata, DB2 - UDB, SQL Server,PL/SQL,Oracle, Netezzaand 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, V9.1 (DataStage, Quality Stage, Information Analyzer, DQ Analyzer), Ascential DataStage V7.5 (Designer, Director, Manager, Parallel Extender)

Database: Vertica, DB2, Oracle, Netezza

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, Dallas,TX

ETL Designer/ DataStage Senior Developer

Environment: IBM DataStage 11.5 (Designer and Manager, Director), Teradata, Oracle 11g, DB2 UDB, Flat files, Sequential files, Autosys, Maestro, TOAD 9.6, UNIX Scripting.

Responsibilities:

  • Involved in creating specifications for ETL processes, finalized requirements and prepared specification document.
  • 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.
  • Developed ETL processes to extract the source data and load it into the enterprise-wide data warehouse after cleansing, transforming and integrating.
  • 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.
  • Created JIL files for Datastage jobs entry in Autosys.
  • Worked extensively With AutoSys for scheduling Jobs, Provided Production support for AutoSys.
  • Prepared Data Mapping Documents and Design the ETL jobs based on the DMD with required Tables in the Dev Environment.
  • Extensively used Parallel Stages like Row Generator, Column Generator, Head, and Peek for development and de-bugging purposes.
  • Extensively used slowly changing dimension Type 2 approach to maintain history in database.
  • Wrote customized server/parallel routines according to complexity of the business requirements.
  • Created stored procedures, functions, sequences, triggers.
  • Used Perl for file parsing for loading into destination.
  • Created JIL files for Datastage jobs entry in Autosys.
  • 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.

Confidential, White Plains, NY

ETL Designer/ DataStage Senior Developer, Technical Lead

Environment: IBM DataStage 8.7 (Designer and Manager, Director), Teradata, Oracle 11g, 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.
  • Pulled and pushed the data into Teradata environment. Loaded the data into Dimension and fact tables in Teradata.
  • 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

Environment: IBM DataStage 8.0 (Designer, Director, Manager, Parallel Extender), Teradata, 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 theTeradata, Oracle/DB2database.
  • 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.
  • 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

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.
  • 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.
  • Created shared container to in corporate 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

Environnent: 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 likeLookup, 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 toimprove performanceby reducing runtime using different partitioning techniques.
  • Designed DataStage jobs usingQuality Stage stages in 7.5for data cleansing & data standardization Process
  • Staged the data coming from various environments in staging area before loading intoDataMarts.
  • Involved in writingTest Plans, Test Scenarios, Test Casesand Test Scripts and performed the Unit, Integration, system testing and User Acceptance Testing.
  • Used stage variables for source validations, to capture rejects and usedJob Parametersfor Automation of jobs.
  • Wrote complex SQL queries using joins, sub queries and correlated sub queries.
  • 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

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.
  • Used different stages of Datastage Designer likeLookup, Join, Merge, Funnel, Filter, Copy, Aggregator, and Sort etc.
  • Designed DataStage jobs usingQuality Stage stages in 7.5for data cleansing & data standardization Process
  • Involved in writingTest Plans, Test Scenarios, Test Casesand Test Scripts and performed the Unit, Integration, system testing and User Acceptance Testing.
  • Used stage variables for source validations, to capture rejects and usedJob Parametersfor Automation of jobs.
  • Developed and modified triggers, functions and stored procedures for data conversions.
  • 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).

Hire Now