# CHAPTER 8: Shared Memory

## 8.1 Introduction

The Shared Memory feature provides users the ability to enhance the performance of the Empress RDBMS through the use of the system shared memory.

Shared memory is an operating system component that allows several processes to share a specified section of memory. Normally, each process has its own memory space and cannot access the memory space of another process. By using shared memory to reduce the file I/O, the database performance can be improved.

Empress uses shared memory in three different ways to provide enhanced performance: lock managers, global buffers and mapped files.

  Figure 8-1: Empress Shared Memory

### 8.1.1 Lock Managers

Empress uses locking to guarantee the information is correct when an access is made to a database. Locking prevents two users (or processes) from modifying the same data at the same time. It also prevents data from being read while it is being modified.

Normally, the lock information is stored in files. Locks are heavily accessed during database operations. Since interaction with system memory is significantly faster than I/O operations, the database performance can be improved by placing the lock information in shared memory.

Lock Managers allow placing of certain lock information in shared memory where processes can access it. Empress also allows the Lock Manager type (shared memory versus file) to be defined on a table-by-table basis. That is, some tables may be defined to use shared memory and some may continue to use files for managing locks.

### 8.1.2 Global Buffers

Every time a process wishes to get data from a file, it must read that file. Global Buffers enable Empress to keep some or all parts of a file or many files in shared memory. This speeds up access and therefore increase performance.

Global Buffers are divided into blocks of memory. Depending on the file association to the Global Buffer, some or all of the blocks are assigned dynamically to parts of a file or files. If the file or files associated with a given Global Buffer contain more data than the Buffer can contain, then some of the blocks of the Buffer are overwritten with new data when required. This overwriting is managed by a least-recently-used (lru) algorithm. An lru algorithm assures that the Buffer block that was accessed least recently will be the one that is overwritten.

When data is written to a file that has a Global Buffer, the data is simultaneously written to the Global Buffer and the file. This is why the Global Buffers are called write-through buffers. This also means that the data in the Global Buffer will always reflect exactly the data in the file.

### 8.1.3 Mapped Files

Mapped Files are similar to Global Buffers in that they store data from files in shared memory. However, a Mapped File in shared memory has only one file associated with it. Also a Mapped File is not divided into blocks. Instead, it is one large block of memory directly associated with a given section of a file. This means that the Mapped File is essentially a window into a given file. The window can be the entire file, part of the file beginning at the start of the file, or a section of the file beginning and ending at user-defined points.

Mapped Files must always have a one-to-one relationship with the file to which they are mapped. Any attempt to map two or more files to the same Mapped File will be allowed but will cause errors when the files are accessed.

## 8.2 General Guidelines on the Usage of Shared Memory

Shared memory is a valuable resource on the system like many other resources. There is usually a trade-off between allocating large amounts of memory for shared memory in order to optimize performance of a database, and leaving sufficient memory available to the CPU and all processes to perform their tasks. Therefore, some general guidelines and suggestions are given below for the use of shared memory with a database:

1. Placing lock information in shared memory usually provides very significant performance gains. This is especially true if record-level locking or group-level locking is in place and the table is accessed frequently. Please note that locking for less frequently-accessed tables may still be done on disk.

2. Read-only or read-mostly files make the best candidates for placing in shared memory. When updates are made to a file which is mapped to shared memory, the in-memory image must be updated as well as the on-disk image. This is called write-through memory. Since an access to disk will always be required for updates to a file there are no gains to be made by using shared memory for updates, only losses incurred by the overhead of updating shared memory as well as disk.

   Prime candidates, therefore, are files which are frequently read or which do not change much. Tables used for referential checks or tables used for reference only might be considered for placement in memory.

   A read-to-write ratio greater than 50% may provide a rough guideline for consideration for placing the file in shared memory. The listing from `empadm database shminfo` shows the read ratio for both Mapped Files and Global Buffers.

3. The block size chosen for Mapped Files should not be smaller than the Empress block size for the system, usually 1024. This is because the block in memory will be smaller than the block of data that Empress must retrieve in each access, forcing Empress to go to disk each time even if part of the block is in shared memory.

4. If indices are used a lot then these can be placed in a Mapped File if the index is small enough (and not updated continuously) or in a Global Buffer if the index is large. If mapped to a Global Buffer, the section of the index needed will not always be in shared memory, but it may be available some of the time. The hit rate will be especially good if most users need a localized part of the index, such as the recent dates versus older dates.

5. This applies to files as well. The hit rate of large files in a Global Buffer can be improved if a particular part of the file is in use at one time, for example, the most recent additions to the end of a table.

   `sys_dictionary` table in

   Empress must be accessed each time any table is accessed, so all its files (main table, index and overflow file) should be considered for placement in a Global Buffer. The other Empress system tables are not normally placed in shared memory.

6. The use of shared memory on overflow files may be unpredictable since record lengths may vary a lot. The hit rate may be very low unless the Global Buffer is very large.

7. Do not place an index overflow file into shared memory without having the primary index file in as well. This is because access to the primary file is always required first in order to find the overflow segments. This is true even for an index configured with 0 bytes in the primary file.

8. The Global Buffer parameter `MSPARTGBUFMAXFILES` must always be chosen to be large enough to handle all files that may be required within that Global Buffer. When too many files are referenced within the Global Buffer, the system will fail. This is an important consideration especially when a default section is included in the parameter file. When a default is set up then all files associated with the table will be accessed through the Global Buffer. The Database Administrator must calculate what the maximum number of files can be, and set the variable accordingly.

