The Server Database manual describes how to initially setup or update the Product 360 server database schemas for a new release.

Custom Indexes
Oracle RAC, Oracle ASM (Automated Storage Management)
Minimum Oracle privileges
- Normal installation (tablespaces and db users are not existing before install process)
- Installation with restricted privileges (similar update)
Binaries
Troubleshooting

Custom Indexes

It is allowed for DBAs to create own, customer specific indexes in tables, as long as those are not unique and therefore only for performance reasons. Search scenarios of productive installations can not all be foreseen by the development team and therefore it might be necessary to create additional indexes. We strongly encourage you to create those using scripts, and to provide also a drop script for them.

All custom indexes must be removed before you execute the database setup - or it might fail because table adjustments can't be done as long as indexes are there which the setup doesn't know.

You can customize the database setup by adding scripts to the corresponding extension point. This is a comfortable way to remove and recreate such customized indexes. See development guide on how to do this.

Oracle RAC, Oracle ASM (Automated Storage Management)

Please note that the standard database setup is not aware of complex tablespace setups which are typical for larger Oracle environments. Since the policies around those tablespaces are quite complex and differ from customer to customer, we recommend to create the tablespaces and users manually. The database setup will skip the user and tablespace creation part in case it recognizes that those elements are already there. For this, the users and tablespaces need to be named correctly otherwise the setup won't recognize them.

The following scripts use PIM_ as prefix and no suffix. You need to make sure that the server.properties file match. If you want to use a different pre/suffix, you need to adjust the scripts accordingly.

Username and Tablespace names need to be in capital letters and start with a latin character. Tablespace names must not be longer than 30 characters, that means prefix + schema name + suffix must not be longer than 24 characters.

Prefix `(db.default.schema.prefix)`	Schema Name	Suffix `(db.default.schema.suffix)`	Username	Temp Tablespace	Data Tablespace	Index Tablespace
PIM_	MAIN		PIM_MAIN	PIM_MAIN_TEMP	PIM_MAIN_DATA	PIM_MAIN_INDEX
PIM_	MASTER		PIM_MASTER	PIM_MASTER_TEMP	PIM_MASTER_DATA	PIM_MASTER_INDEX
PIM_	SUPPLIER		PIM_SUPPLIER	PIM_SUPPLIER_TEMP	PIM_SUPPLIER_DATA	PIM_SUPPLIER_INDEX

The following scripts are examples - they most likeley should be adapted to the needs of the customer. Especially in terms of initial and maximum size!

Example: MAIN Script

CREATE TEMPORARY TABLESPACE "PIM_MAIN_TEMP"
 TEMPFILE '+DATAGRP1/pimfhqa/tempfile/pim_main_temp.263.860573097'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
 
CREATE TABLESPACE "PIM_MAIN_DATA"
 DATAFILE '+DATAGRP1/pimfhqa/datafile/pim_main_data.264.860573159'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 LOGGING
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE TABLESPACE "PIM_MAIN_INDEX"
 DATAFILE '+DATAGRP1/pimfhqa/datafile/pim_main_index.265.860573217'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 LOGGING
 EXTENT MANAGEMENT LOCAL
 SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE USER "PIM_MAIN"
 PROFILE "DEFAULT"
 IDENTIFIED BY pimadmin
 DEFAULT TABLESPACE "PIM_MAIN_DATA"
 TEMPORARY TABLESPACE "PIM_MAIN_TEMP"
 ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "PIM_MAIN";
GRANT "CONNECT" TO "PIM_MAIN";
GRANT "RESOURCE" TO "PIM_MAIN";

Example: MASTER Script

CREATE TEMPORARY TABLESPACE "PIM_MASTER_TEMP"
 TEMPFILE '+DATAGRP1/pimfhqa/tempfile/pim_master_temp.266.860574107'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
 
CREATE TABLESPACE "PIM_MASTER_DATA"
 DATAFILE '+DATAGRP1/pimfhqa/datafile/pim_master_data.267.860574173'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 LOGGING
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE TABLESPACE "PIM_MASTER_INDEX"
 DATAFILE '+DATAGRP1/pimfhqa/datafile/pim_master_index.268.860574237'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE USER "PIM_MASTER"
 PROFILE "DEFAULT"
 IDENTIFIED BY pimadmin
 DEFAULT TABLESPACE "PIM_MASTER_DATA"
 TEMPORARY TABLESPACE "PIM_MASTER_TEMP"
 ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "PIM_MASTER";
