1  DEFINE_STORAGE_MAP
   Creates a storage map for a relation. A storage map associates
   a relation with a particular storage area or areas. The DEFINE
   STORAGE MAP statement allows you to specify:

   o  Which storage areas the records in a relation will be stored
      in

   o  Whether an index will be used to choose a target location for
      storing the record

   o  Whether data compression will be enabled when the records are
      stored

   o  Which storage areas segmented strings will be stored in

   Example:

   RDO>   DEFINE STORAGE MAP JOBS_MAP FOR JOBS
   cont>    STORE WITHIN JOBS
   cont>  END JOBS_MAP STORAGE MAP.
 

2  Format

 (B)0DEFINE STORAGE MAP qqqqqqqqqqq> map-name qqqqqqqqqk
 lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
 mqqqqqqwqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqqqqqqqqk
        mq> DESCRIPTION IS /*  text  */ qqj        x
 lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
 mq> FOR qqqqq> relation-map-clause qqqqqqqqqqqqqqqk
  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj
  mq> END qqwqqqqqqq>qqqqqqwqqwqqqqqqqq>qqqqqqqwq> .
            mq> map-name qqj  mq> STORAGE MAP qj

 

3  map-name
   Specifies the name for the storage map being defined.
 

3  text
   A text string that adds a comment to the storage map definition.
 

3  relation-map-clause

 (B)0relation-map-clause =
 
 qqwq> relation-name qqqq> RELATION qqwqqqqqqqk
   mq> SEGMENTED STRINGS qqqqqqqqqqqqqj       x
  lqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
  mqqwqwqq> map-storage-clause qqqqqqqqqqqqqqqqqqwqwq>
     x tqq> PLACEMENT VIA INDEX qq> index-name qqu x
     x mwqq> DISABLE qqwq> COMPRESSION qqqqqqqqqqj x
     x  mqq> ENABLE qqqj                           x
     mqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj

 

4  relation-name
   Specifies the relation to which the storage map will apply. The
   relation must already be defined and cannot have a storage map
   associated with it.
 

4  SEGMENTED_STRINGS
   Specified when you want to store segmented strings in multiple
   storage areas. Use the store-clause to specify the storage areas
   in which you wish to store the segmented strings.
 

4  map-storage-clause

 (B)0map-storage-clause =
 
 STORE qw> map-within-clause qqqqqqqqqqqqqqqqqqqqqqqqqqwqk
        m> USING qw> field-name qw> map-within-clause qj x
                  mqqqqq , <qqqqqj                       x
  lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
  mqwqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqq>
    m> default-threshold-clause qj

 

5  map-within-clause

 (B)0map-within-clause =
 
 WITHIN qwq> area-name qwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqwqk
         x              m> WITH LIMIT OF qw> literal qwj x
         x                                mqqqq , <qqqj  x
         xlqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
         xmwqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqwqwqk
         x mq> FOR qwqwqw> rel-name qwqqqqqqqqqqqqqqqu x x
         x          x x mqqqq , <qqqqj               x x x
         x          x mw> relation-name.field-name qwj x x
         x          x  mqqqqqqqqqqqq , <qqqqqqqqqqqqj  x x
         x          mqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqj x
         x lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj
         x mqwqqqqqqqqqq>qqqqqqqqqqwqqwqqqqqqqqq>
         x   mq> threshold-clause qj  x
         mqqqqqqqqqqqqq ; <qqqqqqqqqqqj

 

6  area-name
   The name of the storage area you want records stored in. You
   must have defined this storage area with either the DEFINE
   DATABASE statement or the DEFINE STORAGE AREA clause of the
   CHANGE DATABASE statement before you refer to it in the map-
   within-clause.
 

6  WITH_LIMIT_OF
   The maximum value for the index key that will reside in the
   specified storage area.

   The number of literals in this clause must be the less than or
   equal to the number of fields in the USING clause. Repeat this
   clause to partition the index entries among multiple storage
   areas.

   When you define a multisegmented index using multiple keys and
   use the STORE USING...WITH LIMITS clauses, if the values for the
   first key are all the same, then set the limit for the first key
   at that value. By doing this, you ensure that the value of the
   second key determines the storage area in which each record will
   be stored.

   Note that the last storage area you specify CANNOT have a WITH
   LIMIT OF clause associated with it.
 

6  rel-name
   The name of the relation whose segmented strings you want to
   store in the specified storage area. If you want to store the
   segmented strings of more than one relation in the storage area,
   separate the names of the relations with commas.
 

