Documentation Links	About EPIC	EPIC netCDF Conventions	EPIC Gallery
EPIC Database	About EPIC Key File	Write EPIC Data Files	References

EPIC Database Management

Overview of local PMEL EPIC INGRES database structure on unix

I. Overview

There are 4 types of INGRES users:

• Operating System Administrator (OSA)
  who sets up the operating system environment in which INGRES is installed. This person should be root.

• INGRES System Administrator (ISA)
  who must login as ingres and is owner of the ingres account, which provides permissions in the INGRES environment that are needed to install and maintain INGRES. The ISA has the following responsibilities:
  • Authoring INGRES users to acces INGRES
  • Install INGRES files
  • Define INGRES environment variables in the symbol table
  • Start, stop, configure, and monitor server(s)
  • Desconnect or suspend a session connected to a server
  • Shut down the INGRES installation or parts of it

• Database Administrator (DBA)
  Anyone who creates a database becomes the DBA for that database.

• End user
  Anyone who uses INGRES.

II. PMEL INGRES/EPIC Database Users' Privilege

The PMEL INGRES for UNIX has license on corona. The Operating System Administrator is the root of corona and INGRES System Administrator is ingres. Both are operated by PMEL Computering and Network Services Division and they (Robert Deloura) must be contacted if there are any management problems regarding these levels.

At present, there is only one database --epic-- created. The DBA of epic database is user "epic". The user "epic" is also granted INGRES superuser permission by ISA. This allows "epic" to perform many INGRES tasks, especially those necessary for installing tna maintaining the INGRES installations.

The following three user permission levels have been set for epic database:

• DBA level (user "epic"):
  Creates epic database (the owner), creates tables and setup table access permissions.

• Expert user level:
  Load data into the tables. That is, write and delete data (including delete data entered by other expert users). This user must have an account on corona since the loading program must be run on corona and must be the INGRES USER. Only the INGRES superuser can set the access permission to have someone become a INGRES USER. (We will discuss about how to set the INGRES access permission in the later section.)

• General database user level:
  Read data from the database. This user can be anyone who is on a networked machine. The data selection program is preformed by Remote Procedure Calls from a remote machine. Therefore, there is no INGRES access permission requirement for such a user on corona.

  NOTE: The current epicselect script is checking whether a user is using the data select program from a remote machine or from the local machine (corona); then it will initiate RPC version for a remote machine user or a local version for local machine user. The local version of data select program requires that the user must have INGRES epic database read permission.

III. INGRES/EPIC Database Internal Table Structure

There are 3 kind of tables in the INGRES epic database:

• Data tables
• EPIC information table
• TABLE information table

All these tables must be created by user "epic" (DBA) first.

1. Data tables

The data tables contain data information. The name of data table is formed by

      DB_DTYPE_SUBTYPE

where DB is a database name in user's terminology, which indicates the data from which region or from which project; the DTYPE is data file type, i.e. "prof" for profile data, "time" for time series data, or "track" for track data; the SUBTYPE is data type, e.g. CTD, BOT, XTB, PRE, U, TMP, etc.

2. EPIC Information table

There is one information table for the whole epic database. The information will be entered only by user "epic". The name of information table is epic_info. This table has following data fields:

ID	Name	Description	Comments

The ID is an interger from 0 to 3:

   0 -- the entry contains comments

   1 -- the entry contains DB information

   2 -- the entry contains DTYPE information

   3 -- the entry contains data SUBTYPE information

The NAME field contains short string used in naming the data table. The DESCRIPTION field contains long name describes the short name. The COMMENTS contains comments.

3. TABLE information table

The name of this table is tableinfo. This table has following two fields:

Tabele Name	Load Flag

IV. INGRES/EPIC Database Table Creation

Tables in the EPIC database must be created and granted access permissiont by user "epic". C-shell scripts are available for create epic tables and perform other proper procudure (see README.util). Whenever a new table is created, information must be entered to the table epic_info. (You must enter manually since no script is available to do that yet.) Here is an example of how to create a table for DB "equator", DTYPE "prof" and SUBTYPE "ctd":

=======================
Create Meta Data Table
=======================

step 1: login as "epic" on corona

step 2:

change directory to ingres-table-log/$USER so all the log files will be generated in that directory. For example, user "willa" login as "epic" to create table, then she should use ~epic/ingres-table-log/willa directory. If you don't find your directory there, create one.

step 3: run create_tbl.ingres script. For exampl:

This will do these things for you:

Note:

a) If you are granted with write permission, please be careful when using these commands: "drop", "delete", "remove" or and scripts that use above commands. If you have any questions please refer to documentations or consult with other Ingres "expert".

b) We suggest you to load data as yourself instead of as "epic" if you are granted the write permission.

step 4: You are done. logout as "epic".

===========================
Remove EPIC Meta Data Table
===========================

You can use following script (remove_tbl.ingres) to remove the EPIC meta data table from Ingres/EPIC database. You must be user "epic" in order to do that. By running this script, the whole table will be removed and you will have to re-create them again when needed. If you like to just clear the all the entries of a table, not removing the whole table, please refer to the next section "Clear EPIC Meta Data Table".

For example:

The script will give you warning and make sure you want to remove the table. If you still want to proceed, it will drop the table from EPIC database and remove table entry from tableinfo table.

==============================
Clean EPIC Meta Data Table
==============================

You can use following script (clean_tbl.ingres) to clean the EPIC meta data table. You must be granted with write permission to that table in order to clean a table. That is, you want to empty the table but not remove the table from the database.

For example:

The script will give you warning and make sure you want to empty the table. If you still want to proceed, it will delete all the entries in that table.

V. INGRES/EPIC Database Loader Programs

There are two EPIC database loader programs -- one for loading profile data files, and other for loading time series data files. These programs are located at /home/corona/ingres/bin directory. The loader programs can only be run by authorized person who has been granted write permission to INGRES epic database.

1. epicload

   It is a C-shell script. User should start this script no matter what type of data files are going to be loaded. It sets the enviornment variable EPIC_IGNRES_HOME then prompt user to specify the DTYPE. It will initiates the correspoinding loader program (loadprof for profile DTYPE, loadts for time series DTYPE). The loader programs get information from the current INGRES epic database (intermediate files are generated at user's current directory and deleted when the program is done), then prompt user to select one "DB" and one "SUBTYPE" for loading data into DB_DTYPE_SUBTYPE table.

   When it is done, the user's loading record will be logged into log file. (This file is located at EPIC_INGRES_HOME/log. The current EPIC_INGRES_HOME is /opt/src/epic.

   Note: It is user's responsibility to provide correct DB and SUBTYPE so the data will be loaded into the right table. The loader programs only check the correct DTYPE but are not able to check the DB and SUBTYPE.

2. loadprof and loadts

   The loadprof loads the profile data files and loadts loads time series data files. You normally initiate loader programs by running "loadepic" but you can run them stand-along by setting following environment variables manuall first:

   	setenv EPICHOME /opt/src/epic
   
   	setenv EPICLOAD_LOG $EPICHOME/log
   

   So update_log.sh and epicload.log can be found from $EPICLOAD_LOG directory.

3. epicunload

   It unloads data files from the database. You must first check the loader log (/opt/src/epic/log/ingres-epic.log) file for the table name and pointer file name that was used to load the data file. This program will prompt you to enter table name and pointer file name. The data files listed in the pointer file must be accessable when running this program since epopen is used to open pointer file (error will occur if data files cannot be found.)