GRANT "CONNECT" TO "PIM_MASTER";
GRANT "RESOURCE" TO "PIM_MASTER";

Example: SUPPLIER Script

CREATE TEMPORARY TABLESPACE "PIM_SUPPLIER_TEMP"
 TEMPFILE '+DATAGRP1/pimfhqa/tempfile/pim_supplier_temp.269.860574555'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;
 
CREATE TABLESPACE "PIM_SUPPLIER_DATA"
 DATAFILE '+DATAGRP1/pimfhqa/datafile/pim_supplier_data.270.860574619'
 SIZE 1024M REUSE 
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 LOGGING
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE TABLESPACE "PIM_SUPPLIER_INDEX"
 DATAFILE '+DATAGRP1/pimfhqa/datafile/pim_supplier_index.271.860574685'
 SIZE 1024M REUSE
 AUTOEXTEND ON NEXT 1024M
 MAXSIZE 32767M
 LOGGING
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
CREATE USER "PIM_SUPPLIER"
 PROFILE "DEFAULT"
 IDENTIFIED BY pimadmin
 DEFAULT TABLESPACE "PIM_SUPPLIER_DATA"
 TEMPORARY TABLESPACE "PIM_SUPPLIER_TEMP"
 ACCOUNT UNLOCK;
GRANT UNLIMITED TABLESPACE TO "PIM_SUPPLIER";
GRANT "CONNECT" TO "PIM_SUPPLIER";
GRANT "RESOURCE" TO "PIM_SUPPLIER";

Minimum Oracle privileges

Normal installation (tablespaces and db users are not existing before install process)

Normal installation means in this context that the tablespaces and also the needed database users for the server will be created while the installation process. The users and also the tablespace have to be defined in the configuration file server.properties.The database use which will run the installation procedures needs at minimum the following privileges.

Role	Granted	Admin
CONNECT	X	X
RESOURCE	X	X

System Privileges	Granted	Admin
CREATE USER	X	-
CREATE TRIGGER	X	-
CREATE TABLESPACE	X	-
CREATE SEQUENCE	X	-
CREATE TABLE	X	-
CREATE PROCEDURE	X	-
GRANT ANY PRIVILEGE	X	-
CREATE TYPE	X	-
ALTER USER	X	-
CREATE SESSION	X	-
UNLIMITED TABLESPACE	X	-
SELECT ANY DICTIONARY	X	-

Example: Database Install User Script

-- USER SQL
CREATE USER INFA_DB_INSTALLER IDENTIFIED BY "password" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
-- ROLES
GRANT "RESOURCE" TO INFA_DB_INSTALLER WITH ADMIN OPTION;
GRANT "CONNECT" TO INFA_DB_INSTALLER WITH ADMIN OPTION;
-- SYSTEM PRIVILEGES
GRANT CREATE USER TO INFA_DB_INSTALLER ;
GRANT CREATE TRIGGER TO INFA_DB_INSTALLER ;
GRANT CREATE TABLESPACE TO INFA_DB_INSTALLER ;
GRANT CREATE SEQUENCE TO INFA_DB_INSTALLER ;
GRANT CREATE TABLE TO INFA_DB_INSTALLER ;
GRANT CREATE PROCEDURE TO INFA_DB_INSTALLER ;
GRANT GRANT ANY PRIVILEGE TO INFA_DB_INSTALLER ;
GRANT CREATE TYPE TO INFA_DB_INSTALLER ;
GRANT ALTER USER TO INFA_DB_INSTALLER ;
GRANT CREATE SESSION TO INFA_DB_INSTALLER ;
GRANT UNLIMITED TABLESPACE TO INFA_DB_INSTALLER;
GRANT SELECT ANY DICTIONARY TO INFA_DB_INSTALLER ;

Installation with restricted privileges (similar update)

The installation can also be done with more restricted privileges but in this case the tablespaces and the database uesrs have to be created before the installation process by an Oracle DBA. See chapter "Oracle RAC, Oracle ASM (Automated Storage Management)".
The database use which will run the installation procedures needs at minimum the following privileges.

