CREATE TABLE process
(
-- This table contains information about a specific invocation of a program.

-- Database which created this entry
      creator_db         INTEGER NOT NULL WITH DEFAULT 1,

-- BASIC INFORMATION ABOUT THE PROGRAM
-- Program name
      program            CHAR(16) NOT NULL,
-- Version of the program
      version            VARCHAR(64),
-- Where the program is stored in the cvs repository
      cvs_repository     VARCHAR(256),
-- Time when the program was entered into the cvs repository (GPS seconds)
      cvs_entry_time     INTEGER,
-- User comment which describes the program
      comment            VARCHAR(240),

-- INFORMATION ABOUT THIS INVOCATION OF THE PROGRAM
-- Flag to indicate whether it was run on-line (1) or off-line (0)
      is_online          INTEGER NOT NULL WITH DEFAULT 0,
-- Node on which it was run
      node               VARCHAR(64) NOT NULL,
-- Unix username
      username           CHAR(16) NOT NULL,
-- Unix process ID
      unix_procid        INTEGER NOT NULL,
-- Start time (GPS seconds)
      start_time         INTEGER NOT NULL,
-- End time (GPS seconds); not filled initially, but filled when process
-- exits gracefully
      end_time           INTEGER,
-- LDAS job ID (numeric part only)
      jobid              INTEGER NOT NULL WITH DEFAULT 0,
-- LDAS site name (e.g. 'LHO', 'LLO', 'CIT', 'Dev', 'Test')
      domain	         VARCHAR(64) NOT NULL WITH DEFAULT '',

-- Unique identifier to this program, generated by DB2 (not unix process ID)
      process_id         CHAR(13) FOR BIT DATA NOT NULL,
-- Parameter set identifier.  Permits an association between multiple
-- invocations of a program which use the same set of input parameters.
-- Probably not filled initially (because of timing issues if multiple
-- processes start at about the same time), but updated later.
      param_set          INTEGER,

-- INFORMATION ABOUT THE DATA HANDLED BY THIS PROGRAM
-- Interferometer(s) from which data comes.  In general, a process
-- knows in advance what interferometer's data it is analyzing.
-- (This is necessary to retrieve frameset metadata, for instance.)
-- Make this variable long enough to indicate multiple interferometers.
-- (e.g. "H1H2L1V1")
      ifos               CHAR(12),
      
-- Insertion time (automatically assigned by the database)
      insertion_time     TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,

      CONSTRAINT process_pk
      PRIMARY KEY (program, start_time, node, unix_procid, jobid, domain),

-- Create a "secondary key" (my term; to DB2 it's simply a unique index)
-- that other tables can use for a foreign key.
      CONSTRAINT process_sk
      UNIQUE (process_id, creator_db)
)
-- The following line is needed for this table to be replicated to other sites
DATA CAPTURE CHANGES
;
-- Create an index based on start_time
CREATE INDEX process_ind_time ON process(start_time)
;
-- Create an index based on job ID
CREATE INDEX process_ind_jobid ON process(jobid, domain)
;