# CHAPTER 2: The Data Dictionary

## 2.1 Introduction

The Empress database is an operating system directory. The directory contains several operating system files where the data is stored. Empress is the interface between you and these data files. For example, When you create a table, Empress will create an operating system file to store the table's data. When you issue a query to access the tables, Empress will retrieve the information from the corresponding operating system files.

An Empress database is created using the following command at the operating system prompt:

       empmkdb database_name

A directory corresponds to the `database_name` (an empty database) is created with the following files and sub-directories:

    00010001.ix   0003.rel      00070004.dtf  00100001.ix   00130005.dtf
    00010005.dtf  00040001.ix   00070007.dtf  00100001.ixl  00130006.dtf
    0001.rel      00040001.ixl  0007.rel      00100013.dtf  00130007.dtf
    00020001.ix   00040002.ix   00080001.ix   0010.rel      00130011.dtf
    00020008.dtf  00040002.ixl  00080001.ixl  00110001.ix   00130016.dtf
    00020009.dtf  0004.rel      00080003.dtf  00110003.dtf  0013.rel
    00020010.dtf  00050001.ix   0008.rel      00110008.dtf  00140001.ix
    00020011.dtf  00050001.ixl  00090001.ix   00110013.dtf  00140001.ixl
    00020012.dtf  0005.rel      00090001.ixl  0011.rel      00140002.ix
    00020013.dtf  00060001.ix   00090011.dtf  00120001.ix   00140002.ixl
    00020016.dtf  00060008.dtf  00090012.dtf  00120003.dtf  0014.rel
    00020017.dtf  00060009.dtf  00090013.dtf  00120009.dtf  cdinator
    0002.rel      00060011.dtf  00090014.dtf  00120012.dtf  dd_cache
    00030001.ix   00060012.dtf  00090015.dtf  0012.rel      _lock
    00030001.ixl  00060016.dtf  00090016.dtf  00130001.ix   _module
    00030013.dtf  0006.rel      00090017.dtf  00130001.ixl  tabzero
    00030014.dtf  00070001.ix   0009.rel      00130003.dtf  _trn

All these operating system files and directories are Empress database data dictionary which corresponds to:

- data dictionary tables:
  - Their main data file - `.rel` files,
  - Their variable-length data overflow files - `.dtf` files, and
  - Their index and index overflow files - `.ix` and `.ixl` files.
- A text file containing administrative variables for the data dictionary tables - `tabzero`.
- A file containing information that is required most frequently (read) by the database, for example, dynamic keyword - `dd_cache`.
- A database coordinator data file - `cdinator`.
- A directory for transaction information - `_trn`.
- A directory for persistent stored modules - `_module`
- A directory for locking information - `_lock`.

## 2.2 Data Dictionary Tables

Each Empress database has a data dictionary consisting of system tables. These system tables hold information about database tables and attributes, and access to them. The tables are:

Table 2-1: Data Dictionary Tables

Data Dictionary Tables | Description
`sys_dictionary` | Holds all this information in compiled form.
`sys_tables` | Stores information about tables.
`sys_attrs` | Stores information about attributes.
`sys_privs` | Stores information about table access privileges.
`sys_attr_privs` | Stores information about attribute access privileges.
`sys_triggers` | Stores information on trigger definition.
`sys_modules` | Stores Persistent Stored Modules information.
`sys_mod_file` | Stores location information of the dll file in the `USING` clause of the `UPDATE MODULE` command.
`sys_routines` | Stores Persistent Stored Modules routine information.
`sys_params` | Stores Persistent Stored Modules parameter information.
`sys_rep_tables` | Stores general information of Master Tables and Replicate Tables.
`sys_rep_masters` | Stores Replication Master entries.
`sys_rep_replicates` | Stores Replication Replicate entries.
`sys_roles` | Stores information about roles.

