Aperi Database R2 Extensibility

How do I coordinate a database schema change?

This section assumes that the person is familiar with the Database Component Design (MS Word).

When some database change is needed, the team member responsible for the work item (referred as the requester) will analyze the application requirements, draft the database changes on schema, helper, mapper, detectability with the help of the entire design team The requester should analyze the impact of the changes on the whole of Aperi.

The change request will be reviewed and approved by a deadline, by the design team (led by the database team lead). If needed, the committee may request consultation from special expertise area.

The requester will be responsible for the implementation of the change, and negotiate any resource issue if there is a need. The requester should test the new schema file(s) by using them to replace the corresponding files in a proper version of Aperi. The requester should make an overall build with all the changes to ensure nothing breaks. The requester will send out a note to the aperi-dev mailing list highlighting the change.

How do I analyze the impact of a database schema change?

The following changes usually have no impact on Aperi:

1. Add a table, view, column, index (including primary key). An important thing to note is that a new name might conflict with existing ones (See the Helper documentation in the Database Component Design (MS Word) for more details). For example, the table name T_TSM_SERVER conflicts with T_RES_SERVER.

The following changes might have an impact on Aperi:

1. Delete a table, view, column
2. Change the data type of a column
3. Change the encoding of data
4. Delete a primary key, index
5. Change table data population

Changes to the data type of a column are not recommended.

It may be necessary to regenerate a dependent view if a table definition is changed.

Which schema file should I change?

First, we need an explanation of the Aperi schema versioning method.

How do I change the schema file?

When adding a table definition to a schema file or making a change, please keep the following in mind:

1. Vendor-neutral SQL files used in the install
2. Each SQL statement terminated by a ; on a new line.
3. Comments starts with #
4. Data types represented in a vendor-neutral fashion. While CHAR and VARCHAR are the same for all vendors, we use vendor-neutral notations for most others. In the case of DB2, {0} = integer, {1} = smallint, {3} = timestamp -- see the top of the schema file for details.
5. Please add comments for column definitions
6. Update foreign key constraints in the file but leave it commented as we do not support foreign keys yet. However, the information is necessary to generate the ER diagram for visualization.
7. Do not specify schema name
8. For each line not ending with a separator, add a blank since the installer removes newlines
9. Append the schema change to the change log as seen below:

# Change Log:
# Date            Bug       Author          Description
# -------------------------------------------------------------------------------------------------
# Mar 20, 2006    28087     mlamb   	Change the default Out Of Band Fabric discovery time
…..
# May 12, 2006    28594     fiebrich      	 update T_RES_CONFIG_DATA with extended indication filter list
# May 15, 2006    28697     hwassman      Add the view T_VIEW_COMPUTER_ATTRS
# May 16, 2006    28728     ashmic	  Modified T_VIEW_COMPUTER_ATTRS: 
#			  	 Added " " (space) between NODE_HOSTED_ID and FROM

Below is an example of a sample table definition to be added to a schema file.

###############################################################
## TABLE: T_RES_SWITCH_ATTRIBUTE_SNAPSHOT
## Entity representing historical snapshot for T_RES_SWITCH_ATTRIBUTE
## FOREIGN KEYS: SWITCH_WWN
###############################################################
CREATE TABLE T_RES_SWITCH_ATTRIBUTE_SNAPSHOT
(SWITCH_WWN          CHAR(16) NOT NULL, # WWN of switch
 VERSION             VARCHAR(255),      # Version of the switch
 DETECTABLE          {1},               # Switch detectable or not
 OPERATIONAL_STATUS  {0}) {121}         # Switch operational status
; 

ALTER TABLE T_RES_SWITCH_ATTRIBUTE_SNAPSHOT ADD PRIMARY KEY (SWITCH_WWN)
;

# ALTER TABLE T_RES_SWITCH_ATTRIBUTE_SNAPSHOT ADD FOREIGN KEY
# FK_SWITCH_WWN FOREIGN KEY (SWITCH_WWN) REFERENCES T_RES_SWITCH 
# (SWITCH_WWN)