mmdb.ini File for Oracle Databases

[MM SQL]

MULTIDBS=ON

If this entry is set to ON, the parameters are first read from this section and applied. Otherwise, the parameters are read from the [MM ORA] section.

[MM ORA]

• MM_PWD_FILE=OFF

  If this entry is set to OFF, Aurea CRM uses the string entered under PASSWORD as the default value. If this entry is set to ON, a temporary password file is generated in the .. \system\sys directory.

• ID=MM

  This entry contains the name of the database user.

• PASSWORD=MM

  This entry contains the password of the database user.

• HOSTSTRING=MMDB

  This entry contains the name of the data source on the server. The maximum length is 127 characters.

• TABLESPACE_DBS=MMDAT

  This entry defines the Oracle table space for the data tables in Aurea CRM.

• TABLESPACE_IDX=MMIDX

  This entry defines the Oracle table space for the indices in Aurea CRM.

• TABLESPACE_SEL=MMSELDAT

  This entry defines the Oracle table space for the selection tables in Aurea CRM.

• TABLESPACE_SELIDX=MMSELIDX

  This entry defines the Oracle table space for the selection indices in Aurea CRM.

• TABLESPACE_RBS=RBS

  This entry defines the Oracle table space for rollback segments in Aurea CRM.

• TABLE_PREFIX=MMDB

  This entry contains the table prefix for the Aurea CRM tables.

  Note: This value must be in upper case and numbers must not be entered at the beginning. There is no default value, so this entry must contain a value.

• TIMEZONE=110

  This entry specifies the time zone used by the database. Transferred from the system and may not be changed.

• MM_NLS_LANGUAGE=AMERICAN

  This entry contains the Oracle database language information.

• MM_NLS_TERRITORY=AMERICA

  This entry contains the Oracle database country information.

• MM_NLS_CHARSET=WE8ISO8859P1

  This entry contains the character set information for the Oracle database. This value must correspond with the character set used by the Aurea CRM database and with the Oracle client's registry entry.

• MM_NLS_SORT=BINARY,GERMAN,CZECH,...

  This entry defines the Oracle database's available sort order(s) for the current session.

  The first value determines the default value and must always be specified. Additional values (such as GERMAN and CZECH in the example above) should only be entered if multi-language sorting is used, see Multi-language Sort.

• USE_NLSSORT=on

  Add this entry to Aurea CRM web's mmdb.ini file to enable linguistic sort (e.g. MM_NLS_SORT=GERMAN) for Aurea CRM web.

• USER_ROLLBACK_SIZE=100

  This entry defines the number of database blocks used by the selection rollback segment. If this value is set to "0", no user rollback segments are used.

• CASE_INSENSITIVE=ON

  If the database was installed on the server as case-insensitive, then this parameter should be used. Otherwise problems can occur when communicating data if the entries are stored in both upper and lower case. Additionally, the entry can lead to a performance increase as well (set to "ON").

• OPTIMIZER_GOAL=RULE/CHOOSE/ALL_ROWS/FIRST_ROWS

  OPTIMIZER_MODE = ALL_ROWS/FIRST_ROWS/ FIRST_ROWS_n

  This entry activates the optimizer for the current session. OPTIMIZER_MODE is used as the standard optimizer, see Optimizing Access.

• INDEX_MANDNR=ON

  This entry causes each generated index to include the tenant number (only applicable to info areas that include the <xx>-TenNo field).

  The default value is OFF.

• CACHE_READ_CURSOR=ON

  CACHE_WRITE_CURSOR=ON

  CACHE_CURSOR=ON

  Use these entries to enable cursor sharing. The default value for all three entries is OFF.

  CACHE_CURSOR toggles both modes (read and write).

• EXT_SELECTION_OLD=on

  Use this entry to interpret AND links in extended selection conditions as in earlier versions of marketing.manager.

  Behavior up to Version 6.0 Service Pack 5: If sub-criteria defined for a dependent info area are linked with a logical AND, the condition is only met, if at least one independent record meets the first sub-criteria, and at least one record meets the second sub-criteria.

  Current behavior (default setting): Without brackets, the condition is evaluated as before. However, if brackets are used, at least one dependent record needs to meet both sub-criteria.

• SELECT_INNER_JOIN=ON

  This entry determines that the INNER JOIN parameter is used when creating a selection from a base selection. This improves performance. The default value is OFF.

• SELECT_LEFTOUTERJOIN=OFF

  Disables the use of "left outer join" when reading records using a transfer fields format. The default setting is ON.

