Empress Software Database Administrator's Guide
Empress v8.62
May 2006

© 1983, 2006 Empress Software Inc.

All rights reserved. Reproduction of this document in whole or part, by electronic or any other means, is prohibited without written consent from Empress Software Inc.

RESTRICTED RIGHTS LEGEND

Use, duplication, or disclosure by the Government is subject to restrictions as set forth in
subparagraph (c) (1) (ii) of the Rights in Technical Data and Computer Software clause at 52.227-7013.

05/06

This manual discusses database administration using Empress. It describes the use of Empress utilities for database administrative functions, the structure of the Data Dictionary and system variables for tuning or for the system environment.

Table of Contents

CHAPTER 1: Installation Guide

1.1 Installing and Configuring Empress

1.1.1 System Requirements
1.1.2 Distribution File Ownership
1.1.3 Loading Empress Software
1.1.4 Installing Empress
1.1.5 Installation Results
1.1.6 Setting Empress Related Environment Variable (Unix)
1.1.7 Configuring Empress for General Use (Unix)
1.1.8 Accessing the Empress Manual Set

1.1.8.1 Access Manuals from CD-ROM
1.1.8.2 Access Manuals from the Network

1.1.9 Converting Databases

1.2 Update Empress System

1.2.1 Before Installation Process
1.2.2 After Installation Process

1.3 Terminal Setup for Empress 4GL
1.4 Empress on NFS Platforms

1.4.1 Installing Empress on NFS

1.4.1.1 System Requirements

1.4.2 Access to Empress Databases on NFS

1.4.2.1 Administrative Variables

1.4.3 File and Database Privileges on NFS

1.4.3.1 File Access Permissions
1.4.3.2 Database Privileges
1.4.3.3 Database Access under NFS

CHAPTER 2: The Data Dictionary

2.1 Introduction
2.2 Data Dictionary Tables

2.2.1 Data Dictionary for Tables: sys_tables
2.2.2 Data Dictionary for Attributes: sys_attrs
2.2.3 Data Dictionary for Privileges: sys_privs
2.2.4 Data Dictionary for Attribute Privileges: sys_attr_privs
2.2.5 Compiled Data Dictionary: sys_dictionary
2.2.6 Data Dictionary for Triggers: sys_triggers
2.2.7 Data Dictionary for Persistent Sotred Modules: sys_modules
2.2.8 Data Dictionary for Module Files: sys_mod_file
2.2.9 Data Dictionary for PSM Routines: sys_routines
2.2.10 Data Dictionary for UDF Parameters: sys_params
2.2.11 Data Dictionary for Master and Replicate tables: sys_rep_tables
2.2.12 Data Dictionary for Replication Master Entries: sys_rep_masters
2.2.13 Data Dictionary for Replication Replicate Entries: sys_rep_replicates
2.2.14 Data Dictionary for Roles: sys_roles

2.3 Administrative Variable File for the Data Dictionary

2.3.1 Description of Database Administrative Variables

2.3.1.1 MSCOORDDISABLE
2.3.1.2 MSDBADMINISTRATOR
2.3.1.3 MSDBAUDITTERSE
2.3.1.4 MSDBAUDITTRAIL1 and MSDBAUDITTRAIL2
2.3.1.5 MSDBBLOCKSIZE
2.3.1.6 MSDBDBAPRIVS
2.3.1.7 MSDBDFC
2.3.1.8 MSDBDICTINDEX
2.3.1.9 MSDBDICTLOCK
2.3.1.10 MSDBDICTTABLENUMBER
2.3.1.11 MSDBINDEXPRIMARY and MSDBINDEXOVERFLOW
2.3.1.12 MSDBLOCKLEVEL
2.3.1.13 MSDBLOCKSTATS
2.3.1.14 MSDBMAXPROCS
2.3.1.15 MSDBPERMS
2.3.1.16 MSDBPRIVS
2.3.1.17 MSDBRECOVERYLOG1 and MSDBRECOVERYLOG2
2.3.1.18 MSDBSERVERPORTID
2.3.1.19 MSDBTHRESCHAINLEN
2.3.1.20 MSDBVALIDATESIZE
2.3.1.21 MSDBVERSION
2.3.1.22 MSNFSSHARE
2.3.1.23 MSDBLOGFILE
2.3.1.24 MSDBLOCKGRANULARITY
2.3.1.25 MSDBPUBLICPRIVILEGES
2.3.1.26 MSDBVERSION_FEATURES