9. In a network environment, shared memory should be installed on the machine where the database resides.

10. Statistics from the `empadm database shminfo` listing can provide some information for tuning shared memory.

    Within Mapped Files the ratio of logical reads to logical writes indicates the extent of reading versus writing that occurs on the file. If the percentage of reads is high, this file is a good candidate for placement in shared memory. The read ratio is calculated for both Global Buffers and Mapped Files.

    The read-hit ratio determines the percentage of reads that occur from the Mapped File or Global Buffer, not from disk. If this number is low the size of the Global Buffer or Mapped File should be increased, or the starting offset into the Mapped File should be changed, or shared memory should be removed from this file.

## 8.3 Reference Guide on Setting Up Shared Memory

This section contains a quick guidelines to define, create, configure and use shared memory partitions with associated database tables. An option is also provided by `empmkdb` to map a new database to shared memory using default shared memory partition. Please refer to section Mapping Default Shared Memory to Database.

Steps for setting up shared memory:

1. Define shared memory partitions by creating a partition file using the Empress utility `empshm`:

          empshm partitions partition_file

   This utility will prompt you for information, as the result, a file containing ASCII text shared memory partitions information is created. This partition file is a temporary file which will be manually appended to the end of `tabzero` using the system editor (Step 6). The shared memory partitions information should be resided in `tabzero`. This is just a tool to automatically generate the information without great deal of typing.

   Please refer to the section Defining Shared Memory Partitions for detail information and example.

2. If you want to place indices information in shared memory, they must all be named. If they are not named, you will have to drop and recreate them using the SQL `DROP INDEX` and `CREATE INDEX` commands.

   Please refer to Empress SQL: Reference manual under Data Definition Command:

   - `DISPLAY TABLE` - to check if index is named.
   - `DROP INDEX` - to drop index, if index is not named.
   - `CREATE INDEX` - to recreate index with name.

3. Create the parameter files which will map the file, indices and lock information for each table to particular shared memory partitions. This is done by running the following command at the operating system prompt:

          empshm params param_file

   Detail is described in the Defining Mapping Parameters to the Shared Memory section of this chapter.

4. Use the following command at the operating system prompt to ensure no other users (processes) are working in the database:

          empadm database_name coordinfo

   If no other users are working in the database clear all outstanding locks with the following command:

          empclrlk database_name

   `empclrlk` utility is described under the Manual Pages of this manual.

5. Now you are ready to create the shared memory partitions for the database but you must disable the database for other users. Issue the following command:

          empadm database_name dbamode

   This command will only allow Database Administrator (DBA) to access the database in single user mode. `empadm` utility is described under the Manual Pages of this manual.

   Note that you can not reverse step 4 and 5 since `empclrlk` will clear all locks including the lock for the `dbamode`.

6. Append the partition file(s) to the end of `tabzero` by using the system editor. Several partition files describing different shared memory segments may be attached to a `tabzero`.

7. Create the shared memory partitions by:

          empadm database_name shmcreate

8. Confirm that shared memory partition(s) were created correctly with:

          empadm database_name shminfo

   or using the UNIX command:

          ipcs -m

9. All mappings can be made except for a mapping which places `sys_dictionary` lock file in memory. If this line:

          MSPARTLOCKNAME=lock_manager_name

   where `lock_manager_name` is a Lock Manager name, appears in the parameter file for `sys_dictionary`, it should be commented out by placing a "`:`" in the first character position of the line.

   An example of a parameter file for `sys_dictionary` with the line commented out is provided below:

          MSPARTGBUFNAME=gbuf2

          MSPARAMLOCK
          :  MSPARTLOCKNAME=lockmgr2
          MSPARAMLOCKEND

   If `sys_dictionary` table is one of the tables being mapped to shared memory then its parameter file must be appended to the end of `tabzero`. This is necessary so that access to `sys_dictionary` itself can be made. System editor is used to update the `tabzero` and the information must be presented in the following order:

   - inserting the line `MSDBDICTPARAMS`,
   - inserting the contents of the `sys_dictionary` parameter file, and
   - inserting the line `MSDBDICTPARAMSEND`.

   For example:

          MSDBDICTPARAMS
             MSPARTGBUFNAME=gbuf2

             MSPARAMLOCK
             MSPARAMLOCKEND
          MSDBDICTPARAMSEND

   If `sys_dictionary` lock file is also intended to place in memory, then the example above should look like:

          MSDBDICTPARAMS
             MSPARTGBUFNAME=gbuf2

             MSPARAMLOCK
          :        MSPARTLOCKNAME=lockmgr2
             MSPARAMLOCKEND
          MSDBDICTPARAMSEND

   Please note that if `sys_dictionary` locking has been commented out during the "mapping" phase, it must remain commented out in `tabzero` until later (Step 13).

10. Map the parameter files to appropriate tables with the command:

           empadm database_name params param_file table {table}

11. Verify that the associations were made correctly within the database by using the SQL command:

           DISPLAY table ALL;

    This command will show the shared memory information associated with that table.

12. Bring the database back up by logging out of `dbamode`.

