Scroll To View More
Dave Pickles, Oracle PL/SQL Developer in Victoria, BC, Canada
Dave Pickles

Oracle PL/SQL Developer in Victoria, BC, Canada

Member since May 9, 2019
Dave has over 40 years of experience in Oracle server and IBM mainframe technologies and currently specializes in high-performance Oracle data loads. He has participated in two massive (billions of rows) health sector Oracle-based data warehouse projects designing and implementing methodologies to optimize various aspects of the load processes. Dave particularly enjoys solving seemingly impossible performance challenges.
Dave is now available for hire

Experience

  • IBM Mainframe, 20 years
  • SQL, 20 years
  • Oracle DBMS, 20 years
  • ETL, 18 years
  • Data Warehousing, 16 years
  • Oracle PL/SQL, 15 years
  • Exadata, 8 years
  • Unix Shell Scripting, 5 years
Victoria, BC, Canada

Availability

Full-time

Preferred Environment

Oracle PL/SQL

The most amazing...

...success I've had on a project was taking a task initially estimated at 2-1/2 years of execution time and creating a custom process which completed in 12 hours.

Employment

  • Oracle Developer

    2013 - PRESENT
    BC Ministry of Health (Healthideas)
    • Built the load processes to import a complex large volume laboratory results tracking system (115 million sets of results) into the data warehouse which required significant optimization work to make the initial and ongoing loads practical.
    • Developed a utility to streamline the metadata driven code generation functionality of the proprietary data warehousing framework used to build the data warehouse.
    • Performed a detailed analysis of the code of a mainframe system being converted into the data warehouse to determine exact details of the processes being converted, supporting several co-workers also involved in the conversion.
    • Constructed the load processes for a complex client tracking system being imported into the data warehouse consisting of 34 primary and 26 support tables.
    • Developed a utility to track the performance of individual queries within a large data load to determine which queries needed tuning.
    Technologies: Oracle, PL/SQL
  • Oracle Developer

    2011 - 2013
    BC Ministry of Social Development
    • Led a team responsible for extracting legacy IMS/DB2 data from the mainframe for the ministry's main system.
    • Designed, developed, and executed conversion processes to translate the IMS/DB2 data into Oracle for the conversion of the ministry's main system.
    • Developed extract processes for legacy DB2 data for a smaller ministry subsystem which included a large volume of archival image data which needed to be converted.
    • Designed, developed, and executed conversion processes to translate the DB2 data into Oracle for the conversion of the ministry's subsystem.
    Technologies: Oracle, PL/SQL
  • Oracle Developer

    2005 - 2011
    BC Ministry of Health (Healthideas)
    • Designed and built a process to efficiently load a large volume of historical records (650 million) into the data warehouse which featured dynamic control of the load.
    • Designed and implemented a methodology for merging multiple dynamic time series sets of data into a single time series table on an ongoing basis.
    • Designed and implemented a methodology for splitting load processes to allow them to run in parallel to improve load performance.
    • Implemented several changes and enhancements to the code generation abilities of the metadata driven proprietary framework used to build the data warehouse.
    • Co-designed and implemented a strategy for staging incoming data extracted from numerous sources so the data could be queued up for loading into the data warehouse when practical.
    Technologies: Oracle, PL/SQL
  • Oracle Developer

    2001 - 2004
    BC Ministry of Health (HNData)
    • Wrote the conversion specifications for numerous data sources coming into a new data warehouse being built for the ministry.
    • Built load processes for the data warehouse using ETI.
    • Ran the initial load processes for the data warehouse, a process which took over three months, requiring significant evening and weekend support to maintain the progress of the load.
    • Designed and built a process which needed to be able to do 6 billion table lookups and update 1.5 billion rows over a weekend time frame.
    Technologies: Oracle, PL/SQL, Perl, ETI
  • Mainframe Developer

    1997 - 2000
    BC Pension Corp
    • Acted as technical lead for the Y2K conversion of the IMS database pension administration system, designing the approach to be used and performing the implementation.
    • Created the database conversion process for the Y2K conversion.
    • Built and ran a parallel system test for the batch subsystem for the Y2K conversion.
    • Developed utilities to assist the conversion team in locating the source code changes required for the Y2K conversion.
    • Worked with the team converting the codebase for the Y2K conversion.
    • Designed and implemented an approach to support the staged conversion from the IMS system to a new Oracle system, allowing for an immediate fallback path.
    Technologies: IMS PL/1

Experience

  • BC Healthideas Data Warehouse (Development)

    Healthideas is a massive (billions of rows) data warehouse system built for the BC Ministry of Health using a proprietary metadata driven framework.

    I built a high-performance load for a 650 million row fact table which featured a dynamic control of the processes, so we were able to actively load balance the conversion runs on the Oracle RAC during testing and production.

    I also designed and implemented a methodology for maintaining tables, which merged multiple time series tables into a single combined time series.

  • BC Integrated Case Management (ICM) Project (Development)

    My role in the ICM project was to convert the legacy data from IMS/DB2 mainframe systems into Oracle staging areas ready to load into the target system. My conversions did the restructuring of the data from the legacy data model to the ICM data model and performed exceptionally well. It took less than one hour to complete the extractions and transformations compared to the 48 hours it took the target system load team to load my results into their targets.

  • BC HNData Data Warehouse (Development)

    HNData was the BC Ministry of Health's first large (billions of rows) data warehouse project.

    I designed and built a process which was required to do six billion table lookups. The standard methodology being used for the project would have taken at least 2-1/2 years to perform those lookups, my process completed in 12 hours.

  • BC Pension Corp (Development)

    I was the technical lead for the Y2K conversion of the IMS database system which I had been involved with since its creation over 20 years earlier.

    I designed and build a process to do parallel runs of the batch subsystem each night and compare the results to verify the Y2K version was producing the same results as the current system.

    When the system was later being shut down and converted over to a new Oracle system in stages, I designed and implemented an new approach. This approach would still virtually delete converted sets of data but allow the legacy system to be available for limited read-only access for the converted sets, and also allow an immediate full functionality restoration in the case of a failure of the conversion of a set of data or the new system as a whole.

Skills

  • Languages

    SQL, PL/1, C, Perl
  • Paradigms

    ETL
  • Platforms

    Oracle, Unix
  • Storage

    Oracle DBMS, Oracle PL/SQL, Exadata, IMS DB
  • Tools

    Oracle RAC, ISPF, JCL
  • Other

    Data Warehousing, Unix Shell Scripting, IBM Mainframe

Education

  • Bachelor of Science degree with first class honors in Physics and Applied Mathematics
    1976 - 1980
    University of Victoria - British Columbia, Canada
I really like this profile
Share it with others