Database Setup¶

For most tasks relating to the setup of the database, the IFS PSO Installer may be used as it will automatically detect, and then perform, the required actions. (For completeness, we also provide details of how to manually run the database scripts in this section.) Further details pertaining to creating, updating or upgrading your installation may be found later in this guide. This section details configuration settings for the database server which will host the IFS PSO database(s).

Database Physical Structure - SQL Server¶

Ideally the databases should reside on their own dedicated server. The database must not be placed on the same server that the DSE runs on. If a dedicated database server is not possible then it is recommended that IFS PSO products has its own dedicated instance.

Filegroups¶

The databases may benefit from the use of file groups to allow placement of data files across multiple disc sub-systems. Ideally, there should be at least a number of file groups to allow the separation of the objects as follows:

Data files¶

Depending on the size of the databases and the underlying disc sub-systems it may be of benefit to use multiple data files to distribute the I/O within each file group. Generally, a data file per CPU core on the database server may be used. The exact number of files should be determined by discussion between the client's DBA and technical specialists from IFS. Data files should be pre-built to the expected size of the database and monitored rather than allowed to grow automatically.

TempDB data files¶

Whilst the IFS PSO services do not make heavy use of the database in line with general SQL Server recommendations the TempDB database should have multiple data files - one per CPU core.

Fill factor (scheduling database)¶

The Scheduling Database is designed to be 'additive'. That is to say rows are only added. Updated information is added again and the software determines the most up to date data via a versioning mechanism. There is no expansion of rows once inserted in to a table.

Rows are removed by the Scheduling Administration Service after a configured number of days. Because of the nature of the application it is highly desirable to have as many rows as possible in each data block. As such the fill factor should be set to 100%.

Collation¶

The services will treat the database data as case sensitive. The database should also be created case sensitive by using the 'SQL_Latin1_General_CP1_CS_AS' collation.

Parameter settings¶

None of the SQL Server parameters need to be changed from their defaults. Please consult with IFS technical specialists before making any changes.

Database Alterations¶

Alterations may be made to the default setup if it is identified that the change would be beneficial, and provided it does not alter the database structure. We advise that support should always be made aware of any changes from the default settings, and that these should be re-evaluated whenever the database is upgraded to ensure that they are still beneficial.

Recovery model¶

It is recommended that in the event of a failure of the database server a full 'LOAD' of data is provided to the Scheduling system. As such there is little need for transaction recovery and the SIMPLE recovery model can be used.

Permissions¶

There are essentially two levels of database access required by PSO: high-level access to create the database schema, and, application-level access that is required by the software during operation. For the former, the database user/logon will require the db_owner database role and you will need to use a logon with this level of permission access during an initial install to allow tables to be created. The application-level access can be more restrictive and can be limited to: db_reader, db_writer, db_executor and db_viewdatabasestate. Note that db_executor and db_viewdatabasestate are custom database roles that can be created using the following steps (when logged on as a user with db_owner privilege):

• Create the role: CREATE ROLE <role name> AUTHORIZATION dbo
• Grant required permissions. For db_executor and db_viewdatabasestate, the commands are, respectively: GRANT EXEC TO <role name> GRANT VIEW DATABASE STATE TO <role name>
• Add a user to the role: ALTER ROLE <role name> ADD MEMBER <user name>

With regards to setting up PSO for an on-premises installation and ensuring the appropriate level of access for installation and, subsequently, operation, the easiest approach would be to run through the installation twice, using the high-level database user on the first pass such that database actions can be performed. Then, on the second pass (which will not require any database actions as they have been completed in the first pass), switch the connection strings to include the application-level user.

Database Physical Structure - Oracle¶

Ideally the databases should reside on their own dedicated server. The database must not be placed on the same server that the DSE runs on. If a dedicated database server is not possible then it is recommended that the IFS PSO Services and Databases have their own dedicated instance.

Tablespaces¶

The databases may benefit from the use of multiple tablespaces to allow placement of data files across multiple disc sub-systems.

Ideally there should be sufficient tablespaces to allow the separation of the objects between the different Scheduling Databases as follows:

Tablespaces should be locally managed with a uniform segment size.

A temporary tablespace is required but this does not have to be significant as virtually all sorting is small enough to be done in memory.

Data files¶

Depending on the size of the Scheduling Databases and the underlying disc sub-systems it may be of benefit to use multiple data files to distribute the I/O within each tablespace. The exact number of files should be determined by discussion between the client's DBA and technical specialists from IFS. Data files should be pre-built to the expected size of the database and monitored rather than allowed to grow automatically.

Blocksize¶

The databases should be created with the largest blocksize that the platform supports (16K for Windows and up to 32K for Unix/Linux).

Storage parameters (scheduling database)¶

The scheduling database is designed to be 'additive'. That is to say rows are only added. Updated information is added again and the software determines the most up to date data via a versioning mechanism. There is no expansion of rows once inserted in to a table.

Rows are removed by the Administration Service after a configured number of days (see 'IFS Scheduling Administration Guide'). Because of the nature of the application it is highly desirable to have as many rows as possible in each data block. As such the PCTFREE should be set to 0 and PCTUSED to 99. INITRANS should be set to 5 and FREELISTS to 5 to reduce contention on blocks.

