Appendix - RDBMS requirements and configuration

The following sub-chapters are dedicated to each supported RDBMS. In each of them you will find the following sections (if an adjustment is recommended by CAST):

• Memory Management
• Disk Management
• Privileges Management
• Case Sensitivity & collation management
• Tuning Management
• Other

Before going into details about these specific DBMS implementations, here are some general guidelines:

General

Collation compatibility between server hosting the Analysis Service and potential participating Servers

Characters are encoded in different ways called "character sets" - one example is "ASCII". Many different character sets exist. In the early 1990s, UNICODE was created to encode any character of any alphabet. It is impossible to directly compare a character string encoded in ASCII with another string encoded with UNICODE (for example), even if they contain the same elements orthographically. In fact the width of the encoding in UNICODE is double that used in ASCII and the notion of collation will provide the ability to resolve this problem.

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence. The influence of case (this is of particular relevance for Microsoft SQL Server/Sybase ASE: "CS" = Case Sensitive, "CI" = Case Insensitive), the influence of accented characters, kana character types and the sensitivity to storage width: ASCII/EBCDIC uses 2 bits and UNICODE uses 4 bits.

Case sensitivity sample

If A and a, B and b, etc. are treated in the same way then it is case insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of "A" is 65, while "a" is 97. The ASCII value of "B" is 66 and "b" is 98.

Accent sensitivity sample

If a and á, o and ó are treated in the same way, then it is accent insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of "a" is 97 and "á" is 225. The ASCII value of "o" is 111 and "ó" is 243.

Width sensitivity sample

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

How do the RDBMS manage collation and sort order?

• On Sybase ASE, case (in)sensitivity and more generally the collation is an all or nothing setting. It's a server wide parameter during the installation phase. Thus it applies to the data AND the repository objects together (name of tables, name of columns, stored procedures etc).
• On Microsoft SQLServer, case (in)sensitivity and more generally the collation is defined at server level but each database and column could have a different setting. We have here two notions: collation for a specific object (database or column) and default collation for the SQL Server instance (at least including the master, tempdb & model databases). In addition, similar to Sybase ASE, these apply to data AND repository objects.
• On Oracle Server and IBM DB2 Server data is always case sensitive. But for dictionary objects, both DBMS work in uppercase only, unless using "".
• On CAST Storage Service, data is always case sensitive.

CAST and collation/sort orders

Using the above list, when a Microsoft SQL Server is hosting the CAST databases or is acting as a Participating Server (for analysis) or when a Sybase ASE server is acting as a Participating Server (for analysis), you may well encounter some issues.

It is currently possible to analyze Microsoft SQL Server / Sybase ASE and store it in a CAST Analysis Service hosted on any supported RDBMS. However, in both of those environments, there are some limitations with regard to collation compatibility that are currently attached to the product as shown in the table below:

For example, with regard to case (CS,CI), there will be no link related problems if the entire set of analyzed code contains identifiers that use the same case:

select * from T ;
update T set x=2 ;

However, using another example, link resolution may fail during the analysis if the preceding condition is not respected. In the example code below, "t" and "T" represent the same object with regard to a participating server, but are identified as different objects with regard to the collation of the CAST Analysis Service.

select * from T ;
update t set X=2;

Check supported release

• You must make sure you are using a supported RDBMS. Some versions are no longer supported by CAST for Storage purposes. Please see the Release Notes for more information about this.
• You must make sure that you use a supported Client/Server API that it is correctly installed on the CAST Administration workstation. Please see the Release Notes for more information about this.

Check access to the network

• If your CAST Administration workstation does not physically host the RDBMS that will host the Storage item (i.e. a dedicated machine is being used), make sure the CAST Administration workstation can access the required network resources.

Oracle

Memory management

The following is a list of basic values for different memory areas in your Oracle Server. Please note that these are only recommendations and values may be different for your own particular RDBMS environment. In general the following recommendations are valid for machines configured as follows:

• With 4GB RAM and a machine that is not "dedicated to CAST" (i.e. other processes are running)
• With 2GB RAM on a machine that is "dedicated to CAST"

Pools

Pool memory (SGA and PGA) can be changed via OEM (Oracle Enterprise Manager) or via SQL scripts.

Modifications via Oracle Enterprise Manager (SGA and PGA)

If you have access to Oracle Enterprise Manager, use the following screenshots as examples for your own Pool Memory settings.

SGA

PGA

Modifications via SQL script

If you do not have access to Oracle Enterprise Manager, you can make Pool Memory changes using an SQL script command (i.e. via SQL*Plus or equivalent) as follows.

The basic syntax to use is as follows:

alter system set <parameter_name>='<value>' SCOPE=spfile;

So for example:

alter system set SHARED_POOL_SIZE='190M' SCOPE=spfile;
alter system set DB_CACHE_SIZE='570M' SCOPE=spfile;
alter system set LARGE_POOL_SIZE='15M' SCOPE=spfile;
alter system set JAVA_POOL_SIZE='20M' SCOPE=spfile;
alter system set PGA_AGGREGATE_TARGET='500M' SCOPE=spfile;

If you want to check the value of any of the above parameters prior to modification, use the following syntax:

show parameter <parameter_name>;

Sorts

Change via SQL*Plus:

Disk Management

Tablespace

When installing a new schema (via CAST Server Manager) on an Oracle Server specifically for CAST, it is necessary to specify tablespace. This can be existing tablespace (USERS, TEMP etc) on your Oracle server, or you can create dedicated tablespace for Data, Temporary, Tables and Indexes as required.

To create tablespace, use the CREATE TABLESPACE statement, for example:

CREATE TABLESPACE "CASTKB_DATA"
NOLOGGING
DATAFILE 'C:\ORACLE\ORADATA\WESLEY\CASTKB_DATA.ora' SIZE 500M
EXTENT MANAGEMENT LOCAL

The tablespace parameter in the command line must be unique. You can use any name, but CAST recommends using the same name as you choose for the new schema. For example, if your new schema is called "CASTTEST", then CAST recommends using: "CASTTEST_DATA", "CASTTEST_TABLES", "CASTTEST_INDEXES" etc.

To size your tablespace, use the following table as a very approximate guide (where objects = the number of objects stored in the CAST Management, Analysis, Dashboard and Measurement Services):

Undo Tablespace

During an analysis process, depending on the volume of analyzed code, the Undo tablespace (RollBack Segment) can sometimes exceed 3GB in size. Make sure that the undotbs parameters are set in such a way that the associated file can grow as needed (and that you also have enough space on disk).

In addition, before generating snapshots, it may be necessary to increase the size of the Undo tablespace (RollBack Segment) to approximately double that of the total amount of analyzed data stored in the CAST Analysis/Dashboard Services (although more than double can sometimes be required). If this adjustment is made, it must be done after the CAST System Views are updated, but before the snapshot is generated (in other words, after the analysis process - note that in the CAST Management Studio it is possible to run an analysis and then generate a snapshot as two separate functions, thus allowing you to adjust the Undo tablespace if required). The Undo tablespace value can be reset on completion of the snapshot generation process.

Archive Log option

The Archive Log option provides a means to trace changes made to the schema hosting the CAST Analysis Service. This operation produces a large amount of log data and consumes a great deal of CPU resources. As a result, performance could dramatically deteriorate. CAST therefore recommends that the Archive Log option is NOT activated when working with the CAST Analysis Service.

Privileges Management

Required user privileges

The following Oracle user privileges are required to carry out any server-side install procedure with CAST Server Manager (i.e.: the username/password combination specified in the CAST Server Manager Connection Profile (see Connection in Install CAST AIP schemas) that you used to connect to the server, must have the following privileges). If this is not the case, the installation procedure may fail.