6  relation-name.field-name
   The name of the relation and segmented string field that you want
   to store in the specified storage area. If you want to store more
   than one segmented string field in the storage area, separate the
   list items with commas.
 

6  threshold-clause

 (B)0threshold-clause=
 
 qq> THRESHOLDS ARE qqqqqqqqqqk
  lqqqqqqqqqqqqq<qqqqqqqqqqqqqj
  mq> ( qq> val1 qwqqqqqqqqqqq>qqqqqqqqqqwq> ) qq>
                  mq> ,val2 qqwqqqqq>qqqqu
                              mq> ,val3 qj

   Specifies associated threshold values for each storage area with
   uniform format that is specified in the map-within-clause. By
   setting threshold values, you can make sure that Oracle Rdb does not
   overlook a page with sufficient space to store compressed data.

   The threshold values specify when the page is marked as FULL
   in the SPAM page free space inventory lists. For example,
   if you set default values of 70, 85, and 95 percent, ranges
   of guaranteed free space on each data page are 30, 15, and 5
   percent, respectively. If you do not set default values, the
   values are (0,0,0). With values of (0,0,0), Oracle Rdb will use the
   record length when setting the SPAM fullness. Oracle Rdb will never
   store a record on a page at threshold 3. The value you set for
   the highest threshold can be used to reserve space on the page
   for future record growth.

   If you specify a value of 40 for the "val1" parameter, but do not
   specify values for the "val2" or "val3" parameters, the threshold
   values will be set at (40,100,100).

   If you use data compression, you should use logical area
   thresholds to obtain optimum storage performance.
 

5  USING_field-name
   The names of the fields whose values will be used as limits for
   partitioning the relation across multiple storage areas. Oracle Rdb
   compares values in the fields to the values in the WITH LIMIT OF
   clause to determine where to initially store the record.
 

5  default-threshold-clause

 (B)0default-threshold-clause =
 
 qq> DEFAULT THRESHOLDS ARE qqk
  lqqqqqqqqqqqqq<qqqqqqqqqqqqqj
  mq> ( qq> val1 qwqqqqqqqqqqq>qqqqqqqqqqwq> ) qq>
                  mq> ,val2 qqwqqqqq>qqqqu
                              mq> ,val3 qj

   Specifies associated threshold values for each storage area with
   uniform format that is NOT specified in the map-within-clause. By
   setting threshold values, you can make sure that Oracle Rdb does not
   overlook a page with sufficient space to store compressed data.

   The threshold values specify when the page is marked as FULL
   in the SPAM page free space inventory lists. For example,
   if you set default values of 70, 85, and 95 percent, ranges
   of guaranteed free space on each data page are 30, 15, and 5
   percent, respectively. If you do not set default values, the
   values are (0,0,0). With values of (0,0,0), Oracle Rdb will use the
   record length when setting the SPAM fullness. Oracle Rdb will never
   store a record on a page at threshold 3. The value you set for
   the highest threshold can be used to reserve space on the page
   for future record growth.

   If you specify a value of 40 for the "val1" parameter, but do not
   specify values for the "val2" or "val3" parameters, the threshold
   values will be set at (40,100,100).

   If you use data compression, you should use logical area
   thresholds to obtain optimum storage performance.
 

4  PLACEMENT_VIA_INDEX
   Indicates that Oracle Rdb should attempt to store a record in a way
   that optimizes access to that record via the indicated path.

   If the index named is a hashed index, the storage area named must
   have a MIXED page format. If the hashed index definition and the
   storage map for the relation designate the same storage area,
   then the record is stored on the same page as the hashed index
   node. Otherwise, Oracle Rdb uses the same relative page within the
   data storage area as the target page.

   If the index named is a sorted index, Oracle Rdb finds the dbkey of
   the next lowest record to the one being stored and uses the page
   number in the dbkey as the target page.
 

4  COMPRESSION_clause
   Specifies whether data compression will be enabled or disabled
   when the records are stored. ENABLE COMPRESSION is the default.
 

2  More
   To define a storage map for a relation, you need the Oracle Rdb
   DEFINE privilege to the relation.

   You must execute this statement in a read/write transaction.
   If there is no active transaction and you issue this statement,
   Oracle Rdb starts a read/write transaction implicitly.

   Other users are allowed to be attached to the database when
   you issue the DEFINE STORAGE MAP statement. However, they are
   not allowed to be using the relation whose storage map is being
   defined.
 

