Managing the Safran Database

Managing the Safran database

This chapter contains information on initiating a new Safran Project or Safran Planner database, upgrading your Safran database to the latest version, and using the database utilities and system objects features of Safran System Administration.

Note 1: Use collation "Latin_1_General_CI_AS" or "Danish_Norwegian_CI_AS" for the database.
Note 2: We highly recommend that regular database maintenance includes rebuilding indexes and updating statistics.

Initiating a Safran database

MS SQL Server

A user with a "db-owner" role needs to be used to create and update the Safran database.
The best practice is to create a user named "safransa." (The "safransa" user account is a user with special rights hardcoded into the system, enabling you to log into the Safran database without being defined.)
This ensures that the default schema is used. We also recommend that the "safransa" user be granted the View Server State to remove database locks automatically.
The database is initialised on SQL-Servers with a group named POWERPLAN, to which all objects will be granted later.
 
Setting up the Remove "Dead" database lock feature in MSS

1. Log in to the database using an admin account using an MSS Database management tool (e.g., MS SQL Server Management Studio)
2. Go to Security-Logins, right-click, and choose Properties for user Safransa.
3. Set focus on the Safran database and add the db_securityadmin from the list of database roles. Confirm by clicking ok.
4. Log in to the Safran System Administration Tool using the Safransa login.
5. Go to the Tools -> Password menu option and add the Safransa password here. It will be hashed/encrypted in the database. Save and close the Safran System Administration tool. The feature removing "dead" database locks is now active.

Oracle database server

If your DBMS is Oracle, you need to consider the tablespaces, rollback segments, and whether the indices should reside in a separate tablespace (recommended by Oracle).
You need to know the tablespaces' names before starting the installation. If these are anything but "safran_data" for the data and "safran_index" for the indices, you need to edit the SQL-command file named "initdb.sql" in the "ORA" sub-directory. You must replace "tablespace safran_data" and "tablespace safran_index" with the correct names, or, if indices are kept with the data, remove all "tablespace safran_index."
Before installing, you need a user called SAFRANSA, with at least CREATE TABLE, CREATE SYNONYM, and CREATE PROCEDURE privileges. Preferably, the SAFRANSA user should be granted DBA privilege. A default role called "SAFRAN" will be created during database initialisation, and all privileges ("all" on tables/synonyms, "execute" on procedures) will be granted to this role. All tables must be created with SAFRANSA as the owner, and corresponding synonyms will be created through which the application will access the tables ("Create public synonym activities for SAFRANSA.activities" will enable the application to access the activities table without referring to its owner).
Make sure that all files described in the previous chapter are installed.

Database initiation

After this preparation, you may now initiate the Safran database. Start the System Administrator (safransa.exe):

Log into the database as the system administrator (SAFRANSA in Oracle). (You will get a message informing you that the database is not configured correctly for version 3.x, which you already know. Click OK to proceed.)

Select "Tools -> Upgrade" from the menu.

• Click Browse, then check that the path suggested is to the correct database folder in the Safran Project install directory.
• Click Upgrade to start the database initiation. Database tables, Procedures, and Triggers will now be created and initialised.
• The next step is to add users to the database. (See Chapter - Adding users to the database)

Upgrade a Safran database to the latest version.

Note to Oracle users: Before upgrading, you need to know the names of the tablespaces. If these are anything but "safran_data" for the data and "safran_index" for the indices, you need to edit the SQL-command file named "initdb.sql" in the "ORA" sub-directory. You must replace "tablespace safran_data" and "tablespace safran_index" with the correct names, or, if indices are kept with the data, remove all "tablespace safran_index."

General upgrade procedure

Follow these instructions:
• Make sure that the current database is backed up before upgrading.
• Log into the database as "safransa" or with the dbo account.

Validate existing database

1. Validate the existing database from Tools -> Validate.
2. Make sure that the current database is backed up before upgrading.
3. Log into the database as "safransa" or with the dbo account.

4. From the Validate window, click Start and navigate to the validation scripts.
   (You will find the validation scripts in the Safran Project or the Safran Planner installation directory folder named Validation.)

5. Set focus on the Validation folder and click Start.

6. A successful validation returns a confirmation message. If there are issues, they will be listed and addressed before upgrading the database. If you are unsure, please contact Safran Support for advice.

Upgrade an existing database

1. Select Tools -> Upgrade from the menu.

2. Click browse and make sure that the path to the correct database files is selected.

3. Press Upgrade to start the upgrade process.
4. The following window will let you know if the upgrade was successful.
   (It is worth mentioning that the upgrade from some previous releases can take some time; you can follow the continuous progress in the database Upgrade window.)
