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:
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';
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. |
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. |
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. |
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. |
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. |
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. |
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. |
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.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.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.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
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. |
When this variable is set, the database access will by-pass the coordinator. This is done for performance reason and for read only database.
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.
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.
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.
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.
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.
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.
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.
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
MSDBDICTTABLENUMBER indicates the table number corresponding to the compiled dictionary table (sys_dictionary). The default is 1 which corresponds to 0001.rel.
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);
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
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.
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.
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.
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.
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.
This variable is for Empress internal use on the Cray Super Computer system only.
This variable is used to calculate the threshold chain length based on current number locks.
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.
MSDBVERSION is the version number of the product, for example, Version 8.62.
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.
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.
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
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.
This variable as an Empress internal variable. Do not modify.
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.
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.
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.
This directory contains user defined persistent stored modules library.
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.