• CREATE_USER
• ALTER_USER
• GRANT_ANY_PRIVILEGE
• GRANT_ANY_ROLE
• SELECT_ANY_DICTIONARY

Privileges granted to the user created during the installation process

CAST Server Manager grants the following privileges to the username created during the installation of a new schema for the CAST databases (CAST Management/Analysis/Dashboard/Measurement Services). For example, if you create and install a new schema called "CASTTEST", a user also called "CASTTEST" will be created and granted the Oracle system privileges listed below:

• CREATE_TABLE
• CREATE_PROCEDURE
• CREATE_SEQUENCE
• CREATE_SYNONYM
• CREATE_VIEW
• CREATE TRIGGER
• SELECT_ANY_DICTIONARY

Minimizing privileges for the user created during the installation process

The user created during the installation of a new schema is granted the SELECT_ANY_DICTIONARY privilege in order to query the V$SESSION data dictionary view. CAST uses this view to retrieve the session id of the user's connection for its internal locking mechanism to manage concurrent access to objects in the CAST Management/Analysis/Dashboard/Measurement Services.

If you do not want to give the SELECT_ANY_DICTIONARY privilege to the user created during the new schema installation process, you can revoke the privilege by executing the following query (where "CASTTEST" is replaced by the name of the schema that hosts the CAST Management, Analysis, Dashboard or Measurement Service):

grant select on sys.v_$session to CASTTEST

This must be done after installation of the CAST Management/Analysis/Dashboard/Measurement Services and only the SYSTEM user can grant this privilege.

Reasons for granting these privileges

The initial privileges...

• CREATE_TABLE
• CREATE_PROCEDURE
• CREATE_SEQUENCE
• CREATE_SYNONYM
• CREATE_VIEW
• CREATE TRIGGER

...are the minimum required privileges for a user designated as a schema owner.

For:

• SELECT_ANY_DICTIONARY

This privilege is granted to the owner of the new schema because it allows read access to certain Oracle system tables to:

• identify (read only) detailed server version information and other server properties
• list the current sessions (for user management and CAST locks)

Server connections

CAST Server Manager uses two specific connections:

• The first is the connection you define when you first launch CAST Server Manager - this grants access to the RDBMS and you typically need to use the system user or a user with DBA equivalent privileges.
• The second is a connection that uses the user name associated to the schema you are attempting to install the CAST Management/Analysis/Dashboard/Measurement Services on or the user name created by CAST Server Manager before the creation of a new schema. In this case, the privileges granted to the user name will be used.

All other operations are carried out using the user name associated to the schema you are attempting to install the CAST Management/Analysis/Dashboard/Measurement Services on. In this case, the privileges granted to the user name will be used.

Connecting to CAST Analysis/Dashboard Services with a user other than the schema owner or "system"

When manually creating connection profiles to a CAST Analysis/Dashboard Services for use with the CAST Management Studio, CAST Enlighten and CAST Report Center, if you use a user other than the Analysis/Dashboard Services owner or the "system" user with its own privileges, you may find that you cannot perform some actions within the end-user CAST application (typically generating a snapshot in the CAST Management Studio) and an "insufficient privileges" message is displayed in the log.

As such and given that the "public" user is automatically granted access to all CAST objects, an Oracle user that wants to connect to a schema that they do not own requires the following privileges:

• a user with correct rights to access their default schema
• CREATE ANY TABLE
• INSERT ANY TABLE
• DELETE ANY TABLE
• UPDATE ANY TABLE
• SELECT ANY TABLE
• SELECT ANY SEQUENCE
• DROP ANY TABLE
• CREATE ANY INDEX
• DROP ANY INDEX
• ANALYZE ANY

You can find out more details about login privileges for end-user applications in Appendix - Login privileges for CAST Products.

Other

Authentication error: ORA-12638

When using Oracle Server 10g and later to host the CAST Management, Analysis, Dashboard or Measurement Services, you should make sure that SQLNet authentication is set specifically for use with CAST. Locate the network\admin\sqlnet.ora file and make sure that the following line is as shown below:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Native vs Interpreted mode