5. If errors occur during the upgrade, an error log is produced, which contains details about the issue(s). Please get in touch with Safran Support if you need advice.

Pre- and Post Upgrade scripts

With the release of version 22.1, the Safran System Administration tool received a feature upgrade. The feature allows the database upgrade process to automatically handle bespoke database scripts before (pre) and after (post) running the standard database scripts. When no pre- or post-upgrade scripts are available, the database upgrade process works as before.

If an error occurs while running the pre- or post-upgrade scripts, you can find error information in the error log.

Recompile triggers and procedures

Using Safran Project System Administration to recompile triggers and procedures would be best.
Follow this instruction:

1. Log into the database as "safransa" or with the dbo account.
2. From the Tools menu, choose Upgrade.

3. Select the correct directory for the database scripts, and select Recompile Triggers and Procedures before you click Upgrade.

Database objects

Using the Safran System Administration tool, you can browse through different types of database objects; Tables with their definitions, Database views and definitions and view the database triggers and procedures stored in the database.
Choose Tools -> Objects. This will open the "Database objects" window.

All the lists and definitions are for information purposes only.

Database utilities

Execute SQL queries

You can access the "Database queries" window from Tools -> SQL.

You may enter SQL commands here. You get a multiline input field if you double-click on the input line.
Note CAREFUL: commands entered here are executed and committed immediately when you click enter or the Execute button. Be careful what you ask for because you will get that! If you did something by mistake, you must either fix it manually or restore it from the database backup.

Running external applications using the Azure AD user will be limited, as the user only has read access to one table.
However, running the Safran Project "SQL Query" option still allows users to perform additional SQL queries as in previous versions.

Run SQL command files

Access the "Run command file" window from Tools -> File.

You may browse and find the SQL command file you want to run.
You must also inform the system whether each command should be committed after execution (default) or all commits shall be done after execution of the document.
You must also specify the command delimiter used in the command file. The default is ";"

Click "Run" after finding the SQL command file you want to execute.
The system then executes the commands within the file, informing you of any (non-fatal) errors occurring during execution and giving you the options to continue or abort if errors occur.
When the operations are completed, the window gives you a message about the number of lines in the file.

See all current Safran users (MS SQL Server only)

You can see all active database connections by choosing Tools->Connections.

If you suspect that any Safran Project user hangs and that another process may cause this, you may select the "Show Blocked Processes Only"-option. This sets a filter on the users' list, showing only users waiting for a database lock to be released.
To identify the blocker, note the number under "Blocked by" and re-push the button (now renamed to "Show All Processes"). If the blocker is a Safran Project user, you can find him as a Process ID (If there are many users, pushing the "Process ID" header will sort the list on Process ID).
If the blocking process ID is not on the list, it does not belong to a Safran Project user.

Update database statistics

You may trigger an update of database statistics from Tools -> Statistics.

Select the tables you want to update from the "Update statistics" window and click ok. (Remember that updating statistics can also be configured as a scheduled task by the DBA in the DBMS. This is our recommendation.)

Rebuild indexes

You may trigger a rebuild of the database indexes from Tools -> Index.

Click Load in the "Rebuild index" window. This will populate the window with information for all indexes.
You may select the indexes you want to rebuild and click "Rebuild" to start the process. (Remember that rebuilding indexes can also be configured as a scheduled task by the DBA in the DBMS. This is our recommendation.)

Removing database duplicates

The Safran System Administration tool can clean up duplicate objects from the database. This is done from Tools -> Duplicates. There is functionality to prevent duplicates from entering the database through Safran Project or Safran Planner. Still, it could happen if other systems are configured with access to the Safran database.

A window named "Remove duplicates" is displayed after clicking Tools -> Duplicates. This window lists the tables checked for duplicates; the column "Duplicates" tells you how many duplicate values there are in the different tables. You can clean this by clicking "Remove." The system will then add dup1 and dup2 endings to duplicate values to make each value different.

Remove trailing blank characters

The Safran System Administration tool can remove trailing blank/space characters from names in the database values. The empty/space character isn't deleted; you choose which character will replace the blank/space. This is done from Tools->Trailing.
Note: Use with Caution. This is something other than what you would usually do. Feel free to contact Safran Support for advice if you are unsure about this feature. There is no undo feature here, so remember to back up before performing this function. Restoring a backup is the only way to return if the function is mistakenly used.

You need to select the replacement character from the drop-down list. Then, choose which tables you want to update before clicking clean. There is no undo feature here, so remember to back up before performing this function. Restoring a backup is the only way to return if the function was mistakenly used.