2.4 Database Coordinator
2.5 Data Dictionary Cache File
2.6 Database Lock Directory
2.7 Persistent Stored Modules Directory
2.8 Database Transaction Log Directory

CHAPTER 3: Empress System Variables

3.1 Introduction
3.2 Default Values for the System Variables
3.3 Description of Empress System Variables

3.3.1 MS4GLCHKNULVAL
3.3.2 MS4GLCOMPATCHK
3.3.3 MS4GLDEBUGSETUP
3.3.4 MS4GLFLUSHTYPEAHEAD
3.3.5 MS4GLFONTNAME
3.3.6 MS4GLNOLOADRUNMSG
3.3.7 MS4GLPRINTSCREEN
3.3.8 MS4GLPRINTSCREENFORMAT
3.3.9 MS4GLPSHEADER
3.3.10 MS4GLPSHEIGHT
3.3.11 MS4GLPSWIDTH
3.3.12 MS4GLXASYNCOFF
3.3.13 MS4GLXCOLORTOLERANCE
3.3.14 MS4GLXCURSORCOLOR
3.3.15 MS4GLXNEWCOLORMAP
3.3.16 MS4GLXPOINTERCOLOR
3.3.17 MS4GLXWINDOWTITLE
3.3.18 MSAPFMDB
3.3.19 MSBUFFERRECORDFACTOR
3.3.20 MSBULKSEGMENTSIZE
3.3.21 MSCFEXCLRETRY and MSCFEXCLSLEEP
3.3.22 MSCOORDTIMERFREQ
3.3.23 MSCRAYQDELLOGDIR
3.3.24 MSCRAYQDELRETRY and MSCRAYQDELSLEEP
3.3.25 MSCRAYQDELSIGNAL
3.3.26 MSDATELIMIT
3.3.27 MSDATEPIC
3.3.28 MSDDLTRANS
3.3.29 MSDISPLAYKATAKANA
3.3.30 MSDOLLAR
3.3.31 MSEDITOR
3.3.32 MSEXCLRETRY and MSEXCLSLEEP
3.3.33 MSF77BACKSCAN & MSF90BACKSCAN
3.3.34 MSF77BUFCOPY & MSF90BUFCOPY
3.3.35 MSF77IGNORELENGTH & MSF90IGNORELENGTH
3.3.36 MSF77TERMCHAR & MSF90TERMCHAR
3.3.37 MSFILELOCKNBUCKETS
3.3.38 MSFILELOCKNLOCKS
3.3.39 MSFILESOPEN
3.3.40 MSFORCEPLOCK
3.3.41 MSGCWDSIGCHLDRESET
3.3.42 MSGETHOSTTIME
3.3.43 MSGETPW
3.3.44 MSGUIDB
3.3.45 MSGUIDEFAULTSTYLE
3.3.46 MSGUIDEFCOLORFILE
3.3.47 MSGUIFONTSPEC
3.3.48 MSGUIRGBTEXT
3.3.49 MSGUISYSMAIN
3.3.50 MSHELPPATH
3.3.51 MSHOSTNAME
3.3.52 MSIAEXCLRETRY and MSIAEXCLSLEEP
3.3.53 MSIALOCKRETRY and MSIALOCKSLEEP
3.3.54 MSINDEXLISTCUTOFF
3.3.55 MSINDEXLISTCUTOFFMIN
3.3.56 MSINDEXRETRY and MSINDEXSLEEP
3.3.57 MSINDEXSORTCUTOFF
3.3.58 MSINETMESSAGERETRY and MSINETMESSAGETIMEOUT
3.3.59 MSINETPACKETRETRY and MSINETPACKETTIMEOUT
3.3.60 MSINETREPLYRETRY and MSINETREPLYTIMEOUT
3.3.61 MSKEEPNONPRINTCHAR
3.3.62 MSKILLLOGDIR
3.3.63 MSKILLRETRY and MSKILLSLEEP
3.3.64 MSKILLSIGNAL
3.3.65 MSLANG
3.3.66 MSLICENCE
3.3.67 MSLINECONT
3.3.68 MSLKCOMMITADDEND
3.3.69 MSLOCKPLAN
3.3.70 MSLOCKRETRY and MSLOCKSLEEP
3.3.71 MSMAXPROCS
3.3.72 MSMIGRATERETRY and MSMIGRATESLEEP
3.3.73 MSMKDBSHMEM
3.3.74 MSMWNULLOK
3.3.75 MSNLSCODESET
3.3.76 MSNLSDB
3.3.77 MSNRECORDCACHE
3.3.78 MSNULLVALUE
3.3.79 MSODBCINIFILE
3.3.80 MSOLBBACKUPDEVICE
3.3.81 MSOLBBLOCKSIZE
3.3.82 MSOLBRECOVERYLOG
3.3.83 MSPAGELENGTH
3.3.84 MSPAGER
3.3.85 MSPAGEWIDTH
3.3.86 MSPERMS
3.3.87 MSPRINTER
3.3.88 MSQLAUTOPAGE
3.3.89 MSQLCMDSAVE
3.3.90 MSQLCOUNT
3.3.91 MSQLECHO
3.3.92 MSQLGCHARWIDTH
3.3.93 MSQLGDATEWIDTH
3.3.94 MSQLGDECIMALWIDTH
3.3.95 MSQLGFLOATWIDTH
3.3.96 MSQLGINTEGERWIDTH
3.3.97 MSQLONELINE
3.3.98 MSQLPROMPT1 and MSQLPROMPT2
3.3.99 MSQLSELBOX
3.3.100 MSQLSELCOLSEP
3.3.101 MSQLSELGROUPCOLCROSS
3.3.102 MSQLSELGROUPSEP
3.3.103 MSQLSELHEAD
3.3.104 MSQLSELHEADCOLCROSS
3.3.105 MSQLSELHEADSEP
3.3.106 MSQLSELKEEPDUPLICATE
3.3.107 MSQLSELROWCOLCROSS
3.3.108 MSQLSELROWSEP
3.3.109 MSQLSELTRUNCATE
3.3.110 MSQLSELWARN
3.3.111 MSQLTRANSACTION
3.3.112 MSQLVARCHARS
3.3.113 MSQUERYPLAN
3.3.114 MSSAVECWD
3.3.115 MSSELEXTFETCH
3.3.116 MSSERVERHEARTBEAT
3.3.117 MSSERVERNETTYPE
3.3.118 MSSERVERTERSELOG
3.3.119 MSSHAREDMEMORYDIR
3.3.120 MSSHELL
3.3.121 MSSHMLOCATION
3.3.122 MSSHMPERMS
3.3.123 MSSORTBYPASS
3.3.124 MSSORTSPACE
3.3.125 MSTERM
3.3.126 MSTERMDB
3.3.127 MSTERMSYS
3.3.128 MSTHRESHOLDPFLSIZE
3.3.129 MSTMPDIR and MSTMPPFX
3.3.130 MSTRANSCOMMENT
3.3.131 MSTRANSSYNC
3.3.132 MSTRANSTABLELOCK
3.3.133 MSTRANSUFNBI
3.3.134 MSTRANSUFNGFL
3.3.135 MSTRANSWARMPROTECT
3.3.136 MSVALIDATELEVEL
3.3.137 MSVALIDATERETRY and MSVALIDATESLEEP
3.3.138 MSVALIDATESIZE
3.3.139 MSVALSEP
3.3.140 MSVERSIONMESG
3.3.141 MSWRAPMARGIN
3.3.142 MSUSERAUTHCONFIGFILE
3.3.143 MSNETSERVERCONFIGFILE
3.3.144 MSNETTYPECONFIGFILE
3.3.145 MSCONFIGFILEPATH
3.3.146 MSREPLOCKRETRY and MSREPLOCKSLEEP
3.3.147 MSREPENABLEUPDATE
3.3.148 MSREPPURGEINTERVAL
3.3.149 MSCASEINSENSITIVE
3.3.150 MSNTMPFILECACHE
3.3.151 MSQUOTEDIDENTIFIERS
3.3.152 MSSELMAXROWS
3.3.153 MSSELTIMEOUT
3.3.154 MSTMPFILECACHESIZE
3.3.155 MSPSMREPOSITORYDB

