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
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.
run an installation in upgrade mode, a new entity XML repository is
dbverify is not run. A database matching
the old entity XML repository already exists. During the upgrade process,
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
Sometimes, the alter scripts do not include all the
necessary database changes. In these cases,
used to make corrections to the database.
You can run
- (Oracle only) If you are using the Oracle
database, add an ORA_TS_CONTEXT entry to the
<INSTALL_DIR>/properties/sandbox.cfgfile. This entry determines the text search index type for Oracle.
Assign one of the following values to ORA_TS_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:
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.
- BYTE (default)
- 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.cfgfile 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
dbverifyscript from the
<INSTALL_DIR>/bin/folder and pass the
multischema.xmlfile, which specifies database information and sets up sharded table types:
dbverify.sh -colonyxml <INSTALL_DIR>/multischema.xml (on UNIX and Linux)
dbverify.cmd -colonyxml <INSTALL_DIR>/multischema.xml (on Windows)
If you run the
dbverifycommand without passing the
multischema.xmlfile, it runs dbverify for all colonies.Note: By default, the
multischema.applyddlproperty is set to true, which enables the
dbverifyscript to run the associated scripts automatically. If you set it to false, the
dbverifyscript 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)
dbverify.cmd -ColonyId <Colony_Id> (on Windows)
- If you are installing in nonsharded mode,
dbverifyscript from the
<INSTALL_DIR>/bin/folder as follows:
dbverify.sh (on UNIX and Linux)
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 sharded deployments, the updated create and drop SQL scripts can be found in the
- <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:
For example, the script for table alterations for the metadata pool for an Oracle install would be called:
- 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
*.sqlfiles. The appropriate
*.sqlfiles must be run in the proper order as follows:
- Run the
<INSTALL_DIR>/repository/scripts/EFrame_IndexDrops.sqlfor dropping the index.
- Run the
<INSTALL_DIR>/repository/scripts/EFrame_TableChanges.sqlfor altering the size of the datatype for a column.
- Run the
<INSTALL_DIR>/repository/scripts/EFrame_IndexAdds.sqlfor 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
Table 1. Generated scripts Non-sharded Script Name Sharded Script Name Description of the script
Contains all the additional sequences that need to be created.
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.
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.
Adds all of the indexes that need to be created in the database. Modify this file to reference your tablespaces.
EFrame_<Pool_Id>_<Tab leType>_IndexDrops.sq l
Removes any extra indexes in the database.
EFrame_<Pool_Id>_<Tab leType>_TextIndexAdds .sql
Adds new text search indexes that need to be created in the database.
EFrame_<Pool_Id>_<Tab leType>_TextIndexDrop s.sql
Removes text search indexes from the database.
EFrame_<Pool_Id>_<Tab leType>_TextIndexModi fy.sql
Updates the text search indexes in the database.
EFrame_<Pool_Id>_<Tab leType>_TextIndexUpda tes.sql
When executed, updates the content of the text indexes.
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.lstindicates extra objects in the database. In sharded deployments, this file name is
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.
- 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"