Role	Granted	Admin
CONNECT	X	-

System Privileges	Granted	Admin
CREATE TRIGGER	X	-
CREATE SEQUENCE	X	-
CREATE TABLE	X	-
CREATE PROCEDURE	X	-
GRANT ANY PRIVILEGE	X	-
CREATE TYPE	X	-
SELECT ANY DICTIONARY	X	-

Example: Database Install User Script

-- USER SQL
CREATE USER INFA_DB_INSTALL_USER IDENTIFIED BY "password" DEFAULT TABLESPACE "PIM_MAIN_DATA" TEMPORARY TABLESPACE "PIM_MAIN_TEMP";
 
-- ROLES
GRANT "CONNECT" TO INFA_DB_INSTALL_USER;
 
 
-- SYSTEM PRIVILEGES
GRANT CREATE TRIGGER TO INFA_DB_INSTALL_USER ;
GRANT CREATE SEQUENCE TO INFA_DB_INSTALL_USER ;
GRANT CREATE TABLE TO INFA_DB_INSTALL_USER ;
GRANT CREATE PROCEDURE TO INFA_DB_INSTALL_USER ;
GRANT GRANT ANY PRIVILEGE TO INFA_DB_INSTALL_USER ;
GRANT CREATE TYPE TO INFA_DB_INSTALL_USER ;
GRANT SELECT ANY DICTIONARY TO INFA_DB_INSTALL_USER ;

Binaries

The database setup is distributed within the product core archive and has the following format PIM_<Version>_<Revision>_dbSetupClient_win64.zip

The database setup currently cannot be executed from a Linux server. In order to install the database on a Linux server, the setup has to be executed remotely from a Windows computer. The settings have to be adjusted appropriately.

Extract the database setup archive

On the database server extract the PIM_<Version>_<Revision>_dbSetupClient_win64.zip to an installation root of your choice.
For this documentation we will choose C:\INFORMATICA\PIM (=<PIM_DATABASE_INSTALLATION ROOT>)

Provide database connection settings

Before running the database installation, some basic configuration needs to be done. The settings for the database connection are configured in the server.properties file. Templates for this file can be found in the configuration folder of the extracted archive.

If you want to encrypt the database passwords in the configuration file please refer to chapter Encryption of secure information in the Server Installation manual. The passwords marked as to encrypt will be encrypted during the database setup.
Updating to newest Hotfix you should also replace the Java JCE policy files in jre\lib\security folder.

If you want to connect the P360 Server to an Oracle Database via TCPS, please refer to chapter "How to configure a secure database connection for Product 360 Server" in the "Server Configuration" manual.

Perform the following steps to adjust the server.properties file:

Rename the appropriate template file <PIM_DATABASE_INSTALLATION ROOT>\<Version>_<Revision>_dbSetupClient_win64.zip\configuration\server.properties.template.[DBMS] to server.properties
Adjust the settings as described in the following table:

