...

• The NEW_RESULT table typically contains 20-30 million rows. This update takes the longest.
• The NEW_TEST and NEW_SAMPLE table are much faster due to being 3 orders of magnitude smaller.
• Timing is turned on--the scripts will show how long they took to run.

The result table took 2-5 minutes to complete (with indexes in place) for each of ORTLIMS, LIMS19, RTLIMS, and LIMSHQ.

crt_user_exp.sql

Review the script.

• Make a copy of it specifically for the expedition you are processing--e.g. crt_user_398.sql
• Modify the user credential to reflect EPM requirementsCOMPLEX_PASSWORD requirements. If the EPM has provided a credential set, use that.

Run the script via SQL.
Provide an explicit path to the script to run it.
Pass the expedition as a parameter to the script. The pattern to follow based on expedition 398 activity

sql / as sysdba
SQL>@/backup/scripts/rci_work/exp/crt_user_398.sql 398

Note that this script may be run at any time. It is free of any dependencies on other scripts here.

This credential needs to be distributed to the following email distributions for filing: EPM for the expedition in processing, it_ops@iodp.tamu.edu, programmers@iodp.tamu.edu, database@iodp.tamu.edu (Data Librarian), webmaster@iodp.tamu.edu

4. Copy the content

Into the publicly accessible LIMS. - DBA

...

Run this script to generate a pair of scripts that respectively enable and disable triggers.

Future: Remove this script by applying the use of "execute immediate" support for disabling respective triggers automatically in the TRANSFER.EOX_SHORE_MERGE_TRANSFER_NEW_TO_LIMS() script--around LIMS.SAMPLE, LIMS.TEST, LIMS.RESULT, LIMS.GEOD_DATA, LIMS.GEOD_METADATA.

dis_trig_all_<yyyymmdd_hhmmss>.sql

...

sql / as sysdba
SQL>
call transfer.eox_shore_merge_transfer_new_to_lims()

Noted

• Without indexesa unique result_number index, the bulk merging of the NEW_RESULT content took upwards of 6 hours.
• With a unique index on NEW_RESULT.RESULT_NUMBER the bulk merge was observed to complete in 30 minutes.(once) 30 minutes, but more typically in 4 hours.
• Although the data is available, the script does not provide for the merge of DESCINFO2 data--system is no longer used, so omitting now.

Future: The scripts are constructed around MERGE. Test in LIMS19 or RTLIMS to see if this technique is more performant for the bulk data moves. Since RESULT is the largest table with the most indexes we can gain the most improvement in execution times by focussing on this table.

execute immediate "alter table lims.result nologging;"
insert /*+append*/ lims.result
select * from transfer.new_result where result_number in
(select result_number from transfer.new_result
 minus
 select result_number from lims.result
);
execute immediate "alter table lims.result logging;"
commit;

grp_cnt_lims.ksh

Re-run this script for review purposes. Data formerly only in the TRANSFER schema should now be present in the same numbers in the LIMS schema.

...