Peningo Systems, Inc.
Resume of Candidate: PEN177
Netezza Consultant -
Technical Profile:
Over 30 years as a DBA of which the last 20 years have been with NETEZZA and DB2 LUW. Has performed work in all phases of a project including:
• performance tuning at the application and database level
• proof of concept with NETEZZA
• migrating from DB2 to NETEZZA
• installing and configuring DB2 LUW (Unix and Windows) and DB2 (mainframe)
• data replication
• data modeling
• database design
• database implementation
• database maintenance
• experience with data warehousing and VLDB (Very Large Databases)
OTHER
Data Warehousing DB2 LUW EEE / ESE
NETEZZA SQL
UNIX ORACLE 8 Storage Structures
EXPERIENCE IN INFORMATION TECHNOLOGY over 30 years
EXPERIENCE AS A DBA over 25 years
SUMMARY OF TECHNICAL SKILLS
HARDWARE: IBM P-SERIES
SUN FIRE
STORAGE AREA NETWORK (SAN)
IBM 3090/30XX/43XX
OPERATING SYSTEMS: AIX 5.3 (KORN SHELL)
SUN SOLARIS 8, 9 (KORN SHELL)
LINUX REDHAT
OS/VS/MVS/XA, OS 390, Z/OS (PARALLEL SYSPLEX)
LANGUAGES: SQL
ISPF DIALOG MANAGER
COBOL
DATABASE:
NETEZZA – PUREDATA FOR ANALYTICS
DB2 LUW (VERSION 6, 7, 8, 9, 10.1, 10.5)
DB2 LUW PURESCALE / PUREDATA FOR TRANSACTIONS
DB2 LUW ESE WITH DATABASE PARTITIONING (MPP)
DB2 Z/OS (VERSION 7 WITH DATA SHARING)
ORACLE 8 (ACADEMIC)
DATA MODELLING TOOLS: Infosphere Data Architect
POWER DESIGNER
ERWIN
BACHMAN FOR DB2
SYSTEM ARCHITECT
DATA MODELING TECHNIQUES: THIRD NORMAL FORM (Entity Relationship),
STAR and SNOWFLAKE schemas (DIMENSIONAL MODELING)
HISTORY OF WORK
COMPANY YEAR OF MANDATE DURATION FUNCTION
IBM - Canadian Border Services Agency 2015 in progress DB2 Pure Scale
Subject Matter Expert
La Capitale Insurance 2015 1 month NETEZZA SME
Performance Expert
Deloitte Consulting - State of Connecticut 2015 5 months DB2 LUW Technical Lead /
Advisor / SME/ DBA
Government of Canada 2012 - 2014 36 months NETEZZA /
DB2 MPP Data Warehouse
DBA
University of Ottawa 2011 2 months DB2 Migration Expert
Aetna Insurance 2010- 2011 9 months Senior DB2 MPP Data
Warehouse DBA
Blue Cross Blue Shield (Wellmark) 2009- 2010 6 months Senior DB2 MPP Architect –
Performance Expert
TATA Communications (Teleglobe) 2007 - 2009 24 months Senior DB2
MPP Data Base Architect –
MPP Performance Expert
IBM Canada 2006 - 2007 8 months Senior DBA DB2 LUW
Canadian National Railway 2006 4 months Senior DBA DB2 LUW
Royal Canadian Mounted Police - RCMP 2006 5 months Senior DBA DB2 LUW /
DB2 ZOS
Tech Data 2005 – 2006 4 months Senior DBA DB2 LUW
Royal Canadian Mounted Police - RCMP 2004 – 2005 12 months Senior DBA DB2 LUW
/ DB2 ZOS
IBM – Morgan Stanley 2005 2 months DB Architect DB2 LUW
The GAP 2004 1 month DBA DB2 ZOS / DB2 LUW,
Data Propagator expert
CIGNA 2001 – 2004 36 months DBA DB2
MERCK – MEDCO 2000 3 months DBA DB2
SAPIENT CORPORATION 1999 – 2000 6 months DBA DB2 LUW
AXA CANADA TECH 1998 – 1999 12 months DBA DB2
BELL HELICOPTER TEXTRON CANADA 1997 – 1998 12 months DBA DB2
HYDRO-QUEBEC 1996 – 1997 6 months DBA DB2
BELL SYGMA 1995 – 1997 22 months DBA DB2
FINANCIAL COLLECTION AGENCY 1996 4 months Data Architect
HYDRO-QUEBEC 1992 – 1994 20 months DBA DB2
COMPANY: IBM - CBSA (Ottawa, Ontario)
YEAR OF MANDATE: 2015
DURATION OF MANDATE: 5 months (in progress)
FUNCTION: Consultant " DB2 Pure Scale Subject Matter Expert "
As a DB2 Pure Scale Subject Matter Expert working on the E-Manifest Project – USA/Canada Border Crossings MDM, provided technical services as
follows:
• Plan and install DB2 PureScale
• Make recommendations for server architecture (CPUs, memory, disk, network) of Members and Caching Facility
• Work with UNIX administrators on configuration of CPU, disks, file systems, memory
• Create cluster file systems from raw disks for database and logs
• Supported / mentored / advised CBSA team on DB2 Pure Scale
• Perform high availability / failover tests
• Performance testing, monitoring and tuning
• Create and configure MDM database
• Architecting CDC which is used to propagate database changes from DB2 Pure Scale to the CBSA Data Warehouse
• Planning and installation of Optim Performance Manager on PureScale
• Configure PureScale to do backups and log archiving using TSM
• Perform database restores and recoveries on PureScale using TSM
• Write and implement Linux database backup scripts for PureScale
• Write and implement Linux database monitoring/alerting scripts for PureScale
• Resolve performance issues
• Created DB2 Pure Scale Installation Guide
ENVIRONMENT:
DB2 10.5 FP 5 (Pure Scale)
Db2top, Db2mon
IBM Data Studio
IBM InfoSphere Optim Performance Manager
CDC (Change Data Capture)
LINUX Redhat 6.5
COMPANY: La Capitale Insurance (Quebec, Quebec)
YEAR OF MANDATE: 2015
DURATION OF MANDATE: 1 month
FUNCTION: Consultant " NETEZZA SME / Performance Expert "
As a NETEZZA SME / Performance Expert provided technical services as follows:
• perform a heath check analysis of the entire Netezza environment
• verify if Netezza best practices are being followed
• work with architects and review the logical data model
• review the physical data model and make recommendations for improvement (denormalization, proper distribution of data across data slices, co-
location and other techniques)
• implementation of Cluster Based Tables and Materialized Views
• analysis Explain plans for problem SQL
• analysis of zone maps
• improve performance of queries (some queries went from execution time of several hours to seconds)
• review the entire ETL process and make recommendations for improvement
• identify in-house DBA skills which were lacking or needed to be improved on
ENVIRONMENT:
Red Hat Linux, IBM PureData, IBM Netezza
Netezza 6+1 S-Blades
Netezza Performance Server (NPS) 7.0.4, LINUX
Netezza Performance Portal
NzAdmin
Aginity Workbench for PureData System for Analytics
Informatica
COMPANY: Deloitte Consulting - State of Connecticut (Lake Mary, Florida)
YEAR OF MANDATE: 2015
DURATION OF MANDATE: 5 months
FUNCTION: Consultant “DB2 LUW Technical Lead / Advisor / SME / DBA "
As a DB2 LUW Technical Lead / Advisor / SME / DBA provided DB2 technical services for Deloitte on various Health Care systems being developed for the
State of Connecticut.
• Lead and mentor the DBA team
• Provide DB2 support for a team of over 50 developers
• Work with managers and architects on defining requirements and advising on best DB2 solutions / practices
• Collaborate with LINUX administrators on defining server requirements
• Use Erwin to review and make changes to the data model
• Plan and execute database schema changes
• configure HADR
• Upgrade DB2 10.1 to 10.5 FP 5 with PureScale
• Configure/optimize database manager, database and DB2 registry
• Configuration of Buffer Pools
• Create and configure instances (including DB2 Pure Scale instances)
• Design and implement tables and indexes
• Design and implement foreign keys
• Design and implement Columnar Tables using DB2’s BLU acceleration
• Monitoring of instances and databases for performance, locking and other issues
• Analysis of access paths and tuning of SQL
• Create scripts for backups (Full, Incremental and Delta), alerting and reports
• Perform backups and recoveries at the database level and table space level
• Perform database restores including redirected restores from on-line backups
• Reorgs / Runstats / Reorg Check / Data Movement using Load, db2move, ADMIN_COPY_SCHEMA
• Review Stored Procedures and make / implement recommendations to improve performance
• Set up Work Load Manager to control locking issues and run-away queries
• Advocate and implement best practices for statistics gathering, reorgs and backups
• Give training/courses to application developers on SQL performance tuning
• Enforce best practices for database security
• Configure Federated server and data sources (WRAPPER, MAPPING, NICKNAME, etc.)
• Investigated and resolved DB2 server/database issues
ENVIRONMENT:
DB2 10.1 and 10.5 (Pure Scale)
HADR
Db2top, Db2mon
IBM Data Studio
Erwin
IBM InfoSphere Optim Performance Manager
LINUX
COMPANY: Government of Canada (Ottawa, Ontario)
YEAR OF MANDATE: 2012 - 2014
DURATION OF MANDATE: 36 months
FUNCTION: Consultant " NETEZZA / DB2 MPP Data Warehouse DBA "
As a NETEZZA DBA / DB2 LUW DBA provided technical services as follows:
Migrate to MMP (Massive Parallel Processing) DPF BCU environment
• Make recommendations for server architecture (CPUs, memory, disk, network)
• Create a plan for the migration of a single partition database to a 16+1 partition database
• Work with UNIX administrators on configuration of CPU, disks, file systems, memory
• Determine which tables to replicate and which to hash
• Review data model and determine Hash Keys
• Determine which tables are multi-partition and which are single-partition
• Determine which single-partition tables to Replicate (based on queries with table joins)
• Determine Distribution Keys (Partition Key) for partitioned tables
NETEZZA Proof of Concept
• Work with IBM on a proof of concept to establish/validate performance improvements of Netezza versus DB2 MPP
• Establish and document functional differences between Netezza and DB2 MPP
• Load tables using nzload and compare times with DB2 load
• Run resource intensive queries on Netezza and compare times with DB2
• Explaining of queries and review access plans
Migrate to NETEZZA
• Convert DB2 DDL to Netezza DDL
• Creation of hundreds of tables and other Netezza objects
• Design Netezza Distribution keys for DB2 single partition tables
• Develop and test data migration scripts which pipe DB2 data and load this data on Netezza using nzload
• Use the above scripts to migrate the entire DB2 data warehouse to Netezza
Daily Activities
• Use nzadmin and nzhw to monitor / manage NZ SPUs/Disks
• Design and implementation of Netezza tables ensuring proper data distribution and co-location of data for queries
• Design of table columns ensuring that Netezza Zone Maps are used
• Design of Netezza Clustered Based Tables and Materialized views
• Establish Referential Integrity to improve query performance
• Manage NZ objects using nzadmin
• Using nzload load NZ tables with massive amounts of data and validate performance of newly designed / modified tables
• Grooming of tables and generating statistics
• Analysis of access paths and tuning of SQL / NZSQL and/or redesign tables
• Code, test and troubleshoot procedures using NZSQL and LINUX scripting
• Backup and restore of Netezza databases using nzbackup (Full, Cumulative, Differential, Schema Only) and nzrestore (entire database and table
level)
• Using nz_migrate migrate database table(s) from one NPS server to another
• Configure ODBC/JDBC drivers
• Advocate and implement “Best Practices” for statistics gathering, reorgs and backups
• Advise, mentor and lead other DBAs in the group on Netezza technology
• Work with Data Modelers and Enterprise Architect
• Work with ETL Developers on changing strategy from ETL to ELT
• Review data models for the Atomic Data Warehouse, Mining Mart and Reporting Mart and make recommendations for improvement
• Using Aginity Workbench create Netezza databases, make DDL changes
• Using Aginity Workbench Explain SQL and make changes to the Netezza databases to improve performance
• Encryption of data
• Configure Federated server and data sources (WRAPPER, MAPPING, NICKNAME, etc.)
• Upgrade DB2 9.5 to 9.7
• Configure/optimize database manager, database and DB2 registry
• Configuration of Buffer Pools
• Create and configure instances
• Create and configure databases
• Design of tables and indexes
• Design and implement MQTs (Materialized Query Table)
• Design and implement MDC (Multi-Dimension Clustering) tables
• Monitoring production databases for performance and other issues
• Perform backups and recoveries at the database level and tablespace level
ENVIRONMENT:
Red Hat Linux, IBM PureData, IBM Netezza
Netezza 6+1 S-Blades (96 CPUs, 96 SPUs, 96 FPGAs, 240 Data Slices, 64 Terabytes)
Netezza Performance Server (NPS) 7.0.4, LINUX
Netezza Performance Portal
NzAdmin
Aginity Workbench for PureData System for Analytics
TSM (used for NZ backups and restores)
SVN
Db2top, Db2mon, db2pd
IBM Data Studio
IBM Design Studio
IBM InfoSphere Data Architect
IBM InfoSphere Optim Performance Manager
Datastage
Change Data Capture
SPARC Enterprise M9000, 32 CPU
COMPANY: University of Ottawa (Ottawa, Ontario)
YEAR OF MANDATE: 2011
DURATION OF MANDATE: 2 months
FUNCTION: Consultant "DB2 Migration Expert "
Migration from DB2 Mainframe to DB2 AIX
As the DB2 SME technical lead for this position the following were performed:
• make recommendations and guide the migration of DB2 ZOS to DB2 AIX
• review current environment and recommend server architecture on AIX
• make recommendations for partitioning/parallelism
• capacity planning at OS level (disk space, CPUs)
• design of file systems and maximizing of striping
• instance and database creation
• database manager configuration
• database configuration
• tablespace and buffer configuration
• mentor team members and oversee activities
• support team members for complex problem resolution
• preparation of Federated objects
• preparation of DB2 Audits
• DB2 monitoring and resolving performance issues
• oversee activities pertaining to ongoing replication of data from mainframe to AIX
• preparation of backup and restore scripts
ENVIRONMENT: MVS, Z/OS, DB2 V9.1,
DB2 Connect, Distributed Data Facility (DDF),
AIX 5.3, DB2 V9.7, db2mon, db2top, nmon
COMPANY: Aetna Insurance (Hartford, Connecticut)
YEAR OF MANDATE: 2010 - 2011
DURATION OF MANDATE: 9 months
FUNCTION: Senior DB2 MPP Data Warehouse Data Base Administrator
As a Senior Data Warehouse DBA, the following activities were performed for this Insurance company in this Data Warehousing arena:
• Review the logical models for various projects
• Review physical data models and make detailed recommendations for optimization
• Review the DB2 setup at the OS level (file systems, striping, etc.) and identify problems and make recommendations for improvement
• Make best practices recommendations for use of DB2 V9 features (table/range partitioning, row compression, self tuning memory, MQTs, MDCs,
Statistical Views, etc.)
• Database performance tuning through database physical design, configurations and implementations of MQTs, MDCs, database and table
partitioning, indexing and related settings
• SQL analysis and tuning of complex queries (includes rewriting the SQL and/or tuning the database)
• Resolve problems / issues
• Performance monitoring of the servers and DB2
• Database development using system development life cycle (SDLC) approach
• Use of DB2 Advisors
• Provide DB2 LUW related technical expertise to the development team
• Advise Data Architects on issues/questions
• Review the ETL and make recommendations for improvement
• Design and create Tables, Table Spaces, Indexes, etc.
• Migration of DDL from Dev to Prod
• Review the DB2 instance setup, make recommendations for improvement and implement
• Review the database configuration , make recommendations for improvement and implement
• Create/modify DB maintenance scripts
• Create/modify DB Alert and Health scripts
ENVIRONMENT: The production environment consisted of the main data warehouse database (see below) and 3 Data Mart databases.
Production Data Warehouse Database:
DPF - 21 AIX P595 servers (114 CPUs, 1 Terabyte memory, > 50 Terabyte disk)
AIX 5.3,
DB2 LUW V9.5 / V9.7 (with DPF - 114 DB partitions),
MDC, MQT, Range Partitioning, Statistical Views,
Data Stage, Business Objects,
db2mon, db2top, nmon, Workload Manager,
IBM Data Studio, Infosphere Data Architect
COMPANY: Blue Cross Blue Shield - Wellmark (Des Moines, Iowa)
YEAR OF MANDATE: 2009 - 2010
DURATION OF MANDATE: 6 months
FUNCTION: Senior DB2 MPP Architect – DB2 Performance Expert
Provide DB2 subject matter expertise and technical leadership to the corporate DW architects, DBAs, developers, business analysts and systems
analysts on the development of the Integrated Data Store.
As a DB2 architect / DB2 performance Expert the following activities were performed for this Insurance company in this Data Warehousing arena:
• Review the conceptual and logical models
• Review the physical data model and make detailed recommendations for its optimization
• Review the DB2 setup at the OS level (file systems, BCU, striping, etc.) and identify problems and make recommendations for improvement
• Make best practices recommendations for use of DB2 V9 features (table/range partitioning, row compression, self tuning memory, MQTs, MDCs,
Statistical Views, etc.)
• Database performance tuning through database physical design, configurations and implementations of MQTs, MDCs, database and table
partitioning, indexing and related settings
• SQL analysis and tuning of complex queries which the in-house DBAs were unable to tune (includes rewriting the SQL and/or tuning the database)
• Resolve problems / issues which the in-house DBAs were unable to
• Performance monitoring of the server and DB2
• Database development using system development life cycle (SDLC) approach
• Use of DB2 Advisors
• Recommend and configure Workload Manager (previously Query Patroller and DB2 Governor)
• Provide DB2 LUW related technical expertise to the development team
• Mentoring and leading of the corporate DBAs
• Advise Data Architects on issues/questions
• Review the ETL and make recommendations for improvement
• Review the DB2 instance setup, make recommendations for improvement and implement
• Review the database configuration , make recommendations for improvement and implement
• DB maintenance scripts – determine what is need and recommend
• Alert and Health scripts – determine what is need and recommend
ENVIRONMENT: Production:
DPF - 2 servers (40 CPUs, 80 GB memory - 10 DB partitions)
• M4000 - Coordinator/Catalog server (8 CPUs, 16 GB memory - 2 DB partitions)
• M5000 - Data Server (32 CPUs, 64 GB memory - 8 DB partitions)
DB2 V9.5 (with DPF),
MDC, MQT, Range Partitioning, Statistical Views,
Power Designer,
Data Stage, Business Objects,
Solaris 10, Netbackup,
db2mon, db2top, Workload Manager
IBM Data Studio
COMPANY: TATA Communications (Teleglobe) (Montreal, Quebec)
YEAR OF MANDATE: 2007 - 2009
DURATION OF MANDATE: 24 months
FUNCTION: Senior DB2 MPP Data Base Architect – MPP Performance Expert
Work under the supervision of the Business relationship Manager in the IT group for Corporate and Data Warehousing (DW) projects and interact with
the Corporate DW Architect, Business Analyst and the DW production DBAs.
The following activities were performed for this large international telecom company:
• Review the conceptual model (snowflake schema)
• Review the physical data model (snowflake schema) of this 10 tera-byte database (largest fact table contained over 3 billion rows) already deployed
in production and make detailed recommendations for its optimization
• Challenge IBM regarding its previous efforts with performance and set-up of DB2
• Produce a high level architecture report for management outlying observations and recommendations and best practices
• Make recommendations for use of new DB2 V9 features (table/range partitioning, row compression, self tuning memory, MQTs, MDCs, etc.)
• Database performance tuning through database physical design, configurations and implementations of MQTs, MDCs, database and table
partitioning, indexing and related settings
• Review the DBM and DB setup, the ETL (Informatica) processes and make/implement recommendations to improve performance and identify
problem areas
• SQL analysis and tuning (ad hoc queries were generated by Business Objects)
• Performance monitoring (CPU, disk I/O waits, locking, etc.)
• Installation of DB2 V9 on new servers and migration of DB2 V8 database to V9
• Installation of Fix Packs
• Work with OS sysadmin and make recommendations for DB2 file systems and BCU set-up
• Creation and configuration of instance and database
• Perform Backups and Restores
• Database development using system development life cycle (SDLC) approach
• Use of DB2 Advisors
• Use of Query Patroller and DB2 Governor
• Review the production environment and make recommendations to automate the maintenance and monitoring of DB2 and the warehouse
• Provide DB2 LUW related technical expertise to the development team
• Mentoring of Production DBAs
CUBES OLAP :
The purpose of this project was to design and develop a combined fact table (snowflake schema) from the above warehouse which is to be queried by
hundreds of users using an OLAP tool (NovaView). The queries are ad hoc and random. Using MQTs, MDCs and other DB2 features query response time
was improved from 100 to 1000 times compared to normal indexing. Many queries which took 1 hour to run would now run in 3 seconds or less. The user
experience was to query the fact tables using an OLAP tool and having OLTP response times.
Designed the ETL for the combined fact table including the refreshing of the MQTs.
ENVIRONMENT: Production:
BCU MPP - 5 servers (20 CPUs - 17 DB partitions)
Eserver 326m, AMD64 DS4300/DS4800
1 server used as the Admin. -Catalog node and the other 4 servers as database partition nodes.
DB2 LUW V8 (with DPF) & V9 (with DPF)
DB2 Query Patroller, DB2 Governor
DB2 Data Warehouse Edition (DB2 DWE) Version 9.1.1
Linux (Redhat), Netbackup
NMON, db2mon
COMPANY: IBM Canada (Montreal, Quebec)
YEAR OF MANDATE: 2006 - 2007
DURATION OF MANDATE: 8 months
FUNCTION: Consultant "Senior Database Administrator"
As senior DBA, worked mainly on the Enterprise Data Warehouse and Decision Systems Data Marts of the National Bank of Canada.
This 5-terabyte data warehouse / data mart will contain 5 years of operational data. It consisted of 325 tables. The largest table was 1.8 billion rows - 500
GB.
The following activities were performed:
• plan, install and configure DB2 Data Warehouse Edition
• plan and install DB2 ESE
• plan, install, configure and administer DB2 Query Patroller
• plan and install DB2 Fix Packs
• convert non-partitioned instances to partitioned (Dev and Pre-Prod) and create a partitioned instance in Prod
• configure / tune instances for best performance (Sort Heap, FCM Buffers, etc.)
• created partitioned databases in Dev, Pre-Prod and Prod
• configure / tune databases for best performance (TSM, Logs, Buffer pools, IO Servers, etc.)
• work with the UNIX administrator to design file systems which will maximize striping of Table Spaces
• make use of the DB2 Configuration Advisor and the DB2 Design Advisor
• create SMS and DMS table spaces (Regular, System Temp and User Temp)
• make recommendations for partitioning of tables, MDC (Multi-Dimensional Clustering) tables and Replicated MQTs (Materialized Query Table)
• perform SQL reviews, run Explains and make recommendations
• performance monitoring and tuning using VMSTAT, IOSTAT, TOPAS, snapshot monitor and the event monitor
• perform online and offline database and table space backups (Full, Incremental and Delta) and write scripts for backups
• restore multi-partitioned databases and table spaces and Roll Forward
• perform re-directed restores of partitioned databases from one server to another
• create/maintain UNIX scripts which do monitoring, alerts, etc.
• research, recommend and implement DB2 features which reduce maintenance costs and DB2 outage and improve performance
• provided technical direction and support for the development team
• produce technical documentation in order to facilitate DBA maintenance
• problem determination and resolution
• work with the DBA team and architect to develop best practices for instances, databases, backups, etc.
• mentoring of junior DBA's
• provide on-call production support
ENVIRONMENT: Production:
Data Warehouse: 1 P570 with 8 CPUs (64-bit) each and 46.5 Gig of memory,
Data Marts : 1 P570 with 4 CPUs (64-bit) each and 16 Gig of memory
AIX 5.2, DB2 8.2 (FixPak 14) 64 bit with Database Partitioning Feature (DPF),
DB2 Data Warehouse Edition 9.1.1, DB2 Query Patroller
2 instances / 2 databases
Support on other Environments
Also provided DB2 production support on other various AIX, LINUX and Windows platforms which included both partitioned and non-partitioned
databases.
Number of servers supported: 37
Number of instances supported: 40
Number of databases supported: 71
COMPANY: Canadian National Railway (Montreal, Quebec)
YEAR OF MANDATE: 2006
DURATION OF MANDATE: 4 months
FUNCTION: Consultant "Senior Database Administrator"
As a DBA, worked on several projects including the Car Accounting Historical Data Warehouse.
This 3-terabyte warehouse will contain 7 years of operational data when fully populated.
The following activities were performed:
• design and implement partitioning of tables using DPF
• design and implement MDC (Multi-Dimensional Clustering) tables
• design and implement replicated MQTs (Materialized Query Table)
• created fact tables and dimension tables
• creation and use of User Temp Tables
• analysis and implementation and promotion of DDL across environments
• implementation of User Defined Functions
• implementation and tuning of stored procedures
• perform SQL reviews, run Explains, make and implement recommendations
• performance monitoring and tuning using NMON, Quest Central and the event monitor
• exporting/importing/loading of tables
• perform backups and restores on single and multi-partitioned databases
• create/maintain UNIX scripts which do exports, imports, reorgs, monitoring, backups, alerts, etc.
• design a strategy which will automatically roll off partitions of range-partitioned tables and implement the strategy using a stored procedure
• problem determination and resolution
ENVIRONMENT: Production: 2 P570s with 8 CPUs (64-bit) each and 32 Gig of memory each
AIX 5.3, NMON, HADR, Data Propagator, Quest Central for DB2
DB2 8.2 (FixPak 11) 64 bit with Database Partitioning Feature (DPF)
Development - 3 instances, 3 databases
Unit and acceptance testing - 10 instances, 12 databases
Production - 6 instances, 6 databases
COMPANY: Royal Canadian Mounted Police - RCMP (Ottawa, Ontario)
YEAR OF MANDATE: 2006
DURATION OF MANDATE: 5 months
FUNCTION: Consultant "Senior Database Administrator"
Canadian Police Information System
This 24/7 online system tracks criminal activities recorded by RCMP officers across Canada.
As a DBA, the following activities were performed:
• create databases, bufferpools, tablespaces, tables, indexes, primary keys, foreign keys;
• perform recoveries;
• space management;
• using Explain examine access plans and tune SQL queries;
• unloading and loading of tables;
• provide production support;
• transfer of knowledge/training/mentoring in-house DBA's;
• provide guideance to developers regarding data base access issues
Real Time Identification System
provide DBA services for the Real Time Identification System (RTID) which uses COGNOS for generating reports
• trouble-shoot and resolve performance issues;
• provide techincal guidance and support to the development team;
• resolve DB2 Connect issues;
• creation of data base objects;
• exporting and importing/loading of data
PeopleSoft Human Resources
provide DBA services for the PeopleSoft Human Resources (V8) system
• trouble-shoot and resolve performance issues;
• provide techincal guidance and support to the development team;
• resolve DB2 Connect issues
DB2 Connect
• plan, install, configure and tune DB2 Connect Enterprise Edition V8 on various Unix servers (AIX and Solaris) and windows servers;
• install DB2 Clients
ENVIRONMENT: IBM P-Series, Sun Fire (High Availability Cluster),
AIX 5.2, Solaris 8, Solaris 9,
DB2 LUW EEE 7, DB2 LUW ESE 8.1,
Storage Area Network (SAN),
Hummingbird Connectivity, PuTTY, Korn shell,
Replication Center, Task Center,
Control Center, Command Center,
Configuration Assistant, Health Center,
DB2 Connect, OMEGAMON XE,
Tivoli Storage Manager,
MVS, Z/OS (Parallel Sysplex), DB2 V7 (Data Sharing),
RC/Query, RC/Migrator, RC/Update, Database Analyzer, Log Analyzer,
DB2 Connect, Distributed Data Facility (DDF)
COMPANY: Tech Data (Clearwater, Florida)
YEAR OF MANDATE: 2005 - 2006
DURATION OF MANDATE: 4 months
FUNCTION: Consultant "Senior Database Administrator"
Siebel
This CRM system contains approximately 1,500 tables and manages over 2,000 connections at any given time.
As a DBA, the following activities were performed:
• install DB2;
• install Siebel Tools, Siebel Database Server, Siebel Client, Siebel Patches;
• perform Siebel Repository Migration (Imprep/Exprep) across development and production environments;
• perform a study on the current DB2 production environment and make recommendations to improve performance;
• using Snapshot, Event Monitor and Quest Central monitor SQL and data base objects for performance and locking issues;
• monitor CPU utilization, memory use, disks I/O rates, etc;
• monitor and manage table space growth;
• SQL and Explain Plan analysis and tuning and index design (query response time was decreased by up to 170 times);
• tuning of instance and database;
• create and execute (Korn Shell) scripts to monitor DB2 and produce database metrics (ex. transaction response times);
• problem determination and resolution;
• perform backups, reorgs, runstats, recoveries;
• exporting and loading of tables;
• gave DB2 training, presentations, mentoring and support to internal DBA group (5 DBA’s);
• advise and provide guidance to management on best practices
ENVIRONMENT: IBM pSeries – AIX, Korn shell,
DB2 LUW EEE,
Siebel, Quest Central, NMON, Foglight
COMPANY: Royal Canadian Mounted Police - RCMP (Ottawa, Ontario)
YEAR OF MANDATE: 2004 - 2005
DURATION OF MANDATE: 12 months
FUNCTION: Consultant "Senior Database Administrator"
Canadian Police Information System
This 24/7 online system tracks criminal activities recorded by RCMP officers across Canada.
As a DBA, the following activities were performed:
• plan, install, configure and test DB2 LUW ESE 8.1 for a Quality Control environment and a Production environment - this solution provided for High
Availability (Hot Standby Failover) on Sun Clusters;
• install DB2 LUW ESE binaries on each node;
• install Fix Packs;
• instance creation and database creation on global shared disks;
• configure and setup Tivoli Storage Manager (TSM) and enable DB2 to use TSM for archiving of logs and for backups and recovery of databases;
• migration of databases, tables, indexes, etc. from old servers to new servers;
• install DB2 Clients on Windows work stations;
• upgrade the Development environment from DB2 LUW V7 to DB2 LUW V8.1;
• creating and droping instances;
• create admin server;
• Install and evaluate OMEGAMON XE;
• resolve Java application database performance issues;
• create databases, bufferpools, tablespaces, tables, indexes, primary keys, foreign keys;
• create, maintain and execute scripts (Korn shell) for backup, reorg, runstats, etc;
• perform recoveries;
• perform failover testing;
• space management;
• using Snapshot, Event Monitor, Omegamon XE monitor applications and resolve performance and locking contention issues;
• using Explain examine access plans and tune SQL queries;
• configure instances and databases for optimal performance;
• exporting and loading of tables;
• configure and use Heath Center to monitor database objects;
• plan, install and configure DB2 Connect Enterprise Edition on various Unix servers;
• provide production support;
• transfer of knowledge/training/mentoring in-house DBA's;
• provide guideance to developers regarding data base access issues
ENVIRONMENT: Sun Fire (High Availability Cluster), IBM P-Series,
Storage Area Network (SAN),
Solaris 8, Solaris 9, AIX 5.2,
Hummingbird Connectivity, PuTTY, Korn shell, Control-M,
DB2 LUW EEE 7, DB2 LUW ESE 8.1, DB2 Connect,
Replication Center, Task Center,
Control Center, Command Center,
Configuration Assistant, Health Center,
OMEGAMON XE,
Tivoli Storage Manager
.
COMPANY: IBM – Morgan Stanley (Poughkeepsie, New York)
YEAR OF MANDATE: 2005
DURATION OF MANDATE: 2 months
FUNCTION: Consultant "Database Architect – DB2 LUW Specialist"
Data Warehouse / Data Mart
The purpose of this assignment was to benchmark the performance of IBM’s DB2 LUW ESE with Database Partitioning on Morgan Stanley’s data
warehouse. The hardware used was essentially 4 P575’s with 8 CPUs each to store the partitioned tables and a P520 which was used as the catalog
partition and to store the non-partitioned tables. The Fast Communication Manager (FCM) took advantage of a 2 Gigabit Ethernet network to
communicate across the nodes.
Working with a team of IBM specialists, the following activities were performed and timed for performance:
• loading of approximately 100 tables (largest table contained 6.8 billion rows);
• exporting of data;
• inserts/updates/deletes;
• execution of queries (Query Patroller was used to manage concurrency);
• backups/restores using Tivoli Storage Manager;
• a fifth P575 was added increasing the number of partitions from 32 to 40 and scalability performance tests were made;
• failover testing using HACMP;
• implementation of Java Stored Procedures and testing of BRIO and Business Objects;
• Informatica was used to test ETL
ENVIRONMENT: IBM pSeries – AIX 5.2,
DB2 LUW ESE 8.2 with Data Partitioning,
Query Patroller,
Tivoli Storage Manager
.
COMPANY: The GAP (Rocklin, California)
YEAR OF MANDATE: 2004
DURATION OF MANDATE: 1 month
FUNCTION: Consultant "Senior Database Administrator" – Data Propagator Expert
The purpose of this assignment was to assist The Gap with its first installation and setup of Data Propagator.
The following activities were performed:
• install and configure DB2 LUW ESE 8.1;
• establish DB2 LUW/DB2 bidirectional connectivity between AIX and remote servers;
• 3 tier setup of Data Propagator;
• register/subscribe tables, configure, stress test, monitor and troubleshoot Data Propagator;
• make recommendations on configuration of Data Propagator and DB2 LUW;
• space management, Runstats, Backups, Reorgs;
• configure instance and data base for optimal performance;
• configure and use Heath Center to monitor database objects;
• transfer of knowledge/training of in-house DBA's
ENVIRONMENT: IBM pSeries – AIX 5.2,
PuTTY, Korn shell,
DB2 LUW ESE 8.1,
Replication Center, Task Center,
Control Center, Command Center,
Configuration Assistant, Health Center,
MVS, Z/OS
COMPANY: CIGNA (Bloomfield, Connecticut)
YEAR OF MANDATE: 2001 - 2004
DURATION OF MANDATE: 36 months
FUNCTION: Consultant "Senior Database Administrator"
CIGNA HealthCare provides medical insurance coverage through managed care and indemnity programs to approximately 14.3 million people, dental
coverage to approximately 13 million and mental health coverage to approximately 14million. The company employs some 30,000 professionals.
Data Warehouse / Data Marts Project
The purpose of this project is to integrate and store information from over 22 source systems at CIGNA over a 40 month period. This multi-terabyte
database system consists of over 600 tables and the largest table in production contains over 2 billion rows.
At its peak, during the development phase, there were 5 DBA’s, 6 managers and over 50 developers and on the project .
As a senior DBA, I was involved from the design stages through the final installation in the production environment. I was also involved in supporting the
production environment.
The following activities were performed:
• review and revise the logical data model;
• using ERWIN convert the logical model to the physical model;
• design and document a clustering and partitioning strategy which would allow for maximum exploitation of parallelism and scalability;
• design partitioned and non-partitioned tables and indexes;
• using ERWIN complete object definitions and generate DDL;
• create and maintain 8 test environments across 6 DB2 subsystems;
• perform database sizing and space management activities;
• set up and implement STORAGE GROUPS, TABLE SPACES, TABLES, INDEXES;
• create DCLGENs;
• granting of privileges;
• design and implement an image copy / recovery strategy for the warehouse;
• set up and maintain procedures for backup and perform backups;
• perform recoveries of tables and rebuilding of indexes;
• performance monitoring using OMEGAMON and STROBE
• tuning at the database and application level
• creation and tuning of indexes;
• SQL tuning (using the EXPLAIN statement and the PLAN table analyze access paths and optimize queries);
• Tablespace and Index Reorgs;
• partitioning;
• parallelism;
• Runstats;
• Rebinds;
• resolve contention and locking problems;
• resolve BMC load and other utility issues;
• resolve Data Propagator issues;
• benchmark performance and functionality of BMC load utility versus IBM load;
• unloading / loading of tables;
• configure DB2 Connect;
• setup ODBC connectivity to the mainframe which enable queries to execute from the PC using Microsoft Access;
• develop DBA tools which accelerate the productivity and quality of DBA work;
• training, mentoring and leading other DBAs;
• provide technical support and mentoring for the development team;
• interfacing and communicating with the managers of the project;
• prepare DBA production release plans and coordinate with production DBA;
• keep a pulse on issues / problems arising from production and proactively seek / provide solutions which will prevent future occurrences;
• provide production support.
ENVIRONMENT: IBM 3090 - OS 390
PLATINUM ERWIN,
DB2 (version 7 with Data Sharing),
SQL, SPUFI, DCLGEN,
DB2 UTILITIES, BMC UTILITIES,
DB2 COMMANDS,
DB2 CONNECT,
DB2 ESTIMATOR,
DB2 DATA PROPAGATOR,
DB2 CANDLE DB/WORKBENCH,
OMEGAMON II FOR DB2, STROBE,
RCSECURE, TSO/ISPF,
VSAM, FILE-AID, SMS,
ODBC, MICROSOFT ACCESS
.
COMPANY: MERCK – MEDCO (Franklin Lakes, New Jersey)
YEAR OF MANDATE: 2000
DURATION OF MANDATE: 3 months
FUNCTION: Consultant "Senior Database Administrator"
FINANCIAL DATA WAREHOUSE
This warehouse stores financial information relating to claims for drug prescriptions which are processed by Merck – Medco (a wholly owned subsidiary
of Merck, the pharmaceutical giant). The database has 16 tables with the largest one containing 2.5 billion rows.
As the DBA for this project I was involved from the design stages through the final installation in the production environment.
The following activities were performed:
• review and validate the conceptual data model;
• logical data modeling;
• physical modeling (design partitioned and non-partitioned tables, indexes, etc.) ;
• perform database sizing and space management activities;
• set up and implement STORAGE GROUPS, TABLE SPACES, TABLES, INDEXES for the test and production environments;
• plan and execute initial loads for the test and production environments;
• wrote a DB2 COBOL program which was used to determine which partition to load during the production bi-weekly auto-loads;
• using the EXPLAIN statement and the PLAN table analyze access paths and optimize queries;
• plan and set up procedures for backup an recovery;
• prepare and execute the following utilities: LOAD, REORG, IMAGE COPY, QUIESCE, RECOVER, RUNSTATS, MODIFY, etc.;
• support members of the team for all database and SQL related problems;
• verify all SQL access paths before the final move of the application to production;
• granting of privileges to various DB2 groups;
• provide production support.
ENVIRONMENT: IBM 3090 - OS 390
DB2 (version 6), SQL, SPUFI, DCLGEN,
DB2 UTILITIES, BMC UTILITIES,
DB2 COMMANDS,
DB2 ESTIMATOR,
OMEGAMON II FOR DB2,
RACF, TSO/ISPF, VSAM,
FILE-AID, FILE-AID FOR DB2
WINDOWS NT.
.
COMPANY: SAPIENT CORPORATION (Boston, Massachusetts)
YEAR OF MANDATE: 1999 - 2000
DURATION OF MANDATE: 6 months
FUNCTION: Consultant "Senior Database Administrator"
ARCHIMEDES DATA WAREHOUSE
The Archimedes data warehouse enables loan accounting information to be systematically obtained and stored to provide accurate, flexible, and
consistent reporting for internal and external customers. This system was developed in DB2 LUW version 6 (Universal Database EEE). It consisted of 28
Solaris processors and 4 database partitions. It made use of DB2 LUW’s Inter and Intra partitioning and query parallelism. The tables in this system
consisted of several hundred million rows each. As a DBA for this project the following activities were performed:
• review and validate logical and physical data models;
• determine causes for performance degradation and outline and implement a plan which would resolve these problems
• create and configure databases
• configuring of log files
• create SMS and DMS table spaces
• prepare, execute and schedule scripts which perform RUNSTATS and REORGs of tables
• prepare and execute scripts to IMPORT, EXPORT and LOAD tables
• copy tables from one database to another
• analyze system configuration parameters (buffer pools, locking, etc.) and make recommendations;
• using the EVENT MONITOR and the SNAPSHOT MONITOR determine causes for performance issues;
• using VISUAL EXPLAIN determine the access paths for problem SQL;
• tune SQL by reducing joins, eliminating table space scans and making better use of indexes;
• add / modify indexes and implement online index reorganizations in order to maximize performance;
• prepare, execute and schedule scripts which perform online and offline BACKUPs of table spaces and databases;
• RESTORE and ROLL-FORWARD of table spaces and databases
• using the Client Configuration Assistant, enable data manipulation of the tables via Microsoft ACCESS
ENVIRONMENT: UNIX (SUN SOLARIS), WINDOWS NT,
UNIVERSAL DATABASE (DB2 LUW) EEE,
SQL, ERWIN, TCP/IP
COMPANY: AXA CANADA TECH (Montreal, Quebec)
YEAR OF MANDATE: 1998 - 1999
DURATION OF MANDATE: 12 months
FUNCTION: Consultant "Senior Database Administrator"
COMMERCIAL INSURANCE SYSTEM
The Commercial Insurance System manages insurance policies for corporations. It was developed using the C language running on PC’s accessing DB2
data on the mainframe via DB2 CONNECT. This client-server system is composed of 100 DB2 tables. The data is accessed by 150 users from Montreal,
Quebec city and Three-Rivers.
As the DBA for this project I was involved from the initial stages of development through the final installation in the production environment. The
following activities were performed:
• review and validate logical and physical data models;
• set up and implement STORAGE GROUPS, DATABASES, TABLE SPACES, TABLES, INDEXES, VIEWS for various test environments, a performance
environment, a training environment and the production environment;
• using OMEGAMON, monitor and resolve database performance and contention problems;
• using the EXPLAIN statement and the PLAN table analyze access paths and optimize SQL;
• migration of databases from one environment to another;
• perform database sizing and space management activities;
• plan and set up procedures for backup and recovery;
• plan and participate in disaster / recovery simulations;
• prepare and execute the following utilities: LOAD, REORG, IMAGE COPY, QUIESCE, RECOVER, RUNSTATS, MODIFY, etc.;
• binding of PLANS and PACKAGES on PC’s and the mainframe;
• set up procedures and standards for binding of PLANS and PACKAGES;
• define STORED PROCEDURES to the DB2 sub-systems;
• set the DB2 Communication database to verify “come from” checking of users from LU’s (gateways);
• grant and revoke privileges to secondary authorization id’s for STORAGE GROUPS, DATABASES, TABLESPACES,TABLES, PLANS, PACKAGES and
COLLECTIONS;
• install the REFERENTIAL INTEGRITY and analyze and resolve data integrity problems;
• support members of the team for all database and SQL related problems;
• verify all SQL access paths before the final move of the application to production;
• provide production support;
• make and follow up on recommendations to the system DBA’s in New York relating to the definition of the DB2 subsystems (locking and idle time
out parameters, DB2 logs, stored procedures, etc.).
ADHOC DB2 LUW SUPPORT
Provide support to a junior DBA in the tuning of a DB2 LUW system.
ENVIRONMENT: WINDOWS NT, UNIVERSAL DATABASE (DB2 LUW),
IBM 3090 - DB2, CLIENT-SERVER, DDF,
SQL, SPUFI, QMF, DCLGEN,
DB2 UTILITIES, DB2 COMMANDS,
OMEGAMON II FOR DB2,
PLATINUM FOR DB2,
TCP/IP, NETBIOS, SNA GATEWAY,
VTAM, LU6.2, DB2 FOR WINDOWS,
DB2 CONNECT, TOP SECRET,
OS 390, TSO/ISPF, VSAM, FILE-AID.
COMPANY: BELL HELICOPTER TEXTRON CANADA (Mirabel, Quebec)
YEAR OF MANDATE: 1997 - 1998
DURATION OF MANDATE: 12 months
FUNCTION: Consultant "Senior Database Administrator"
PEOPLESOFT HUMAN RESOURCES SYSTEM
The mandate of this project was to assist in the installation of the PEOPLESOFT Human Resources System.
As senior consultant, the following activities were performed:
• set up and implement DATABASES, TABLE SPACES, TABLES, INDEXES, VIEWS, VSAM CLUSTERS for the test and production environments;
• using OMEGAMON, monitor and resolve database performance and contention problems;
• using the EXPLAIN statement and the PLAN table analyze access paths and optimize SQL;
• estimate and manage space required for TABLE SPACES and INDEXES;
• plan and set up procedures for backup an recovery;
• prepare and execute JCL to LOAD, REORG, IMAGE COPY, QUIESCE, RECOVER, RUNSTATS, MODIFY, CHECK DATA, CHECK INDEX, etc.;
• grant and revoke privileges to secondary authorization id’s for DATABASES, TABLESPACES, TABLES, PLANS;
• provide production support;
• support members of the team for all database and SQL related problems;
• give DB2 training to the in-house DBA’s.
ENVIRONMENT: IBM 3090 - PEOPLESOFT, CLIENT-SERVER,
SQL, SPUFI, QMF, SQR,
DB2 UTILITIES,
DB2 COMMANDS,
OMEGAMON II FOR DB2,
PEOPLE TOOLS,
OS 390, TSO/ISPF, VSAM.
COMPANY: HYDRO-QUEBEC (Montreal, Quebec)
YEAR OF MANDATE: 1996 - 1997
DURATION OF MANDATE: 6 months
FUNCTION: Consultant "Database Administrator"
Database administration for 18 systems used by various development teams consisting of over 250 people. These DB2 systems consisted of over 500
Tables.
The following activities were performed:
• creation of environments on multiple platforms / multiple Database Management Systems;
• implement changes to environments across multiple platforms / multiple Database Management Systems;
• migration of data across multiple DB2 subsystems;
• migration of data across multiple platforms / multiple Database Management Systems;
• creation of DATABASES, STORAGE GROUPS, TABLE SPACES, TABLES, INDEXES and VIEWS;
• creation of WORKLISTS using BMC CHANGE MANAGER;
• down-sizing of Tables;
• extraction and reloading of data;
• enforce referential integrity;
• reorganization and image copies of TABLE SPACES;
• support development team.
ENVIRONMENT: IBM 3090 - DB2, SQL,
DB2I (SPUFI, DCLGEN, BIND, UTILITIES, ETC.),
BMC CATALOG MANAGER,
BMC CHANGE MANAGER,
DB2 FILE-AID,
DB2 FILE-AID / RDX,
XDB, SQL WIZARD,
OS/MVS, TSO/ISPF, VSAM.
COMPANY: BELL SYGMA (Montreal, Quebec)
YEAR OF MANDATE: 1995 - 1997
DURATION OF MANDATE: 22 months
FUNCTION: Consultant "Database Administrator"
BILLING SYSTEM
This DB2 system which bills over 6.5 million customers across Quebec and Ontario consists of approximately 150 DB2 Tables.
As a DBA for this project, the following implementation and support activities were performed:
• set up the Billing system for the performance environment;
• implemented DATABASES, TABLE SPACES, TABLES, INDEXES, VIEWS, etc.;
• estimate space required for TABLE SPACES and INDEXES and create the VSAM clusters;
• prepared and executed procedures to LOAD, REORG, IMAGE COPY, RECOVER, etc.;
• implement and verify the REFERENTIAL INTEGRITY across the various Tables after they have been loaded;
• participated in the analysis and resolution of performance issues;
• develop tools which facilitate RECOVERY;
• supported a team of application programmers;
• trained and planned the work for another DBA.
HUMAN RESOURCE INFORMATION SYSTEM
This DB2 system manages employee information for approximately 50,000 BELL CANADA employees. It is composed of approximately 50 Tables.
As the DBA for this project, the following activities were performed:
• analyze and resolve data integrity problems;
• analyze and resolve database performance problems;
• support members of the team for all database related problems.
RECRUITMENT SYSTEM
This DB2 Client-Server system is used to manage and fill in vacant positions throughout BELL CANADA using applications of both internal and external
resources. It was developed using VISUAL BASIC and accesses via personal computers DB2 Tables on the mainframe.
As the DBA for this project, the principle task was to optimize performance of DB2 access from the PC’s to the mainframe.
ENVIRONMENT: IBM 3090 - DB2, SQL,
SPUFI, QMF, RACF,
OS/MVS, TSO/ISPF, VSAM,
DB2 BMC UTILITIES,
DB2 FILE-AID,
DB2 OMEGAMON,
CANDLE DB2 WORKBENCH.
COMPANY: FINANCIAL COLLECTION AGENCY (FCA) INTERNATIONAL (Montreal, Quebec)
YEAR OF MANDATE: 1996
DURATION OF MANDATE: 4 months
FUNCTION: Data Architect
TRACE SYSTEM
The mandate of this project was to develop a system which would trace over 4 million debt accounts of companies / individuals on behalf of other
companies / individuals.
As the data architect, the following activities were performed:
Develop the Conceptual Data Model
• define elements (attributes)
• define entities
• define relationships
• define associative entities
• establish cardinalities
• establish primary keys
• produce the entity-relationship diagram
Transform the Conceptual Data Model into the Logical Data Model
• define domains for elements
• define foreign keys
• add “non-conceptual” elements as required
• define format of elements
• establish volumes and frequencies
• review the business processes and ensure model is satisfactory
• denormalize model (in lieu of processes)
• adjust primary keys
• denormalize model (in lieu of performance)
Transform the Logical Data Model into the Physical Data Model
• define Tables
• define Indexes
• define Referential Integrity
• add “system” tables
ENVIRONMENT: TANDEM - NONSTOP SQL,
SYSTEM ARCHITECT
COMPANY: HYDRO-QUEBEC (Montreal, Quebec)
YEAR OF MANDAT: 1993 - 1994
DURATION OF MANDATE: 8 months
FUNCTION: Consultant "Senior Database Administrator"
PERSONNEL EXPENSE SYSTEM
At the time of the development of this system, expenses of personnel at Hydro-Quebec were managed manually and independently throughout the
various departments within the company. The objective of this new system is to integrate and automate management of expenses, advances,
reimbursements and payroll deductions related to these expenses for all 20,000 individuals at Hydro-Quebec. The system would eliminate approximately
55 person-years of work per year for the company.
As the DBA for this project, the following activities were performed:
• validated the conceptual data model;
• made adjustments to the conceptual data model;
• using the conceptual data model derived the logical data model;
• validated all on-line requirements and consequently made adjustments to the logical data model;
• derived the relational model;
• defined TABLES for entities and relationships in the conceptual data model;
• defined columns, primary and foreign keys thus establishing the referential integrity for the TABLES;
• defined TABLE SPACES and assigned the TABLES to the TABLE SPACES;
• created INDEXES for the TABLES.
ENVIRONMENT: IBM 3090 - DB2, SQL,
SPUFI, QMF,
OS/MVS, TSO/ISPF, VSAM,
LE DEVELOPPEUR.
COMPANY: HYDRO-QUEBEC (Montreal, Quebec)
YEAR OF MANDATE: 1992 - 1993
DURATION OF MANDATE: 12 months
FUNCTION: Consultant "Database Administrator"
As a database administrator, working for the Methods and Data Administration division, the following activities were performed:
• derived the relational and physical data models (DDL) for the Accounts Payable Management system;
• derived a model on security based on the security offered by DB2;
• participated in the development of a software tool which derived the conceptual data model within BACHMAN (Information Engineering
methodology for DB2) using the conceptual data model within the DEVELOPPEUR (MERISE methodology);
• developed a software tool which derived the relational data model (DB2) within the DEVELOPPEUR using the relational data model within BACHMAN
(DB2).
• The following entities were derivable using this software engineering tool:
• Relational Database
• Table Space
• Storage Group
• Table
• Index
• Column
• Primary Key
• Foreign Key
• View;
• optimized databases;
• performed quality assurance functions on databases produced by other DBA's;
• provided technical DBA support to other divisions at HYDRO-QUEBEC;
• produced a software which estimates space requirements for databases and determines certain optimization parameters.
ENVIRONMENT: IBM 3090 - BACHMAN, DB2, DB2/2, SQL,
SPUFI, QMF, QUERY MANAGER,
OS/MVS, , RACF,
TSO/ISPF, VSAM/IDCAMS,
LE DEVELOPPEUR.
 | |  | |  | |  | |  | |  | |  |
If your organizations has an IT Staffing or Consulting need for
a Netezza - IBM PureData Resource, please email us at
sales@peningo.com . If you wish to speak with someone from
Peningo Systems, please click here to contact Peningo.
a Netezza - IBM PureData Resource, please email us at
sales@peningo.com . If you wish to speak with someone from
Peningo Systems, please click here to contact Peningo.