Skip to content

Forest Client Database Migration Strategy

Jump to bottom
MCatherine edited this page Oct 11, 2022 · 29 revisions

Data Quality Issue
image image

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, it will 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 doesn't own

Database Migration Options

  • Case one: We publish the new application, but not ready to retire the old application
    • not recommend, hard to manage two database behind
  • Case two: All the target users switch to use the new CLIENT application once we publish, and related applications switch to read from the new Postgres db, or use forest client API
    • ideal, but hard to promise
  • Case three: All the target users switch to use the new CLIENT application once we publish, but still have related applications read from Oracle db
    • very likey, will be our start point
  • Case four: All the target users switch to use the new CLIENT application, but some related applications still read and write into Oracle db
    • possible, but not very likely
    • check the Power BI report for client tables usage for last 4 weeks, not found any other application is writing into the client tables
    • if we could keep checking the client tables usage for the next 3 month
      • if still not found:
        • when we publish the new app, disable all the write access for Oracle client tables
        • if break any app, we provide an API to them
        • this will be like case three
      • if found any:
        • it could be just 1 or 2 or a few tables, won't be too hard to do data sync
        • so it will be like case three, with some additional care for a certain tables
        • for example: when write into Postgres, write into Oracle as well, so for that certain table, only bring data from Oracle to Postgres

How We Work with Two Databases

  • Use Postgres for new CLIENT application, read/write
  • Sync Postgres data back to Oracle, so keep Oracle up to date
  • For some external tables like ORG_UNIT, if still use them, could get a proxy account to read from Oracle, or ask them to provide an API

Database Migration Method

  • Copy data over initially (one-time):
    • 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 application: 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:
    • Plain function in application: 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

Our Next Steps (start based on case three, all users will use the new CLIENT application, all the rest related apps just read from Oracle)

  • Start by creating the new ER Model in Postgress for FOREST_CLIENT, CLIENT_TYPE_CODE, CLIENT_STATUS_CODE
  • Copy the Oracle data into the Postgres db using FME Desktop
  • Keep regular development, and check the client table usages montly
  • Once get write access to Oracle db, investigate method to sync data from Postgres to Oracle, very likely will be writing plain functions in our application

Questions:

  • 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?
    Power BI report only shows the client table usage for the last 4 weeks. So far only found tables are read by other apps, no write access for other apps
  • For external tables CLIENT are using, can we know how they are used? do we know which attribute in each table it actually looks for?
    They do read only, so we could get a proxy account to still read from Oracle
  • If it's better to bring all tables own by CLIENT to Postgres all at once? or bring as need? Bring needed ones first
  • Why some table client only has select access? Power BI report only shows the client table usage for the last 4 weeks, so it could have other access in other time
  • We don't have access to read external tables Could request a new proxy account for reading specific external tables

Client Table Analysis
Missing CLIENT_AUDIT_CODE (not in use),
EXTERNAL_CLIENT_REORG_RESULT (not in use),
EXTERNAL_CLIENT_REORG_SYSTEM (not in use),
all REORG tables are not in use any more,
NURSERY_LOCATION (external, owns by SPAR)

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), THE schema(select/update, this is very likely a manual update, not through any application), 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

Forest Client API

Clone this wiki locally