JDBC Backing Store Setup : Updating an Existing Backing Store Schema

Updating an Existing Backing Store Schema
If you change the project ontology, that is, if you create, alter or delete a concept or an event, you must update the backing store schema so it matches the updated ontology. In the case of changes in project ontology, you must update the backing store schema before you deploy the updated project.
You may also wish to change which entities are excluded from the backing store using CDD settings (see Excluding Entities from the Backing Store for more information. This change does not require project redeployment. It requires that the updated CDD file is copied to all runtime machines.
Examine the alter script before you run it. The section What the Schema Update Utility Can and Can’t Handle Automatically provides more information.
Preparing to Run and Running the Schema Update Utility
1.
If you modified aliases when you created the schema, locate the yourname.aliases file you used. It will help you to modify those aliases in the newly generated file, so they match.
2.
Open the be-jdbcdeploy.tra file for editing and set the following properties:
be.jdbc.schemamigration.url=DbURL
be.jdbc.schemamigration.user=username
be.jdbc.schemamigration.pswd=password
These properties enable the program to compare the schema of the existing database with the ontology in the project EAR file, and generate the alter script.
3.
4.
Run the be-jdbcdeploy utility as explained in Task I, Generate the Project-Schema-Specific SQL Scripts, using the updated EAR file.
5.
If any of the new or changed definitions result in entries in the yourname.aliases file, and you want to change the provided aliases, follow instructions in Task J, Check the Aliases File and Modify Aliases as Desired. If you modify aliases, remember to generate the scripts again so the modified aliases are used.
You must use the same aliases that you used before. If any were modified when the schema was created, you must modify them the same way when updating the schema. It can be useful to refer to the original aliases file.
6.
Examine the generated yourname_alter.sql script and modify as needed so you only run statements for changes you want to make. See What the Schema Update Utility Can and Can’t Handle Automatically for details.
7.
Run the yourname_alter.sql script.
Your database tables are now configured for use.
What the Schema Update Utility Can and Can’t Handle Automatically
You must examine the alter script before you run it. Decide what changes to make manually and what changes to make using the script, taking into account the kind of data in the tables. Entries that could result in data loss are commented. Remove or comment entries for changes you will make manually.
Adds
The schema migration utility handles addition of entity types and attributes. New entity types and attributes are added to the database schema.
Changes (Drop and Add) — Assess individually
The utility handles changes to attributes (entity properties) as DROP and ADD operations. However, DROP operations are commented in the script to avoid data loss.
If a column is empty, or you don’t want to keep the data they contain, you can enable the DROP operation and let the utility handle the change.
If the column contains data that you want to keep, then make the change manually using an appropriate database tool. For example, you can change the data type of a column from string to double without loss of data, as long as all the column values are numeric values.
Entity Deletions
If an entity is deleted from the Studio project, the corresponding tables are not dropped from the database schema. Existing data is not lost. Deleted entities are not mentioned in the alter script. Manually keep track of and delete such tables as needed.
Attribute Deletions
The schema update utility does handle deletion of entity attributes. SQL statements for deleted attributes are generated but they are commented. Examine the alter script and enable these commands if you want to execute them. Note that existing data is lost when you drop an attribute.
Example Alter Script
Below is an example yourname_alter.sql script.

 
Property type change
-- ##### WARNING : Non-alterable Ontology changes found. Please see following errors. Manual schema-migration is required.
 
--* For Concept Concept1 field PROPERTY_1 type changed from VARCHAR2 to LONG
 
-- ALTER TABLE D_Concept1 DROP ( Property_1 );
 
ALTER TABLE D_Concept1 ADD ( Property_1 numeric(19) );
 
New table
DROP TABLE D_Book_rrf;
 
CREATE TABLE D_Book_rrf (pid numeric(19), propName char varying(255), id$ numeric(19) not null);
 
New property
-- ALTER TABLE D_MyConcept DROP ( FOLDER_1 );
 
ALTER TABLE D_MyConcept ADD ( Folder_0 char varying(255) );