Property	Description
General Settings
`repository.default.language`	The default language of the repository regarding all language specific aspects like e.g. default logical key language. Possible values: Key synonyms of the corresponding language entries defined in the repository enumeration "`Enum.Language`", e.g. `"de"` or `"en_US"` - default is German, if property does not exist.
Database settings for Microsoft SQL Server (We only describe the default settings here. Most of those can be adjusted individually for each database schema as you will see in the server.properties template file. However, splitting the schemas on multiple database hosts/instances is not supported since there are cross schema sql statements which would not work!)
`db.integrated.security`	If your security guidelines do not allow passwords in configuration files this preference allows you to use integrated authentication on Windows operating systems. "Integrated Security" is a security functionality of Microsoft SQL Server. If other password protection mechanism is used, then keep this setting in the configuration file and set to false.
`db.default.type`	`MSSQL` This property should never be changed!
`db.default.server`	The host name of the Microsoft SQL Server; Change this in case you have a separate database server
`db.default.port`	Port of the Microsoft SQL Server instance, usually this is 1433
`db.default.user`	User name of the database user (if integrated authentication is used this property can be empty)
`db.default.password`	Password of the database user (if integrated authentication is used this property can be empty)
`db.default.dir`	Base folder for the database schema and database transaction log files Note: This folder needs not to be local to the application server but to the database server!
`db.default.dir.data`	Folder for the database schema files (`*.mdf`)
`db.default.dir.log`	Folder for the transaction log files (`*.ldf`)
`db.default.data.size`	Default size in MB allocated for a database schema; adapt this setting to your needs
`db.default.data.size.growth`	Default increment value in MB allocated when space for a database schema is insufficient; adapt this setting to your needs In a productive environment you should define the initial size of the database to the expected maximum. A data base growth action always "stops the world" of the database until the files are enlarged. In case the growth size is too small, this might occur very often which is a serious performance problem!
`db.default.log.size`	Default size in MB allocated for a database transaction log file; adapt this setting to your needs
`db.default.log.size.growth`	Default increment value in MB allocated when space for a database transaction log file is insufficient; adapt this setting to your needs Default increment value in MB allocated when space for a database schema is insufficient; adapt this setting to your needs In a productive environment you should define the initial size of the database log files to the expected maximum. A data base growth action always "stops the world" of the database until the files are enlarged. In case the growth size is too small, this might occur very often which is a serious performance problem!
`db.default.schema.prefix`	Usually, this property needs not to be changed. The common prefix for all server schemas; it must be in capital, start with a latin character and must not contain any special characters except '_'!
`db.default.schema.suffix`	Usually, this property needs not to be changed. The common suffix for all erver schemas; it must be in capital, start with a latin character and must not contain any special characters except '_'! This property is helpful to distinguish between productive and test schemas (e.g. `_PRO` and `_TEST`)
`db.default.debug.show_sql`	Usually, this property needs not to be changed. Generated SQL statements during runtime will be shown in the log file. This is a debugging feature which will slow down the application drastically if turned on.
`db.default.rowPrefetchSize`	Affects the default prefetch size which is especially important for mass data retrieval. In SQL Server there is usually no need to change that.
`db.default.pool.hibernate.dialect`	The corresponding dialect for your MSSQL version For MSSQL 2016: com.heiler.ppm.persistence.db.internal.dialect.SQLServer2016 For MSSQL 2014: com.heiler.ppm.persistence.db.internal.dialect.SQLServer2012
Database settings for Oracle (we only describe the default settings here. Most of those can be adjusted individually for each database schema as you will see in the server.properties template file. However, splitting the schemas on multiple database hosts/instances is not supported since there are cross schema sql statements which would not work!)
`db.default.type`	`ORACLE` Never change this property!
`db.default.database`	Oracle Service Name(SID)
`db.default.server`	The host name of the Oracle server; change this in case you have a separate database server.
`db.default.port`	Port of the Oracle instance, usually this is 1521
`db.default.password`	Password for the created schema users
`db.default.dir`	Base folder for the database schema and database transaction log files Note: This folder needs not to be local to the application server but to the database server!
`db.default.dir.data`	Folder for the database schema files
`db.default.dir.temp`	Folder for the database transaction log files
`db.default.dir.index`	Folder for the index tablespaces
`db.default.data.size`	Default size in MB allocated for a database schema; adapt this setting to your needs
`db.default.data.size.growth`	Default increment value in MB allocated when space for a database schema is insufficient; adapt this setting to your needs In a productive environment you should define the initial size of the database log files to the expected maximum. A data base growth action always "stops the world" of the database until the files are enlarged. In case the growth size is too small, this might occur very often which is a serious performance problem!
`db.default.temp.size`	Default size in MB allocated for a database transaction log file; adapt this setting to your needs
`db.default.temp.size.growth`	Default increment value in MB allocated when space a transaction log file is insufficient; adapt this setting to your needs In a productive environment you should define the initial size of the database log files to the expected maximum. A data base growth action always "stops the world" of the database until the files are enlarged. In case the growth size is too small, this might occur very often which is a serious performance problem!
`db.default.index.size`	Default size in MB allocated for an index tablespace; adapt this setting to your needs
`db.default.index.size.growth`	Default increment value in MB allocated when space for an index tablespace is insufficient; adapt this setting to your needs In a productive environment you should define the initial size of the database log files to the expected maximum. A data base growth action always "stops the world" of the database until the files are enlarged. In case the growth size is too small, this might occur very often which is a serious performance problem!
`db.default.schema.prefix`	The common prefix for all server schemas; it must be in capital, start with a latin character and must not contain any special characters except '_'! Note that the resulting tablespace name (prefix + <MAIN\|MASTER\|SUPPLIER> + suffix) must not be longer than 24 characters.
`db.default.schema.suffix`	The common suffix for all server schemas; it must be in capital, start with a latin character and must not contain any special characters except '_'! Note that the resulting tablespace name (prefix + <MAIN\|MASTER\|SUPPLIER> + suffix) must not be longer than 24 characters. This property is helpful to distinguish between productive and test schemas (e.g. `_PRO` and `_TEST`).
`db.default.debug.show_sql`	Generated SQL statements during runtime will be shown in the log file. This is a debugging feature which will slow down the application drastically.
`db.default.rowPrefetchSize`	Affects the default prefetch size which is especially important for mass data retrieval. This value might be modified in case you have a lot of memory. The oracle driver is allocating the complete, theoretically needed memory for a single round trip. In case you run into memory problems because of the Oracle database access, you might want to decrease this property. See also the How to enable Java Management Extensions (JMX).
`db.default.pool.hibernate.dialect`	The corresponding dialect for your Oracle version. Currently only one value, no need to change. com.heiler.ppm.persistence.db.internal.dialect.Oracle12c

