SUMMARY:

• In - depth experience in Extraction Transformation and Loading (ETL) processes using IBM Websphere DataStage 9.1/8.5/ 8.0.1/8.0/7.5/7.1/7.0/6.0/5.2/ EE (Parallel Extender).
• Utilized DataStage client tools: DataStage Designer, DataStage Director, DataStage Manager.
• Over 9 years of experience in systems analysis, design and development in the fields of data warehouse and client server technologies.
• Experience in migration, integration, development and implementation of ETL systems using strong project management methodologies with a focus on process efficiency and productivity.
• Utilized QualityStage Designer and Information Analyzer for column analysis, table analysis, primary key analysis and foreign key analysis.
• Experience with Parallel Extender (Orchestrate)/DataStage Enterprise Edition for parallel processing as part of ETL conversion process integrating multiple source systems.
• Extensive experience in using Ascential Version Control to migrate DataStage jobs from one environment to another and maintaining them.
• Experience in the integration of various data sources such as Oracle, DB2UDB, SQL Server, Sybase, Teradata, flat files and mainframe into the target tables.
• Worked extensively on SQL, PL/SQL, Packages, Procedures, Functions and Triggers. Experience in Performance Tuning of SQL statements, stored procedures and functions.
• Conducted unit testing and performance tuning of DataStage jobs.
• Proficiency in data warehousing techniques for data cleansing, Slowly Changing Dimension phenomenon, Surrogate key assignment and change data capture
• Expertise in deploying OLAP tools, Materialized Views and Partitioning techniques for highly responsive Decision Support Systems.
• Strong analytical, diagnostic and troubleshooting skills and ability to work in a dynamic, team-oriented environment.

TECHNICAL SKILLS:

ETL Tools: IBM Information Server 8.5/8.0.1 (Websphere Datastage and Qualitystage InformationAnalyzer),AscentialDatastage 9.1/8.5/8.0.1/7.5.2/7.1/6.0 , 5.1(Designer,Director,Manager, Administrator)

Data Modeling Tools: ERwin 4.2/3.5.2

Databases: Oracle 10g/9i, DB2, Teradata,MS SQL Server 2000/2005,Sybase

Database tools: SQL*Loader

Operating Systems: MS-DOS 6.22, Windows NT/98/2000, Solaris,IBM-AIX,UNIX, LINUX

Languages: SQL, PL/SQL, T SQL,C, C++,COBOL,UML, XML, HTML

Scripting: UNIX Shell Scripting,DataStage Basic Scripting,Perl Scripting

Other: Autosys,SOAP,SAPMM&BW,Webservices,MQSeries,TOAD, Developer 2000, Discoverer 4i/9i/10g,ASG Zena, Control-M

PROFESSIONAL EXPERIENCE:

ETL DataStage Developer

Confidential, MN

Responsibilities:

• Worked on POC for the UHG Framework and presented to the Management to in corporate it to the UDW project.
• Worked on TSD's (Technical specification document) for different Subject areas of the entities from Source to Atomic.
• Participated in Design, Data model and Business meetings to understand the requirements.
• Involved with senior management in terms of supplying input for key design and architecture decisions.
• Designed DataStage parallel jobs to move the data from source to Target for Member, Provider, and Claims subject areas.
• Developed and altered scripts to move the files from Landing zone to Estage on Unix server which is used by DataStage to extract.
• Documented S2T(Source to Target ) mappings, test case scenarios for the jobs with the source data and output.
• Developed jobs using different stages like Transformer, Aggregator, Lookup, Source dataset, External Filter, Row generator, XML, Sequential file, OCI, Column Generator and Peek stages.
• Implemented SCD Type 1 and SCD Type 2 by using Datastage Jobs as per the requirement.
• Worked extensively on XML files, Created XML using XML Output stage and XML connector stage with the XSD's and requirements provided from the business.
• Worked on designing the work flow from Source to Atomic with, audit tables and with referential integrity.
• Worked on the Unix scripts to collect Errors, Stats and the status of the flow and load the details to tables for operational team to prepare reports.
• Worked on the design flow for tracking down the Error record and send it back to the source system team to makes changes.
• Worked in Agile and Scrum methodologies for new requirements, change in the requirements and defects.
• Guided offshore team in understanding the requirements, reviewing the code and providing feedback.

Environment: IBM Infosphere DataStage 11.3/9.1/8.5 , Teradata database, SVN version control, Tivoli, SQL server database, Unix, ECG file transfer, HP-ALM, Putty, Ultra Edit.