CHAPTER 4: Command Logging and Backup

4.1 Introduction
4.2 Audit Trail
4.3 Database Log File
4.3.1 Database Log Analyzer Utility
4.4 Recovery Log
4.5 Archives and Recovery

4.5.1 Database Archives
4.5.2 Database Recovery from the Archives

4.6 On-Line Backup and Recovery

4.6.1 On-Line Backup

4.6.1.1 On-Line Backup Device
4.6.1.2 On-Line Backup Blocking Factor
4.6.1.3 On-Line Backup Process Information
4.6.1.4 On-Line Backup with Continuous Recovery Logging
4.6.1.5 On-Line Backup without Continuous Recovery Logging

4.6.2 Database Recovery

CHAPTER 5: Exporting and Importing Data

5.1 Introduction
5.2 Exporting Data from a Database
5.3 Importing Data from an Empress Export File
5.4 Importing Data from an Operating System File

5.4.1 The Specification File
5.4.2 FORMAT Clause
5.4.3 NULLIF Clause
5.4.4 Examples

CHAPTER 6: Warm Restart and Transaction

6.1 Introduction
6.2 Automatic Warm Restart Utility - empwarm
6.3 Transactions Over Several Databases

6.3.1 Warm Restarting the Coordinator
6.3.2 Warm Restart at a Participant
6.3.3 Concurrent Transactions

