Database Setup

The configuration of 3^rd party software i.e. Operating Systems, Databases, Application Server, etc., in the context of this documentation is for illustration purposes only. iGrafx doesn't imply that the provided examples are the best or only way of configuration for the described scenario. Nor can we guarantee that it is the best option for performance and security. You apply the instructions at your own risk, please consult an expert of the 3^rd party software if you are not sure.

Oracle Databases are no longer supported

Oracle Databases are no longer supported in Platform version 17.7.0.756 and later. The last version to support Oracle Databases is 17.7.0.755.

Before you install the iGrafx platform, you need to do the following database setup tasks:

1. Install Microsoft SQL Server (or Oracle*).
2. Create the required databases.
3. Create and map a database user.

These steps are described in the following sections. Some have special requirements depending on the database server you use. The explanations provided here use Microsoft SQL Server as an example.

*The required Oracle scripts, without detailed explanation, are listed at the end of this article under Oracle Scripts.

Install SQL Server

The iGrafx platform runs on any version of Microsoft SQL Server listed on the System Requirements page. Please note that while iGrafx supports SQL Server, iGrafx does not provide technical support for SQL Server products themselves.

Installing SQL Server 2019

Before installing SQL Server 2019, check the system requirements for running it at microsoft.com.

The Microsoft SQL Server installer uses a wizard to prompt you through the installation steps. For setup to continue, you must click the Yes button to accept the license agreement when requested.

For more detailed installation instructions, system requirements, and additional information related to Microsoft SQL Server, see the Microsoft resources that are available on the microsoft.com website or in the Planning section of the SQL Server setup wizard.

To install SQL Server 2019, do the following:

1. Insert the Microsoft SQL Server 2019 DVD or mount the downloaded ISO image.

2. Run the setup.exe program.

3. Click System configuration checker. If the checker reports any issues, it is recommended that you fix the issues before continuing with the installation.

4. Click Installation in the navigation area on the left. The Installation dialog box appears.

5. Click New SQL Server stand-alone installation or add features to an existing installation.

6. Follow the prompts in the Microsoft SQL Server Setup wizard. Note the following iGrafx platform requirements (other configuration options can use the default settings or be configured as desired):

• On the Feature Selection page, select the following features:

• Database Engine Services
• Management Tools - Complete

• Documentation Components (optional).

  

  If you had previously installed an older version of the iGrafx platform, you might have previously installed the Full-Text and Semantic Extractions for Search also known as Full-Text-Index Engine (FTE) during this step. With iGrafx Platform versions after 16.2 the indexing is done by the application server and the FTE should not be installed anymore.

Create a Database

iGrafx supports Microsoft® SQL Server and up to and including 17.7, Oracle® servers. For SQL Server, the required steps are summarized above in Install SQL Server.

There are two different types of table structures required by the iGrafx platform: the administrative tables and the repository tables. These two structures can share the same database or can be separated into different databases.

You can also combine multiple repositories into one database. Having repositories share the same database is not recommended in an environment where you require backups of individual repositories, because if you need to roll back to a database backup and you have more than one repository in a database you will restore all of them at the same time even if no restore is required for the other repositories. The ideal scenario depends on your environment and backup requirements. Sharing the same database for multiple repositories is useful if you are not interested in individual backups and want to keep the number of databases as low as possible. Also, if you have only one repository, you may prefer having the administrative tables together with the repository to share the same backup and keep them consistent.

For the best performance and data security, keep the administrative tables and repository tables separate, and avoid having more than one repository per database. All backups should be made on the same schedule, to ensure consistency if a recovery is required.

To create a database, do the following:

1. Open Microsoft Server Management Studio.
2. Right-click on the Databases folder and select New Database.
3. Specify a database name.

4. On the Options tab, change the settings to meet your requirements. The recommended collation setting is SQL_Latin1_General_CP1_CI_AS. 

   

   If you choose a different collation, please note that a Case Insensitive (CI) collation is required.

5. Click OK to create the database.

Create a Database User

To connect the iGrafx platform application server with the database, you need either a local or directory (i.e. Active Directory) user. The roles or permissions required for this user depends on the database version you use.

If you use multiple databases, as described above, you can use the same or separate users depending on your needs.

Microsoft SQL JDBC Driver

The Microsoft SQL JDBC driver does not allow the iGrafx Platform to connect to the MS SQL database using a different directory user (i.e. Active Directory user) than the one being used to run the iGrafx Platform service. When configured to use JDBC and a directory user - the sign-in is pass through.

