KFS Application Database Refresh
- Former user (Deleted)
- Yi Zhang
- Benjamin Daniels
- Richard Ritchie
DB Copy Procedure
An official request will be sent to the DBA team by the Kuali Tech. Manager or Finance System Manager, initiating the DB copy process. The following are high-level steps entailed.
- CM stops Tomcat instances in the target environment(s).
- DBA's copy from Production to target environment. They exclude KFS_PRD.KRNS_ATT_T, KR_PRD.KRNs_ATT_T and KR_PRD.KREW_ATT_T tables.
- They run the encryption process when the copy is complete. Tables excluded by those script runs are re-created.
- Assigned developers check the database copy integrity and on confirmation the CM or SA restarts the Tomcat instance on Manager's request to begin post processing steps.
KFS-1700 - Getting issue details... STATUS
Full Process (including Post-Processing steps)
- Shut down the tomcat application servers on each of the nodes in the environment that will be copied to (kfs, kr, krapps, batch)
- Allow the DBA's to complete the copy from the desired prd backup/export.
- Start up the tomcat application servers on each of the nodes in the environment that was copied to (kfs, kr, krapps)
1. Cleanse the KFS data (kfs db only) (DEV, UAT, TRN, YEN)
The CleansePII.sql file generates values and updates each KFS table that contains PII data. The new
data is encrypted before updating the tables. The following data tables/fields are cleansed:
- fp_bank_t.bnk_acct_nbr
- check_reconciliation_t.bank_account_nbr
- fp_dv_wire_trnfr_t.dv_payee_acct_nbr
- pdp_ach_acct_nbr_t.ach_bnk_acct_nbr
- pdp_payee_ach_acct_t.bnk_acct_nbr
- ar_cust_t.cust_tax_nbr
- tax_payee_t.hdr_vndr_tax_nbr
- pur_vndr_tax_chg_t.vndr_prev_tax_nbr
- pur_vndr_hdr_t.vndr_tax_nbr
- fp_prcrmnt_card_hldr_t.trn_cc_nbr
- fp_prcrmnt_card_hldr_dtl_t.cc_nbr
- fp_prcrmnt_card_trn_t.trn_cc_nbr
2. Clear the service registry table (rice db only) (ALL)
The service registry table contains endpoints for the published web services. It is populated upon startup of the servers. After refreshing from production the production values need to be removed.
The clearServiceRegistry.sql file runs the following command to remove all existing entries from the table:
truncate table KRSB_SVC_DEF_T;
3. Fix the docHandler URLs for the KR Apps documents (rice only) (ALL) ***Future
The doc_typ_t table contains a document handler URL (doc_hdlr_url) for each document type. For all of the
document types that belong to the KR Apps applications, the document handler URL is hardcoded in the table.
These URLs must be corrected so they point to the correct environment and not the production environment.
The URLs for the KFS document types are all parameterized, so there is no need to change the value in the
database. This is only needed for the UCONN* document types that are hardcoded.
There are 2 ways we could fix this.
Solution A: Parameterize the UCONN* document types. This would be the preferred solution because it would mean
nothing would need to be changed when refreshing the databases from production data. The drawback is that currently
we don't know what that will take. Can the parameter replacement happen in rice services or does it have to be
changed in each application?
Solution B: Update the rows in the doc_typ_t table that apply to UCONN* document types. The problem with this solution
is that there are different values that are required for DEV and UAT environments. Plus environments that aren't used
by KRApps applications do not need to be updated (or maybe the rows should be removed completely). Therefore, each
environment would require a unique update process.
4. Remove the workflow data for KR apps documents (rice db only) (DEV, UAT, TRN, YEN) ***Future
The RemoveKrAppsWorkflowData.sql script finds all of the document ids where the document type starts with 'UCONN'
and deletes all records for those document ids in the following rice tables:
- krew_actn_itm_t
- krew_actn_rqst_t
- krew_actn_tkn_t
- krew_app_doc_stat_tran_t
- krew_doc_hdr_cntnt_t
- krew_doc_hdr_ext_dt_t
- krew_doc_hdr_ext_flt_t
- krew_doc_hdr_ext_long_t
- krew_doc_hdr_ext_t
- krew_doc_hdr_t
- krew_doc_nte_t
- krew_edl_dmp_t
- krew_edl_fld_dmp_t
- krew_init_rte_node_instn_t
- krew_out_box_itm_t
- krew_rmv_rplc_doc_t
- krew_rmv_rplc_grp_t
- krew_rmv_rplc_rule_t
- krew_rte_node_instn_t
- krns_adhoc_rte_actn_recip_t
- krns_maint_doc_att_t
- krns_maint_doc_t
- krns_maint_lock_t
- krns_pessimistic_lock_t
- krns_sesn_doc_t
- krns_doc_hdr_t
5. Re-encrypt the CLOB fields in both KFS and Rice databases. (DEV, UAT, TRN, YEN)
6. Remove attachments from the document content XML stored as CLOB types (DEV, UAT, TRN, YEN) ***Future
The XML documents that are stored in the krns__maint_doc_t table in KFS contain all of the data for a
document, including any attachments attached to the document. As the attachments can be extremely large,
and we already remove the data from the attachment table when we refresh, we could gain a significant
amount of space and time by removing the attachments from the document content XML, also.
7. Adjusting DocHandler URL's for UAT workflow applications
FOR WORKFLOW APPLICATIONS, go one by one and change the dochandler url's on the travel doc types
- Select the Administration tab and then choose Document Type
In the Search criteria select "UCONN" and the Workflow Doc Types should appear.
Select edit under Actions for each Doc type, and then alter the Document Handler URL on the right side. Make sure you also fill in "Document Header Change" or another note in the change description, and click Submit.
Do NOT click Save or Blanket Approve. This will not change the Document Handler URL properly.
Environmental Configurations:
Depending on the target environment that has been refreshed, you will need to change the Document Handler URL to a specific path to point it at the correct KRAPPS server.
They are as follows:
UAT:
tst.krapps.uconn.edu (or testworkflow.apps.uconn.edu)
DEV:
dev.krapps.uconn.edu
Once the change has been made, you can validate that it worked by clicking on the Document in another Search, and it should appear with the updated Document Handler URL.
- If there are any further issues, make sure that the KSB registry has been cleaned, and that no PII exists once the nodes are brought back online.
- Select the Administration tab and then choose Document Type
Known Issues: The refresh procedure also brings in all of the production parameters, such as email notification and Workflow application dochandler's. Some parameters will have to be changed by hand, and workflow application xml files may need to be re-ingested in order to fix the dochandler issues.
The following documents all of the processes that need to happen when we copy production data to
non-production environments. Some of these steps have not been implemented yet, or need decisions
made about implementation.
Scripts for Post-Processing to stop batch jobs from failing in UAT
For the receivingpaymentrequestjob :
UPDATE PUR_REQS_T SET REQS_STAT_CD='CANC' WHERE REQS_STAT_CD='ACMR'; commit;
For the PurchasingPreDisbusementExtractJob :
UPDATE AP_PMT_RQST_T SET PMT_EXTRT_DT='01-JAN-14' WHERE PMT_EXTRT_DT IS NULL; UPDATE AP_CRDT_MEMO_T SET CRDT_MEMO_EXTRT_DT='01-JAN-14' WHERE CRDT_MEMO_EXTRT_DT IS NULL; UPDATE FP_DV_DOC_T SET DV_EXTRT_DT='01-JAN-14' WHERE DV_EXTRT_DT IS NULL; commit;