6.4 Displaying Transaction Status
6.5 Manual Overrides
6.6 Recovering to a Save Point
6.7 Transaction Protection
6.8 Transaction Related Query Language Commands

6.8.1 DISPLAY WORK
6.8.2 START WORK
6.8.3 COMMIT WORK
6.8.4 ROLLBACK WORK

6.9 Transaction Status Messages

6.9.1 Database Status Messages
6.9.2 Overall Status Messages

6.10 Examples

6.10.1 Running a Transaction
6.10.2 Examining the Current Transaction
6.10.3 Interrupting the Transaction
6.10.4 Examining the Transaction
6.10.5 An Unavailable Participant
6.10.6 An Unavailable Coordinator
6.10.7 Warm Restart on a Participant
6.10.8 Warm Restarting a Coordinator
6.10.9 A Forced Rollback

CHAPTER 7: On-Line Help Files

CHAPTER 8: Shared Memory

8.1 Introduction

8.1.1 Lock Managers
8.1.2 Global Buffers
8.1.3 Mapped Files

8.2 General Guidelines on the Usage of Shared Memory
8.3 Reference Guide on Setting Up Shared Memory
8.4 Defining Shared Memory Partitions

8.4.1 Shared Memory Key
8.4.2 Partition for Data Dictionary Indicator
8.4.3 Partition Definitions

8.4.3.1 Parameters for Lock Manager
8.4.3.2 Parameters for Global Buffers
8.4.3.3 Parameters for Mapped Files
8.4.3.4 End Partition Definitions

8.4.4 End Shared Memory Definition Indicator

8.5 Defining Mapping Parameters to the Shared Memory

8.5.1 Define Default Mapping
8.5.2 Define Mapping of Main Data File
8.5.3 Define Mapping of the Variable-Length Data Overflow Files
8.5.4 Define Mapping of the Index Files
8.5.5 Define Mapping of the Lock Files

8.6 Managing Shared Memory - empadm

8.6.1 Disable the Database
8.6.2 Create Shared Memory
8.6.3 Mapping Database Files and Locks to Shared Memory
8.6.4 Database Locking and Shared Memory Information
8.6.5 Clearing Locks
8.6.6 Clearing Shared Memory
8.6.7 Resetting Lock and Shared Memory Statistics Information
8.6.8 Removing Shared Memory