13. If `sys_dictionary` locking should be handled in shared memory then uncomment the line that was commented out in step 9, i.e. in parameter file or in `tabzero` if applicable.

    Then the new mapping of `sys_dictionary` to shared memory to include locking, can be made as follows:

           empadm database params param_file sys_dictionary

    The mapping of `sys_dictionary` locking cannot be done in the `dbamode` is because the `dbamode` has the effect of placing a lock on `sys_dictionary`. If while in `dbamode` the association of this locking is modified, then the unlock of the database cannot occur correctly. For this reason the mapping of all database files and locks to shared memory are done in `dbamose`, except for those relating to `sys_dictionary`.

14. The process of setting up shared memory is now complete and the database is ready for the uses.

15. The shared memory usage status can be checked by issuing the following command at the operating system prompt:

    |  |  | `empadm database_name` | `|shminfo |shmstats` | `| |`

    Should a problem arise, the shared memory partitions can be cleared and reset to their initial state using the command:

           empadm database_name shmclear

    To remove a shared memory partition, make sure there is no Empress activity on the database, then run the command:

           empadm database_name shmremove

    This command must be run if the shared memory definitions in `tabzero` are to be changed and before the `tabzero` is modified.

## 8.4 Defining Shared Memory Partitions

Shared Memory is divided into partitions. There are three type of partitions: Lock Manager, Global Buffer, and Mapped File. There can be any number of each type in shared memory but it is not necessary that all types be there. For example, shared memory could contain just one Lock Manager partition or it could contain three Lock Manager partitions, two Global Buffers and four Mapped Files. The only limitation on the number and size of each partition is the amount of memory in the system available for shared memory.

A partition file has a specific layout which is set up by `empshm` utility automatically. The example below shows how this utility works. User response is illustrated in the bold face.

In this example, a shared memory segment will be defined which will contain two Global Buffers, two Mapped Files and a Lock Manager. The command to create the partition file is:

       empshm partitions part_file

and `empshm` will prompt for the following information:

       Enter your key number: 123456

       Is this partition to be used by sys_dictionary? n

       What type of partition do you want?
          LOCK for Lock Manager
          GBUF for Global Buffer
          MAPF for Mapped Files
          END to finish
       >   LOCK

       What is the partition name? Lock1

       What is the maximum number of processes that will access
       this Lock Manager? 64

       What is the maximum number of locks handled by this
       Lock Manager? 500

             MSPARTNAME=Lock1
             MSPARTTYPE=LOCK
                   MSPARTLOCKNLOCKS=500
             MSPARTEND

       Is this information correct?   yes

       What type of partition do you want?
             LOCK for Lock Manager
             GBUF for Global Buffer
             MAPF for Mapped Files
             END to finish
       >   GBUF

       What is the partition name? Global1

       What is the number of files using this Global Buffer? 15

       What is the block size (in multiples of 512 bytes) in
       this Global Buffer? 2

       What is the number of blocks in this Global Buffer? 20

             MSPARTNAME=Global1
             MSPARTTYPE=GBUF
                   MSPARTGBUFMAXFILES=15
                   MSPARTGBUFBLOCKSIZE=1024
                   MSPARTGBUFNBLOCKS=20
             MSPARTEND

       Is this information correct?
       yes

       What type of partition do you want?
             LOCK for Lock Manager
             GBUF for Global Buffer
             MAPF for Mapped Files
             END to finish
       >   GBUF

       What is the partition name? Global2

       What is the number of files using this Global Buffer? 15

       What is the block size (in multiples of 512 bytes) in
       this Global Buffer? 2

       What is the number of blocks in this Global Buffer? 20

             MSPARTNAME=Global2
             MSPARTTYPE=GBUF
                   MSPARTGBUFMAXFILES=15
             MSPARTGBUFBLOCKSIZE=1024
             MSPARTGBUFNBLOCKS=20
       MSPARTEND

       Is this information correct?
       yes

       What type of partition do you want?
                 LOCK for Lock Manager
                 GBUF for Global Buffer
                 MAPF for Mapped Files
                 END to finish
       >   MAPF

       What is the partition name? Mapped1

       What is the size (in multiples of 512 bytes) of this
       Mapped File? 2

       What is the starting offset for this Mapped File? 0
             MSPARTNAME=Mapped1
             MSPARTTYPE=MAPF
                   MSPARTMAPFSTART=0
                   MSPARTMAPFSIZE=1024
       MSPARTEND

       Is this information correct?
       yes

       What type of partition do you want?
             LOCK for Lock Manager
             GBUF for Global Buffer
             MAPF for Mapped Files
             END to finish
       >   MAPF

       What is the partition name? Mapped2

       What is the size (in multiples of 512 bytes) of this
       Mapped File? 2

       What is the starting offset for this Mapped File? 0

             MSPARTNAME=Mapped2
             MSPARTTYPE=MAPF
                   MSPARTMAPFSTART=0
                   MSPARTMAPFSIZE=1024
             MSPARTEND

       Is this information correct?
       yes

       What type of partition do you want?
             LOCK for Lock Manager
             GBUF for Global Buffer
             MAPF for Mapped Files
             END to finish
       >   END

