|
|
Ali Manouchehri Senior DB2 LUW DBA Consultant with 16+
Years of Experience Mobile
(416) 457 1649, Email:
ali@manouchehri.ca |
|
At a glance Work Experience in: |
I am a senior database administrator with 16+ years of experience in designing, coding, testing and supporting database solutions in DB2 on Linux, UNIX and Windows (LUW) platforms. In this context, I have worked for prominent companies such as Manulife Financial, CN, Royal Bank, Scotiabank, DND, and IBM. I have a secret clearance and my current contract is with the Canada Border Services Agency in Ottawa. I am fluent in English and have a strong working knowledge in French.
My key skills and expertise include but not limited to:
- Work
experience in
- Over 15 years of experience working with DB2 LUW installation, configuration, backup and recovery, troubleshooting, monitoring, performance tuning, Data Partitioning (DPF) and High Availability and Disaster Recovery (HADR).
- Over 10 years of experience in Logical and Physical database design (using Erwin); experienced in designing data models in third normal form and star schema (for data warehousing).
- Over 10 years of experience supporting large Data Warehouse and Data Mart environments running on DB2 LUW.
- Over 10 years of experience designing Offline and Online ETL and data replication solutions; Experience in using Data Integration Software and indepth understanding of DB2 LUW Change Data Capture.
- Over 15 years of experience coding complex SQL, Java and UNIX scripting.
- Over 10 years of experience coding complex, highly efficient and scalable Java Stored Procedures.
- Over 15 years of experience deploying changes to production and troubleshooting production incidents under extreme pressure.
- Over 5 years of experience designing, implementing and supporting large Data Warehouse environments running on DB2 LUW and DPF.
- Over 2 years of experience working with large SAP environments running on DB2 LUW and DPF.
- Two years of management skills, leading a group of 9 DBAs. Demonstrated exceptional organizational and people skills.
- 1 year of experience experience in OpenSource technologies such as CentOS, PostgreSQL, MySQL, Cacti, Munin, Nagios, Londiste, pgq, pgbouncer and pgfouine. Hands on experience in Cloud Computing (Amazon) and Cloud Management (RightScale).
- I have
received a B.A.Sc in Electrical Engineering (Honour Standing) from
|
PROFESSIONAL EXPERIENCE |
04/2012 – Present Canada Border Services Agency, Ottawa
Ontario Contract Position
Senior DB2 LUW DBA, Consultant
Technologies:
DB2 LUW 9.7, 9.5, Solaris, Data Stage, Data Warehousing
- Was
hired as a Senior DB2 LUW consultant to perform advance database
administration, troubleshooting and development tasks on the CBSA Data
Warehouse project.
- Coded
a generic SQL stored procedure for transferring data from staging tables
to the main ADW tables. The stored
procedure would take source and target table names, and would
automatically generate the SQLs required to transfer the data and
maintaining history.
- Participated
in the DB2 V9.7 upgrade of the development and production databases.
- Performed
a completed analysis of DB2 Shared Memory configuration for all the
databases and tuned the bufferpools and certain
database configuration parameters for better performance.
- Setup
automatic database stats collection and processing scripts.
07/2011 – 03/2012 Department of National Defence , Ottawa Ontario Contract
Position
Senior DB2 LUW DBA, Consultant
Technologies:
DB2 LUW 9.7, SAP (BI, ERP), AIX 6.1, Optim
Performance Manager 4.1.0.1
- Was
hired as a Senior DB2 LUW Consultant to concentrate on getting DRMIS on
LUW working and performing as per DND's high standards of availability and
reliability. The environment
consisted of over 100 SAP installations (BI Data Warehouse, ERP, DoE, PI, Soluition Manager, ...)
running on AIX 6.1 and DB2 V9.7, amounting to over 13 TB of data.
- Look
at the internal table design for Optim
Repository Manager (OPM) version 4.1.0.1.
- Setup
data collection templates for databases with different code pages to work
around certain product limitations.
- Setup
a number of databases for OPM Performance Monitoring to better understand
the functionality of the dash-ports, alerts, and the canned reports.
- Investigated
the OPM Console performance problems and configured the automatic
maintenance policy for the OPM Repository to issue the backups and Runstats at a more appropriate interval.
- Reported
OPM bugs to IBM with detailed instructions on how the bug could be
re-produced. Received, applied and
validated the patches from IBM.
- Calculate
space requirement to turn on OPM data collection on all DRMIS databases
supporting various SAP applications.
- Produced
a product evaluation document for OPM and looked at the product from these
perspectives: Licensing, data
Collection and Configuration, Organization of Collected Data, Performance
and Health Alerts, Alert Management, In-flight Dashports,
Canned Reports, Database Registration, General User Interface, Data
Accuracy, and Repository Database Maintenance.
- Wrote
up an RFC for the installation of OPM within the DND WAN. Provided a detailed write up on the Business
Case, Cost Analysis, Security Implications, Testing and Implementation
Requirements.
- Performed
day to day database maintenance such as re-launching failed backup and REORG
jobs through the DBA Cockpit.
- Assisted
two senior DBAs in the successful recovery of a crashed database on board
of a Canadian Ship.
- Setup
a number of korn shell script to collect and
process DB2 Snapshots, Configuration and Installation information from all
the DB2 LUW databases from one central location. The data was collected from a number of
most relevant SYSIBMADM administrative views, SYSPROC stored procedures
and SYSCAT views.
- Wrote
up a System Operating Procedure (SOP) to document location, use case and
detailed description of each script.
- Produced
over 20 landscape-wide reports using the data collected from the korn shell scripts including: Online and Offline
Maintenance Windows, Auto Reorg Policy, Backup History, Tables with no
Indexes, Table Usage, Tables
Needing REORG, Database Memory Usage, Active Event Monitors, Bufferpool Hit Ratio, REORG and Backup Conflict, and
Database Inventory.
- Used
DB2 Password Encryption to store and retrieve database connect credentials
in a secure way.
02/2011 – Present Recognia Inc,
PostgreSQL DBA, Consultant
Technologies:
Cloud computing (Amazon), Cloud Management (RightScale),
Linux (CentOS 5.4), PostgreSQL
8.4, MySQL, Cacti, Munin, Nagios,
Londiste and pgq
·
Started as a permanent employee
and switched to a consultant role in July 2011.
·
Self thought the OpenSource technologies used at Recognia
in a short span of time. These
technologies included Linux CentOS, PostgreSQL, MySQL, pgbouncer, pgfouine, SkyTools
(pgq and londiste), Cacti, Munin and Nagios.
·
Reviewed the existing monitoring
graphs in Cacti and identified some key missing ones. Setup CPU and I/O monitoring for the entire
server farm and provided training on Cacti to the development team.
·
Produced a number of documents
such as Server Inventory, Production Recovery Procedures, PostgreSQL
Environment Setup, Database Backup and Recovery, and Setting up Standby
Databases. Archived away all the old and
obsolete documents.
·
Reverse engineered the databases
and created the first draft of the company’s data models.
·
Identified a major gap in
database backup strategy and coded a new set of scripts to eliminate this
gap. Deployed these scripts to
Production and RC (Release Candidate) with a new weekly schedule.
·
Re-vamped the process of copying
production databases to RC and Test environments to reduce the down time from 1
day to 1 hour.
·
Successfully migrated
an old production database server to a new blade server within the 4-hour
allowed time window. The blade server
was built from scratch and the databases were migrated through a backup and
restore operation.
·
Put forth a new proposal for warm
standby and disaster recovery strategy and got it approved by the architect
team. Setup a test version of the
solution in RC.
·
Performed general DBA task such
as diagnosing database errors, analyzing the top SQLs, projecting database
growth, resolving database replication issues with londiste,
and fine tuning database configuration for fixing various performance problems.
·
Played a lead role in migrating the DR databases to the Amazon Cloud. Used RightScale as
the cloud management tool and built the cloud servers through RightScripts.
·
Provided on-call support on a
weekly rotation basis. In a number of
occasions helped the team resolving production issues while I was not on-call.
08/2010 – 05/2011 Scotiabank,
DB2 LUW DBA, Consultant
Technologies: DB2 V9.1, AIX 5.3, Java 1.5, WebFocus
7.6
·
Worked with WebFocus
development team to use WebFocus as Data Integration
Software from production Data Warehouse to the group’s data mart running on DB2
LUW.
·
Setup DB2 Snapshot Collection to
analyze the database workload. Identify
and rectified the DB2 and AIX bottlenecks to improved the performance of
BNS_CUST report from 150 sec/hit to 8 sec/hit.
·
Setup and configured a new development
environment while correcting table schemas and tablespaces. Performed an in-depth analysis of WebFocus ACX files to identify the tables that were
obsolete. Build the new development
database without the obsolete tables.
·
Setup Monitoring scripts to
produce daily performance report from the data in WebFocus
resource analyzer tables.
·
Setup CVS server on the AIX
database server for code versioning.
·
Setup and configured a new
production environment similar to the new development environment and migrated
the production database over.
·
Conducted knowledge sharing
sessions.
11/2009 – 06/2010 CN,
DB2 LUW DBA, Consultant
Technologies: DB2 LUW 9.5, DB2 DPF, AIX 5.3, SAP BW 7.0.3
·
Built the data repartitioning
recipe for CN Business Warehouse upgrade project – expanding from 3 to 9 nodes
using primarily the SAP DB6CONV utility.
Executed and fine tuned the recipe in three occasions with large
production size data warehouse (2 TB).
·
Provided core DBA support during
performance testing, identified and fixed configuration issues in a 9-node
database environment causing deadlocks.
·
Performed a number of SAP System
Copies involving DB2 Restores and Roll Forwards. Followed CN standards for setting up and
configuring the new database environments.
·
Setup new monitoring scripts to
capture database snapshots at the database partition level.
·
Found the root cause for a number
of serious performance problems post Data Re-Partitioning. Worked with the vendor (SAP and IBM) to
install the patch and validate the resolution.
10/2004 – 12/2009 Manulife Financial (John Hancock),
Senior DB2 LUW DBA Consultant, Team Lead
Technologies:
DB2 LUW 7.1, 8.2, 9.5, DB2 HADR, ERWin, AIX 5.2, 5.3,
Java 1.4.2, ANT, CVS, WebSphere
·
The project involved design,
development and support of JHRPS eComm and Data
Warehouse like databases running on DB2 LUW.
·
Provided 3rd level
production support on all the DB2 LUW environments. Was
the ultimate escalation point for problem management during production crisis.
·
Was promoted to the DBA Team Lead
position to manage a team of Seven DBAs.
The team was composed of both full time and contract staff.
·
Was responsible for reviewing
project estimates and planning resources accordingly.
·
Led the DBA team in successful
upgrade of DB2 environments from Version 7.1 to Version 8.2, and subsequently
to Version 9.5
·
Led the DBA team in successful
implementation of HADR and used HADR to perform live maintenances.
·
Was the Release DBA on majority
of Manulife ECOMM projects. The
responsibilities included change grid planning, execution of release steps and
post-release monitoring.
·
Introduced a new database
standard to make the support of all the test and production environments much
more straight forward. Furthermore, scripted
the database build process which eliminated many environmental issues during
the QA cycles.
·
Setup historical production stats
collection at the O/S and DB2 level.
Used these stats for capacity planning, database profiling and database
performance measurements.
·
Was responsible to conduct
database performance testing for all releases.
Caught and fixed poorly written SQLs prior to going live in
production.
·
Performed day to day hands on Application
DBA tasks such as developing ETL scripts, writing Java stored procedures and
making database changes. Used CVS as
the code repository system.
·
Provided QA support such as running
the ETLs, investigating bugs or applying database fixes on the QA environment.
·
Addressed the capacity and
stability issues of the test environments by building three additional in-house
database servers on AIX and Windows platform. Introduced the concept of Virtual PCs.
·
Setup a new process for
extracting data from production while adhering to SOX requirements in terms of
data masking. Used this process for
updating HomeState and creating masked production-sized databases for special
performance testing requirements.
·
Mentored junior DBAs and
interviewed candidates for full time and contract positions.
08/2003 – 10/2004 IBM France, La Gaude (Nice) France Contract Position
Senior DB2 LUW DBA Consultant
Technologies:
DB2 LUW 7.1, 8.2, AIX 5.2, 5.3, SAP, Informatica
·
Administered SAP and DSS
production databases (Multi Tera Bytes) running on DB2 for AIX V7 and V8
platforms.
·
Led the DB2 team in three major
disaster recovery incidents, involving database restores (Tera Bytes) plus roll
forward operations.
·
Participated in the weekly DB2
on-call rotation and resolved numerous critical problems under extreme time
pressure.
·
Worked with System Architects to
devise a new database layout strategy to improve performance, allow better
manageability, and promote stability.
·
Produced detailed specifications
for the migration of 22 production databases towards the new layout (including
Tera Byte SAP databases). At the Systems
level, produced specifications for the allocation of the LUNs (Logical Disks)
in the ESSBAY and the creation of VGs, LVs and FSs. At the database level, detailed out the
migration steps for each database, some using redirected restore and others
using exports/loads. Coded a Java
application to produce all the necessary scripts for each database migration.
·
Conducted ALL database migrations
successfully and received excellent feedback from the client.
·
Participated in DB2 Fixpak
installation and DB2 version 8 upgrades.
In two occasions, conducted emergency rollback of the installed Fixpack.
·
Identified problematic SQLs using
historical dynamic SQL snapshots. Used
DB2 Explain utilities to find and resolve the bottlenecks.
·
Used historical “List Tablespace
[Container]” information to produced forecast reports. These reports allowed studying the growth of
all environments at database, tablespace and file system levels.
·
Used “Database, Bufferpool, …” snapshots to identify database bottlenecks
and waste. Some bottlenecks included
high percent sort overflows, low bufferpool
hit ratios and high occurrence of deadlocks.
Waste included unused bufferpools and
over-allocated tablespaces. Put forth recommendations, made appropriate
adjustments and measured the difference (improvement) in a quantitative manner.
·
Coded scripts (perl and korn shell) for
automatically expanding the DMS tablespaces and
producing tablespace utilization reports.
·
Oversaw and performed
“code-promotion” of Informatica ETL jobs/korn shell scripts.
Participated in the daily conference calls with the client regarding the
DSS ETL activities.
·
Traveled to
·
Conducted interviews for hiring
new staff.
10/2000 – 02/2003 Manulife Financial,
Senior DB2 LUW DBA Consultant
Technologies: DB2 LUW 6.1, 7.2, AIX 4.3, 5.2, Java 1.1.8, ANT,
CVS, ERWin, WebSphere
·
Produced and maintained
conceptual data models for US Group Pension Broker Dealer, Account Balance
Offload, Viewfunds, and EZK eCommerce
web-sites using ERWin. Further denormalized
multi-level dimensional hierarchies in the Broker Dealer model to achieve more
efficient star schemas.
·
Lead the Data Track in the design and
construction of relational databases to meet scalability, availability and
fail-over capability required by solution architects.
·
Developed small and large POCs for the
different components of the system.
Designed and conducted large POCs such as “Live Bulk Update”, and
produced POC specifications for smaller components such as “Profile ID
Generation”.
·
Translated solution architecture into detailed
technical design. Produced
specifications for ETL, data access layer, and backup and recovery strategy.
·
Developed database standards, guidelines and
procedures. Database standards included
naming convention for tables, table spaces, indexes; guidelines included best
coding practices for Java stored procedures; and procedures included the
defined processes for implementing database changes in production.
·
Coordinated with the infrastructure group to
obtain appropriate hardware and software for development, testing and
deployment.
·
Mentored DBAs, transferred design knowledge
through technical walkthrough sessions and peer reviews.
·
Ensured that test plans were prepared and
executed on time and that they produced the expected results. Worked closely with Quality Control team to
ensure their test data requirements were met.
·
Designed and built repeatable
processes for database tuning. Setup
performance profiles and benchmarks to measure enhancement progress.
·
Setup stress and load test-runs to measure and
tune the system’s performance under high concurrency and volume.
·
Handled DB2 LUW product bugs, reported them
to IBM and followed up for resolutions.
·
Provided on-call production support and lead
the production support team in a number of database-crash recovery situations.
·
Worked with project managers and business
analysts to capture requirements, define alternatives and costs, and setup
project plans. Participated in hiring of permanent and contract staff.
·
Lead a team of five DBAs, distributed work,
managed the workload and ensured that the tasks were completed as
specified.
·
Ensured that all work was performed in
accordance to audit requirements.
Conducted presentation sessions with the Audit department to illustrate
that data was handled in a secure and accurate manner.
10/1999 – 10/2000 Caremark RX, Bannockburn IL USA Contract
Position
Senior DB2 LUW DBA Consultant
Technologies: DB2 LUW 6.1, DB2 EEE, AIX 4.3
·
Provided DBA production support
for Caremark’s Data Warehouse environment – 500 GB DB2/EEE system running on
twelve SP nodes.
·
Participated in the Data
Warehouse Expansion task - adding six new SP nodes, and re-distributing the
data.
·
Set up DB2 Governor to control the Data
Warehouse usage and reduce the number of database crashes.
·
Identified and corrected the bottlenecks with
the Data Warehouse and brought the CPU utilization down from 100% to 50%.
·
Designed and built an in-house monitoring
tool using C, embedded SQL and Brio reports to identify and shut down
problem-queries before they caused Data Warehouse outages. This initiative saved the company over
$150,000 in expected software expenses (third party monitoring tools).
·
Through four months of collecting and
processing data from the DB2 Event Monitor, created a Data Warehouse profile.
Used this information to identify the non-utilized tables and columns, and
removed them from the Data Warehouse.
This initiative saved the company over $250,000 dollars in expected
hardware expenses (buying an additional SP node).
·
Built a new test environment - 40 GB DB2/EEE system running on two
SP nodes. Built ETL scripts to populate
the test environment from the production Data Warehouse.
·
Helped Data Warehouse users with SQL and
performance tuning requests. Some
solutions required application re-design complemented by database changes.
·
Investigated query performance problems on
Mainframe. Reduced the execution of
monthly reports from 30 to 3 hours.
·
Handled the opening and closing of the PMRs
with IBM Canada – Toronto Lab.
·
Produced a complete set of documentation to
explain all the required fine-tuning on the Data Warehouse.
09/1998 – 09/1999 Royal Bank of
DB2 LUW DBA Consultant
Technologies:
DB2 LUW 5.2, RS/6000, AION/DS, BACHMAN, ERWin, Brio
·
Worked with business analysts and
project managers to put together the solution architecture and obtained sign
off.
·
Produced, maintained and
certified the data model for the new Royal Bank Merchant Risk Management System
(MRMS) using BACHMAN and ERWin.
·
Forward engineered the data model
to MRMS physical database. Designed the
table spaces, buffer pools, and indexes in accordance to database initial size
and growth factor; set up triggers for audit trails.
·
Produced data mapping rules
between legacy source systems and new MRMS database and ensured that mainframe
developers coded the extract logic in accordance to my specifications. Maintained the mainframe jobs after
developers’ contracts were expired.
·
Designed data transformation and
load scripts using C, embedded SQL and shell scripts to support data updates in
a near real-time fashion. Designed and
coded near real-time summarization jobs to populate OLAP cubes.
·
Used DB2 Visual Explain,
Application Snapshots and Event Monitors to identify and resolve performance
bottlenecks. Participated in the AION/DS
code walkthrough and helped identify application bottlenecks.
·
Validated fraudulent transactions
identified through AION/DS code through Brio reports. Used Brio as a data-mining tool to identify
potential fraudulent cases that were not identified by AION/DS.
·
Installed DB2/LUW
·
Lead the developers and testers
in the system integration and acceptance testing. Ensured that all bugs were fixed in a proper
priority sequence.
·
Produced documentation for the
near real-time ETL process, backup and recovery strategy, and system
growth/capacity. Conducted training
sessions to full-time staff and ensured that proper hand-off was completed.
03/1998 – 08/1998 Independent Order
of Foresters,
Red Brick DBA
Technologies:
Red Brick, ERWin, Brio
·
Participated
in the design of the data model for Actuarial Data Warehouse using Erwin.
Helped forward engineer the data model to physical database (running on Red
Brick Warehouse) and maintained the database changed through ERWin. Collapsed
multi-level dimensional hierarchies into simpler single level dimensions for
better query performance.
·
Built
the ETL process to populate the data mart.
Built the jobs to extract data from the DB2 legacy system - used Data
Junction as the data transformation tool and TMU to load the tables.
·
Built
compare jobs to ensure that data in the new data warehouse matched the one in
the old AXIS valuation system. Produced
discrepancy reports and corrected the errors in the ETL process.
·
Built
macros to monitor database activities.
Captured user queries and used Explain utilities to identify and resolve
performance bottlenecks. Made
enhancement to segmentation and star indexing strategies.
·
Helped
design and construct business intelligence reports using Brio. Setup sample Brio reports to illustrate pivot
tables and drill-down capabilities.
05/1995 – 02/1998 IBM Canada,
DB2 LUW DBA
Technologies:
DB2 V2, DB2 LUW 5.2, OS/2, RS/6000, BACHMAN
·
Designed
and maintained the logical data model for Royal Bank’s Call Centre Reporting
project using BACHMAN. Forward
engineered the logical data model to physical database.
·
Estimated
database size, traffic and future growth – used this information to order
appropriate hardware for database servers.
Installed and configured DB2/2 version 2.1 on test and production
database servers.
·
Designed
and built the ETL process using IBM Data Propagator/2 to populate the reporting
database in a near real-time manner.
Tuned the ETL process to handle 1.5 million records per day.
·
Designed
and coded the scripts using C and embedded SQL to populate the OLAP cubes in a
near real-time fashion.
·
Used
Application Snapshots and Visual Explain to identify and resolve performance
bottlenecks. Monitored system and
database resource utilization during the ETL process to ensure smooth
end-to-end operation.
·
Designed
and coded database utilities such as REORG, RUNSTATS, REBIND, Backup and
Restore using C and DB2 APIs. Designed a
GUI application using VisualAge for C++ to dispatch
and monitor database jobs.
·
Installed
and configured DB2/6000 and performed AIX system administrator duties on the
new RS/6000. Migrated the physical database and
supporting scripts/jobs from OS/2 to AIX.
·
Conducted
training sessions to full-time staff and ensured that proper hand-off was
completed.
·
Prepared Meta-Data
using BACHMAN and Data Guide/2. Designed
and coded conversion routines to merge BACHMAN “.var”
files with external data dictionary to generate Data Guide/2 input files.
·
Coded load scripts
using C for MVS to populate Data Warehouse reference tables. Played a significant role in the design and
development of the front-end reporting application using Visualizer Development
Tool Kit.
·
Designed the work
frame for the application help facility using Hyperwise
and lead three team members to produce the content.
·
Prepared test
scripts and ensured that they were executed as specified.
|
EDUCATION |
1995 B.A.Sc
in Electrical Engineering (Honour Standing),
|
REFERENCES |
Available upon request