Manually create the database tables

About this task

If you did not choose to create database tables during the Foundation installation, a database administrator can create these tables manually now. The dbverify utility generates SQL scripts to manage database table creation and modification. Depending on the task, you may apply all the dbverify-generated SQLs or specific SQLs. For example, when adding a column to a table, you need only to apply the relevant "Create Column" SQL.

When you run an installation in upgrade mode, a new entity XML repository is installed, but dbverify is not run. A database matching the old entity XML repository already exists. During the upgrade process, the initupgrade target compares the entity XML repositories from the old and new runtimes and generates alter scripts for shard migration. The alter scripts are applied to the database, which then reflects the new installation and the customer changes from the old runtime.

Sometimes, the alter scripts do not include all the necessary database changes. In these cases, dbverify is used to make corrections to the database.

You can run dbverify as follows:

Procedure

(Oracle only) If you are using the Oracle database, add an ORA_TS_CONTEXT entry to the <INSTALL_DIR>/properties/sandbox.cfg file. This entry determines the text search index type for Oracle.
Assign one of the following values to ORA_TS_CONTEXT:
- CONTEXT
  For fast retrieval of unstructured text.
- CTXCAT (default)
  For retrieval of structured text like numbers and dates.
(Oracle only) If you are using the Oracle database, add an ORACLE_NLS_LENGTH_SEMANTICS entry to the sandbox.cfg file in the properties subdirectory of your installation directory. This entry determines the type of length semantic to be used for Oracle database, when using the dbverify tool.
Assign one of the following values to ORACLE_NLS_LENGTH_SEMANTICS:
- CHAR
- BYTE (default)
Note: If the database or the specific session in which database was created has length semantic as CHAR, this property must be set to CHAR before running the dbverify tool.
You may want to prevent dbverify from generating "IndexAdds" SQLs for indexes that have been dropped from the database; for example, you dropped an index in an earlier release and are now using dbverify to make a correction to the database. To prevent dbverify from generating "IndexAdds" SQLs for indexes that have been dropped from the database, use a text editor to create the indexes_not_created.txt file and place the file in the <INSTALL_DIR>/extensions/schemagenerator/ directory. This is the default name and location of the index-creation-suppression file. When creating the file, ensure that you specify dropped indexes on separate lines of the file. If you want to use a different name and location for the file, provide the INDEXES_NOT_CREATED property in the <INSTALL_DIR>/properties/sandbox.cfg file and set the property's value to the full path where you want the index-creation-suppression file to be read from.
If you have a non-sharded deployment, skip this step and go to Step 5.
If you have a sharded deployment and are installing in upgrade mode, run the dbverify script from the <INSTALL_DIR>/bin/ folder and pass the multischema.xml file, which specifies database information and sets up sharded table types:
```
   
dbverify.sh -colonyxml <INSTALL_DIR>/multischema.xml (on UNIX
and Linux) 
```
or
```
dbverify.cmd -colonyxml <INSTALL_DIR>/multischema.xml (on
Windows)
   
```
If you run the dbverify command without passing the multischema.xml file, it runs dbverify for all colonies.

Note: By default, the multischema.applyddl property is set to true, which enables the dbverify script to run the associated scripts automatically. If you set it to false, the dbverify script generates DDLs but does not apply them. In this case, you must run these scripts manually.

To run dbverify on a specific colony, pass the Colony ID in the command line as follows:
```
   dbverify.sh -ColonyId <Colony_Id> (on UNIX and Linux) 
```
or
```
   dbverify.cmd -ColonyId <Colony_Id> (on Windows) 
```
If you are installing in nonsharded mode, run the dbverify script from the <INSTALL_DIR>/bin/ folder as follows:
```
   dbverify.sh (on UNIX and Linux) 
```
or
```
   dbverify.cmd (on Windows)
   
```
If you have enabled the text search feature and change the text search index type in Oracle from ctxcat to context or vice-versa, the updated create and drop SQL scripts can be found in the <INSTALL_DIR>/bin/EFrame_TextIndexUpdates.sql file.
In sharded deployments, the updated create and drop SQL scripts can be found in the <INSTALL_DIR>/bin/EFrame_<Pool_Id>_<TableType>.sql file, where:
- <Pool_Id> to the name of the jdbc pool for which the sql scripts should be run as specified in the jdbc.properties or multischema.xml file.
- <FileType> refers to one of a set of hardcoded names that are used to contains different types of sql statements. Possible values include:
  - UpdateQueries
  - TableChanges
  - Sequence
  - IndexDrops
  - IndexAdds
  - TextIndexDrops
  - TextIndexAdds
  - TextIndexUpdates
  - TextIndexModify
  For example, the script for table alterations for the metadata pool for an Oracle install would be called: EFrame_oraclePool_TableChanges.sql

The differences between the entity XMLs and the database are generated in the form of SQL scripts, which can be run against the database to rectify the differences.