A file called `part_file` is created which contains the following information:

       MSSHMKEY=123456
       MSSHMPROTECTION=

             MSPARTNAME=Lock1
             MSPARTTYPE=LOCK
                   MSPARTLOCKNLOCKS=500
             MSPARTEND

             MSPARTNAME=Global1
             MSPARTTYPE=GBUF
                   MSPARTGBUFMAXFILES=15
                   MSPARTGBUFBLOCKSIZE=1024
                   MSPARTGBUFNBLOCKS=20
             MSPARTEND

             MSPARTNAME=Global2
             MSPARTTYPE=GBUF
                   MSPARTGBUFMAXFILES=15
                   MSPARTGBUFBLOCKSIZE=1024
                   MSPARTGBUFNBLOCKS=20
             MSPARTEND

             MSPARTNAME=Mapped1
             MSPARTTYPE=MAPF
                   MSPARTMAPFSTART=0
                   MSPARTMAPFSIZE=1024
             MSPARTEND

             MSPARTNAME=Mapped2
             MSPARTTYPE=MAPF
                   MSPARTMAPFSTART=0
                   MSPARTMAPFSIZE=1024
             MSPARTEND

       MSSHMEND

The format of the partition file is as follow:

         Shared Memory Key
         Partition for Data Dictionary Indicator

            Partition Name -----------------------v
            Lock Manager Partition                |
               Maximum Processes                  |
               Number of Locks                    |
            End Lock Manager                      |
                                                  |
                  .                               |
                  . (zero or more)                |
                  .                               |
                                                  |
            Partition Name                        |  Partition
            Global Buffer Partition               |      Definitions
               File Maximum                       |
               Block Size                         |
               Number of blocks                   |
            End Global Buffer                     |
                                                  |
                  .                               |
                  . (zero or more)                |
                  .                               |
                                                  |
            Partition Name                        |
            Mapped File Partition                 |
               Offset                             |
               Size                               |
            End Mapped File      -----------------^

         End Shared Memory Definition Indicator

The contents of the file is discussed below.

### 8.4.1 Shared Memory Key

The first entry in the partition file is the key. The key is a user-defined number that the system uses to identify a given shared memory block. This number is arbitrary, but each site should have a procedure to assign keys. All databases that want to use the same shared memory block must use the same key to identify it. It looks like this:

       MSSHMKEY=123456

### 8.4.2 Partition for Data Dictionary Indicator

Next comes the Empress variable `MSSHMPROTECTION`. If this partition is used by `sys_dictionary`, this should be set to "`on`" to get special protection on the shared memory partition. If it is not used by `sys_dictionary` then this variable will be unset. It looks like this:

       MSSHMPROTECTION=on

### 8.4.3 Partition Definitions

There can be any number of partitions in any order. All the partition definitions have the same first two lines. They are name and type, in that order. The partition name is any string of characters whose length is less than 32 (any more will be truncated). A partition name from the example looks like this:

       MSPARTNAME=Lock1
       MSPARTNAME=Global1
       MSPARTNAME=Global2
       MSPARTNAME=Mapped1
       MSPARTNAME=Mapped2

`empshm partitions` will calculate the size of a partition automatically. There are three partition types that can be chosen: `LOCK`, `GBUF`, `MAPF` for Lock Manager, Global Buffer, and Mapped File respectively. Partition type from the example looks like:

       MSPARTTYPE=LOCK
       MSPARTTYPE=GBUF
       MSPARTTYPE=GBUF
       MSPARTTYPE=MAPF
       MSPARTTYPE=MAPF

Each partition type has unique parameters that must be defined:

#### 8.4.3.1 Parameter for Lock Manager

The Lock Manager parameter is: number of locks.

The number of locks is the maximum that this Lock Manager can handle. If there are no more locks available, an error message will be displayed and the process halted. The rule of thumb for determining this number is that it should be proportional to the number of database users and proportional to the total number of actively used records in the database. The entry looks like this:

       MSPARTLOCKNLOCKS=500

#### 8.4.3.2 Parameters for Global Buffers

The parameters unique to a Global Buffer are: maximum number of files, block size and number of blocks.

The file maximum is the ceiling on the number of files that can be accessed using this Global Buffer. The entries for both `Global1` and `Global2` in the example look like this:

       MSPARTGBUFMAXFILES=15

The block size determines the size of each block in the Global Buffer. It should be a multiple of the file system block size for optimal input and output. Multiples of 512 bytes are a good starting point. The entries for both `Global1` and `Global2` in the example look like this:

       MSPARTGBUFBLOCKSIZE=1024

The block number is the number of blocks in this Global Buffer. It should be chosen so that the most actively used data is always in the Global Buffer. This parameter will probably require tuning. The shared memory administrative program, `empadm`, will provide statistics to assist in the tuning. The entries for both `Global1` and `Global2` in the example look like this:

       MSPARTGBUFNBLOCKS=20

#### 8.4.3.3 Parameters for Mapped Files

A Mapped File partition has two unique parameters: starting offset and size. The starting offset is the offset from the start of the file where the mapping will begin (i.e., the start of the file window). The entries for both `Mapped1` and `Mapped2` in the example look like this:

       MSPARTMAPFSTART=0

The size should be chosen to hold the most often used data, or if the file is small enough, perhaps even the entire file. The entries for both `Mapped1` and `Mapped2` in the example look like this:

       MSPARTMAPFSIZE=1024

#### 8.4.3.4 End Partition Definitions

The end of each partition is indicated by the line:

       MSPARTEND

### 8.4.4 End Shared Memory Definition Indicator

The end of shared memory definition is indicated by the line:

       MSSHMEND

## 8.5 Defining Mapping Parameters to the Shared Memory

After shared memory has been defined and the segment created, the files and locks from the database must be associated to the various shared memory partitions. A mapping parameter file must be created to hold details of how one table will map to shared memory. Each table in the database that maps to shared memory will have a mapping parameter file. However, it is not necessary that each table have a unique mapping parameter file.

