Database Prerequisites

This topic discusses Redwood Server database prerequisites, which can vary from one implementation to another, depending on the use that is made of Redwood Server. There are different requirements for large repositories in terms of "tablespace" size, for example.

Warning: When you uninstall Redwood Server, the last step is to drop the user so the user should be used by Redwood Server only.

Oracle

Prerequisites

Oracle database versions 11.2 and higher are supported.
500Mb of tablespace.
Oracle user must exist and must be able to create tables (otherwise you will have to execute SQL during installation and for every upgrade), the user must be table owner.
Oracle user must be granted the following privileges and have the following properties:.
- CREATE SESSION and CREATE TABLE.
- UNLIMITED QUOTA on the tablespace you are going to use, USERS by default.
Supported database character set (UTF8 or UTF16-based).

Creating an Oracle User

Creating an Oracle user

Copy

$  sqlplus system/mypassword@orcl
SQL>  create user example identified by mypassword
  2 default tablespace users
  3 quota unlimited on users;
SQL>  grant create session, create table to example;

Note that in this example, orcl is the connect string, example is the user and users is the tablespace. In Oracle 12c and later, Redwood recommends you install Redwood Server in a pluggable database.

Note, also, that mypassword is not a very safe password and just used here for illustration purposes.

Database Character Set

Check to see which character set your database has:

Copy

SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

Check to see which national character set your database has:

Copy

SQL> select value from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET';

Problematic character sets are non-Unicode character sets, which include WE8ISO8859P1, AMERICAN_AMERICA.US7ASCII, and GERMAN_GERMANY.WE8ISO8859P1, for example. Supported character sets are UTF-8 or UTF-16 based character sets. Redwood recommends that you install using the AL32UTF8 character set. This is in line with the recommendations of Oracle:

http://docs.oracle.com/cd/E16655_01/server.121/e17750/ch6unicode.htm#NLSPG323

To ensure you have the correct database character set, please see Oracle specific documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch6unicode.htm#g1014017

Under the section: Example 6-1 Creating a Database with a Unicode Character Set

More information on choosing a character set is here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch2charset.htm#i1007017

If you think you have an unsupported character set, read the Redwood Note #57474:

http://note.redwood.com/57474

Oracle Recommendations

