Database configuration parameters

To connect the supported databases and the trading partner UI to ITXA and to enable the command line utilities, configure properties according to your database type.

Where to configure

This summary shows the files that are updated to set database properties:

ITXA database properties are configured in the dbprops.cfg file. See Configuring the ITXA and Trading Partner UI databases.
Trading partner UI properties are configured in the server.xml file. See Configuring a web server for the Trading Partner UI and Configuring the ITXA and Trading Partner UI databases.
Properties are configured for your host application as specified in Database configuration for host applications.

SPE database properties, trading partner UI properties, and (as applicable) host application database properties must have the same values.

Derby

Note: 10.0.1.9 and later versions of ITXA no longer include the Derby client or support the Derby database.

This database is created when the SPE spedeploy-pack or the spedbinit command is run.

If the database already exists, these commands update the database. They do not recreate it.

A Derby database does not need to be created in advance.

Table 1. Database configuration properties for the Derby database
Property type	Syntax or example	Configuration notes
Driver	Default: `org.apache.derby.jdbc.ClientDriver`	No configuration is needed. The javax.persistence.jdbc.driver takes the derbyclient.jar JDBC driver that is included in the `spe_install_dir`/jars directory.
Database URL	`jdbc:derby//db_server:port/database_name` where `db_server` is `localhost` or, if the database server is on another server, the IP address of that server. `port` is the Derby port. Default: `1527` `database_name` is the database name for your installation. Default: `spe2` Example: `jdbc:derby//localhost:1527/spe2`	The URL for a Derby database can include the user ID and password. Restriction: The SPE database and the Trading Partner UI configuration files must reference the same server, port, and database name.
User ID	`UserId` Default: `derbyuser` Example: `user=derbyuser` or `jdbc:derby://localhost:1527/spe2;create=true;user=derbyuser;password=derbyuser`	The user ID can be included in the database URL. Restriction: The SPE database and the Trading Partner UI configuration files must reference the same user ID.
Password	`Password` Default: `derbyuser` Example: `password=derbyuser` or `jdbc:derby://localhost:1527/spe2;create=true;user=derbyuser;password=derbyuser`	The password can be included in the database URL. Restriction: The SPE database and the Trading Partner UI configuration files must reference the same password. Important: To encrypt the database password, use the spesetdbpsw command.

DB2

This database must be created before the ITXA spedeploy-pack or the spedbinit command is run.

If the database already exists, these commands update the database. They do not recreate it.

Requirement:

A minimum page size of 8k (8192) is required on DB2® databases to support ITXA. If not specified, a default DB2 page size of 4k (4096) is used.

Example:

CREATE DATABASE SPE201 AUTOMATIC STORAGE YES ON 'C:\' DBPATH ON 'C:\' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 8192

Table 2. Database configuration properties for the DB2 database
Property type	Syntax or example	Configuration notes
Driver	Default: `com.ibm.db2.jcc.DB2Driver`	The JDBC driver JAR file is not installed by default. Place it in these directories: `spe_install_dir`/jars (If you are using the Trading Partner UI) `spe_install_dir`/tpuiserver/usr/servers/defaultServer/apps/spe.war/WEB-INF/lib
Database URL	`jdbc:db2://db_server:port/database_name` where `db_server` is the database host for your installation. Valid values: `localhost` or, if the database server is on another server, the IP address of that server. `port` is the DB2 port. Default: `50000` `database_name` is the database name for your installation. Default: `spe2` Example: `//murphy.test.com:50000/spe2`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same server, port, and database name.
User ID	`UserID` Default: `db2user`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same user ID. Specify the user ID on a separate line. Do not include the user ID in the URL.
Password	`Password` Default: `db2password`	Restriction: The SPE database and the Trading Partner UI configuration files must reference the same password. Specify the password on a separate line. Do not include the password in the URL. Important: To encrypt the database password, use the spesetdbpsw command.

Microsoft SQL Server

This database must be created before the SPE spedeploy-pack or thespedbinit command is run.

If the database already exists, these commands update the database. They do not recreate it.

Table 3. Database configuration properties for the Microsoft SQL Server database
Property type	Syntax	Configuration notes
Driver	Default: `com.microsoft.sqlserver.jdbc.SQLServerDriver`	The JDBC driver JAR file is not installed by default. Place it in these directories: `spe_install_dir`/jars (If you are using the Trading Partner UI) `spe_install_dir`/tpuiserver/usr/servers/defaultServer/apps/spe.war/WEB-INF/lib
Database URL	`jdbc:sqlserver://host:port;DatabaseName=database_name` where `db_server` is the database host for your installation. Valid values: `localhost` or, if the database server is on another server, the IP address of that server. `port` is the database port. Default: `1433` `database_name` is the database name for your installation. Default: `spe2` Example:`jdbc:sqlserver://murphy.test.com:1433;DatabaseName=spe2`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same server, port, and database name values.
User ID	`UserId` Default: `mssqluser`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same user ID. Specify the user ID on a separate line. Do not include the user ID in the URL.
Password	`Password` Default: `mssqlpassword`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same password. Specify the password on a separate line. Do not include the password in the URL. Important: To encrypt the database password, use the spesetdbpsw command.

Oracle

This database must be created before the ITXA spedeploy-pack or the spedbinit command is run.

If the database already exists, these commands update the database. They do not recreate it.

Table 4. Database configuration properties for the Oracle database
Property type	Syntax	Configuration notes
Driver	Default: `Oracle.jdbc.OracleDriver`	The JDBC driver JAR file is not installed by default. Place it in these directories: `spe_install_dir`/jars (If you are using the Trading Partner UI) `spe_install_dir`/tpuiserver/usr/servers/defaultServer/apps/spe.war/WEB-INF/lib
Database URL	For Oracle service name: `jdbc:oracle:thin:@host_name_or_ip:1521/spe2` For Oracle SID: `jdbc:oracle:thin:@host_name_or_ip:1521:spe2` where `host_name_or_ip` is the database host for your installation. Valid values: `localhost` or, if the database server is on another server, the IP address of that server. `port` is the Oracle port. Default: `1521` `database_name` is the database name for your installation. Default: `spe2` Example: For Oracle service name: `jdbc:oracle:thin:@murphy.test.com:1521/spe2` For Oracle SID: `jdbc:oracle:thin:@murphy.test.com:1521:spe2`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same port and database name.
User ID	`UserId` Default: `oracleuser`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same user ID. Specify the user ID on a separate line. Do not include the user ID in the URL.
Password	`Password` Default: `oraclepassword`	Restriction: The ITXA database and the Trading Partner UI configuration files must reference the same password. Specify the password on a separate line. Do not include the password in the URL. Important: To encrypt the database password, use the spesetdbpsw command.

Creating Oracle user with associated grants

CREATE USER &&USER_NAME IDENTIFIED BY <password>
DEFAULT TABLESPACE &&USER_NAME
TEMPORARY TEABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT RESOURCE, connect, 
SELECT_CATALOG_ROLE TO &&USER_NAME;
ALTER USER &&USER_NAME DEFAULT ROLE all; 
GRANT QUERY REWRITE TO &&USER_NAME;
GRANT UNLIMITED TABLESPACE TO &&USER_NAME;
GRANT CREATE TABLE TO &&USER_NAME;
GRANT CREATE VIEW TO &&USER_NAME;

Note: Please do not provide more grants thee than mentioned above.