It is possible to use the same mapping parameter file for different tables in a database, but the one-to-one association between a Mapped File and an actual database file must be maintained.

The mapping parameter file can be generated automatically using the utility `empshm`. An example is provided below.

This example will use the partitions defined in the previous example to set up a parameter file. The command to generate the parameter file is:

       empshm params param_file

and `empshm` will prompt for the following information:

       Do you want to use Global Buffers for all the files in
       this table? yes

       Enter the name of the Global Buffer partition: Global1

       Do you want to specifically associate the main data file
       to a shared memory partition? yes

       Enter MAPF for a mapped file or GBUF for a global buffer: GBUF
       Enter the name of the 'global buffer' partition: Global2

       Do you want to specifically associate any variable-length data overflow
       files to a shared memory partition? yes

       What is the name of the attribute? attribute_1

       Enter MAPF for a mapped file or GBUF for a global buffer: GBUF

       Enter the name of the 'global buffer' partition: Global2

       Do you want to enter another attribute name? no

       Do you want to specifically associate any index files to a shared
       memory partition? yes

       What is the name of the index to be accessed? index_1

       Enter PRIMARY for a primary index, OVERFLOW for an
       overflow index, or BOTH for primary and overflow: PRIMARY

       Enter MAPF for a mapped file or GBUF for a global
       buffer: MAPF

       Enter the name of the 'mapped' partition: Mapped1

       Do you want to specifically associate an overflow index to a shared
       memory partition? yes

       Enter MAPF for a mapped file or GBUF for a global buffer: MAPF

       Enter the name of the 'mapped' partition: Mapped2

       Do you want to enter another index name? no

       Are there any Lock Manager Partitions defined in shared
       memory? yes

       What is the partition name of the Lock Manager? Lock1

A file called `param_file` is created which contains the following information:

       MSPARTGBUFNAME=Global1

       MSPARAMTABLE
             MSPARTGBUFNAME=Global2
       MSPARAMTABLEND

       MSPARAMATTRNAME=attribute_1
             MSPARTGBUFNAME=Global2
       MSPARAMATTRNAMEND

       MSPARAMINDEXNAME=index_1
             MSPARAMINDEXPRIMARY
                   MSPARTMAPFNAME=Mapped1
             MSPARAMINDEXPRIMARYEND
             MSPARAMINDEXOVERFLOW
                   MSPARTMAPFNAME= Mapped2
             MSPARAMINDEXOVERFLOWEND
       MSPARAMINDEXNAMEND

       MSPARAMLOCK
             MSPARTLOCKNAME= Lock1
       MSPARAMLOCKEND

File can be divided into five parts: default mapping, mapping of main data (`.rel`) file, mapping of the variable-length data overflow (`.dtf`) files, mapping of the index (`.ix` and `.ixl`) files and mapping of the lock (`.lck`) files. None of these sections are mandatory. However, the order is essential and at least one section must be included. There can be multiple tables, variable-length data overflow and index files. However, only one default mapping and one mapping for the lock files can exist in a given parameter file.

### 8.5.1 Define Default Mapping

You can map all main data file, index files and overflow files that are associate to the table into one Global Buffer as default. This is to respond `yes` to the prompt:

       Do you want to use Global Buffers for all the files in
       this table? yes

       Enter the name of the Global Buffer partition: Global1

If this is all you intended to do, answer `no` to the rest of the prompt. The result mapping parameter file will look like this:

       MSPARTGBUFNAME=Global1

       MSPARAMLOCK
       MSPARAMLOCKEND

This will have all the files associate to the table in the shared memory except lock files. If Lock Manager partition is defined and you wish to include locking in the shared memory, then respond `yes` to the following prompt:

       Are there any Lock Manager Partitions defined in shared
       memory? yes

       What is the partition name of the Lock Manager? Lock1

Then you will have all files (including locking) that are associate to the table in the shared memory. The mapping parameter file will look like this:

       MSPARTGBUFNAME=Global1

       MSPARAMLOCK
           MSPARTLOCKNAME=Lock1
       MSPARAMLOCKEND

### 8.5.2 Define Mapping of Main Data File

Whether you choose to use the default mapping or not, you can specifically assign the association between the main data file (all data in the table without the variable-length, ie. `TEXT` or `BULK`, overflow data) and the shared memory partition (either Global Buffer or Mapped File). This will override the default setting of the main data file. For example:

       Do you want to use Global Buffers for all the files in
       this table? yes

       Enter the name of the Global Buffer partition: Global1

       Do you want to specifically associate the main data file
       to a shared memory partition? yes

       Enter MAPF for a mapped file or GBUF for a global buffer: GBUF
       Enter the name of the 'global' partition: Global2

       Do you want to specifically associate any variable-length data overflow
       files to a shared memory partition? no

       Do you want to specifically associate any index files to a shared
       memory partition? no

       Are there any Lock Manager Partitions defined in shared
       memory? yes

       What is the partition name of the Lock Manager? Lock1

This produces the following mapping parameter file:

       MSPARTGBUFNAME=Global1

       MSPARAMTABLE
           MSPARTGBUFNAME=Global2
       MSPARAMTABLEND

       MSPARAMLOCK
           MSPARTLOCKNAME=Lock1
       MSPARAMLOCKEND

This defines the main data file of the table in `Global2` Global Buffer partition, lock file in the `Lock1` Lock Manager partition and the rest of the files, if exist, (such as index files and variable-length data overflow files) in the `Global1` Global Buffer partition.