These data dictionary tables have the following structures:

    ***  System Table: sys_dictionary  ***

      Attributes:
        dict_creator                      nlscharacter(31,1,0)
        dict_tabname                      nlscharacter(32,1,0)  Not Null
        dict_tabtype                      character(32,1)  Not Null
        dict_comptime                     time(1)
        dict_comp                         bulk(20,0,1024,1)

      Indices:     UNIQUE BTREE sysdidx ON (dict_tabname)

    ***  System Table: sys_tables  ***

      Attributes:
        tab_name                          nlscharacter(32,1,0)  Not Null
        tab_number                        integer  Not Null
        tab_type                          character(32,1)
        tab_numattrs                      integer  Not Null
        tab_lock                          character(1,1)  Not Null
        tab_lkgranty                      integer  Not Null
        tab_perms                         character(16,1)  Not Null
        tab_index                         nlstext(20,0,50,1)
        tab_refer                         nlstext(20,0,50,1)
        tab_view                          nlstext(20,0,50,1)
        tab_vwcomp                        bulk(20,0,1024,1)
        tab_location                      nlstext(20,0,50,1)
        tab_property                      nlstext(20,0,50,1)
        tab_creator                       nlscharacter(31,1,0)  Not Null
        tab_crtime                        time(1)  Not Null
        tab_comment                       nlstext(20,0,50,1)
        tab_param                         nlstext(20,0,512,1)

      Indices:     UNIQUE BTREE tabidx ON (tab_name)

    ***  System Table: sys_attrs  ***

      Attributes:
        attr_name                         nlscharacter(32,1,0)  Not Null
        attr_number                       integer  Not Null
        attr_tabnum                       integer  Not Null
        attr_type                         character(32,1)
        attr_dtname                       character(32,1)  Not Null
        attr_dtpar1                       longinteger  Not Null
        attr_dtpar2                       longinteger  Not Null
        attr_dtpar3                       longinteger  Not Null
        attr_dtpar4                       longinteger  Not Null
        attr_dtpar5                       longinteger  Not Null
        attr_dtpar6                       longinteger  Not Null
        attr_notnull                      character(1,1)  Not Null
        attr_range                        nlstext(20,0,50,1)
        attr_comment                      nlstext(20,0,50,1)

      Indices:     NORMAL (10, 5) BTREE attridx ON (attr_tabnum)

    ***  System Table: sys_privs  ***

      Attributes:
        priv_grantor                      nlscharacter(31,1,0)  Not Null
        priv_grantee                      nlscharacter(31,1,0)  Not Null
        priv_tabnum                       integer  Not Null
        priv_updattr                      character(1,1)  Not Null
        priv_delete                       character(1,1)  Not Null
        priv_insert                       character(1,1)  Not Null
        priv_select                       character(1,1)  Not Null
        priv_update                       character(1,1)  Not Null
        priv_display                      character(1,1)  Not Null
        priv_alter                        character(1,1)  Not Null
        priv_drop                         character(1,1)  Not Null
        priv_empty                        character(1,1)  Not Null
        priv_index                        character(1,1)  Not Null
        priv_sort                         character(1,1)  Not Null

      Indices:     NORMAL (10, 5) BTREE privsidx ON (priv_tabnum)
                   NORMAL (10, 5) BTREE privsidx2 ON (priv_grantee)

    ***  System Table: sys_attr_privs  ***

      Attributes:
        apriv_grantor                     nlscharacter(31,1,0)  Not Null
        apriv_grantee                     nlscharacter(31,1,0)  Not Null
        apriv_tabnum                      integer  Not Null
        apriv_attrnum                     integer  Not Null
        apriv_update                      character(1,1)  Not Null

      Indices:     NORMAL (10, 5) BTREE aprivsidx ON (apriv_tabnum)

    ***  System Table: sys_triggers  ***

      Attributes:
        trig_name                         nlscharacter(32,1,0)  Not Null
        trig_tabnum                       integer  Not Null
        trig_occur                        character(1,1)  Not Null
        trig_event_insert                 character(1,1)  Not Null
        trig_event_update                 character(1,1)  Not Null
        trig_event_delete                 character(1,1)  Not Null
        trig_event_select                 character(1,1)  Not Null
        trig_event_upd_list               nlstext(32,0,64,1)
        trig_event_sel_list               nlstext(32,0,64,1)
        trig_event_for_each               character(1,1)  Not Null
        trig_event_condition              nlstext(32,0,64,1)
        trig_event_cond_comp              bulk(20,512,512,1)
        trig_proc_name                    nlscharacter(32,1,0)  Not Null
        trig_priority                     double precision
        trig_enabled                      character(1,1)  Not Null
        trig_comment                      nlstext(20,0,50,1)

      Indices:     UNIQUE BTREE systridx ON (trig_name)

    ***  System Table: sys_modules  ***

      Attributes:
        mod_name                          nlscharacter(32,1,0)  Not Null
        mod_number                        integer  Not Null
        mod_language                      nlscharacter(16,1,0)  Not Null
        mod_lang_params                   nlstext(20,0,50,1)
        mod_creator                       nlscharacter(32,1,0)  Not Null
        mod_crtime                        time(1)  Not Null
        mod_comment                       nlstext(20,0,50,1)

      Indices:     UNIQUE BTREE sysmodidx ON (mod_name)

    ***  System Table: sys_mod_file  ***

      Attributes:
        modf_mod_num                      integer  Not Null
        modf_system_tag                   nlscharacter(32,1,0)
        modf_using_filename               nlstext(32,32,64,1)  Not Null

      Indices:     NORMAL (10, 5) BTREE sysmfidx ON (modf_mod_num)

    ***  System Table: sys_routines  ***

      Attributes:
        routine_name                      nlscharacter(32,1,0)  Not Null
        routine_number                    integer  Not Null
        routine_mod_num                   integer  Not Null
        routine_class                     nlscharacter(64,1,0)  Not Null
        routine_language                  nlscharacter(16,1,0)
        routine_deterministic             character(1,1)
        routine_SQL_data_access           character(1,1)
        routine_specific_name             nlscharacter(32,1,0)
        routine_param_style               character(1,1)  Not Null
        routine_num_params                integer  Not Null
        routine_external_name             nlstext(32,32,32,1)  Not Null
        routine_workspace                 nlstext(32,32,32,1)
        routine_begin_expression          nlstext(32,32,32,1)
        routine_begin_group               nlstext(32,32,32,1)
        routine_end_group                 nlstext(32,32,32,1)
        routine_end_expression            nlstext(32,32,32,1)
        routine_comment                   nlstext(20,0,50,1)

      Indices:     NORMAL (10, 5) BTREE sysrtidx ON (routine_mod_num)

    ***  System Table: sys_params  ***

      Attributes:
        param_name                        nlscharacter(32,1,0)
        param_number                      integer  Not Null
        param_mod_num                     integer  Not Null
        param_routine_num                 integer  Not Null
        param_mode                        character(1,1)  Not Null
        param_dtname                      character(32,1)  Not Null
        param_dtpar1                      longinteger  Not Null
        param_dtpar2                      longinteger  Not Null
        param_dtpar3                      longinteger  Not Null
        param_dtpar4                      longinteger  Not Null
        param_dtpar5                      longinteger  Not Null
        param_dtpar6                      longinteger  Not Null
        param_comment                     nlstext(20,0,50,1)

      Indices:     NORMAL (10, 5) BTREE sysparaidx ON (param_mod_num,
                   param_routine_num)

    ***  System Table: sys_rep_tables  ***

      Attributes:
        rep_tabnum                        integer  Not Null
        rep_tabtype                       integer  Not Null
        rep_tab_condition                 nlstext(32,0,64,1)
        rep_tab_timestamp                 microtimestamp(0)
        rep_recov_timestamp               microtimestamp(0)
        rep_purge_timestamp               microtimestamp(0)
        rep_orig_mashost                  character(32,1)  Not Null
        rep_orig_masdb                    nlstext(32,32,32,1)  Not Null
        rep_orig_mastab                   nlscharacter(32,1,0)  Not Null
        rep_orig_mas_start_timestamp      microtimestamp(0)  Not Null
        rep_orig_mas_hash_value           longinteger  Not Null
        rep_cur_mas_start_timestamp       microtimestamp(0)  Not Null
        rep_world                         bulk(20,0,1024,1)

      Indices:     UNIQUE BTREE sysreptabidx ON (rep_tabnum)

    ***  System Table: sys_rep_masters  ***

      Attributes:
        repm_tabnum                       integer  Not Null
        repm_massv                        nlscharacter(32,1,0)  Not Null
        repm_masdb                        nlstext(32,32,32,1)  Not Null
        repm_mastab                       nlscharacter(32,1,0)  Not Null
        repm_order                        double precision  Not Null
        repm_enabled                      character(1,1)  Not Null
        repm_pulled_success_timestamp     microtimestamp(0)
        repm_pulled_timestamp             microtimestamp(0)
        repm_pulled_status                nlstext(32,64,64,1)
        repm_pushed_success_timestamp     microtimestamp(0)
        repm_pushed_timestamp             microtimestamp(0)
        repm_pushed_status                nlstext(32,64,64,1)

      Indices:     UNIQUE BTREE sysrepmasidx ON (repm_tabnum, repm_order)

    ***  System Table: sys_rep_replicates  ***

      Attributes:
        repr_tabnum                       integer  Not Null
        repr_rephost                      character(32,1)  Not Null
        repr_repdb                        nlstext(32,32,32,1)  Not Null
        repr_reptab                       nlscharacter(32,1,0)  Not Null
        repr_condition                    nlstext(32,0,64,1)
        repr_cond_comp                    bulk(20,512,512,1)
        repr_repptab                      nlstext(32,32,32,1)
        repr_rephval                      longinteger  Not Null
        repr_pulled_success_timestamp     microtimestamp(0)
        repr_pulled_timestamp             microtimestamp(0)
        repr_pulled_status                nlstext(32,64,64,1)
        repr_repsv                        nlscharacter(32,1,0)
        repr_repsv_enabled                character(1,1)
        repr_pushed_success_timestamp     microtimestamp(0)
        repr_pushed_timestamp             microtimestamp(0)
        repr_pushed_status                nlstext(32,64,64,1)

      Indices:     NORMAL (10, 5) BTREE sysreprepidx ON (repr_tabnum)

    ***  System Table: sys_roles  ***

      Attributes:
        role_grantor                      nlscharacter(31,1,0)  Not Null
        role_grantee                      nlscharacter(31,1,0)  Not Null
        role_grantee_type                 character(1,1)  Not Null
        role_name                         nlscharacter(32,1,0)  Not Null
        role_admin                        character(1,1)  Not Null

      Indices:     NORMAL (10, 5) BTREE sysroleidx ON (role_name)
                   NORMAL (10, 5) BTREE sysroleidx2 ON (role_grantee)