For example, if there is a mismatch in the size of a datatype for a column [varchar2(20) to varchar2(40)] that has an associated index, dbverify generates SQL statements for:

Dropping the Index
Changing the size of the datatype for the column
Creating the new Index

The three SQL statements described in the previous list appear in different *.sql files. The appropriate *.sql files must be run in the proper order as follows:

Run the <INSTALL_DIR>/repository/scripts/EFrame_IndexDrops.sql for dropping the index.
Run the <INSTALL_DIR>/repository/scripts/EFrame_TableChanges.sql for altering the size of the datatype for a column.
Run the <INSTALL_DIR>/repository/scripts/EFrame_IndexAdds.sql for creating a new index.

If the SQL statements are not run in the sequence as mentioned above, it results in script failure.

The scripts shown in Table 1 are generated.

Note: All scripts listed below can be found in the <INSTALL_DIR>/bin directory.

Table 1. Generated scripts
Non-sharded Script Name	Sharded Script Name	Description of the script
`EFrame_Sequence.sql`	`EFrame_<Pool_Id>_<Tab leType>_Sequence.sql`	Contains all the additional sequences that need to be created.
`EFrame_TableChanges.s ql`	`EFrame_<Pool_Id>_<Tab leType>_TableChanges. sql`	Contains all the table column differences that need to be applied on the database shard. Modify this file to reference your tablespaces.
`EFrame_Drops.lst`	`EFrame_<Pool_Id>_<Tab leType>_Drops.lst`	This file contains sample and/or informational changes that are not applied to the database by the entity deployer because they could cause data loss. Review the file thoroughly and take action on the entries as necessary for your environment. Note: Sterling™ Order Management System Software does not provide a .sql file for removing tables from the database but does provide the drop statements in the .lst file. If you want to drop these tables, you must do it manually. Review the entries in the file carefully before taking action.
`EFrame_IndexAdds.sql`	`EFrame_<Pool_Id>_<Tab leType>_IndexAdds.sql`	Adds all of the indexes that need to be created in the database. Modify this file to reference your tablespaces.
`EFrame_IndexDrops.sql`	`EFrame_<Pool_Id>_<Tab leType>_IndexDrops.sq l`	Removes any extra indexes in the database.
`EFrame_TextIndexAdds. sql`	`EFrame_<Pool_Id>_<Tab leType>_TextIndexAdds .sql`	Adds new text search indexes that need to be created in the database.
`EFrame_TextIndexDrops .sql`	`EFrame_<Pool_Id>_<Tab leType>_TextIndexDrop s.sql`	Removes text search indexes from the database.
`EFrame_TextIndexModif y.sql`	`EFrame_<Pool_Id>_<Tab leType>_TextIndexModi fy.sql`	Updates the text search indexes in the database.
`EFrame_TextIndexUpdat es.sql`	`EFrame_<Pool_Id>_<Tab leType>_TextIndexUpda tes.sql`	When executed, updates the content of the text indexes.
`EFrame_UpdateQueries. sql`	`EFrame_<Pool_Id>_<Tab leType>_UpdateQueries .sql`	For upgrades, updates the table column values in order to apply other changes made to the columns. For example, if a table column is changed from nullable to not nullable in the installation of a previous release, the column values must be updated before the column can be made not null in the current release because the column default values for the current release may contain null values.

Note: In nonsharded deployments, the <INSTALL_DIR>/bin/EFrame_Drops.lst indicates extra objects in the database. In sharded deployments, this file name is <INSTALL_DIR>/bin/EFrame_<Pool_Id>_<TableType>_Drops.lst.

These extra objects could be custom objects or objects that are dropped as the result of a shard change or an upgrade. Please look through this script carefully.

This script may also contain reduced columns. These are columns that were changed to have a smaller size in the newer version. These changes are suppressed because:

Not all databases will allow you to apply the changes.
Databases that do allow you to apply the changes can behave unpredictably if the table already contains values that are longer than the new length.

Run the scripts specified for your database type, as shown in the following topics. You must run these scripts only if you are manually creating the views after installation (REINIT_DB=no). In the normal installation mode (REINIT_DB=yes), the views will be applied automatically.

Example

As a more expanded example, you could run the following scripts in this order after installation:

EFrame_TextIndexDrops.sql
EFrame_IndexDrops.sql
EFrame_TableChanges.sql
EFrame_IndexAdds.sql
EFrame_TextIndexAdds.sql
EFrame_Sequence.sql
EFrame_TextIndexModify.sql
EFrame_TextIndexUpdates.sql
EFrame_TextIndexUpgrade.sql
EFrame_Static.sql

To run these using the db2 command line:

Connect to the database by entering:db2 "connect to <SERVER> user <USER> using <PASSWORD>"
Then for each file: db2 -v -f <SQLFILE>
Then enter: db2 "connect reset"