### 8.5.3 Define Mapping of the Variable-Length Data Overflow Files

Whether you choose to use the default mapping or not, you can specifically assign the association between the variable-length, ie. `TEXT` or `BULK`, data overflow file and the shared memory partition (either Global Buffer or Mapped File). This will override the default setting of the main data file. For example:

       Do you want to use Global Buffers for all the files in
       this table? yes

       Enter the name of the Global Buffer partition: Global1

       Do you want to specifically associate the main data file
       to a shared memory partition? no

       Do you want to specifically associate any variable-length data overflow
       files to a shared memory partition? yes

       What is the name of the attribute? attribute_1

       Enter MAPF for a mapped file or GBUF for a global buffer: GBUF

       Enter the name of the 'global' partition: Global2

       Do you want to enter another attribute name? no

       Do you want to specifically associate any index files to a shared
       memory partition? no

       Are there any Lock Manager Partitions defined in shared
       memory? yes

       What is the partition name of the Lock Manager? Lock1

This produces the following mapping parameter file:

       MSPARTGBUFNAME=Global1

       MSPARAMATTRNAME=attribute_1
           MSPARTGBUFNAME=Global2
       MSPARAMATTRNAMEND

       MSPARAMLOCK
           MSPARTLOCKNAME=Lock1
       MSPARAMLOCKEND

This defines the variable-length data overflow file of the attribute name `attribute_1` in `Global2` Global Buffer partition, lock file in the `Lock1` Lock Manager partition and the rest of the files (such as main data file and any index files) in the `Global1` Global Buffer partition.

### 8.5.4 Define Mapping of the Index Files

Whether you choose to use the default mapping or not, you can specifically assign the association between the index file and the shared memory partition (either Global Buffer or Mapped File). This will override the default setting of the main data file.

The index overflow file (`.ixl`) can share the same Global Buffer, or each can have its own association to a Global Buffer or Mapped File. Indices must be named to use this feature.

However you should be aware that if Mapped Files are used to access index files, it is possible for the index file to grow so that all parts of the file can no longer fit into the associated Mapped File.

For example, to assign both primary and overflow index to the same Global Buffer, use an entry like this:

       MSPARAMINDEXNAME=index_1
           MSPARTGBUFNAME= Global1
       MSPARAMINDEXNAMEND

To assign just the primary index to a Global Buffer use an entry like this:

       MSPARAMINDEXNAME=index_1
           MSPARAMINDEXPRIMARY
               MSPARTGBUFNAME=Global1
           MSPARAMINDEXPRIMARYEND
       MSPARAMINDEXNAMEND

To assign just the primary index to a Mapped File use an entry like this:

       MSPARAMINDEXNAME=index_1
           MSPARAMINDEXPRIMARY
               MSPARTMAPFNAME=Mapped1
           MSPARAMINDEXPRIMARYEND
       MSPARAMINDEXNAMEND

To assign just the overflow index to a Global Buffer use an entry like this:

       MSPARAMINDEXNAME=index_1
           MSPARAMINDEXOVERFLOW
               MSPARTGBUFNAME=Global1
           MSPARAMINDEXOVERFLOWEND
       MSPARAMINDEXNAMEND

To assign just the overflow index to a Mapped File use an entry like this:

       MSPARAMINDEXNAME=index_1
           MSPARAMINDEXOVERFLOW
               MSPARTMAPFNAME=Mapped1
           MSPARAMINDEXOVERFLOWEND
       MSPARAMINDEXNAMEND

Both primary and overflow indices can have it's own specific associations:

       MSPARAMINDEXNAME=index_1
           MSPARAMINDEXPRIMARY
               MSPARTGBUFNAME=Global1
           MSPARAMINDEXPRIMARYEND
           MSPARAMINDEXOVERFLOW
               MSPARTMAPFNAME=Mapped1
           MSPARAMINDEXOVERFLOWEND
       MSPARAMINDEXNAMEND

### 8.5.5 Define Mapping of the Lock Files

Regardless default mapping, if Lock Manager partition is defined and you wish to have locking of the table in the shared memory, you need to respond "`yes`" to the following prompt:

       Are there any Lock Manager Partitions defined in shared
       memory? yes

       What is the partition name of the Lock Manager? Lock1

The mapping parameter file will look like this:

       MSPARAMLOCK
           MSPARTLOCKNAME=Lock1
       MSPARAMLOCKEND

If default mapping is used and you do not wish to use shared memory to handle locking, an empty lock section must be included in the parameter file. For example:

       MSPARTGBUFNAME=Global1

       MSPARAMLOCK
       MSPARAMLOCKEND

## 8.6 Managing Shared Memory - empadm

Empress provides utility, `empadm`, to help the Database Administrator to manage shared memory. It is used for:

- creation of the shared memory partitions,
- mapping database files and locks to shared memory,
- removing shared memory,
- clearing shared memory contents selectively based on process ID, or in full,
- monitoring shared memory performance, and
- gathering statistical information on the usage of shared memory.

This utility is also used for:

- disabling the database for other users, and
- clearing locks.

### 8.6.1 Disable the Database

The command syntax for temporarily disabling user access to the database is:

       empadm database singlemode

This command can only be issued by the Database Administrator (DBA) and will place a database lock so that no one other than a single DBA process can access the database. After the lock is placed, another shell process is brought up. The shell is the one specified in the Empress variable `MSSHELL`. The Database Administrator should perform all necessary work on the database and on the shared memory configuration while in this shell. Any work that requires a quiet database such as backups or table alters can be done.