Note that the parameters for the `BULK` and `TEXT` attributes in the data dictionary table may vary depending on the version of Empress.

These tables are examined in detail below. They are updated automatically by Empress when changes are made to a database, and may also be updated directly by the Database Administrator, although this is not normal practice and is not recommended.

Under no circumstances should you execute an `ALTER` or `SORT` command on any data dictionary table, as this corrupts implicit access to those tables in Empress.

The database dictionary tables may be queried by anyone with the necessary privileges. For example, to find out the current locking level on a table such as `personnel`, use:

       SELECT tab_lock FROM sys_tables
            WHERE tab_name = 'personnel';

To find if select and update privileges have been granted on `personnel` to the login id `mosca`, use:

       SELECT priv_select, priv_update, priv_grantor
            FROM sys_privs, sys_tables
            WHERE sys_privs.priv_tabnum = sys_tables.tab_number
            AND tab_name = 'personnel' AND priv_grantee = 'mosca';

### 2.2.1 Data Dictionary for Tables: sys_tables

The `sys_tables` table contains a record for each table or view in the database, including the tables in the data dictionary.

Table 2-2: Data Dictionary for Tables - sys_tables

Attributes | Description
`tab_name` | Name of the table.
`tab_number` | Number of the table. This number is used internally to identify the operating system file corresponding to the table. For instance, table number 1 is the file `0001.rel` in the database directory.
`tab_type` | Represents the type of the table; i.e. `normal`, `master`, `replicate`, `view`, `master for subset`, `replicate for subset` etc.
`tab_numattrs` | Number of attributes in the table.
`tab_lock` | Locking level set for the table. This is either `n` for `none`, `r` for `record`, `g` for `group` or `t` for `table` level locking.
`tab_perms` | Operating system access permissions or protections set for the file corresponding to the table.
`tab_index` | Information on any indices created on the table.
`tab_refer` | Definitions of any referential constraints on the table.
`tab_view` | If a view, the `SELECT` statement that defines the view.
`tab_vwcomp` | Compiled data defining a view.
`tab_location` | Location of the table, if remote.
`tab_property` | Property of the table, if remote.
`tab_creator` | Name of the user who is the creator of the table.
`tab_crtime` | Date and time the table was created.
`tab_comment` | Any comment placed on the table by the `CREATE` `COMMENT` command.
`tab_param` | For future use.

### 2.2.2 Data Dictionary for Attributes: sys_attrs

The `sys_attrs` table contains a record for each attribute of each table or view in the database, including the tables in the data dictionary.

Table 2-3: Data Dictionary for Attributes - sys_attrs

Attributes | Description
`attr_name` | Name of the attribute.
`attr_number` | Number of the attribute in the table. This is used internally to identify the attribute.
`attr_tabnum` | Number of the table containing the attribute. This number corresponds to the `tab_number` attribute of the `sys_tables` table.
`attr_type` | Type of the attribute.
`attr_dtname` | Data type of the attribute; i.e., one of `CHAR`, `INTEGER`, `DATE`, etc.
`attr_dtpar1` | Value of the first parameter of the attribute's data type. This is `0` if not applicable.
`attr_dtpar2` | Value of the second parameter of the attribute's data type. This is `0` if not applicable.
`attr_dtpar3` | Value of the third parameter of the attribute's data type. This is `0` if not applicable.
`attr_dtpar4` | Value of the fourth parameter of the attribute's data type. This is `0` if not applicable.
`attr_dtpar5` | Value of the fifth parameter of the attribute's data type. Represents the variable checksum for bulk, text and nlstext data. This is `0` if not applicable.
`attr_dtpar6` | Value of the sixth parameter of the attribute's data type. A reserved value.
`attr_notnull` | Either `y` or `n` depending on whether null values are allowed for the attribute.
`attr_range` | Specification for any range check applicable to the attribute.
`attr_comment` | Any comment placed on the attribute with the `CREATE COMMENT` command.