Creating/Updating schemas - Microsoft SQL Server (GUI)

Start the database setup with a double click on Database.exe file. The wizard will open
Select the server.properties file which you created before
In case you want to create/update an Oracle database, you will have to provide the credentials of a user which has DB Admin rights. For example, the SYSTEM user.
After clicking Proceed setup you will get an overview of some settings of the server.properties file you selected.
By clicking Proceed setup the database will be created/updated with the settings defined by the server.properties file
In case of an exception, the setup will be aborted and the cause will be shown. After resolving the problem, the setup can be executed again. It will continue at the point where it was aborted. This is achieved by some checkings against the database.
The log files contain all steps which where executed and in case of an exception the stack trace can be found here. If the exception is caused by a sql query, the exact query which was sent to the database will be logged too.

Creating/Updating schemas - Oracle (GUI)

Start the database setup with a double click on Database.exe file. The wizard will open.
Select the server.properties file which you created before.
In case you want to create/update an Oracle database, you will have to provide the credentials of a user which has DB Admin rights. For example, the SYSTEM user. You will also have to choose wether you want to use a TCP or a TCPS connection.
After clicking Proceed setup you will get an overview of some settings of the server.properties file you selected.
By clicking Proceed setup the database will be created/updated with the settings defined by the server.properties file
In case of an exception, the setup will be aborted and the cause will be shown. After resolving the problem, the setup can be executed again. It will continue at the point where it was aborted. This is achieved by some checkings against the database.
The log files contain all steps which where executed and in case of an exception the stack trace can be found here. If the exception is caused by a sql query, the exact query which was sent to the database will be logged too.

Creating/Updating schemas (Headless)

Open a console and navigate to the extracted package where the database.exe file is located
Execute the setup by entering 'database -application com.heiler.ppm.dbsetup.core.app -consoleLog -noExit <full path to server.properties>'
If you like to create/update an Oracle db, you have to specify also a user and password right after the server.properties file path perameter.
For a detailed information on the params simply enter 'database -application com.heiler.ppm.dbsetup.core.app -noExit help'

In case local policies do not allow the automatic creation of database schemas, or on case of a more complex setup of tablespaces, DBAs can create the empty schemas manually. They need to make sure that the configured users exist and have privileges on the schemas. The setup will recognize that the tablespaces / schemas already exist and will use them.

Troubleshooting

We can't guarantee that the schema setup will run without any issues in case of a migration of old schemas.

This has several reasons:

Existence of customer specific indexes -> These will break automatic extension / change of table structure if not removed prior to the database setup
Missing user rights of db user -> This will lead to a stopped execution of db setup
Some technical limitation which appears especially in cross schema modifications -> Can happen especially during migrations of schemas which are older than version 7

In case of an exception the setup will be stopped and the error shown. Try to solve the issue and restore your backup and execute the setup fresh. Please do not just restart the setup since depending on the error which occured we can not guarantee a consistent state of the database.