M Working at BCRS

Client: BC Registry Services, the official government register for corporations, names, personal properties and other, Victoria, BC
Duration: Aug. 2004 - Mar. 2010, Feb. 2011 - Apr. 2011
Roles: Database Developer (85%), Report Developer (10%), Customer Support (5%)
  • I joined the client just as they moved their primary module (Corporate Online) from the Mainframe to the Oracle/JEE platform. I have been involved with three more modules/projects.
  • The client has been through a number of changes over those 6 years I've been there. They have been under three different ministries; I recall three changes in the top management. A similar dynamic could be seen in a technical field, with a number of technologies usually based on Java and Oracle being used. I was for instance exposed to four different report platforms. For a short time I even substituted as JEE developer.
  • Projects below are sorted chronologically, from the latest to the oldest

Project: All projects
  • Defined a standard for a database build process for the entire project in a form of a DB package. Package logic is capable of keeping track of scheduled and emergency builds, as well as database, web or other application specific changes
  • Defined a standard for a database logging and exception handling in a form of a DB package
  • Provided data mining for the business analysts and the project managers; performed ad-hoc data fixing
  • Optimized existing SQL code; used such techniques as indexes and hints, parallelism or pipelined where appropriate. Worked closely with DBA
  • Provided limited SQL tutoring to selected employees and consultants

Project: Societies and Firms Online
  • Developed reports and corresponding SQL code
  • Spent an additional effort optimizing reports SQL and Java code, as well as report server settings, to improve performance

Project: TILMA (Trade, Investment and Labour Mobility Agreement)
  • Designed and coded processes for collecting and processing data into the data exchange module; processes utilized PL/SQL batches, DB triggers and views
  • Maintained scripts in the data exchange module so they conformed to the changing requirements; technology used was PL/SQL and SQL Loader
  • Created comprehending test cases to simulate all the traffic in the data exchange module

Project: Name Request Online
  • Copied all project data from the Mainframe (DB2) to the temporary Oracle database (with an identical data model). Consequently, copied the existing data with newly generated data into a new data model in a number of steps, using PL/SQL scripts and DB link. The new data model was significantly different, originally drafted by business analysts and systems architect and finalized by me
  • Created PL/SQL scripts for data synchronization between the new application and existing modules; scripts were scheduled to run with Oracle Scheduler
  • Created reports and reporting related views and tables
  • Especially at the beginning of the project I was doing a lot of data mining for the business analysts and the management to better understand and clean the data from the Mainframe

Project: Corporate Online
  • Wrote a project specific code, such as Company dissolution: before my involvement the official government register had no such a capability. I designed and coded the entire process, from identifying the companies, to moving them through various stages of dissolution. Every step resulted in a generation of legal documents and emails, which creation and processing was also done by me
  • Created a reporting module with its own data model and dozens of views. Reports can be called from a database, DB scheduler or via HTTP. Each run is identified with a unique ID and reports can be run and re-run with the same results if desired (handled via data buffer)
  • Created an emailing module for sending emails from the database. Module is based on its own DB package and data model for storing emails and logs
  • Created a module for generating data files and FTP them to the Linux and Mainframe servers. Both, file generation and FTP was done from a database, so they could be done from a database batch
  • Created and modified code for converting DB data to XML for further messaging and reporting
  • Created report specific test cases, with corresponding logic and data; created one case for each report variation

Technology: Oracle (9i - 11g), DB2, SQL, PL/SQL, SQL*Plus, SQL Developer, DBVisualizer, Toad, Java, XML, XSLT, JasperReports, Oracle Reports, Jreport, Fop/Xalan, SVN, CVS, PuTTY, VPN, Core FTP, SQL Loader, Windows Virtual PC, MS Visio, MS Windows, Linux, Shell