### 2.2.3 Data Dictionary for Privileges: sys_privs

The `sys_privs` table contains a record for each grantor/grantee pair defining a set of privileges for each table and view in the database, including the tables in the data dictionary.

Note: The explanation for the values of some of the attributes in this table might represent only subset of possible values.

Table 2-4: Data Dictionary for Privileges - sys_privs

Attribute | Description
`priv_grantor` | Person granting the privileges.
`priv_grantee` | Person receiving the privileges.
`priv_tabnum` | Identifying number of the table to which the privileges apply.
`priv_updattr` | Indicates whether there is a record in `sys_attr_privs` describing attribute `UPDATE` privileges (either `y` or `n`). If the grantor has given the grantee `UPDATE` privilege on at least one attribute of the table, but not all, there will be a record in `sys_attr_privs.`
`priv_delete` | Privilege to delete records from the table. Either `n` for no `DELETE` privilege, `y` for `DELETE` privilege without the ability to grant this privilege to others, or `g` for `DELETE` privilege with the ability to grant the privilege.
`priv_insert` | Privilege to insert records into the table. Either `n` for no `INSERT` privilege, `y` for `INSERT` privilege without the ability to grant this privilege to others, or `g` for `INSERT` privilege with the ability to grant the privilege.
`priv_select` | Privilege to select records from the table. Either `n` for no `SELECT` privilege, `y` for `SELECT` privilege without the ability to grant this privilege to others, or `g` for `SELECT` privilege with the ability to grant the privilege.
`priv_update` | Privilege to update all attributes of the table. Either `n` for no `UPDATE` privilege or partial `UPDATE` privilege, `y` for full `UPDATE` privilege without the ability to grant this privilege to others, or `g` for full `UPDATE` privilege with the ability to grant the privilege. Partial `UPDATE` privilege occurs when only some attributes may be updated; in this case the `attr_privs` attribute has a `y` value directing attention to the `sys_attr_privs` table.
`priv_display` | Privilege to display the table. Either `n` for no `DISPLAY` privilege, `y` for privilege without the ability to grant this privilege to others, or `g` for `DISPLAY` privilege with the ability to grant the privilege.
`priv_alter` | Privilege to alter the table. Either `n` for no `ALTER` privilege, `y` for `ALTER` privilege without the ability to grant this privilege to others, or `g` for `ALTER` privilege with the ability to grant the privilege.
`priv_drop` | Privilege to drop the table. Either `n` for no `DROP` privilege, `y` for `DROP` privilege without the ability to grant this privilege to others, or `g` for `DROP` privilege with the ability to grant the privilege.
`priv_empty` | Privilege to empty the table. Either `n` for no `EMPTY` privilege, `y` for `EMPTY` privilege without the ability to grant this privilege to others, or `g` for `EMPTY` privilege with the ability to grant the privilege.
`priv_index` | Privilege to create an index on the table. Either `n` for no `INDEX` privilege, `y` for `INDEX` privilege without the ability to grant this privilege to others, or `g` for `INDEX` privilege with the ability to grant the privilege.
`priv_sort` | Privilege to sort the table. Either `n` for no `SORT` privilege, `y` for `SORT` privilege without the ability to grant this privilege to others, or `g` for `SORT` privilege with the ability to grant the privilege.

### 2.2.4 Data Dictionary for Attribute Privileges: sys_attr_privs

The `sys_attr_privs` table contains a record for each attribute update privilege making up the partial update privileges on the tables and views in the database.

Table 2-5: Data Dictionary for Attribute Privileges - sys_attr_privs

Attributes | Description
`apriv_grantor` | Person granting the privilege.
`apriv_grantee` | Person receiving the privilege.
`apriv_tabnum` | Number of the table to which the privilege applies.
`apriv_attrnum` | Number of the attribute in the table to which the privilege applies.
`apriv_update` | Either `y` for `UPDATE` privilege with no option to grant the privilege to others, or `g` for `UPDATE` privilege with the option to grant the privilege.

### 2.2.5 Compiled Data Dictionary: sys_dictionary

The `sys_dictionary` table contains all the information in the other four system tables in compiled form. Empress uses it internally for speedier access to data.

Table 2-6: Compiled Data Dictionary - sys_dictionary

Attributes | Description
`dict_creator` | Name of the user who is creator of the table: same as `tab_creator` in the `sys_tables` table.
`dict_tabname` | Name of the table: same as `tab_name` in the `sys_tables` table.
`dict_tabtype` | Type of the table.
`dict_comptime` | Date and time the table was created: same as `tab_crtime` in the `sys_tables` table.
`dict_comp` | Contains all information in the given Empress table, compiled and stored in bulk form.

### 2.2.6 Data Dictionary for Triggers: sys_triggers

The `sys_triggers` table contains trigger definition for a table.

Table 2-7: Data Dictionary for Triggers - sys_triggers

Attributes | Description
`trig_name` | Name of the trigger.
`trig_tabnum` | Table number that trigger definition is applied to.
`trig_occur` | To indicate the occurrence of the trigger is before or after the operation. Value `b` stands for `before` and `a` stands for `after`.
`trig_event_insert` | Indicator of trigger event on the select operation.
`trig_event_update` | Indicator of trigger event on the update operation.
`trig_event_delete` | Indicator of trigger event on the delete operation.
`trig_event_select` | Indicator of trigger event on the select operation. Currently this feature is not supported. This is reserved for future use.
`trig_event_upd_list` | Attribute list, if any, for trigger event update.
`trig_event_sel_list` | Attribute list for trigger event select. Currently this feature is not supported. This is reserved for future use.
`trig_event_for_each` | Indicator for `FOR EACH ROW`.
`trig_event_condition` | Condition statement for the trigger event.
`trig_event_cond_comp` | Condition statement compiled in the bulk format .
`trig_proc_name` | Trigger procedure name.
`trig_priority` | Trigger event priority.
`trig_enabled` | Indicator of whether trigger is currently enabled.
`trig_comment` | Comments on the trigger.

