BOX=beginning of expedition
EOX=end of expedition
ETL=etract, transfer, load

Overview

This document reviews scripts found in all IODP data TRANSFER schemas. An overview of DBA scripts found on the OEM (Oracle Enterprise Manager) NSF shares is also provided--these scripts are used to perform a variety of bulk database management tasks.

TRANSFER schema procedures

These scripts assist in automating shipboard beginning of expedition (BOX) database preparations.

BOX_ASMAN_CATALOG_DELETE
BOX_LIME_AUDIT_DELETE
BOX_LIMS_CLEANUP
BOX_LIMS_DEFRAG
BOX_LIMS_EXPEDITION_DELETE
BOX_SM_DELETE_REQUEST_CODES

One of the shipboard systems (moisture and density) requires data for pre-weighed containers. This script aids in loading content from an Excel file into the shipboard LIMS.

CL

History. These scripts were once routinely applied by shipboard developers to prepare a copy of expedition data for transport to shore via the TRANSFER schema. These EOX activities have been dropped.

The scripts are retained as they provide a semi-automated means for aggregating an expedition's data for transport into a test environment.

COPY_ASMAN_CATALOG_TO_NEW
COPY_DESCINFO_TO_NEW
COPY_LIMS_EXPEDITION_TO_NEW
COPY_MERGE_NEW_TO_LIMS

These scripts support the computation of summary data presented by the database OVERVIEW application. Each script summarizes a different kind of data.

DBO_COMPUTE_1001
DBO_COMPUTE_1011
DBO_COMPUTE_1021
DBO_COMPUTE_1041
DBO_COMPUTE_1051
DBO_COMPUTE_1061
DBO_COMPUTE_1071
DBO_COMPUTE_1081
DBO_COMPUTE_1091
DBO_COMPUTE_1101
DBO_COMPUTE_1111
DBO_COMPUTE_1121
DBO_COMPUTE_1131
DBO_COMPUTE_1141
DBO_COMPUTE_1151
DBO_COMPUTE_1161
DBO_COMPUTE_1171
DBO_COMPUTE_1181
DBO_COMPUTE_1191
DBO_COMPUTE_1201
DBO_COMPUTE_1211
DBO_COMPUTE_1221
DBO_COMPUTE_1225
DBO_COMPUTE_1231
DBO_COMPUTE_1241
DBO_COMPUTE_1251
DBO_COMPUTE_1255
DBO_COMPUTE_1261
DBO_COMPUTE_1271
DBO_COMPUTE_1281
DBO_COMPUTE_1291
DBO_COMPUTE_1301
DBO_COMPUTE_1311
DBO_COMPUTE_1321
DBO_COMPUTE_ALL
DBO_COMPUTE_EXP

These scripts automate occasional data cleaning activities.

UTIL_CLEANOUT_TRANSFER_TABLES
UTIL_LIMS_TEST_RESULT_DELETE

Database management scripts

The OEM (Oracle Enterprise Manager) hosts on shore and ship host an NSF mount point used by each ODA (Oracle Data Appliance). Scripts used for a variety of database management purposes may be found here: /backup/scripts, /backup/scripts/rci_work

rci_work/exp

The collection of scripts in this location is used to automate the shore components of EOX processing: retrieve last expedition content from the database dump, create and apply moratorium supports, merge the data into production.

These are the scripts (in-order) that are typically used

# Shore EOX processing for a data load
# per Sunil Armarni from a Slack thread.
export EOX=#
chk_eox_count.ksh
select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TRANSFER'
import_transfer.ksh
transfer_upd_stat.ksh
data_pre_01_crt_unq.ksh
data_pre_01_chk_dup.ksh
eox_gen_in_moratorium_script.ksh <GROUP>
in_moratorium_<GROUP>.ksh
crt_user_exp.ksh $EOX
chk_tablespace_free.ksh
grp_cnt_transfer.ksh
data_pre_03_dis_trig.ksh
dis_trig_all_<YYYMMDD_HHMMSS>.sql
chk_eox_count.ksh exp#
exec transfer.lims_expedition_delete('#')
chk_eox_count.ksh
grp_cnt_lims.ksh
data_pre_05_without_DI_v6.ksh # calls scors also
grp_cnt_lims.ksh
chk_scale_data.ksh
data_pre_05_scale_data.ksh
chk_scale_data.ksh
enb_trig_all_<YYYMMDD_HHMMSS>.sql
merge_descinfo2_data_v2.ksh
insert_dbo_#.sql - N/A
dbo_compute_v4.sql exp#
data_post_06_upd_stat.ksh