Notes about modifying/adding LDAS database tables

Preamble

Most of the LDAS database tables were originally designed a few years based on guesses about what would be needed. It is entirely appropriate to change them to better serve the data analyses actually being done. However, this requires coordination between several different parties. This page will try to cover some of the relevant issues.

Coordination

A new or modified table might require changes to all of the following:

The DB2 database servers running in all LDAS installations
The LDAS software which handles database input/output
Software in LAL which defines structures for database table entries
The 'guild' user interface in LIGOtools

The DB2 database servers can be modified "on the fly" by any of several people who are familiar with basic database administration. However, in general, we try not to modify LDAS software installations except when a new release is issued. Since LDAS has copies of the table definitions within its own software base, and requires these to match the definitions on the database servers, the consequence is that generally, database table definitions should be changed at the same time that a new release of LDAS is installed. The same constraint tends to be true for LAL, but it depends on whether that particular table is represented by a structure in LAL source code.

Modifying an existing table

Some modifications can be made to an existing table without disturbing the data which has already been inserted into it (if any). These include:

Add a column (with any data type)
Change the maximum length of a variable-length character string
Create or delete indexes

Other modifications cannot be made without deleting and regenerating a table. These include:

Delete a column
Change the name of a column
Change the data type of a column
Change the length of a fixed-length character string

If one of these modifications needs to be made and there is valuable data in the table, it may be possible to extract it, delete and regenerate the table, and then insert the data back into the modified table; however, this tends to be tricky.

Creating a new table

If no existing database table is suitable for your needs, even with minor modifications, then it makes sense to create a new database table from scratch. See other table definitions in the LDAS CVS repository for examples. Table definition files are not case sensitive, but we tend to type SQL statements in uppercase for readability. We generally stick to the following data types for the columns in a table (although DB2 supports some other types):

INTEGER (4-byte)
REAL (4-byte)
DOUBLE (8-byte)
CHAR(n) (fixed-length string, length = n characters)
VARCHAR(n) (variable-length string, maximum length = n characters)
VARCHAR(n) FOR BIT DATA (variable-length string to store binary data, i.e. arbitrary bytes)
BLOB ("Binary Large OBject" to store arbitrary bytes)

One of the unfortunate limitations of DB2 is that there is no convenient way to store an array of values. The numeric data types, e.g. INTEGER, REAL, and DOUBLE, are all scalars. A smallish numeric array (perhaps up to a few hundred elements) can be stored in a VARCHAR FOR BIT DATA column; a larger array can be stored in a BLOB. In either case, the data representation is just an array of bytes as far as DB2 is concerned, so it's up to you to know how to interpret them as 4-byte real numbers or whatever. This opens up issues of byte ordering. Some existing LDAS database tables have a "mimetype" column to indicate the byte ordering of the data in a BLOB.