• SELECT_INNERJOIN=OFF

  Disables the use of INNER JOIN when reading records using a transfer fields format. The default setting is ON.

• AGGREGATE_CURRENCY=ON

  This entry ensures that sorting by sum total fields works correctly when using multiple currencies. The default setting is OFF.

• NO_CURRENCY=ON

  This entry ensures that sorting by sum total fields works correctly if you are not using multiple currencies. The default setting is OFF.

• COLLATE=OFF

  If different sort orders (collations) are defined in the database and configuration see ORACLE), the sort order entered in the configuration is used when evaluating conditions applied to string fields. Set COLLATE=OFF to use the database's sort order.

• ORDER_CATALOG_FUNCTION=OFF

  By default, when sorting records by catalog fields (e.g. "select Firma,Land,Ort from FI order by Land") Aurea CRM generates a more or less complex SQL statement without accessing the Catalog info area.

  Set ORDER_CATALOG_FUNCTION=ON to use the SQL function "prefix"_ CatText( ) instead. However, this sorting method is much slower because the function accesses the Catalog info area.

• ORACLE_NO_DECODE=1

  By default (ORACLE_NO_DECODE undefined or 0), when sorting data via indices and in descending order a DECODE( dummy,...) command is added to the SQL statement to allow for a correct result when additional sorting is used. This method impacts performance since a full table scan is executed.

  Add ORACLE_NO_DECODE=1 to Aurea CRM web's mmdb.ini file to prevent the generation of the DECODE( dummy,...) command. Please note that the query result may be incomplete and/or sorted incorrectly if this setting is active.