The database is re-enabled again by simply logging out of the `singlemode` shell process. The database lock will be removed.

`singlemode` should not be invoked when modifications will be made to the `sys_dictionary` locking. This is because `singlemode` disables the database by placing a lock on `sys_dictionary`. If the association for `sys_dictionary` locking is changed or if all locks on the database are cleared, then upon return from `singlemode`, the database lock cannot be released correctly.

If there are `sys_dictionary` locks that require clearing or if the association for its locking is changed in any way, this work must take place outside of `singlemode`. The Database Administrator must manually confirm that no other users are in the database while this work is in progress.

### 8.6.2 Create Shared Memory

The command syntax for creating shared memory partitions from `tabzero` is:

       empadm database shmcreate

Only the Database Administrator can run this command.

The definition of shared memory partitions is created by the utility `empshm partitions`. This produces a partition file which is appended to the `tabzero` file of the database. The partition information will provide details of each shared memory partition such as name, type (Global Buffer, Lock Manager or Mapped File), size, and other characteristics of the partition. There may be several partition files attached to the `tabzero` file.

The starting address of the shared memory partition can be provided in the partition file. A range of addresses (ex: 8000000 - 18000000) can also be given. In any case the system will attempt to attach at the exact address given or the high address of the range. If the attachment cannot be made there, the system will automatically decrement the address by 1 MB each time it fails thereby moving down the address space until a successful attachment is made or the range of free memory space is exhausted.

The permissions on the shared memory segments are set to allow read and write permissions to everyone. This may change in future versions.

The partitions will be created in shared memory as specified. They are not yet mapped to any database files. This is done with the "`empadm database params`" option.

Each database must have its own partitions set up in shared memory. This command therefore must be run on each database separately with unique partition information.

Please note that you will need to run this command again after a system crash since shared memory establishment will no longer exist after system crash.

To check that the shared memory partition was created as expected, the user can run "`empadm database shminfo`" or can use the standard UNIX command "`ipcs -m -a`" to list shared memory segments.

### 8.6.3 Mapping Database Files and Locks to Shared Memory

The command syntax for mapping database files and locks to specific shared memory partitions is:

       empadm database params param_file table {table}

To run this command the user must have update privilege on `sys_dictionary` and `sys_tables` and must be the Database Administrator.

This command will associate specific files (such as the main index file for example) or all files (main data file, overflow files, index files) and locks to specific partitions in memory. This must be done for each table which will map to shared memory or which will have locking in shared memory. The parameter file is created by the utility `empshm params`.

The mappings can be verified by using the SQL command:

       DISPLAY TABLE table_name ALL;

The parameters are stored in the data dictionary for each table. They can be easily changed by re-applying the `empadm` command with a different parameter file. The mapping to shared memory can be removed by using a parameter file which contains only a space or linefeed.

If `sys_dictionary` is mapped to shared memory, its parameter file must be placed in `tabzero` surrounded by the following delimiters:

       MSDBDICTPARAMS
            :
            : (copy the full parameter file for sys_dictionary here)
            :
       MSDBDICTPARAMSEND

This is required because `sys_dictionary` table contains information about all user tables in the database. Empress will access `sys_dictionary` before any other table in the database for information. Since `sys_dictionary` itself is a database table, all locking, indexing and shared memory features are applicable to this table. `tabzero` is an ASCII text file which contains information about `sys_dictionary` so that Empress will know to access index file, shared memory, ... etc. before accessing the `sys_dictionary` table.

Please note also that all table mappings other than that for locking on `sys_dictionary` should take place within `dbamode` while the database is temporarily disabled.

### 8.6.4 Database Locking and Shared Memory Information

The command syntax for listing the database locking and shared memory information is:

|  |  | `empadm database` | `|lockbrief |lockmangers |lockactive |lockinfo |shminfo |shmstats` | `| | | | | |`

These commands can be run on a database that is currently in use.

The `lockinfo` option will produce a full listing of all locks currently held within the database, whether in shared memory or on file. The table name, type of lock, record number, status (read, update, etc.) and Empress ID of the process will be given for each lock. A summary of all users holding locks, along with their user name, process ID and host ID is also given.

The other lock listing provide shorter reports than `lockinfo` does. The `lockactive` report lists only all active locks. The `lockbrief` report lists all active locks plus active process information. The `lockmanagers` report lists all Lock Manager information plus all active locks.

The `shminfo` option provides basic details about the set up parameters of the partitions. The name, type of partition and location are provided. For Global Buffers, the size is given as well as the read hit ratio (percentage of reads that are done in shared memory without the need to go to disk) and the read ratio (percentage of reads to writes). For Mapped Files, the size and start address is given along with the read hit ratio and the read ratio. For Lock Managers, the size details are provided.

The `shmstats` option shows configuration details and raw statistics gathered. For Global Buffers, the `DIN` (disk in to shared memory), `DOUT` (shared memory out to disk), `PIN` (process in to shared memory) and `POUT` (shared memory out to process) figures are displayed. For Mapped Files, the statistics for read from shared memory, read from disk, write to shared memory, and write to disk are given.

