Forest Client Database Migration Strategy

Data Quality Issue

Database options

• Stay with oracle
  • Pros:
    • No need to migrate data
    • Only need to manage one database
  • Cons:
    • Take time to get full read/write access to the oracle db
    • If we want to improve the ER Model, hard to change the existing structure, might affect other tables and existing users
    • Expensive
• Create and use a new postgres db
  • Pros:
    • Have full control of the db
    • Could improve the ER Model as we like
  • Cons:
    • Need to transfer data from oracle to postgres
    • Need think about data synchronize
    • Need to think about the tables that CLIENT is using but not owns

Database Migration Options

• Case one: If all the target users could switch to use the new CLIENT application once we publish, and related applications can switch to read from the new postgres db, or use forest client api
  • Create new ER Models in postgres
  • Develop the new application, could transfer oracle data over to postgres for testing
  • Once application is ready to be published, transfer oracle data over to postgres again to get latest data
  • Will only use postgres for CLIENT in the future
  • Synchronize data from oracle to postgres for external tables that not owned by CLIENT
• Case two: If all the target users could switch to use the new CLIENT application once we publish, some related applications still read from oracle db
  • Create new ER Models in postgres
  • Develop the new application, could transfer oracle data over to postgres for testing
  • Once application is ready to be published, transfer oracle data over to postgres again to get latest data
  • Will use postgres to add or update client in the future
  • Sync the data from postgres back to oracle, so other applications can get the new data
  • Synchronize data from oracle to postgres for external tables that not owned by CLIENT
• Case three: If we publish the new application, but not ready to retire the old application, or some related applications still write into oracle db
  • Create new ER Models in postgres
  • Develop the new application, could transfer oracle data over to postgres for testing
  • Once our application is ready to be published, transfer oracle data over to postgres again to get latest data
  • While the old application is still in use, setup cron job to to check data difference daily, need to consider carefully which one has the "good" data
  • Synchronize data from oracle to postgres for external tables that not owned by CLIENT

Database Migration Steps

• Copy data over initially (one-time), method:
  • FME Desktop: desktop tools that is very powerful for data transferring, support renaming, restructuring, speed is fast, but need to learn the tool
  • ora2pg: command line tools to copy oracle data to postgres, support simple data transfer, but not that powerful, installation process is not very easy for new user who doesn't know this tool
  • Plain function in nestjs: read from one database and do insert in another, have fully control of the data structure, but takes a lot of time
• Do data synchronize if need, method:
  • Plain function in nestjs: setup cron job daily to read from one database for any new/update record from yesterday, and do insert/update in another. We're not expecting a lot of new data everyday, so speed should be fine, but need to write code for each table
  • FME Server with API: could support schedule jobs in openshift, but need to learn the FME Server, and how to do data merge with FME
  • NATS: two way real time data synchronize tool, need to learn

Question:
For applications which use forest client data, do they just have read access? or they have write access as well? which tables are they using?
For external tables client are using, do we know which attribute it actually looks for?

Client Table Analysis
Missing CLIENT_AUDIT_CODE, EXTERNAL_CLIENT_REORG_RESULT, EXTERNAL_CLIENT_REORG_SYSTEM, NURSERY_LOCATION (external)

31 tables below:

Table name	Used by which app/Access
BUSINESS_CONTACT_CODE	CLIENT(select)
CLIENT_ACTION_REASON_XREF	CLIENT(select)
CLIENT_CONTACT	CLIENT(select/insert/update), SCS(select), GAS2(select)
CLIENT_DOING_BUSINESS_AS	CLIENT(select)
CLIENT_DOING_BUSINESS_AS_AUDIT	CLIENT(select)
CLIENT_ID_TYPE_CODE	CLIENT(select)
CLIENT_LOCATION	CLIENT(select/insert/update), HBS(select/update), select only for all other apps
CLIENT_RELATIONSHIP_CODE	CLIENT(select)
CLIENT_RELATIONSHIP_TYPE_XREF	CLIENT(select)
CLIENT_STATUS_CODE	CLIENT(select), CBR(select)
CLIENT_TYPE_CODE	CLIENT(select), HBS(select)
CLIENT_TYPE_COMPANY_XREF	CLIENT(select)
CLIENT_UPDATE_ACTION_CODE	CLIENT(select)
CLIENT_UPDATE_REASON	CLIENT(select/insert)
CLIENT_UPDATE_REASON_CODE	CLIENT(select)
CLI_CON_AUDIT	CLIENT(insert)
CLI_LOCN_AUDIT	CLIENT(select/insert)
FOREST_CLIENT	CLIENT(select/insert/update), select only for all other apps
FOR_CLI_AUDIT	CLIENT(select/insert), FREP(select)
MAILING_CITY	CLIENT(select)
MAILING_COUNTRY	CLIENT(select)
MAILING_PROVINCE_STATE	CLIENT(select)
MAX_CLIENT_NMBR	CLIENT(update)
MOF_USER_ORG_DEFAULT (external table)	FTA(select/insert/update), select only for all other apps
ORG_UNIT (external table)	select only for all apps
REGISTRY_COMPANY_TYPE_CODE	CLIENT(select)
RELATED_CLIENT	CLIENT(select/insert/delete), FTA(select)
REL_CLI_AUDIT	CLIENT(select/insert)
CLIENT_ACRONYM	select only for all apps
V_CLIENT_PUBLIC	select only for all apps