### 2.2.7 Data Dictionary for Persistent Stored Modules: sys_modules

The `sys_modules` table contains persistent stored module definition.

Table 2-8: Data Dictionary for Persistent Stored Modules: sys_modules

Attributes | Description
`mod_name` | Name of the module.
`mod_number` | Module number.
`mod_language` | The programming language; currently only C programming language is supported.
`mod_lang_params` | Parameters for the language.
`mod_creator` | User name that created the module.
`mod_crtime` | The time that module was created.
`mod_comment` | Comments.

### 2.2.8 Data Dictionary for Module Files: sys_mod_file

The `sys_mod_file` table contains system tag and dll file name for the module.

Table 2-9: Data Dictionary for Module Files - sys_mod_file

Attributes | Description
`modf_mod_num` | Module number.
`modf_system_tag` | Name of the system.
`modf_using_filename` | Module dll file name.

### 2.2.9 Data Dictionary for PSM Routines: sys_routines

The `sys_routines` table contains information on the Persistent Stored Modules routines.

Table 2-10: Data Dictionary for PSM Routines - sys_routines

Attributes | Description
`routine_name` | Name of the routine.
`routine_number` | Routine number.
`routine_mod_num` | Module number that this routine is associated with.
`routine_class` | Routine class, such as, procedure, aggregrate function, boolean function, prefix operator ... etc.
`routine_language` | Programming language that the routine is written in. Currently only support C programming language.
`routine_deterministic` | This attribute is reserved for future use.
`routine_SQL_data_access` | This attribute is reserved for future use.
`routine_specific_name` | This attribute is reserved for future use.
`routine_param_style` | Indicates the parameter style, `g` is for `PARAMETER STYLE GENERAL` and `s` is for `PARAMETER STYLE SQL`.
`routine_num_params` | Number of parameters.
`routine_external_name` | Routine external name.
`routine_workspace` | The size of workspace.
`routine_begin_expression` | Begin expression external routine name.
`routine_begin_group` | Begin group external routine name.
`routine_end_group` | End group external routine name.
`routine_end_expression` | End expression external routine name.
`routine_comment` | Comments.

### 2.2.10 Data Dictionary for UDF Parameters: sys_params

The `sys_params` table contains information for the user defined function parameters.

Table 2-11: Data Dictionary for UDF Parameters: sys_params

Attributes | Description
`param_name` | Name of the parameter.
`param_number` | Parameter number.
`param_mod_num` | Module number.
`param_routine_num` | Routine number.
`param_mode` | Parameter mode. It has value of `i` for `IN`, `o` for `OUT` and `x` for INOUT.
`param_dtname` | Parameter data type.
`param_dtpar1` | First data type parameter.
`param_dtpar2` | Second data type parameter.
`param_dtpar3` | Third data type parameter.
`param_dtpar4` | Fourth data type parameter.
`param_dtpar5` | Fifth data type parameter.
`param_dtpar6` | Sixth data type parameter (Reserved).
`param_comment` | Comments.

### 2.2.11 Data Dictionary for Master and Replicate tables: sys_rep_tables

The `sys_rep_tables` table contains general information of master tables and replicate tables. One record is kept in this table for each Replication Table in the database.