ETL DataStage Developer

Confidential

Responsibilities:

• Worked closely with the Data Modeler and Database Administrator to understand the business processes and participated in the analysis of business requirements to build the data warehouse model.
• Extensively used DataStage Designer to develop the Parallel jobs and Server jobs.
• Designed several DataStage jobs using Join, Merge, Lookup, Change Apply, Change Capture, Funnel, Filter, ODBC,Column Generator,Transformer, Modify, Surrogate Key, Aggregator, Dcode, Row Generator and XML stages.
• Developed parameter-driven ETL processes to map source systems to target data systems with DataStage complete source system profiling.
• Implemented various partition methods like Hash, Entire, Auto, and DB2.
• Utilized various Database Connector stages like DB2/UDB, ODBC.
• Tuned DataStage jobs for better performance by creating DataStage Lookup files for staging the data and lookups.
• Developed complex transformations, surrogate keys, routines, dimension tables and fact tables.
• Utilized Environment Variables, Stage Variables and Routines for developing parameter-driven jobs and debugging them.
• Identified facts and dimensions for developing logical design of the data marts.
• Enhanced the job performance by using proper partitioning methods.
• Analyzed the resources utilized using Job Monitor.
• Extract and transform source data from DB2 database and XML sources to load into data warehouse in Oracle and Teradata.
• Perform data extractions and loads using Teradata database utilities like Multiload, Fast Load and Fast Export for large warehouse and for datamarts in Teradata.
• Developed sequencers to control the job execution of the DataStage jobs using Job activity and Email Notification stages.
• Created many user defined server routines, before/after job subroutines, shared containers which facilitated in implementing some of the complex logics. Also used stage variables.
• Extensively involved in creating the partitioned tables, indexes, triggers and many complex SQL queries
• Utilized DataStage Director for running and monitoring performance statistics.
• Performed the unit testing for jobs developed to ensure that it met the requirements.
• Conducted the migration of DataStage code to the Integration and Production and conducted Integration testing.
• Developed UNIX shell scripts to automate file manipulation and data loading procedures and to create indexes, drop the indexes in the tables and enhanced the master script to run the master sequencer.

Environment: DataStage 11.3/9.1/8.5 , ASG ZENA, Teradata, DB2 UDB, Oracle 10g, SQL Scripts, BTEQ Scripts, UNIX Shell Scripting, Serena, XML, MS SQL Server, MS Word.

ETL DataStage Developer

Confidential, IL

Responsibilities:

• Worked on analyzing the systems and gathering of requirements
• Involved in designing and development of data warehouse environment
• Involved in implementing star schema for the data warehouse using Erwin for Logical/Physical Data Modeling and Dimensional Data Modeling
• Extensively used Data Stage Manager, Designer, Director and Integrity for creating and implementing jobs
• Used the DataStage Director and it’s run-time engine to schedule running the jobs, testing and debugging it’s components, and monitoring the resulting executable versions (on an ad-hoc or scheduled basis)
• Developed jobs using different stages like Transformer, Aggregator, Lookup, Source dataset, External Filter, Row generator, XML, Sequential file, OCI, Column Generator and Peek stages.
• Implemented SCD Type 1 and SCD Type 2 by using Dataset, Change Capture, Transformer and Oracle Enterprise stages
• Used DataStage to transform the data to multiple stages and prepared documentation
• Improved performance by optimizing SQL statements and implemented Bitmap Indexes.
• Extensively used DataStage Director to validate, monitor, schedule and execute the jobs..
• Migrated the jobs from Development to QA and then to Production server using Version Control
• Created and monitored sequences using DataStage sequencer also Created containers to use in multiple jobs.
• Used Teradata database utilities for extraction and loading the records.
• Developed User Defined Sub Routines to implement some of the complex transformations, data conversions, code validations and using various Datastage supplied functions
• Created different types of reports, such as Master/Detail, Cross Tab and Chart
• Used Analyzer and Explorer to view reports from different prospect like Slice and Dice and Drill down

Environment: Data Stage 7.5.2 (Enterprise Edition), Erwin 4.2,Oracle 9i, SQL Server, Windows NT, Business Objects, Autosys, Perl scripting, XML, HTML, Teradata, Web services, Unix Shell Scripts.

ETL DataStage Developer

Confidential, CA

Responsibilities:

• Assisted in updating the logical model with all related entities, attributes and relationships between entities based on the rules provided by the business manager using Erwin.
• Involved in requirement gathering, analysis and study of existing systems.
• Extensively used DataStage Designer for developing Server jobs, Parallel jobs and performed complex mappings based on user specifications.
• Used DataStage Designer for Extracting Data, Performing Data Transformations and Aggregate Data.
• Performed lookups using Hashed files.
• Used DataStage Manager to store and manage reusable Metadata, and created custom routines and transforms for the jobs.
• Experience in using DataStage Director to Validate, Run, Schedule and Monitor DataStage jobs.
• Worked extensively on all the stages such as OCI, Sequential, Aggregator, Hashed Files, Sort, Link Partitioner, Link Collector and ODBC.
• Developed jobs in Parallel Extender using different stages like Transformer, Aggregator, Lookup, Source dataset, External Filter, Row generator, Column Generator and Peek stages.
• Distributed load among different processors by implementing the Partitioning of data in Parallel Extender.
• Used Ascential MetaStage for data integration and standardization for loading in Oracle data warehouse.
• Used Partition methods and collecting methods for implementing parallel processing.
• Used Control-M for scheduling to run the DataStage Jobs on a daily, weekly or monthly interval.
• Extracted data from Oracle and SQL server databases, transformed data and loaded into Oracle data warehouse.
• Also extracted data from Flat files, Sequential files and access files, applied transformations and loaded the data into Oracle data warehouse.
• Created DataStage jobs and Job sequences and tuned them for better performance.
• Developed Unix shell scripts to automate the Data Load processes to the target Data warehouse.

Environment: Data Stage 7.x (Parallel Extender), Ascential Datastage Version Control, Erwin4.0, Oracle 8i/9i, TOAD 7.4, PL/SQL, SQL Server, Web services, Windows NT server, Control-M and AIX Unix 5.2

ETL DataStage Developer

Confidential, MI

Responsibilities:

• Requirements gathering and source data analysis and identifying business rules for data migration to data warehouse and data marts.
• Automation of ETL processes using DataStage Job Sequencer, shell scripting and PL/SQL programming.
• Performing administrative tasks such as setting up users and privileges, Creating and maintaining DataStage Projects and timely Project cleanup.
• Tuning Jobs using various configurations by understanding it’s complexion during volume testing before moving to production.
• Development of parallel jobs in Parallel Extender framework. Implementing type. II slowly changing dimensions
• Extract and transform source data from DB2 Database and XML sources to load into data warehouse in Oracle and Teradata.
• Perform data extractions and loads using teradata database utilities like Multi Load, Fast Load and Fast Export for a large warehouse and for data marts in Teradata .
• Create Source and Target Data Base to Load Metadata for Profiling the Data and Generate the Analysis reports As per the Client Requirements
• Worked on Data Profiling to do the Column Analysis, Table Analysis, Primary Key Analysis
• Create custom Reports For Column Analysis & Tables Analysis adding Notes & comments for the Analysis results for Business User to develop the Business rules to Implement them in ETL process
• Generated Source to Target Specification Reports to Assist the Data Modelers
• Processing of XML documents using XML Stages.
• Writing Autosys JIL Scripts to activate the UNIX scripts in production i.e. Jils for the Box and Commands.
• Query optimization using HINTS, EXPLAIN Plan etc.
• Writing Job Control routines and Transform functions in the process of designing the job.
• Maintain backup of ETL code, shell scripts using the PVCS Tracker and PVCS version Manager.

Environment: DataStage 7.0, ProfileStage, Oracle 9.x, DB2, DB2 UDB, COBOL, JCL, UNIX-AIX, Teradata, Windows 2000/XP, Oracle Designer, SQL Server, AutoSys.

Oracle Developer

Confidential

Responsibilities:

• Installed the database and overseen the implementation of users and security of the application’s database.
• Set the environment of the client database using the export files.
• Involved in requirement analysis and design documentation.
• Designed and developed database tables necessary along with the necessary constraints to enforce business rules.
• Created and modified several Database Triggers, especially for Audit and Security purposes
• Developed many Stored Procedures, Packages, and Cursors and involved in the performance tuning of PL/SQL.
• Developed packages for various financial analysis.
• Performed several imports on the same database instance.
• Resolved several hardcore business rules and issues at the client site.
• Developed several Complex Reports for various requirements using to increase the functionality and usability.
• Designed PL/SQL libraries for various multi functional usages.
• Involved in production support for the client.
• Corrected major form bugs, and errors through client, end user interaction.

Environment: Oracle8i, PL/SQL, SQL, Designer 2000, Oracle, Developer 2000, Windows 2000/NT, UNIX.