• ORACLE_NO_ROWNUM=1

  By default (ORACLE_NO_ROWNUM undefined or 0) query results displayed in lists may be sorted incorrectly. As documented by Oracle, Oracle applies the ROWNUM clause before the sorting, i.e. first the number of rows is limited and only the resulting rows are sorted.

  If you add ORACLE_NO_ROWNUM=1 to Aurea CRM web's mmdb.ini file the statement is generated without the ROWNUM clause. This ensures correct sorting of lists but leads to considerable performance loss.

  Note: Both NO_DECODE and NO_ROWNUM can be activated globally (in Aurea CRM web's mmdb.ini file) and per cursor (noOracleRownNum and NoOracleDecode).Activating NO_ROWNUM automatically activates NO_DECODE as well.

[Communication]

TimezoneTransformation=OFF

If this entry is set to "OFF", combined date and time fields (type: "date,time") are not converted to the target station's time zone when communicating between stations in different time zones.

The time stamps (modification date and time) are always converted to the target station's time zone when inputting communication data.

This setting only affects the input process. It has no effect on Aurea CRM web (conversion between the server and client).

Multi-language Sort

Aurea CRM supports unicode character sets and therefore the input of data using different codepages. Using multi-language sorts, each user can determine their own language-specific sort settings.

Types of Sort

• Data is sorted by field
  • by selecting Data > Sort from the menu
  • by clicking on the column header in a list
  • in the dialog box used to select catalog values

  The sort order for text fields is based on the Windows regional settings (Start > Settings > Control Panel > Regional and Language Options).

  

• Data is sorted by index
  • by selecting Data > Sort from the menu
  • when sorting in selections

  The sort order used for indices is defined in the index itself. The Oracle database parameter MM_NLS_SORT entered in the mmdb.ini file is used to construct the index. This allows all users to access the same sort independent of their regional settings.

In order to allow users of an Oracle database to use a language-specific sort order for indices, define the following sort order:

1. Enter all the sort orders you require additionally in the mmdb.ini file. These need to be "Linguistic Sorts" defined by Oracle (>> Oracle documentation). The first value must always be present and is used as the default value.

   Example: Aurea CRM installation using a central database with users in three countries (Austria, the Czech Republic and Russia).

   [MM ORA]

   MM_NLS_SORT=BINARY,CZECH,RUSSIAN,GERMAN

   Note: Each new index impacts negatively on the performance of the database. Consider whether using a "Multilingual Linguistic Sort" would also meet your requirements (see Oracle documentation). If you do need to add language-specific sorts, only add those that you definitely require.
2. When starting one of Aurea CRM's modules, a message is displayed informing you that the necessary indices need to be added ("ADD").
3. Switch to the SQL tool and click on the Rebuild NLS_SORT-Indexes to add the indices for the selected info area, see SQL Tool. Click on Rebuild ALL NLS_SORT-Indexes to add indices for all info areas.
   Note: Aurea recommends always using the Rebuild ALL NLS_SORT Indexes option.

   Only those indices are affected that access text fields.

   Note: To remove an index that is no longer required, delete the corresponding value from the MM_NLS_SORT parameter. When starting one of Aurea CRM's modules, a message is displayed informing you that the indices need to be deleted ("REMOVE"). Click on either the Rebuild NLS_SORT-Indexes or Rebuild ALL NLS_SORT-Indexes buttons in the SQL tool.
4. Assign a sort order to users and rights groups in the Configuration info area in the Rights module:

   Field	Contents
   Category	ORACLE
   Option	NLS_SORT
   Text	<Sort order>, e. g. ENGLISH
   Rep/Group	Enter the desired rep or group

   You cannot assign the sort order to a station in the Configuration info area. In order to assign a sort order to a station, define the MM_NLS_SORT parameter when adding the tables.

   If no sort order has been assigned to a user (neither individually nor for the user's group or station), the default value is used (i.e. the first value entered under MM_NLS_SORT).

   If a user is assigned a sort order for which no index exists, an error message is displayed when Aurea CRM is started. The user can proceed, but uses the default value instead.

Replacing NLS_SORT With a Custom Function

To replace all references to the built-in NLS_SORT function in Oracle with a custom function in the database:

1. Define your function externally. (The function is not part of Aurea CRM.)
2. To activate the function add the following entry to mmdb.ini file ([MM ORA] section):

   NLS_FUNCTION=...functionname<col>...<len>

   Replace functionname with the name of the external function.

   <col> is placeholder for the affected column(s)

   <len> is the (optional) placeholder for the column length

   Example:

   NLS_FUNCTION =cast(german_lower(<col>) as varchar2(<len>))

3. Make sure the following entry is present in the mmdb.ini file ([MM ORA] section):

   NLS_SORT=BINARY

4. Recreate all indexes via SQL Tool (Drop All Indexes, Create All Indexes).

   The statement

   (NLSSORT(UPPER(Firma),'NLS_SORT=GERMAN')

   is replaced with

   cast(german_lower(Firma) as varchar2(120))

[STORAGE PARAMETERS]

• STORAGE_DAT= storage( initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)

  This entry defines the extents' dimensions for new Oracle objects (tables, indices). It refers to objects in the table space that correspond to the value under TABLESPACE_DBS.

• STORAGE_IDX= storage( initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0)

  This entry defines the extents' dimensions for new Oracle objects (tables, indices). It refers to objects in the table space that correspond to the value under TABLESPACE_IDX.

• STORAGE_SELDAT= storage( initial 100k next 1M minextents 1 maxextents unlimited pctincrease 0)

  This entry defines the extents' dimensions for new Oracle objects (tables, indices). It refers to objects in the table space that correspond to the value under TABLESPACE_SELDAT.

• STORAGE_SELIDX= storage( initial 100k next 1M minextents 1 maxextents unlimited pctincrease 0)

  This entry defines the extents' dimensions for new Oracle objects (tables, indices). It refers to objects in the table space that correspond to the value under TABLESPACE_SELIDX.

• STORAGE_PCT_DAT=PCTFREE 20 PCTUSED 60

  These two parameters define the filling of the blocks in the table space that correspond with the value under TABLESPACE_DBS.

• STORAGE_PCT_IDX=PCTFREE 10

  These parameters define the filling of the blocks in the table space that correspond to the value under TABLESPACE_IDX.

• STORAGE_PCT_SELDAT=PCTFREE 5 PCTUSED 80

  These two parameters define the filling of the blocks in the table space that correspond with the value under TABLESPACE_SELDAT.

• STORAGE_PCT_SELIDX=PCTFREE 5

  These parameters define the filling of the blocks in the table space that correspond with the value under TABLESPACE_SELIDX.

[STORAGE]

• INITIAL=1M

  This entry concerns the dimensions of the extents for temporary rollback segments (selections, rebuilds). The initial size of the first extent is defined here.

• NEXT=1M

  This entry concerns the dimensions of the extents for temporary rollback segments (selections, rebuilds). The size of the subsequent extents is defined here.

• MINEXTENTS=10

  This entry concerns the dimensions of the extents for temporary rollback segments (selections, rebuilds). The minimum number of extents is defined here.

• MAXEXTENTS=UNLIMITED

  This entry concerns the dimensions of the extents for temporary rollback segments (selections, rebuilds). The maximum number of extents is defined here.

• PCTINCREASE=0

  This entry concerns the dimensions of the extents for temporary rollback segments (selections, rebuilds). The increase in size of the extents is defined here.

[LOCAL INDEX]