Parameter settings¶

Depending on the size of the implementation there are a number of changes required to the parameter settings. Please consult with IFS technical specialists before making any changes. In general the IFS PSO Services do not require a large 'shared pool', 'java pool', 'large pool'. It benefits from as large a buffer cache as can be accommodated.

Table Partitioning¶

If necessary for performance reasons partitions may be added to the following tables:

• LOAD_INFO
• INPUT_LOAD_STATUS
• PLAN_INFO
• PLAN_LOAD_STATUS
• DATASET

We advise that customers who wish to do this create a hash partition with the number of partitions matching the number of datasets in use. This can be done by appending the line below to the CREATE TABLE part of the database create script, where N is the number of datasets:

PARTITION BY HASH(organisation_id, dataset_id) PARTITIONS N;

For example to partition the LOAD_INFO table when using 10 datasets:

CREATE TABLE "LOAD_INFO" (
    "ORGANISATION_ID" NUMBER(10,0) NOT NULL,
    "DATASET_ID" NVARCHAR2(32) NOT NULL,
    "LAST_LOAD_INTERNAL_ID" NUMBER(10,0) NOT NULL,
    "LAST_CHANGE_INTERNAL_ID" NUMBER(10,0) NOT NULL,
    "MAX_INTERNAL_ID" NUMBER(10,0) NOT NULL,
    "LAST_UPDATE_DATETIME" TIMESTAMP NOT NULL)
  PARTITION BY HASH(organisation_id, dataset_id) PARTITIONS 10;

Database Alterations¶

Alterations may be made to the default setup if it is identified that the change would be beneficial, and provided it does not alter the database structure. We advise that support should always be made aware of any changes from the default settings, and that these should be re-evaluated whenever the database is upgraded to ensure that they are still beneficial.

In particular the following changes may be made:

• The parameter _optim_peek_user_binds may be set to False.
• On an Oracle RAC environment, the sequence cache size may be increased from the default of 20 on each of the trigger sequences. We advise a maximum of 100.

Archive logging¶

It is recommended that in the event of a failure of the database server a full 'LOAD' of data is provided to the IFS PSO system. As such there is little need for transaction recovery via archive logging.

Read-Only Tablespace (HTM)¶

As the HTM objects are written once then read the tablespace containing the HTM objects can be made 'read-only'.

Compression (HTM)¶

Depending on the geographic area in which the scheduling is to take place the HTM may be quite large. To reduce disc space the objects may be compressed. Whilst this incurs a CPU overhead when accessing the blocks this is outweighed by the increase in data held in the buffer cache.

Permissions¶

There are essentially two levels of database access required by PSO: high-level access to create the database schema, and, application-level access that is required by the software during operation. For the former, the easiest approach is for the user in the connection strings to have the DBA, CONNECT and RESOURCE roles, although this can be made more restrictive by removing the DBA role and instead assigning the "CREATE VIEW" system privilege to the user. The application-level access can be more restrictive and can be limited to the CONNECT and RESOURCE roles. Further details and examples of this approach can be found online (e.g. https://oracle-base.com/articles/misc/schema-owners-and-application-users).

With regards to setting up PSO for an on-premises installation and ensuring the appropriate level of access for installation and, subsequently, operation, the easiest approach would be to run through the installation twice, using the high-level database user on the first pass such that database actions can be performed. Then, on the second pass (which will not require any database actions as they have been completed in the first pass), switch the connection strings to include the application-level user.

Database Create Scripts¶

The scripts that need to be run during installation are included in the 'Database' sub-directory of the release folder. These are divided into further sub-directories, one for each database that may need to be created. The precise scripts that need to be run will depend on the function(s) that the system is being used for. See the 'Database Requirements' section of the Architecture and Sizing Guide for details.

The scripts for the various databases can, if desired, be run into a single database schema. However we would recommend as a minimum that the Data Archive database be kept separate.

The directory for each database will contain scripts for each supported database type (Oracle, SQL and SQL Azure). Choose the appropriate folder for your database, then select the 'Create' script (e.g. Scheduling System Database Create.sql).

The script assumes that an empty database has already been created for the script to run against. The script will then create all the necessary tables, keys, procedures and other objects that are required. An example is given below for creating the Scheduling System Database in SQL server.

SQL Server Example¶

This details the steps to create the Scheduling System database.

• Create a SQL server user for the services to run against (e.g. 'IFS_PSO'). This user should, as a minimum, be granted the bulkadmin server role.
• Create a database called 'Scheduling System Database' under the account created above.
• Execute the script 'Scheduling System Database Create.sql' located in the 'System' folder of the install set, on the new database.

Security Considerations¶

For enhanced security you may wish to use integrated security as follows:

• Create a dedicated IFS_PSO user that only runs the IFS PSO services.

• Ensure this IFS_PSO user has read/write privileges on the C:\IFS\Scheduling folder. By default if any of our services need to write any output it will go here.

  • If your system administrator makes any changes to the default input/output folders ensure the IFS_PSO user's rights are changed accordingly.

• Use Integrated Security on the DB.

  • This is an example of the connection string with integrated security: data source=localhost; initial catalog=Scheduling Database; Integrated Security=True
  • The Oracle connection string will include:Integrated Security = ""/""