CAST Products will run in either Native or Interpreted mode, provided that each mode is set up correctly on the Oracle instance that you are installing CAST databases on:

• If Native mode is deactivated (default), CAST Products will use Interpreted mode instead
• If Native mode is activated CAST Products will:
  • Install DML Objects using Native mode
  • Use Native version of PL/SQL objects

Check instance version

On an Oracle Server, each instance can run a different Oracle Server version. In order to check the version an instance is running, use the following command:

select comp_name, status, substr(version,1,10) as version from dba_registry

CURSOR_SHARING parameter

Please make sure that the CURSOR_SHARING parameter is set to "EXACT" on the Oracle Server hosting your CAST Management, Analysis or Dashboard Services. You can determine the value of the CURSOR_SHARING parameter by executing the following query:

select * from v$version;
select COMP_ID||' : '||COMP_NAME||' : '|| VERSION||' : ' ||STATUS||' : '|| MODIFIED Comp from dba_registry;
show parameter cursor_sharing;

If the value returned for the CURSOR_SHARING value is anything other than "EXACT", then please execute the following against the server:

alter system set cursor_sharing = EXACT scope = spfile;
alter system set cursor_sharing = EXACT;

Microsoft

Memory management

From a DBA perspective, it is better to use a fixed memory size rather than the dynamically adjusted memory size (which is the default). The recommended value is 75% of the physical memory of the server when the machine is a dedicated data server. Nothing else. If this is not the case, then you need to reduce this value as a compromise between each "intensive" process.

Disk management

Data and Log files

CAST recommends using different physical disk systems for the data and the log. However, system and tempdb databases should be on the same disk, which, in turn, should not be the disk used to store the CAST database. The tempdb file size growth must be specified in absolute values (i.e in megabytes, not percentage).

Transaction Log

Before calculating snapshots, it may be necessary to increase the size of the Transaction Log to approximately double that of the total amount of analyzed data stored in the Analysis/Dashboard Services (although more than double can sometimes be required). If this adjustment is made, it must be done after the CAST System Views are updated, but before the snapshot is calculated (in other words, after the analysis process - note that in the CAST Management Studio it is possible to run an analysis and then generate a snapshot as two separate functions, thus allowing you to adjust the Transaction Log value if required). The Transaction Log value can be reset on completion of the snapshot calculation process.

Privileges management

Installation Privileges

The following Microsoft SQL Server user privileges are required to carry out any install procedure with CAST Server Manager (i.e.: the username/password combination specified in the CAST Server Manager Connection Profile (see Manually creating a connection profile in Install CAST AIP schemas) that you used to connect to the server, must have the following privileges). If this is not the case, the installation procedure may fail.

• Login with the "sa" or "sysadmin" role (System Administrator)

Usage Privileges

When using any CAST products (CAST Enlighten etc.) in a Microsoft SQL Server environment make sure that the login has been granted the VIEW SERVER STATE privilege. This is not necessary if the "sa" or "sysadmin" logins are being used (not recommended), which already have this privilege.

This privilege grants access to the server's current process list via master..sysprocesses or via sys.dm_exec_requests. This information is required by CAST products for internal management of database locking and the analyzer save process.

To avoid product usage errors, users will be prevented from accessing the database if their login does not have access to the current process list.

Case sensitivity and collation

The following rules must be adhered to:

• The Microsoft SQL Server hosting the CAST Analysis Service SHOULD be case sensitive in order to analyze case sensitive languages (for example, Java, .NET etc.). If the server is case insensitive, different objects whose names only differ in terms of case (such as MyFunction and myfunction) will be merged into the same object
• The CAST Analysis Service must be installed in a database with a collation that is identical to the default collation of the DBMS server itself
• The collation of the CAST Analysis Service must be compatible with the collation of any Participating Servers (i.e. other DBMS servers that will be accessed for analysis purposes)