MS SQL Server

Setting up the Database

Utilities

---

ORACLE

This guide includes the individual steps for setting up an Oracle databaseA database is an organized collection of data including relevant data structures. for AE usage.

Important: Refer to the relevant documents about optimizing the AE database performance before you set up the database. See:Configuration & Performance of the Database and the list of supported database versions.

Do not limit resource consumption. Aborting transactions due to limitations specified in the database can impede processing in the AE system. Additionally, inconsistent database contents can result, which can cause subsequent errors or endless loops.

It is important to remember that modifying database content result s in an inconsistent database.

A Automation Engine crash under Oracle is only reliably recognized if the Dead Connection Detection is activated. This is set with the entry SQLNET.EXPIRE_TIME in the SQLNET.ORA file to a maximum of 60 seconds.

Visit the Automic website where we provide whitepapers about Oracle usage for download. 

One effective way to maintain the AE database is to use partitioning with ILMStands for Information Lifecycle Management, which refers to a wide-ranging set of strategies for administering storage systems on computing devices. (Information Lifecycle Management). Automic recommends reading the document Maintaining the data records before you start setting up the database.

Code-Page setting

The code-page setting of the DB client must comply with the settings made in the database.

You can choose from either of the following three code pages, whichever best fits your needs:
WE8ISO8859P1, WE8ISO8859P15 and WE8MSWIN1252.

The choice depends on the characters you need to store in the database.
As WE8ISO8859P1 doesn't support the euro sign (€), WE8ISO8859P15 seems the better choice.
WE8MSWIN1252 supports not only the euro sign, but additional characters as well. So if you set your database up from scratch, this would be the recommended code page.
Please also refer to ORACLE's own support document no. 264294.1 dealing with the choice of code pages.

If you have your database already set up using WE8ISO8859P1 and you don't need the additional characters, there is no need to convert the database to a new character set.

Database settings can be queried as follows:

SELECT * FROM NLS_DATABASE_PARAMETERS

Specifying code-page setting:

Windows: Set HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\...\NLS_LANG in the registry according to the database setting.

UNIX: The environment variable NLS_LANG can be set as shown below:

NLS_LANG =<NLS_LANGUAGE>_<NLS_TERRITORY>.<CHARACTER SET>;export NLS_LANG

For example:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15;export NLS_LANG

Code-page settings can also be specified in the INI files of components (section [ODBC]).

Procedure

	1.	Requirements

• DB computer
• A 64-bit client must be installed.
• Install the appropriate ORACLE client software on the Automation Engine computer in order to enable access to the database. Note that the ORACLE-client version must correspond to the ORACLE version in use.
• Enter the database name in the file TNSNAMES.ORA.
• Change the block size to 8192 bytes when you install the database, and use the character set WE8ISO8859P15. Other character sets should only be used with prior approval of the Automic Support team.
• The package DBMS_LOB has to be installed.

Oracle UTF-8 and the Automation Engine

