This section outlines how to create a clean database that will contain metadata for NexJ CRM. Use NexJ Studio to configure connections to the database, as well as to create and populate the database.
To create tables, drop tables, and insert data into tables, you need the appropriate database role. In order to use NexJ Studio Data Load Tool commands recreate, recreateschema, and insert, you need create and drop permissions for database objects; security granting permissions to update the permissions on those newly created objects; and read/write access to the newly created objects.
If you cannot perform these tasks against the database, you must generate SQL scripts using the Database Schema Tool and have a user with the appropriate permissions execute the scripts.
If the initial database creation SQL scripts are provided to you by NexJ, they can be customized to meet naming conventions and standards. Work with your NexJ representative to ensure you are modifying the scripts correctly. Otherwise, you can generate these scripts in NexJ Studio by using the setup command in the Database Schema Tool.
To configure a database environment for your NexJ application, you need to create the default relational database and create data source connections. After configuring the default relational database, you create the database schema for the data sources and insert data into them.
The general steps to configuring a database environment are as follows:
Prepare a script for which will create the required database on the database server.
If the <dbtype>_create_database.sql file (where <dbtype> is the type of the database server) was provided to you by NexJ, edit this file to make it applicable to your specific deployment. Configure the database name, user name, file location, database names, tables, and file names. In NexJ Studio, use the setup command in the Database Schema Tool to generate a <dataSourceConnection>_setup.sql setup script, where <dataSourceConnection> is the data source listed in the connections file for the model. You can generate a script for each required data source. See Generating database schema SQL.
Info
When generating the script using the setup command, ensure that the Test option in the Server and Connections section of the Database Schema Tool dialog is not enabled.
Otherwise, the script will additionally configure the user generating the script as the db_owner role and give this user too many privileges for a production environment.
When generating the script using the setup command, in the VM Arguments section of the Database Schema Tool dialog, ensure that the directories specified for the following parameters exist on the machine where the script will be run:
datapath
indexpath
longpath
undopath
Run the script against the database. This script creates the new default relational database that stores the NexJ metadata information. The setup script also generates a database role or group of njdbuser. If you wish to alter this name, work with your NexJ representative to have it changed.
Note
Any defined file name paths must exist prior to executing the script. If any paths do not exist, create them. Otherwise, the script will fail.
Add the required channel connections and configure each channel as required. A list of required channels with sample configurations can be found here: Channel connection settings.
Add the required SOA connections and configure each channel as required. A list of required SOA connections with sample configurations can be found here: SOA connection settings.
Create the schema and data in the database for the added data sources.
Use the Data Load Tool recreate command to both recreate the database schema and populate the tables, optionally from a dump file. You must have the appropriate permission to drop and create tables in the database. See Recreating databases for information on using the Data Load Tool.
Use the Database Schema Tool create command to generate scripts for creating the database schema and the insert command to generate scripts to populate the tables using system default data or data from a dump file. Separate scripts must be created for each data source. See Creating database tables and Generating the initial database data for deployment for information on generating the appropriate scripts.
The database environment has now been configured.
Each database server also has additional settings and best practices that can be configured. See the corresponding list for your database server.
Microsoft SQL Server best practices
The vast majority of configuration settings for Microsoft SQL Server and your databases will remain unchanged. However, you may want to adhere to the following suggestions for best performance.
In order to interact with the database, NexJ CRM uses a customized version of the open source jTDS driver. The current version of the driver is jtds-1.2.2-9.jar. The driver file is included with the NexJ Studio plugin, in the <NEXJ_PLUGIN>\ext folder.
To configure your application server to interact with the database, install the jTDS driver into your application server.
The following settings are required for a production environment:
Enable TCP/IP connections.
Set a non-zero value for TCP Port (the default value is 1433).
Disable dynamic ports to prevent a new port number to be selected upon server startup. If enabled, any external incoming connections to the server requiring a fixed port number will be lost.
In a production environment, database users should only be members of the njdbuser database role, which provides create, read, update and delete (CRUD) access to the data contained in NexJ tables. No other database roles are required.
The following settings are recommended for a production environment:
For enhanced security, use SQL Server and Windows Authentication mode, or a mixed authentication mode.
Use a strong password for the sa account.
Limit the amount of memory the system uses for file caching in order to optimize use of system memory.
Begin with a Default Index Fill Factor value of 90%. This setting reduces fragmentation of data. Monitor the fragmentation over a week and adjust the fill factor accordingly.
The following settings are best practices for development:
Limit maximum memory available to the instance.
Limit maximum worker threads.
Scale maximum file sizes for data files and log files.
The vast majority of configuration settings for Oracle Database will remain unchanged. However, you may want to adhere to the following suggestions for best performance.
Install the appropriate Oracle JDBC driver into your application server to allow NexJ applications to communicate with the database.
The following settings are required for a production environment:
When running the script oracle_create_database.sql, if you encounter error ORA-12638, comment out the line SQLNET.AUTHENTICATON_SERVICES=(NTS) in ORACLE_HOME\network\admin\sqlnet.ora.
Ensure that datasource, environment, and connection files are up to date, and set any additional parameters through NexJ Studio. Unset parameters will use template defaults where available.
Set the memory size to 15% of the total development desktop capacity.
In a production environment, database users should only be members of the njdbuser database role, which provides create, read, update and delete (CRUD) access to the data contained in NexJ tables. No other database roles are required.
The following settings are recommended for a production environment:
Configure the size of redo log files to improve performance. Determine the optimal size by querying the OPTIMAL_LOGFILE_SIZE column in the V$INSTANCE_RECOVERY view. Log files ranging from 100 MB to a few gigabytes are considered reasonable.
If your application is designed for an older release of Oracle Database and you are installing a later version, set the COMPATIBLE initialization parameter to the older release. This setting allows you to use the maintenance improvements of the new release in the production environment without having to test the new functionality.
Set the block size to 8192. A setting of 8192 is typical for transaction processing systems, and larger for database warehouse systems.
Configure tablespaces to use automatic segment-space management. Oracle will automatically manage segment space for best performance.
All databases can benefit from a higher open_cursors setting. It is recommended that you set the value to the relational database connection's statement cache size + 10. There is no extra cost to setting the open_cursors higher, as cursors are allocated only when needed. This setting simply sets an increased upper limit.
The following settings are best practices for development:
DB2 Database for Linux, UNIX, and Windows best practices
The vast majority of configuration settings for DB2 Database for Linux, UNIX, and Windows will remain unchanged.
However, you may want to adhere to the following suggestions for best performance. Install the appropriate DB2 JDBC driver on your application server to enable communication between the database and your NexJ application.
The following setting are required for a production environment:
In a production environment, database users should only be members of the njdbuser database role, which provides create, read, update and delete (CRUD) access to the data contained in NexJ tables. No other database roles are required.
The following settings are recommended for a production environment:
Set the amount of table spaces for each database partition under 1000. Too many data partitions or table spaces can increase overhead and decrease performance.
Limit the amount of data partitions for each table to a few hundred per database partition. Thousands of data partitions increase overhead, which can negatively impact performance.
Use the Database Schema Tool's setup command to generate an SQL script for creating an initial database schema.
You or your database administrator can review and run this script against your database system to create the databases required by the model. To generate code for setting up a new database schema:
In NexJ Studio, click the arrow next to the Run Tool button and select Database Schema Tool. The Database Schema Tool opens.
From the Model section, choose the model that you want to create a schema for:
For the currently loaded model, shown in the label, select Current.
For a base model in your Model Library, select Base Model and select one of the available models from the drop-down.
For a previously published model in your file system, select Published Model JAR and click the Browse button to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
If you want to use your database to run the application in a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.
Info
If you are creating a schema for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
In the Server field, specify either the server or environment file that contains the server configuration information you want to use when creating the database schema. If you specify an environment file, then it will also include the connections information.
In the Connections field, specify the connections file that you want to use when creating the database schema. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select setup.
In the Data Source field, select the model data source that you want to set up the schema for. Your selection must be one of the relational data sources described in the model.
In the Output Directory field, click the Browse button , specify where you want to save the resulting SQL file, and click OK.
[Optional] In the Owner field, specify an owner prefix for your tables. This field refers to the metadata table owner value. If you leave the field blank, a default value of dbo is used.
Click Advanced to display the additional VM arguments that will be used in the script.
In the VM Arguments field, review the parameters and their values.
Note
The directories set for the following parameters must exist on the machine where the scripts will be run:
datapath
indexpath
longpath
undopath
Click Run.
The tool outputs a script file named <modelname>_<datasourcename>_setup.sql to the output directory, where <modelname> is the name of the chosen model and <datasourcename> is the name of the chosen data source.
You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.
Recreating databases
Use the Data Load Tool's recreate or recreateschema command to drop and recreate all tables in the database associated with the environment. Use the recreate command to import the data after recreating all tables.
The recreate and recreateschema commands drop all tables defined in the selected datasource. The process then creates new tables based on the current model's datasource definitions. This method differs from the reset command in that a recreated database will always contain correctly structured tables for the current model.
Differences between recreate, recreateschema, and reset commands
Command
Truncates tables
Drops tables
Creates tables
Inserts data
recreate
No
Yes
Yes
Yes
recreateschema
No
Yes
Yes
No
reset
Yes
No
No
Yes
To recreate a database:
In NexJ Studio, click the arrow next to the Run Tool button and select Data Load Tool. The Data Load Tool opens.
In the Model section, select the model you want to use:
For the currently loaded model, shown in the label, select Current.
For a base model in your Model Library, select Base Model and select one of the available models from the drop-down list.
For a previously published model in your file system, select Published Model JAR and click the Browse button to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
Select the Test checkbox if you want to use a test environment.
Specify the server and connections that you want the application to support when running with this database.
In the Server field, specify either the server or environment file that contains the server configuration information you want to use when recreating the database. If you specify an environment file, then it will also include the connections information.
In the Connections field, specify the connections file that you want to use when recreating the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
Info
You must choose an environment or connections file that uses IDs with the appropriate permissions to change the database table structure, such as the db_owner fixed database role in SQL Server. This is necessary because the recreate command and the recreateschema command overwrite existing databases.
If you are using the current model, then the fields allow you to select from among the environment, server, and connections files within the model. If you are using a base or published model, then your choice is limited only to the environment, server, and connections files contained in your global environment folder.
In the Command field, select recreateschema or recreate.
In the Data Source field, select the desired data source. Selections are limited to the data source connections included in the environment or connections files for the model.
Click Run.
If using a non-test environment, a dialog opens, asking you to confirm that you want to overwrite the data. After confirming that it is acceptable to do so, select the checkbox and click OK.
[Optional] Publish the model from the Model Library. Publishing the new version before making any other changes creates a reference version for future upgrades. It also ensures that you have a backup working model if future changes make the model unusable.
Creating database tables
Use the Database Schema Tool's create command to generate an SQL script for creating your database tables. This command allows you to choose a set of parameters with initial values to load into your database.
To generate code for creating the database tables:
In NexJ Studio, click the arrow next to the Run Tool button and select Database Schema Tool. The Database Schema Tool dialog opens.
From the Model section, choose the model that you want to create database tables for:
For the currently loaded model, shown in the label, select Current.
For a base model in your Model Library, select Base Model and select one of the available models from the drop-down list.
For a previously published model in your file system, select Published Model JAR and click the Browse button to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
If you want to use a test environment, select the Test checkbox.
Specify the server and connections that you want the application to support when running with this database.
In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
Info
If you are creating the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
In the Command field, select create.
In the Data Source field, select the model data source that you want to use. Selections are limited to the relational data sources described in the model.
In the Output Directory field, click the Browse button , specify where you want to save the resulting SQL file, and click OK.
[Optional] In the Owner field, specify an owner prefix for your tables. This field refers to the metadata table owner value. If you leave the field blank, a default value of dbo is used.
Click Run.
The tool outputs a script file name <modelname>_<datasourcename>_create.sql to the output directory where <modelname> is the name of the chosen model and <datasourcename>is the name of the chosen data source.
You can now provide your database administrator with this file to create the database schema, or you can run it yourself if you have database administrator privileges.
Upgrading databases
Before upgrading a database, make sure you have updated your model's upgrade file.
To upgrade a database:
In NexJ Studio, click the arrow next to the Run Tool button and select Data Load Tool. The Data Load Tool opens.
In the Model section, select the model you want to use:
For the currently loaded model, shown in the label, select Current.
For a base model in your Model Library, select Base Model and select one of the available models from the drop-down.
For a previously published model in your file system, select Published Model JAR and click the Browse button to locate the published model.
If you want to use a test environment, select the Test checkbox. Specify the server and connections that you want the application to support when running with this database.
Info
If you are using the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are using a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
In the Server field, specify either the server or environment file that contains the server configuration information you want to use when upgrading the database. If you specify an environment file, then it will also include the connections information.
Info
You must choose an environment or server file that uses IDs with permissions to change the database table structure, such as DBAdmin. This is necessary because the upgrade command overwrites existing databases.
In the Connections field, specify the connections file that you want to use when upgrading the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
In the Command field, select upgrade.
In the Data Source field, select the desired data source. Selections are limited to the model's relational data sources.
Click Run.
If using a non-test environment, a dialog opens, asking you to confirm that you want to overwrite the data. After confirming that it is acceptable to do so, select the checkbox and click OK.
[Optional] Publish the model from the Model Library. Publishing the new version before making any other changes creates a reference version for future upgrades. It also ensures that you have a backup working model if future changes make the model unusable.
Generating the initial database data for deployment
Use the Database Schema Tool's insert command to generate an SQL script for inserting system default data into a database.
The insert command reads the system default data or serialized class instance data from a specified dump file, then generates an SQL script that inserts this class data into the appropriate tables as defined by the persistence mapping.
Note
Only insert data if it does not already exist in the database, otherwise the insert will fail. If the insert fails because data already existed in the database, the duplicate data will need to be deleted from the model before proceeding. To delete data, use the truncate command. For more information about using the truncate command, see Truncating database schema tables.
To insert data into a database from a dump file:
In NexJ Studio, click the arrow next to the Run Tool button and select Database Schema Tool. The Database Schema Tool opens.
In the Model section, choose the model that you want to insert data from:
For the currently loaded model, shown in the label, select Current.
For a base model in your Model Library, select Base Model and select one of the available models from the drop-down list.
For a previously published model in your file system, select Published Model JAR and click the Browse button to locate the published model. You might be prompted to specify the directory where the published model's mixins are located.
Specify the server and connections that you want the application to support when running with this database.
In the Server field, specify either the server or environment file that contains the server configuration information you want to use when setting up the database. If you specify an environment file, then it will also include the connections information.
In the Connections field, specify the connections file that you want to use when setting up the database. If you previously selected an environment file in the Server field, then it already has the connections information, and this field becomes inactive.
Info
If you are setting up the database for the current model, then the fields allow you to select from among the environment, server, and connection files within the model. If you are creating a schema for a base or published model, then your choice is limited to only those server and connection files contained in your global environment folder.
In the Command field, select insert.
In the Data Source field, select the data source that you want to insert data into. Selections are limited to the model's relational data sources.
In the Output Directory field, click the Browse button , specify where you want to save the resulting SQL file, and click OK.
[Optional] If you want to specify an owner prefix for your tables, enter it in the Owner field. This field refers to the metadata table owner value. If you leave the field blank, a default value of dbo is used.
Click Run.
The tool outputs a script file to the specified directory called <modelname>_<datasourcename>_insert.sql, where modelname is the name of the chosen model and <datasourcename> is the name of the chosen data source.
JavaScript errors detected
Please note, these errors can depend on your browser setup.
If this problem persists, please contact our support.