8.7 Mapping Default Shared Memory to Databases

CHAPTER 9: Database Administration

9.1 Introduction
9.2 DBA and Single User Mode

9.2.1 DBA Mode
9.2.2 Single User Mode

9.3 Locking Information for Active Locks
9.4 Lock Manager Statistics
9.5 Remove Defunct Locks
9.6 Remove Disk Lock Files
9.7 Create Shared Memory
9.8 Remove Unprotected Shared Memory
9.9 Remove All Shared Memory
9.10 Refresh Shared Memory
9.11 Clear Shared Memory
9.12 Shared Memory Information
9.13 Shared Memory Statistics
9.14 Map Tables to Shared Memory
9.15 Coordinator Information
9.16 Clear Coordinator Information
9.17 Recompile Data Dictionary Entries
9.18 Physical Location of Databases
9.19 Process Limit
9.20 Database Administration Help Command

CHAPTER 10: Database Checking and Repairing

10.1 Introduction
10.2 Database Integrity Assurance Utility Operations

10.2.1 Server Operations

10.2.1.1 Restart A Dead Server
10.2.1.2 Remove Dangling Clients

10.2.2 Data Dictionary Operations

10.2.2.1 Re-Compilation
10.2.2.2 Partial Re-Compilation
10.2.2.3 Remove Dangling Data Dictionary Entries
10.2.2.4 Remove Dangling Rel-Files
10.2.2.5 Remove Non-Database Files in the Database Directory

10.2.3 Resolve Transactions
10.2.4 Clear Dangling Locks
10.2.5 Repair Database Files
10.2.6 Remove Dangling Temporary Files
10.2.7 Cleanup Database Coordinator File
10.2.8 Recreate Missing Shared Memory Partitions

10.3 Invoking the Database Integrity Assurance Utility
10.4 Database Integrity Assurance Utility Configuration File

10.4.1 Creating a Configuration File

10.4.1.1 Configuring for Local Databases
10.4.1.2 Configuring for Servers

10.4.2 Configuration File Layout

10.4.2.1 Servers
10.4.2.2 Operations
10.4.2.3 Host Configuration
10.4.2.4 Database Specific

10.4.3 Additional Customizations

10.5 Running Database Integrity Assurance Utility Program
10.6 Limitations

CHAPTER 11: Dirty Read and Checksum

11.1 Introduction
11.2 Dirty Read User Specifications
11.3 Checksum User Specifications
11.4 Dirty Read System Variables

11.4.1 MSVALIDATELEVEL
11.4.2 MSVALIDATERETRY
11.4.3 MSVALIDATESLEEP

11.5 Dirty Read Privileges
11.6 Error Codes and Error Messages

CHAPTER 12: Empress Locking

12.1 Introduction
12.2 Basic Aspects of Locking

12.2.1 Operational Category Locks
12.2.2 Relational Category Locks
12.2.3 Physical Category Locks
12.2.4 Lock Managers

12.3 Miscellaneous Aspects of Locking

12.3.1 Locking and Transactions
12.3.2 Single User DBA Mode
12.3.3 Locking in the 4GL and AG

12.4 Managing Locks

12.4.1 Explicit Locking
12.4.2 Implicit Locking
12.4.3 Environment Variables
12.4.4 Clearing Locks
12.4.5 Creating Shared Memory Lock Managers

12.5 Monitoring Locks

12.5.1 Internal Lock Monitors

12.5.1.1 MSLOCKPLAN
12.5.1.2 MSLOCKSTATS

12.5.2 External Lock Monitors

12.5.2.1 Locking Information
12.5.2.2 Lock Statistics

12.5.3 Relational Level Default Locks for Selected Queries

12.6 Tuning Empress Locking

12.6.1 The Implicit Locking Scheme
12.6.2 Adjusting the Locking Parameters
12.6.3 Shared Memory vs. File Lock Managers
12.6.4 Tuning Lock Managers

CHAPTER 13: Set Up In-Memory Database System

CHAPTER 14: Manual Pages