Adelia Studio can be used to create an environment in an existing database schema via the "Create an environment > in an existing schema or database" option in Adelia icons. The schema containing the tables must be created by the administrator before executing the tool. By default, Adelia uses the SYSADEL schema.

It is preferable for the database name to be in uppercase. If the database manager is case-sensitive for identifiers, it is mandatory.

Access to an environment created in a schema is provided by an expansion of the connection URL syntax. The URL is comprised of the name of the schema containing the environment, followed by the "/" character and the database connection information.

When a schema is specified, the environment name is comprised of the schema name followed by the database name. If no schema is specified, or in the case of the default SYSADEL schema, the environment name is the name of the database.

Special precautions

• Though the default SYSADEL schema can coexist with named schemas within a given database, this type of configuration should be avoided, particularly due to risks of confusion when running scripts designed for a conventional environment.
• Important: restoring a database replaces all environments defined in the database. To backup and/or restore a specific environment, we recommend importing / exporting schema data using the Adelia tools or the database management tools. The backup and restore options on an environment's Database management sub-menu are not enabled for the environments defined in a schema.

Constraints

• When the tables are created in an existing database, the database must comply with certain conditions (database code pages, specific rights):

DB2 database

The database must not be UNICODE type but use the system's ANSI code page - you need to specify "codeset" and "territory" parameters. For example, for a Western European country you can use the following command. We recommend increasing the number of secondary logs (LOGSECOND parameter at 50 or more) if you want to use the default configuration for logs (no archiving).

New schema creation is implicit. You do not need to create them manually.

Oracle database

The database must not be UNICODE type. The character set used must be compatible with the code page of Windows systems. For example, for a Western European country you must use the main code page WE8MSWIN1252. In the current implementation, the CASE does not use national types. However, we recommend that you configure the database to use the UTF-16 (AL16UTF16) character set for NCHAR/NVARCHAR types.

Adelia uses requests on certain system views to identify the real name of the database and server. You need to assign selection rights for V_$DATABASE, V_$SESSION and V_$VERSION system views to the CASE users (PUBLIC by default).

The schemas used must be created in advance when creating the tables via the "CREATE USER..." command, with at least the RESOURCE role (the schema owner does not need to be authorized to connect to the database if the tables are created by a database administrator). The rights on the schema must be assigned to the CASE users.

SQL Server database

We recommend that you create the database with a non-case-sensitive collation.

The schemas used must be created via the "CREATE SCHEMA..." command before the tables are created. The rights on the schema must be assigned to the CASE users.

MySQL database

We recommend (imperative on a UNIX server) that MySQL is configured so it is not case sensitive for identifiers ( "lower_case_table_names=1" parameter in the server configuration file). The problem does not arise if MySQL is installed on a Windows server.

We recommend that you use the UTF-8 character set for the server and databases (default settings).

Long SQL queries may be generated due to image types being supported. We recommend that you configure the MySQL client to authorize large network packets ("max_allowed_packet=64M" parameter) to avoid problems when saving layouts containing large images.

MySQL does not support the notion of schemas. The environments must be created in a database without indicating a schema (CREATE DATABASE command).

PostgreSQL database

We recommend that you use the UTF-8 character set for the databases. NB: we do not recommend using code page 1252. Support in PostgreSQL seems incomplete, which may cause conversion errors when using the Unicode ODBC driver (which is used by default, in preference to the ANSI driver if it is available).

The schemas used must be created via the "CREATE SCHEMA..." command before the tables are created. The rights on the schema must be assigned to the CASE users.
Caution: Adelia is case sensitive for the schema name and specifies the name in uppercase letters. The schema name must therefore be specified in uppercase letters and listed as a case-sensitive ANSI identifier (e.g. create "SYSADEL" schema).

• The schema name must be entered in uppercase characters and must not contain any spaces or special characters.

• Customized schema names containing a buffer (other than SYSADEL) must start with the prefix HST.

• If the Adelia license server is not installed on the same machine as the database server, the machine name must be specified in the "AUTHENT_SERVER" file key of the "Adeliws.ini" file on the machine running the creation scripts (for pre-entering the value when the database is created).

• The user used to create the tables must have database administration rights.

• Adelia does not provide any tools for deleting a database schema. You must de-catalog the Adelia icons schema then delete it manually using the tools provided by the concerned database management system.

Example: creating a version manager in an Oracle instance

This example details the configuration of an Oracle instance to create a version manager from an existing environment. The Oracle instance used is the default instance installed by Oracle (ORCL).

You must possess a user account with DBA rights for the creation of Adelia objects, along with SYSDBA rights for initial instance configuration.

The default system profile (system) is used in this example.

We shall convert a TEST environment to a version manager containing three environments. TESTEXP (production), TESTCOR (correction) et TESTDEV (development).

The different steps in creating the example are as follows:

1. Minimum Oracle instance configuration - definition of catalog rights

You must log on as SYSDBA to grant specific rights. To simplify the configuration process, we shall perform all operations directly on the server.

2. Create schemas for the environments

From this step onwards, you can work on the client workstation. Make sure that the ORCL instance has been cataloged under this name in the TNSNAMES.ORA file on the client workstation.

Note: These users do not require any specific rights. They do not need the CONNECT right either. Only the schema is required.

At this point in the example, you can also create Oracle users corresponding to the system profiles of the software engineers registered with the source TEST database.

3. Create the environment

From the Adelia Studio Administration menu, select Create environment/buffer > In an existing schema or database.

Select Oracle as the target DBMS, then enter the following connection parameters:

Wait while the wizard creates the tables. Once the environment has been created, use the Adelia import or copy tools to load the data from the TEST environment into the TESTEXP schema.

Note that you can create and import an environment into an earlier version using the migration scripts, in the same manner as with previous versions.

Optionally, to simplify the task of version manager configuration, the DBA profile used should also be registered as the software engineer in the environment. Since V12, however, this is no longer necessary. In this case the ADELIA software engineer is redefined on SYSTEM.

4. Create the correction environment

To create the correction environment, display the TESTEXP environment attributes, then select the Type tab.

Enter "TESTCOR/ORCL" as the name of the correction environment, click on Build and follow the wizard's instructions. Ensure that you specify the system profile in the database management tool's connection parameters:

5. Create the development environment

To create the development environment, display once more the TESTEXP environment attributes, then select the Type tab. Next, select Create from the environment list context-sensitive menu.

In the dialog box that is displayed, enter "TESTDEV/ORCL" as the development environment name, "Development environment" as the environment type, then click Create. Follow the wizard's instructions. Use the default connection parameters in the database management tool:

Version manager creation is complete. If you did not redefine the software engineer administrator at step 3, or if you have not yet created the Oracle users corresponding to the Adelia software engineers, then you must do so before you can connect to the environments.

With the exception of the Oracle-specific configuration operations, this step by step guide also applies to DB2 and SQL Server, under the following conditions:

• for SQL Server, you must also create the schemas and connect with a profile that has "db_owner" (or at the least "ddl_admin") rights for the database.
• for DB2, you simply need DBADM rights for the database.

↑ Top of page