• The Automation Engine functions using ANSI/ASCII code and supports neither UniCode nor UTF-8. If the database needs to operate in UTF-8, the DB client must be configured accordingly, or data cannot be converted correctly. For jobs and job reports, AE converts data to ANSI/ASCII according to the deployed code tables and writes it to the AE Database without changes. AE never supplies UTF-8 format.
  
  The UserInterface functions using UTF-8, and converts data to ANSI/ASCII according to the specified code page before it is transported to the Server (see key XML_ENCODING in the variable UC_SYSTEM_SETTINGS). Only use other character sets in close cooperation with Automic Support.
  
  The database in UTF-8 only works correctly if the code pages of DB client and UserInterface correspond to each other. Otherwise, data cannot correctly be converted. In the Connect String, set the third digit to "1" in order to ignore the check for string data (otherwise the Automation Engine cannot boot). Logs provide information about the Oracle characters that have been converted to ? and are not available.
  
  The following requirements are critical:
  • The database must be set with UTF-8 and NLS_LENGTH_SEMANTICS = CHAR.
  • The database client must use WE8ISO8859P15. Define NLS_LENGTH_SEMANTICS = CHAR.
  • variable UC_SYSTEM_SETTINGS, key XML_ENCODING: Define the same code page as the one used by the database client.
  • INI-file parameter SQLDRIVERCONNECT= (section [ODBC]: Use the same settings for the Automation Engine and the utilities.

Oracle client with UTf-8: Note that a code conversion is required for each SQL statement.

RAC

• The parameter _lm_dd_interval must be set to <= 10 seconds. Value "0" can also be specified for this parameter in order to recognize deadlocks earlier.
• A database User ID for AE with sufficient authorizations is required.

	2.	Definition of tablespaces

• DB computer
• Automic recommends facilitating unlimited extensions (MAXEXTENTS) instead of limiting them.
• Tablespace sizes can be adjusted individually (minimum 1 GB UC4_DATA, 500 MB UC4_INDEX for a test environment).
• Tablespace administration by ASSM (Automatic Segment Space Management): SEGEMENT_SPACE_MANAGEMENT=AUTO.
• Log on to the ORACLE database using a user ID that has DBA rights, and define the tablespaces:

CREATE TABLESPACE UC4_DATA
DATAFILE 'Path to uc4_data.ora'
SIZE 1024M
AUTOEXTEND ON NEXT ???M MAXSIZE ????M
DEFAULT STORAGE (INITIAL 512K NEXT 2048K MAXEXTENTS UNLIMITED);

CREATE TABLESPACE UC4_INDEX
DATAFILE 'Path to uc4_index.ora'
SIZE 512M
AUTOEXTEND ON NEXT ??M MAXSIZE ???M
DEFAULT STORAGE (INITIAL 512K NEXT 512K MAXEXTENTS UNLIMITED);

•  Note that changing the name of the tablespaces (UC4_DATA or UC4_INDEX) requires that these names are also changed in the installation file UC_DDL.SQL. This also applies for every AE update.
• Automic strongly recommends using the tablespaces UC_INDEX and UC_DATA only, as otherwise you would have to manually adjust all SQL files during the upgrade process.

	3.	Creating a database user

• DB computer
• Automic recommends creating an additional database user for AE. This user must have the required privileges.

• DB computer
• Initial values should be increased as required for productive environments and large tables. This applies to the following tables:
  
  INI, MELD, ODOC, OT, AH, AJPP, AJPPA, RH and RT.

• Note that the installation file UC_DDL.SQL for the tables shown below must also be adjusted when the size is changed:

CREATE TABLE MELD (
MELD_Idnr INTEGER NOT NULL,
MELD_USR_Idnr INTEGER NOT NULL,
MELD_Seen SMALLINT NULL,
MELD_TimeStamp DATE NULL,
MELD_MsgNr INTEGER NULL,


MELD_MsgInsert VARCHAR2 (255) NULL,
MELD_DeleteFlag SMALLINT NULL,
MELD_Client SMALLINT NULL,
MELD_Source INTEGER NULL,
MELD_Category INTEGER NULL,
MELD_Type INTEGER NULL,
MELD_Host VARCHAR2 (200) NULL,
MELD_ArchiveFlag SMALLINT NULL,
CONSTRAINT PK_MELD PRIMARY KEY
(
MELD_Idnr
) USING INDEX TABLESPACE UC4_INDEX
) TABLESPACE UC4_DATA
STORAGE (INITIAL 51200K NEXT 7168K MAXEXTENTS UNLIMITED);

Automic recommends facilitating unlimited extensions (MAXEXTENTS) instead of limiting them.

	5.	Defining the roll-back segment

• DB computer
• Defining a large roll-back segment for the Automation Engine is strongly recommended. The size should be 10-25% of the Automation Engine data.