As of Oracle 12c, you now have the option to have a container database (CDB) and one or more (up to 252 in Enterprise Edition) pluggable databases (PDB's). The architecture resembles SQL Server instances where you can have multiple independent databases in any given instance.

Oracle recommends you install an application in a pluggable database (PDB) as there are no performance impacts nor functional differences between a PDB and a non-CDB database. Note that you can plug and unplug a PDB with ease, allowing greater database management capabilities.

Also, a single PDB comes at no additional costs in each Standard Edition, Standard Edition One, and Enterprise Edition.

Note that you connect to a PDB via its service name; connecting via instance name actually connects to the CDB. The CDB and all PDB's share the same instance name. Database user/schema names in the CDB must have a specific prefix.

See the Oracle Multitenant white paper and the Oracle 12c Licensing Information document which are available on Oracle website for more information.

At the time of the writing, these database capabilities are not available in Oracle Express Edition.

DB2

Prerequisites

IBM DB2 versions 10.5 and higher are supported.
500Mb of table space.
DB2 user must exist and must be able to create tables (otherwise you will have to execute SQL during installation and for every upgrade), the user must be table owner.
Database must be configured for TCP/IP communication.
Pagesize must be 8k or higher (16k recommended).
Increased log size (for Redwood Server transactions), recommended setting: 100 primary and secondary log files with default file size.

Enable TCP/IP Communications

Run the following command to inspect the current settings:

Copy

$ db2set -all

If you do not see 2COMM=tcpip, run the command below to enable network connections

Copy

$ db2set DB2COMM=tcpip

You should also check which port it is listening on, by default, the port is set to 50000 or 50001.

Run the following command to discover which port DB2 is listening on:

Copy

$ db2 get dbm cfg

Look for the TCP/IP Service name parameter, if (SVCENAME) is set to a number, that is your port number, if it is set to a string, like db2c_db2, you must look up the port number in /etc/services on UNIX or %WINDIR%\System32\drivers\etc\services on Microsoft Windows.

If you are on UNIX, you can use grep as follows:

Copy

  db2 get dbm cfg | grep TCP
   TCP/IP Service name                          (SVCENAME) = db2c_db2

$  grep db2c_db2 /etc/services

Increase Pagesize and Buffer Pool

Copy

  db2 connect to <name>
  db2 create bufferpool rwpbuff deferred size 16384 automatic pagesize 16k
  db2 create tablespace rwtbl pagesize 16k managed by system \
  using ('d:\rwt_cont1', 'e:\rwt_cont2', 'f:\rwt_cont3') \
  bufferpool rwpbuff
  db2 create database <name> pagesize 16 k

Increase Log Size

Inspect the values with the following command:

Copy

db2 connect to <name>
db2 GET DATABASE CONFIGURATION

Check the values of LOGPRIMARY and LOGSECOND:

Copy

 Number of primary log files                (LOGPRIMARY) = 100
 Number of secondary log files               (LOGSECOND) = 100

Only update LOGPRIMARY as necessary:

Copy

  db2 update database configuration for <name> using logprimary 100
  db2 update database configuration for <name> using logsecond 100

Note: The LOGPRIMARY parameter takes effect the next time either the database is activated or a connection to the database is established. The sum of logprimary and logsecond should not exceed 255.

Enable MVCC Currently Committed semantics

To enable Currently Committed semantics, specify the following command:

Copy

db2 set parameter CUR_COMMIT = ON

Microsoft SQL Server

Prerequisites

MS SQL Server versions 2012 SP3 and higher are supported.
- MS SQL Server instances in Docker are not supported.
- MS SQL Server for Linux is not supported.
500Mb of tablespace.
Database user must exist and must be able to create tables (otherwise you will have to execute SQL during installation and for every upgrade), the user must be table owner.
Row versioning-based isolation

Ensure Microsoft SQL Server is listening on a static port. A default instance will listen on port 1433 by default, a named instance on the other hand will listen on a dynamic port.

See Q823938 in the Microsoft Knowledge Base for more information.

Setting the static port for a named instance

Start SQL Server configuration Manager.
Navigate to Network Configuration.
Navigate to Protocols.
Navigate to TCP/IP.
Navigate to IP addresses.
Disable IP1, IP2,... IPn.
Enable IPAll, and set the TCPDynamicPort to blank (empty) and the TCP Port to 1433.
Save and Exit.
Restart SQL Server Express.

Enable Mixed-mode authentication

Open and logon to the SQL Server instance with SQL Server Management Studio, you must log in to the instance where the database will be running.
Choose Properties from the root node, usually named <server>\<instance> (SQL Server <version> <user>, for example EXAMPLE\SQLEXPRESS (SQL Server 13.0.2213.0 -sa.
In the Server Properties dialog, choose Security.
Make sure SQL Server and Windows Authentication mode is selected.
Choose Ok to close the dialog.

SQL Server JDBC Driver Settings

Redwood strongly recommends you enable response buffering in the JDBC driver connection string. You also need to remove selectMethod=cursor if set, as this setting overrides the response buffering setting.

In this example, the connect string becomes:

Copy

jdbc:sqlserver://mssql.example.com:1433;databasename=MSLN;responseBuffering=adaptive

Note: The sqlserver JDBC connection strings have semi-colon (;) separators; you must be especially careful when changing the database connection string.

Microsoft SQL Server Collation

The Microsoft SQL Server Collation is set to SQL_Latin1_General_CP850_BIN2; this is set on the column-level for all tables which are used by Redwood Server.

Row Isolation

You enable this by turning the database parameter READ_COMMITTED_SNAPSHOT to on.

Perform the following Transact-SQL statement to enable READ_COMMITTED_SNAPSHOT:

Copy

ALTER DATABASE <DB> SET READ_COMMITTED_SNAPSHOT ON;

Note: In order to set READ_COMMITTED_SNAPSHOTON, there must be no active connections to the database except for the connection executing the ALTER DATABASE statement.

PostgreSQL

Prerequisites

PostgreSQL versions 9.6 and higher are supported.
500Mb of tablespace.
OS user must exist and must be able to create tables (otherwise you will have to execute SQL during installation and for every upgrade), the user must be table owner.
UTF8 encoding and collation.
Redwood Server functions correctly with the default PostgreSQL settings.

Checking the Version

Issue the following from psql:

Copy

select version();

Create User and Database

You perform the following commands as user postgres:

Copy

<create_user>
psql postgres
create database example;
alter user example with password 'example';

The <create_user> command is OS-specific:

On Windows: net user <username> <password> /ADD, for example: net user example badsecret /ADD (Run cmd.exe with elevated privileges).
Linux: # createuser example.
Solaris: # useradd -d /export/home/example -m -s /bin/ksh -c "Example" example.
For other operating systems refer to the documentation provided by the operating system vendor.

Checking Encoding and Collation

Issue the following statement on the command line:

Copy

$ psql -l
                                         List of databases
   Name    |  Owner   | Encoding  |  Collation  |    Ctype    |          Access Privileges
-----------+----------+-----------+-------------+-------------+-------------------------------------
 example   | example  |   UTF8    | en_GB.UFT8  | en_GB.UTF8  |
 postgres  | example  |   UTF8    | en_GB.UTF8  | en_GB.UTF8  |

Note: On UNIX, PostgreSQL derives the encoding and collation from the LC_CTYPE environment variable, by default. This means that you need a UTF-8 code page in LC_CTYPE in your environment or you will have to specify a UTF8 encoding and collation at database creation time. Specify locale on the command line to inspect your current locale; the code page comes after the dot (.), for example fr_FR.utf-8. If you need to specify an encoding and collation, see the CREATE DATABASE documentation for your version of PostgreSQL.

SAP HANA

Prerequisites

HANA version 2.0 or later.
SAP HANA JDBC driver version 2.12 or later.
- The JDBC driver is available in the maven repository and in ${HANA_INSTALLATION_DIRECTORY}\hdbclient\ngdbc.jar.

Create User and Database

Copy

hdbsql -n <host> -i <instance> -u <user> -p <password> "CREATE DATABASE <database_name> SYSTEM USER PASSWORD <SYSTEM_user_password>"
hdbsql -n <host> -i <instance> -u <user> -p <password> "CREATE USER <dbuser> PASSWORD <password>;"
hdbsql -n <host> -i <instance> -u <user> -p <password> "GRANT DATABASE ADMIN TO <dbuser>;"

Example

Copy

hdbsql -n hana.example.com -i 00 -u SYSTEM -p badPassword1 "CREATE DATABASE rmjprod SYSTEM USER PASSWORD badPassword1"
hdbsql -n hana.example.com -i 00 -u SYSTEM -p badPassword1 "CREATE USER jdoe PASSWORD badPassword2;"
hdbsql -n hana.example.com -i 00 -u SYSTEM -p badPassword1 "GRANT DATABASE ADMIN TO jdoe;"

The database URL is jdbc:sap://hana.example.com:30015/?databaseName=rmjprod.

Prepare admin-server

Download the HANA JDBC driver.
Copy the JAR file to ${INSTALL_DIR}/j2ee/cluster/global/tomcat/lib/.
Rename the ${INSTALL_DIR}/j2ee/cluster/global/tomcat/lib/manifest file to ${INSTALL_DIR}/j2ee/cluster/global/tomcat/lib/manifest.old.
Start the admin server (${INSTALL_DIR}/j2ee/cluster/adminserver1/bin/start.{sh,cmd}), HANA is listed as an option; select SAP HANA in the Database field, specify the host, database name, username and password.
Choose Connect and Update to perform the installation.
Once the schema has been initialized stop admin server (${INSTALL_DIR}/j2ee/cluster/adminserver1/bin/stop.{sh,cmd}) and start server1 (${INSTALL_DIR}/j2ee/cluster/server1/bin/start.{sh,cmd}).

Database Prerequisites

Oracle

Prerequisites

Creating an Oracle User

Database Character Set

Oracle Recommendations

DB2

Prerequisites

Enable TCP/IP Communications

Increase Pagesize and Buffer Pool

Increase Log Size

Enable MVCC Currently Committed semantics

See Also

Microsoft SQL Server

Prerequisites

SQL Server JDBC Driver Settings

Microsoft SQL Server Collation

Row Isolation

PostgreSQL

Prerequisites

Checking the Version

Create User and Database

Checking Encoding and Collation

SAP HANA

Prerequisites

Create User and Database

Example

Prepare admin-server

See Also