The `shmstats` option also provides lock information including the number of processes acceding the lock partition, the maximum number of processes, the current number of locks, the maximum number of locks, and an indication of the efficiency of the hashing algorithm used for the Lock Manager. The number of hits on each has bucket is gathered. It is grouped on the basis of the number of hits. For each number of hits, the number of hash buckets having the same number of hits and the bucket number is printed. Also, the number of buckets with a hit percentage greater than 60% is printed. This will indicate whether the hashing function is evenly distributed or not. The maximum chain length for each hash bucket is gathered and grouped by chain length.

### 8.6.5 Clearing Locks

The command syntax for clearing locks is:

       empadm database lockclear [empress_id {empress_id}]

This command requires that the user have read access to `sys_dictionary`. Only the Database Administrator can invoke this command without a list of Empress IDs. If a list of Empress IDs is given, the user must be either the Database Administrator or the owner of the processes attached to the IDs.

The `lockclear` option will clear locks whether they are stored in Lock Manager partitions in shared memory or on disk. If a list of Empress IDs is supplied, only the locks pertaining to those processes will be removed. If a list of Empress IDs is not supplied, all locks in shared memory and on disk for that database will be cleared.

The Empress ID is obtained by running the `lockinfo` option.

Please note that the Empress variable `MSSHMPROTECTION` does not prevent the commands "`empadm database lockclear`" or `empclrlk` from removing locks in that partition. This is in contrast to the `empadm` option `shmremove` and `shmclear` which will be prevented from clearing partitions that have this variable set.

Since locks cannot be protected, and because a database lock is required when in `dbamode`, the commands `empadm database lockclear` and `empclrlk` should not be used while in `dbamode`.

### 8.6.6 Clearing Shared Memory

The command syntax for clearing shared memory is:

       empadm database shmclear

The user must have read access to `sys_dictionary` and must be the Database Administrator.

The `shmclear` option will clear information in all shared memory partitions. This command is similar to `empadm database shmremove` followed by `empadm database shmcreate`. The `shmclear` option is useful when a program corrupts memory.

The variable `MSSHMPROTECTION` can protect shared memory partitions from the `shmclear` option. If this variable is set both `shmremove` and `shmclear` cannot touch the partition. This variable should be set to protect partitions which reference `sys_dictionary`. If this variable is set, the option `shmrmall` must be used to remove the partition.

### 8.6.7 Resetting Lock and Shared Memory Statistics Information

The command syntax for resetting lock and shared memory statics information is:

|  |  | `empadm database` | `|lockrststats |shmrststats` | `| |`

The user must have read access to `sys_dictionary` and must be Database Administrator.

The `lockrststats` option will clear all information gathered so far regarding locks. (The gathered lock statistics are viewed using the `shmstats` option.) Statistics will begin to be gathered again after resetting.

The `shmrststats` option will clear information gathered for Mapped Files and Global Buffers. This option will not reinitialize the shared memory partitions itself, just the statistics associated with them. After running for some time, the new statistics can be viewed by running the `empadm` with the `shmstats` option.

### 8.6.8 Removing Shared Memory

The command syntax for removing shared memory partitions is:

|  |  | `empadm database` | `|shmremove |shmrmall |shmzap` | `| | |`

Only the Database Administrator can run these commands.

Since the shared memory segment is created with permissions much like a file, the user must have write permission on the shared memory segment in order to delete it.

Once the command has run, the shared memory segment is removed completely. That address space as well as unique partition IDs can be reused.

Before using the database again the partition information in `tabzero` must be completely removed. Otherwise, Empress will still attempt to use shared memory with the database.

The Empress variable `MSSHMPROTECTION` is provided in `tabzero` to prevent inadvertent removal of crucial shared memory partitions. If it is set to anything, the `shmremove` option will not remove those shared memory partitions; if it is unset, then `shmremove` will remove the partitions. It is recommended that this variable be set for partitions that map the table `sys_dictionary` to shared memory.

The option `shmrmall` is used to remove all partitions regardless of `MSSHMPROTECTION`. Please note that the Database Administrator should not be in `dbamode` when issuing `shmrmall`.

The option `shmzap` will remove all shared memory segments, even if the variable `MSSHMPROTECTION` is set. This includes the partition information in `tabzero` of all shared memory definitions. The database will be left without any connection to shared memory.

## 8.7 Mapping Default Shared Memory to Databases

An option is provided by `empmkdb` to map a new database to shared memory. It will append a shared memory partition file to `tabzero` file which will in turn map all locks to a shared memory lock partition, map the coordinator to a shared memory Mapped File and map all tables to a shared memory Global Buffer. The usage is as follows:

       empmkdb -shmem shm_key database_name

The shared memory key, `shm_key`, must be provided. This is a positive integer which uniquely identifies this shared memory block. Each database mapped to shared memory must have a unique shared memory key.

The partition file mapped by default to new databases is found in `$EMPRESSPATH/rdbms/custom/shmzero`. This file defines the Lock Manager, Mapped File and Global Buffer partitions that will be used.

If the default shared memory mapping is not desired, the user can either edit `$EMPRESSPATH/rdbms/custom/shmzero`, or can set the Empress variable `MSSHMZERO` to an alternate Mapping File.

Please be aware that the default shared memory mapping contains upper limits regarding the maximum number of simultaneous locks that can be supported (2000), and the maximum number of database files (500). If either of these limits is exceeded the database will produce error messages, and it will be necessary to remove the partitions and map larger ones to shared memory.

Shared memory cannot be added to a database with `empmkdb` once the database already exists. The command `emprmdb` will automatically remove shared memory partitions when the database is removed.