2  Examples
   Example 1

   The following statement defines a storage map for the employees
   relation in MF_PERSONNEL.

   DEFINE STORAGE MAP EMPLOYEES_MAP
      DESCRIPTION IS /* EMPLOYEES partitioned by EMPLOYEE_ID */
      FOR EMPLOYEES RELATION
    STORE USING EMPLOYEE_ID
     WITHIN
      EMPIDS_LOW WITH LIMIT OF "00200";
      EMPIDS_MID WITH LIMIT OF "00400";
      EMPIDS_OVER
   END EMPLOYEES_MAP STORAGE MAP.

   Example 2

   The following example shows how to define a storage map that
   stores all records from a relation in the named storage area:

   RDO>  DEFINE STORAGE MAP SALARY_HISTORY_MAP
   cont>  DESCRIPTION IS /* Map for salary history records */
   cont>  FOR SALARY_HISTORY RELATION
   cont>   STORE WITHIN SALARY_HISTORY
   cont> END SALARY_HISTORY_MAP STORAGE MAP.

   This statement stores all the records from the SALARY_HISTORY
   relation into the storage area, SALARY_HISTORY. SALARY_HISTORY
   is the name of a storage area that was created with the DEFINE
   DATABASE statement.

   Example 3

   The following example uses the PLACEMENT VIA INDEX clause to
   store records in a storage area according to a hashed index:

   RDO>   DEFINE STORAGE MAP DEPARTMENTS_MAP
   cont>    FOR DEPARTMENTS RELATION
   cont>    STORE WITHIN DEPARTMENTS
   cont>    PLACEMENT VIA INDEX DEPARTMENTS_INDEX
   cont>  END DEPARTMENTS_MAP STORAGE MAP.

   Example 4

   The following example defines a database and two relations that
   contain segmented strings. The two relations are mapped along
   with their segmented strings to separate storage areas.

   DEFINE DATABASE SEGSTR_0
     DESCRIPTION IS
     /*
     This database is used to show the Oracle Rdb
     segmented string support.  Two relations that contain
     segmented strings are mapped along with their segmented
     string data to separate storage areas.
     */
     DICTIONARY IS NOT USED

     DEFINE STORAGE AREA RDB$SYSTEM
       FILENAME 'SEGSTR_0'
     END

     DEFINE STORAGE AREA SEGSTR_1
       FILENAME 'SEGSTR_1'
     END

     DEFINE STORAGE AREA SEGSTR_2
       FILENAME 'SEGSTR_2'
     END

     DEFINE STORAGE AREA SEGSTR_SS1
       FILENAME 'SEGSTR_SS1'
     END

     DEFINE STORAGE AREA SEGSTR_SS2
       FILENAME 'SEGSTR_SS2'
     END

     SEGMENTED STRING STORAGE AREA IS SEGSTR_SS2.

   DEFINE FIELD STANDARD_DATE
   DATATYPE DATE.

   DEFINE FIELD TITLE
   DATATYPE TEXT SIZE 50.

   DEFINE FIELD DIARY_ENTRY
   DATATYPE SEGMENTED STRING
   SUB_TYPE TEXT
   SEGMENT_LENGTH 200.

   DEFINE RELATION DAILY_DIARY
     DESCRIPTION /* Simple diary */.
     ENTRY_DATE          BASED ON STANDARD_DATE.
     TITLE.
     DIARY_ENTRY.
   END.

   DEFINE RELATION SPECIAL_EVENTS
     DESCRIPTION IS
     /* Special events - birthdays, anniversaries, etc. */.
     EVENT_DATE           BASED ON STANDARD_DATE.
     EVENT_NAME           BASED ON TITLE.
     EVENT_DESCRIPTION    BASED ON DIARY_ENTRY.
     SPECIAL_INSTRUCTIONS BASED ON DIARY_ENTRY.
   END.

   DEFINE STORAGE MAP DAILY_DIARY_MAP
     DESCRIPTION IS
     /* Diary entries are randomly partitioned over two areas */
     FOR DAILY_DIARY
     STORE WITHIN SEGSTR_1; SEGSTR_2
   END.

   DEFINE STORAGE MAP SPECIAL_EVENTS_MAP
     DESCRIPTION IS
     /* Special events entries are stored in one area */
     FOR SPECIAL_EVENTS
     STORE WITHIN SEGSTR_1
   END.

   DEFINE STORAGE MAP DIARY_TEXT
     DESCRIPTION IS
     /* Keep segmented strings in separate areas */
     FOR SEGMENTED STRINGS
     STORE WITHIN
       SEGSTR_SS1 FOR DAILY_DIARY, SPECIAL_EVENTS.EVENT_DESCRIPTION;
       SEGSTR_SS2 FOR DAILY_DIARY;
       SEGSTR_1 FOR SPECIAL_EVENTS.SPECIAL_INSTRUCTIONS;
       RDB$SYSTEM      ! RDB$SYSTEM stores other segmented strings
       END.

   Example 5

   In the following example, three new storage areas (EMPIDS_LOW_
   UNIFORM, EMPIDS_MID_UNIFORM, and EMPIDS_OVER_UNIFORM) and a new
   relation (EMPLOYEES2) are defined in the MF_PERSONNEL database.
   Then a storage map is defined so that new EMPLOYEES2 records are
   stored in either the EMPIDS_LOW_UNIFORM, EMPIDS_MID_UNIFORM, or
   EMPIDS_OVER_UNIFORM storage areas, depending on the value stored
   in the LAST_NAME field.

   RDO> CHANGE DATABASE FILENAME MF_PERSONNEL
   cont>  DEFINE STORAGE AREA EMPIDS_LOW_UNIFORM
   cont>      FILENAME EMPIDS_LOW_UNIFORM.RDA
   cont>      END EMPIDS_LOW_UNIFORM STORAGE AREA
   cont>  DEFINE STORAGE AREA EMPIDS_MID_UNIFORM
   cont>      FILENAME EMPIDS_MID_UNIFORM.RDA
   cont>      END EMPIDS_MID_UNIFORM STORAGE AREA
   cont>  DEFINE STORAGE AREA EMPIDS_OVER_UNIFORM
   cont>      FILENAME EMPIDS_OVER_UNIFORM.RDA
   cont>      END EMPIDS_OVER_UNIFORM STORAGE AREA.
   RDO> !
   RDO> INVOKE DATABASE FILENAME MF_PERSONNEL
   RDO> !
   RDO> DEFINE RELATION EMPLOYEES2
   cont> DESCRIPTION IS /* new relation for testing */.
   cont>  EMPLOYEE_ID BASED ON ID_NUMBER.
   cont>  /* Generic last name */
   cont>     LAST_NAME DATATYPE IS TEXT SIZE IS 14.
   cont>  /* Generic first name */
   cont>     FIRST_NAME DATATYPE IS TEXT SIZE IS 10.
   cont>  /* Generic middle initial */
   cont>     MIDDLE_INITIAL DATATYPE IS TEXT SIZE IS 1.
   cont>  /* Street name */
   cont>     ADDRESS_DATA_1 DATATYPE IS TEXT SIZE IS 25.
   cont>  /* Mail stops, suite addresses, street numbers, etc. */
   cont>     ADDRESS_DATA_2 DATATYPE IS TEXT SIZE IS 25.
   cont>  /* City name */
   cont>     CITY DATATYPE IS TEXT SIZE IS 20.
   cont>  /* State abbreviation (or DISTRICT) */
   cont>     STATE DATATYPE IS TEXT SIZE IS 2.
   cont>  /* Postal code (in US = ZIP) */
   cont>     POSTAL_CODE DATATYPE IS TEXT SIZE IS 5.
   cont>  /* M, F */
   cont>     SEX DATATYPE IS TEXT SIZE IS 1.
   cont>     BIRTHDAY BASED ON STANDARD_DATE.
   cont>  /* A number */
   cont>     STATUS_CODE DATATYPE IS TEXT SIZE IS 1.
   cont> END EMPLOYEES2 RELATION.
   RDO> !
   RDO> DEFINE STORAGE MAP EMP2_LAST_NAME_MAP
   cont> FOR EMPLOYEES2 RELATION
   cont> STORE USING LAST_NAME
   cont> WITHIN EMPIDS_LOW_UNIFORM WITH LIMIT OF "IRONS"
   cont>        THRESHOLDS ARE (70,80,95);
   cont>        EMPIDS_MID_UNIFORM WITH LIMIT OF "QUIST";
   cont>        EMPIDS_OVER_UNIFORM
   cont> DEFAULT THRESHOLDS ARE (60,70,80)
   cont> END EMP2_LAST_NAME_MAP STORAGE MAP.

   Note that the EMPIDS_LOW_UNIFORM storage area receives the
   threshold values of (70,80,95) specified with the THRESHOLDS
   clause, and the EMPIDS_MID_UNIFORM and EMPIDS_OVER_UNIFORM
   storage areas receive the default threshold values of (60,70,80)
   specified with the DEFAULT THRESHOLDS clause.