Refer to [Empress Replication User's Guide : References] for contents of `sys_rep_tables`.

### 2.2.12 Data Dictionary for Replication Master entries: sys_rep_masters

The `sys_rep_masters` table contains information on replication master entries of replication tables in a database. One record is kept in this table for any Replication Master Entry.

Refer to [Empress Replication User's Guide : References] for contents of `sys_rep_masters`.

### 2.2.13 Data Dictionary for Replication Replicate entries: sys_rep_replicates

The `sys_rep_replicates` table contains information on replication replicate entries of replication tables in a database. One record is kept in this table for any Replication Replicate Entry.

Refer to [Empress Replication User's Guide : References] for contents of `sys_rep_replicates`.

### 2.2.14 Data Dictionary for Roles: sys_roles

The `sys_roles` table contains information about database roles.

Table 2-12: Data Dictionary for Roles - sys_roles

Attributes | Description
`role_grantor` | Person granting the role.
`role_grantee` | A person (username) or a role receiving the role.
`role_grantee_type` | Type of a grantee; a user or a role.
`role_name` | Name of the role.
`role_admin` | Grantability of the role.

## 2.3 Administrative Variable File for the Data Dictionary

In every Empress database there is a file called `tabzero` which contains default values for a number of Empress database administrative variables. This file is read by Empress when it first accesses the database, and the values in it will override any default, environment or user settings of these variables.

This is intended to secure the database. For example, users should not be able to declare themselves Database Administrators, with all the attendant authority, simply by setting an environment variable.

`tabzero` is created when you create a database, and the values in it may be altered with any system editor. (Normal operating system file access permissions for `tabzero` are necessary before it can be altered.) The default values are taken from the file `tabzero` in the `custom` directory where Empress is installed.

The Database Administrator is set to the name of the user creating the database.

If a database is copied to another user's account, it is essential to edit `tabzero` and change the name of the Database Administrator, or the new owner will be unable to access the database.

For example, the `tabzero` in the `repairs` database created by `joe` with Empress Version 8.62 contains the following standard default values:

       :       '(c) Copyright  Empress Software Inc.   1983, 2006'

       MSDBADMINISTRATOR=joe

       MSNFSSHARE=

       MSDBPERMS=
       MSDBDBAPRIVS=dba dba dba; dba dba user grant
       MSDBPRIVS=creator creator dba; creator user user grant
       MSDBLOCKLEVEL=record
       MSDBLOCKGRANULARITY=1
       MSDBLOCKSTATS=
       MSDBTHRESCHAINLEN=1.5
       MSDBVALIDATESIZE=

       MSDBINDEXPRIMARY=2
       MSDBINDEXOVERFLOW=15

       MSDBAUDITTRAIL1=
       MSDBAUDITTRAIL2=
       MSDBRECOVERYLOG1=
       MSDBRECOVERYLOG2=
       MSDBLOGFILE=

       MSDBDICTTABLENUMBER=1

       MSDBDICTLOCK=record
       MSDBDICTINDEX=1 0

       MSDBVERSION=8.62
       MSDBVERSION_FEATURES=8.62

       MSDBSERVERPORTID=
       MSDBAUDITTERSE=
       MSCOORDDISABLE=
       MSDBMAXPROCS=256
       MSDBBLOCKSIZE=2
       MSDBPUBLICPRIVILEGES=\
         [SYS_OS_WIN32]        X
         [ALL]

       MSDBDFC=421

       MSSHMKEY=21105037
       MSSHMPROTECTION=

           MSPARTNAME=DEF_LOCK_PART
           MSPARTTYPE=LOCK
               MSPARTLOCKNLOCKS=2000
           MSPARTEND
       MSSHMEND

       MSDBDICTPARAMS
               MSPARAMLOCK
                       MSPARTLOCKNAME=DEF_LOCK_PART
               MSPARAMLOCKEND
       MSDBDICTPARAMSEND

       MSPARAMLOCK
               MSPARTLOCKNAME=DEF_LOCK_PART
       MSPARAMLOCKEND

### 2.3.1 Description of Database Administrative Variables

Empress has a number of variables whose values are taken from the file `tabzero`. Their values can only be altered by editing `tabzero` and these values will override all settings in the operating system or in the SQL session.

When a new Empress database is created, its `tabzero` is copied from a template `tabzero` found in the `custom` directory where Empress is installed. To alter the default values for the entire system, edit `tabzero` in the `custom` directory. It is strongly recommended that you make a copy of the original file in the `custom` directory, naming it something like `tabzero.orig` before you edit it to make changes. You will need to restore the original file if you ever run Empress system testing routines. It will produce spurious errors if the system variables do not have their original values.

A list of the database administrative variables and brief descriptions of their functions follow:

Table 2-12: Empress Database Administrative Variables

Name | Function
`MSCOORDDISABLE` | Disables the Database Coordinator.
`MSDBADMINISTRATOR` | Holds the name of the Database Administrator.
`MSDBAUDITTERSE` | Specifies the audit trail logging without data values.
`MSDBAUDITTRAIL1` | Specifies the first file to hold the audit trail logging.
`MSDBAUDITTRAIL2` | Specifies the second file to hold the audit trail logging.
`MSDBBLOCKSIZE` | Specifies the system block size to be used by the database.
`MSDBDBAPRIVS` | Controls privileges granted on data dictionary tables.
`MSDBDFC` | Specifies the data format code.
`MSDBDICTINDEX` | Indicates the usage of index on `sys_dictionary` for implicit table searching.
`MSDBDICTLOCK` | Sets locking on compiled dictionary table, `sys_dictionary`.
`MSDBDICTTABLENUMBER` | `sys_dictionary` table number.
`MSDBINDEXOVERFLOW` | Gives default secondary storage length for indices.
`MSDBINDEXPRIMARY` | Gives default primary storage length for indices.
`MSDBLOCKLEVEL` | Sets default locking level for newly created tables.
`MSDBLOCKSTATS` | Activates lock statistic information gathering.
`MSDBMAXPROCS` | Specifies the maximum number of processes allowed to access the database.
`MSDBPERMS` | Sets default access permissions for database tables.
`MSDBPRIVS` | Controls privileges granted on tables at creation.
`MSDBRECOVERYLOG1` | Specifies the first file to hold a recovery log.
`MSDBRECOVERYLOG2` | Specifies the second file to hold a recovery log.
`MSDBSERVERPORTID` | This variable is for Empress internal use and it is for Cray system only. |
`MSDBTHRESCHAINLEN` | Determines the acceptable amount of fluctuation in lock distribution. |
`MSDBVALIDATESIZE` | Controls the size of the checksum value of the record.
`MSDBVERSION` | Empress version number.
`MSNFSSHARE` | Specifies concurrent access over NFS.
`MSDBLOGFILE` | Specifies the file to hold Database Log.
`MSDBLOCKGRANULARITY` | Sets a user defined lock granularity on a table.
`MSDBPUBLICPRIVILEGES` | Bypasses Empress database privileges.
`MSDBVERSION_FEATURES` | Empress internal variable.

#### 2.3.1.1 MSCOORDDISABLE

When this variable is set, the database access will by-pass the coordinator. This is done for performance reason and for read only database.

#### 2.3.1.2 MSDBADMINISTRATOR

`MSDBADMINISTRATOR` gives the login name of the Database Administrator (DBA) for the database. To set the DBA for a database to `joe` edit `tabzero` and change the value of `MSDBADMINISTRATOR` to:

       MSDBADMINISTRATOR=joe

Note that it is essential to change the name of the DBA if an entire database is copied for another user, so that the new user is listed as the DBA and can access the database.

#### 2.3.1.3 MSDBAUDITTERSE

The audit trail is used for keeping track of all accesses to the database. It records all operations on the database, even those that do not modify the data. If `MSDBAUDITTERSE` is set, the audit trail records all operations without data values.

#### 2.3.1.4 MSDBAUDITTRAIL1 and MSDBAUDITTRAIL2

`MSDBAUDITTRAIL1` gives the location of a file to hold audit trail information, and `MSDBAUDITTRAIL2` gives the location of a second file to hold the same information. The option of a second file is given for added protection; it is normal to specify these files in different file systems, and preferably on different disks, in case of operating system or hardware failure.

By simply setting or insetting these variables, you can enable or disable audit trail logging.

#### 2.3.1.5 MSDBBLOCKSIZE

This specifies the block size of the machine in multiples of 512 bytes. Default value varies from system to system. In most system it is set to 2. If the database access is mostly read with large data set, it is more efficient to set this variable to a higher value.

#### 2.3.1.6 MSDBDBAPRIVS

`MSDBDBAPRIVS` controls the privileges granted for database dictionary tables when they are created. The format for setting this parameter is:

       MSDBDBAPRIVS = grantor grantee {, grantee} privilege {, privilege} [GRANT]
            {; grantor grantee {, grantee} privilege {, privilege} [GRANT]}

where:

`grantor` | is the user name of the person granting the privilege.
`grantee` | is the user name of the person to whom the privilege is granted, or one of the special names `dba`, `creator`, or `public`.
`privilege` | is one of: `ALTER`, `DELETE`, `DISPLAY`, `DROP`, `EMPTY`, `INDEX`, `INSERT`, `SELECT`, `SORT` or `UPDATE` `[(attr{, attr})]`.

For convenience, there are also three special privilege types: `dba`, `all`, and `user`. `dba` includes `ALTER`, `DROP`, `EMPTY`, `INDEX`, and `SORT`; `all` includes all the privileges; and `user` includes `DELETE`, `DISPLAY`, `INSERT`, `SELECT` and `UPDATE`.

The default value for `MSDBDBAPRIVS` is `dba dba user grant`, which grants the DBA for the data dictionary tables all user privileges, with `GRANT` option, and lists the grantor as the DBA.

To have data dictionary tables created with all privileges granted to `Joe` by the DBA, with `GRANT` option, and select and display privileges granted to `Mosca` and `Jones` by `Joe`, without `GRANT` option, edit `tabzero` in the `custom` directory where Empress is installed and change the appropriate line to:

       MSDBDBAPRIVS=dba joe all grant; joe mosca display, select;\
                    joe jones display, select

Note that the entry should be on one line in `tabzero`. If you require more than one line, use the line continuation character (set in `MSLINECONT`, with a default value of `\`) to end every line except the last, as illustrated in the example above.

#### 2.3.1.7 MSDBDFC

This variable speficies the Data Format Code (DFC) of the database. By default, database is created using the native DFC. `MSDBDFC` can also be set to a different DFC from the native system during the database creation through `empmkdb` command. For example, a database is designed to be accessed by a remote host. If the remote host has different architecture, using the remote host's DFC to create the database will eliminate the data conversion overhead.

The variable `MSDBDFC` contains 3 ASCII digits, `lse`, which describes the 3 characteristics of a computer:

`l` | the size of a long integer in bytes.
`s` | the size of a short integer in bytes.
`e` | Byte order. Big endian is 0 and little endian is 1.

For example, the DFC of a Sun Sparc Station is 420. This means a long integer is 4 bytes, a short integer is 2 bytes and the byte order is big endian.

#### 2.3.1.8 MSDBDICTINDEX

`MSDBDICTINDEX` gives the indication if index was created and the type of index on the `dict_tabname` attribute in the `sys_dictionary` table (compiled dictionary table) for implicit system searches.

If the variable is set, Empress expects an index on the `dict_tabname` attribute when searching `sys_dictionary` for table names. If the variable is not set, Empress assumes that the index is not available.

This variable is set by Empress during creation of database. User should not change the value of this variable or the following message will occur:

       *** Database Problem ***  no file 'database_directory_path/00010001.ix'
       *** Data Dictionary creation fails *** 'database_name'

The value for this variable is:

       MSDBDICTINDEX=1 0

This indicates an unique index on the `dict_tabname` attribute.

#### 2.3.1.9 MSDBDICTLOCK

`MSDBLOCKDICT` sets the locking mode for the compiled dictionary table (`sys_dictionary`). It takes a word as a value, of which the first letter is significant. Beginning letters may be `n`, `r`, `g`, or `t`, for `none`, `record`, `group`, or `table` locking, respectively. To enable record level locking for this table, set this to the following in `tabzero` in the `custom` directory where Empress is installed:

       MSDBLOCKDICT=record

#### 2.3.1.10 MSDBDICTTABLENUMBER

`MSDBDICTTABLENUMBER` indicates the table number corresponding to the compiled dictionary table (`sys_dictionary`). The default is 1 which corresponds to `0001.rel`.

#### 2.3.1.11 MSDBINDEXPRIMARY and MSDBINDEXOVERFLOW

When index is created (other than `UNIQUE INDEX`), the primary and overflow storage length are specified by `MSDBINDEXPRIMARY` and `MSDBINDEXOVERFLOW` variables. The default values are 2 and 15 respectively.

For example, to have indices created as unique by default (`PRIMARY=1, OVERFLOW=0`), edit `tabzero` so the entries for these two variables are:

       MSDBINDEXPRIMARY=1
       MSDBINDEXOVERFLOW=0

Then, the command:

       CREATE INDEX ON table_name (attr_name);

is same as:

       CREATE UNIQUE INDEX ON table_name (attr_name);

#### 2.3.1.12 MSDBLOCKLEVEL

`MSDBLOCKLEVEL` sets the locking mode for newly-created tables. It takes a word as a value, of which the first letter is significant. Beginning letters may be `n`, `r`, `g`, or `t`, for `null`, `record`, `group`, or `table` locking, respectively. To enable record level locking for new tables, set the following in `tabzero`:

       MSDBLOCKLEVEL=record

#### 2.3.1.13 MSDBLOCKSTATS

This variable activates the statistic gathering of the Lock Manager. The information is used by the `empadm lockstats` command for a detailed report of Lock Manager statistics.

The statistics gathering is turned off by default. To enable statistics, the `MSDBLOCKSTATS` variable should be set to any value.

#### 2.3.1.14 MSDBMAXPROCS

This variable specifies the maximum number of processes allowed to access the database. The value is set to the minimum value specified by the license key or the `MSMAXPROCS` variable in the `initfile`.

#### 2.3.1.15 MSDBPERMS

`MSDBPERMS` sets the default access permissions for the newly created database tables. The access permissions are any combination of `R` (for `read`), `W` (for `write`), and `X` (for `execute`). They are given in order of `owner`, `group` and `other`. To set the new tables as `read-write` by `owner`, `read` by `group`, `read` by `other`, set the following in `tabzero` in the `custom` directory where Empress is installed before creating the database:

       MSDBPERMS=RW,R,R

`MSDBPERMS` has no value by default, so the operating system default permission applies.

#### 2.3.1.16 MSDBPRIVS

`MSDBPRIVS` controls privileges granted for user tables (table that is not a data dictionary table) when they are created. The format for setting this parameter is:

       MSDBPRIVS=grantor grantee {, grantee} privilege {, privilege} [GRANT]
                 {; grantor grantee {, grantee} privilege {, privilege} [GRANT]}

The default value for `MSDBPRIVS` is `creator creator dba; creator user user grant`, which grants the creator of the table all `dba` privileges, without the `GRANT` option, listing the grantor as the creator, and also gives the current user all `user` privileges with the `GRANT` option, granted by the creator.

For example, if you wish to have ordinary tables created with all privileges granted to Joe by the Database Administrator, with `GRANT` option, and `SELECT` and `DISPLAY` privileges granted to Mosca and Jones by Joe, without `GRANT` option, set the following:

       MSDBPRIVS=dba joe ALL grant; joe mosca DISPLAY, SELECT;\
                 joe jones DISPLAY, SELECT

Note that the entry should be on one line in `tabzero`. If you require more than one line, use the line continuation character (set in `MSLINECONT`, with a default value of `\`) to end every line except the last line, as illustrated in the example above.

#### 2.3.1.17 MSDBRECOVERYLOG1 and MSDBRECOVERYLOG2

`MSDBRECOVERYLOG1` specifies a file which will hold the recovery log. Changes to a database may be restored from this file in the event of an operating system failure. Setting this variable and creating the empty file enables recording of the recovery log. Unsetting it disables recording. A duplicate copy of the recovery log may be kept in the file specified by `MSDBRECOVERYLOG2`. A common practice is to keep a second copy of the log on a different file system - preferably on a different disk drive from the first - to guard against operating system or disk failure.

#### 2.3.1.18 MSDBSERVERPORTID

This variable is for Empress internal use on the Cray Super Computer system only.

#### 2.3.1.19 MSDBTHRESCHAINLEN

This variable is used to calculate the threshold chain length based on current number locks.

#### 2.3.1.20 MSDBVALIDATESIZE

This variable controls the size of the checksum value of the data dictionary table records. By default it is set to no checksum on the data dictionary tables.

#### 2.3.1.21 MSDBVERSION

`MSDBVERSION` is the version number of the product, for example, Version 8.62.

#### 2.3.1.22 MSNFSSHARE

When set, `MSNFSSHARE` clears memory buffers on each node in a Sun NFS network; this ensures that data cannot be corrupted when accessed by several users concurrently.

Set `MSNFSSHARE` in `tabzero` using the syntax:

       MSNFSSHARE='y'

There is a performance penalty for use of this variable - `MSNFSSHARE` can double processing time.

#### 2.3.1.23 MSDBLOGFILE

Specifies the file to hold Database Log. The database log is used for keeping track of accesses to the database. It serves as an input for Empress Database Log Analyzer utility. If not specified, there will not be any default database log file.

#### 2.3.1.24 MSDBLOCKGRANULARITY

This variable sets a user defined lock granularity on a table.

Specifying `MSDBLOCKGRANULARITY`=n locks each record in a table as it is encountered in a command plus (n-1) surrounding records. n records, locked in this case, are constituting a "PAGE" lock.

This variable provides the same functionality as:

         LOCK LEVEL [ON] table [IS] RECORD (n) ;

Choosing a right value for lock granularity (i.e. a number of locks in a "PAGE") will be a trade-of between performance and concurrency.

The default setting is: MSDBLOCKGRANULARITY=1

#### 2.3.1.25 MSDBPUBLICPRIVILEGES

This variable, set by default on Win32 platforms (`MSDBPUBLICPRIVILEGES`=X), will bypass Empress database privileges. Therefore, granting privileges in the database will not have any effect unless MSDBPUBLICPRIVILEGES variable is set to nothing.

#### 2.3.1.26 MSDBVERSION_FEATURES

This variable as an Empress internal variable. Do not modify.

## 2.4 Database Coordinator

To maintain database integrity, programs accessing the database are required to register themselves with a facility called the coordinator. The coordinator keeps track of the state of all clients, including the state of the on-line backup process itself.

When a client starts, it registers itself with the coordinator and remains registered until it terminates, at which time it removes itself from the coordinator. This tracking of active clients ensures that, for example, clients can be made aware that an on-line backup is occurring.

Communication of Empress processes with the coordinator is completely transparent to the user. When a database is first created the coordinator will be automatically created within the database directory. Coordinator information is maintained within a file called `cdinator` within the database directory. The coordinator will use this file to transparently track all processes accessing the database.

It is possible to view the information in the `cdinator` file by using `empadm` utility. Please refer to Database Administration chapter of this manual for detail information.

## 2.5 Data Dictionary Cache File

The `dd_cache` file contains universal format cache of data dictionary. Currently this file only contains persistent stored modules information. It is extensible for supporting more information in the future.

## 2.6 Database Lock Directory

Empress allows two physical implementations of a lock manager: file lock managers, and shared memory lock managers.

File lock managers are the default physical locking implementation. In a database directory, there will exist a lock directory `_lock` which will contain a number of files with names like `xxxx.lck`. The numbers `xxxx` correspond to the table number in the table's `.rel` file name. For example, if table `t` corresponds to `0092.rel`, then its lock file will be `_lock/0092.lck`. If this file is removed, the next time that lock manager is needed to hold a lock, it will be automatically created.

You can use `empadm` utility to view the lock information on the database. Please refer to Database Administration chapter of this manual for detail information on `empadm`.

## 2.7 Persistent Stored Modules Directory

This directory contains user defined persistent stored modules library.

## 2.8 Database Transaction Log Directory

A transaction is a set of database operations that are treated as a unit. When a transaction process is started, there will be a transaction log file created automatically under the database transaction log directory `_trn`. The name of this log file will look like `xxxxxx.trn`. The numbers `xxxxxx` correspond to the process id of the transaction. Once transaction is completed this log file will be removed automatically.

The SQL `DISPLAY WORK` command, warm re-start (`empwarm`) utility, on-line backup and recovery (`empolbak` and `emprecov`) utilities access these files for transaction processes information.