If you plan to use a directory user to connect the iGrafx Platform to the Database and/or Repositories - make sure you create a Database user for the user configured to run the iGrafx Platform service.

Failure to do so will result in the following error getting caught in the igrafx_server.log. "com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user"

SQL Server

To create a SQL Server user, do the following:

1. Open Microsoft Server Management Studio.
2. Right-click on the Security / Logins folder and select New Login.
3. Add a Windows or SQL Server authenticated user.
4. On the Server Role page, keep the public default role.
5. On the User Mapping page, map the user to the database and assign the following database roles:

• public
• db_owner

Or, alternately:

• public
• db_datareader
• db_datawriter

• db_ddladmin

• db_executor

You will then need to create the role db_executor since it does not exist by default:

-- Create a db_executor role
CREATE ROLE db_executor
-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

Oracle Scripts

The script provided below can only be taken as a suggestion, and will require adjustment to meet your corporate standards, system requirements, and performance needs. If you don't use roles make sure the user get the listed permissions through other mechanisms as they are required during the initial setup phase as well as during normal operation of the application server.

Note

In order to use the iGrafx Platform with an Oracle database, the database character set needs to be UTF8

The below script uses separate tablespaces for the administrative tables and repository tables. Technically this is not necessary and you can combine them into one space if you like, however it might be easier to keep them in separate tablespaces if you want to have different backup and retention policies. Also in case you only want to restore one repository without impacting others it may be easier to keep each repository in a separated tablespace as well. If you want to combine repositories into one tablespace you need to provide a table prefix when you add a repository which will be used to identify the tables per repository. The administrative tables use Adm as prefix for easier identification.

The Users created by the script are used to connect the iGrafx Platform application server to the database, no end user need to connect to the database directly. You can keep the database in a network segment unavailable for the end users but available for the iGrafx Platform application.

The actual administrative tables are created by the application during the initial setup. The tables for a repository will be created every time you add a repository. The database details and credentials can be different as described above and illustrated in the script below.

-- Administrative Tables
CREATE TABLESPACE IGRAFX_ADMIN datafile 'IGRAFX_ADMIN.dbf' size 64 M autoextend on next 32 M maxsize 512 M extent management local;
             
-- Repository Tables
CREATE TABLESPACE IGRAFX_REPO datafile 'IGRAFX_REPO.dbf' size 512 M autoextend on next 512 M maxsize 10240 M extent management local;
             
-- ROLE
CREATE ROLE IGRAFX_RL_OWNER;
GRANT CREATE SESSION,ALTER SESSION,CREATE TABLE,CREATE VIEW,CREATE SYNONYM,CREATE PROCEDURE,CREATE SEQUENCE,CREATE MATERIALIZED VIEW,CREATE TRIGGER TO IGRAFX_RL_OWNER;
             
-- USER for Admin tablespace
CREATE TEMPORARY TABLESPACE IGRAFX_ADMIN_TMP TEMPFILE 'IGRAFX_ADMIN_TMP.dbf' size 32 M autoextend on next 32 M maxsize 512 M extent management local uniform size 16 M;
CREATE USER IGRAFX_U_APP IDENTIFIED BY "iGrafxPassword" default tablespace IGRAFX_ADMIN quota unlimited on IGRAFX_ADMIN quota unlimited on IGRAFX_REPO temporary tablespace IGRAFX_ADMIN_TMP;
GRANT IGRAFX_RL_OWNER TO IGRAFX_U_APP;
             
-- USER for Repository tablespace
CREATE TEMPORARY TABLESPACE IGRAFX_REPO_TMP TEMPFILE 'IGRAFX_REPO_TMP.dbf' size 64 M autoextend on next 64 M maxsize 512 M extent management local uniform size 16 M;
CREATE USER IGRAFX_U_REPO IDENTIFIED BY "iGrafxPassword" default tablespace IGRAFX_REPO quota unlimited on IGRAFX_REPO temporary tablespace IGRAFX_REPO_TMP;
GRANT IGRAFX_RL_OWNER TO IGRAFX_U_REPO;
             
-- Profile considerations
CREATE PROFILE IGRAFX_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE IGRAFX_PROFILE LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER USER IGRAFX_U_APP PROFILE IGRAFX_PROFILE;
ALTER USER IGRAFX_U_REPO PROFILE IGRAFX_PROFILE;

For Oracle 12c, we recommend adding the following statement to the beginning of the script - as otherwise you may encounter script errors (the first of which being: SQL Error: ORA-65096: invalid common user or role name)

CODE

alter session set "_ORACLE_SCRIPT"=true;